How to insert and select global temporary table using jdbc oracle?

Question

 String query = "INSERT INTO St (id) values ('Admin'); "  + 
            "select * from Student where id in (select id from St);";

Getting error if try to execute the above query

Exception in thread "main" java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:226)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:59)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:910)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1119)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3780)
at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1343)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3822)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1165)
at com.prototype.service.DBConnection.main(DBConnection.java:167)

Caused by: Error : 933, Position : 36, Sql = INSERT INTO St (id) values ('Admin'); select * from Student where id in (select id from St);, OriginalSql = INSERT INTO St (id) values ('Admin'); select * from Student where id in (select id from St);, Error Msg = ORA-00933: SQL command not properly ended

You can't send two statements at once, you need two calls, one for the insert and one for the query. (It isn't obvious why you want/need a GTT from your example though.) — Alex Poole, Sep 18 '18 at 12:13
I havn't enable allowMultiQueries in preparedStatement, and i have removed the semicolon(;) at the end still facing sane issue. — Vivek Swansi, Sep 18 '18 at 12:19
What do you want to achieve? You have one complete correct insert statement and one complete correct select statement. One of them should be removed. — Dmitriy, Sep 18 '18 at 12:32
Note that this duplicate might be limited to mysql, I never had to really use it so only tried it for "fun". — AxelH, Sep 18 '18 at 14:16
Can we insert multiple queries into GTT without using stored procedure in oracle? — Vivek Swansi, Sep 19 '18 at 05:56

score 0 · Answer 1 · answered Sep 18 '18 at 12:35

0

you can use a insert select statement and this way avoid multi statemenst query

String query = "INSERT INTO St (id) 
            select your_col_for_id  from Student where id in (select id from St);";

and you must be sure you have corresponding number and type of column in into clause and in select clause

answered Sep 18 '18 at 12:35

ScaisEdge

131,976
10
91
107

I have achieved by using batch, I have added batch for insert query and executeBatch. – Vivek Swansi Sep 19 '18 at 10:17

How to insert and select global temporary table using jdbc oracle?

1 Answers1