I have a sql function named TEST_FUNCTION which post data to a global temporary table named GLOBAL_TEMP when calling. When I call the function it returns string "SUCCESS" if data posted to GLOBAL_TEMP table. Then when I select data from GLOBAL_TEMP table, it gives no data. As I know I have to keep the same session which was used to call the function TEST_FUNCTION to access the data from GLOBAL_TEMP table since it was created with the command on commit delete rows.
My question is how do I handle this in java code?(keep the same ORACLE session in java to access data)
Asked
Active
Viewed 2,936 times
1

Roshanck
- 2,220
- 8
- 41
- 56
-
Q: Are you opening a JDBC connection (as in a standalone Java application), or accessing a datasource (as in a Tomcat servlet or JBoss app server)? If the former, all you should have to do is make your connection a class variable, don't close it, and access the same connection from all your class methods. IMHO... – paulsm4 Nov 18 '13 at 05:06
-
I am opening a JDBC connection. Calling the select query before closing the connection. But get nothing. I am sure data posted to the table – Roshanck Nov 18 '13 at 05:10
-
So you're inserting the data, then doing the select, *ON THE SAME JDBC CONNECTION*, correct? Is this a Java application, a servlet, or "something else"? – paulsm4 Nov 18 '13 at 05:15
-
yes it is a java application. Doing the insertion first and then select data ON THE SAME JDBC CONNECTION – Roshanck Nov 18 '13 at 05:31
-
1just a thought - maybe it's doing a COMMIT? is the JDBC connection doing an autocommit? – Jeffrey Kemp Nov 18 '13 at 05:47
1 Answers
0
ON COMMIT DELETE ROWS means just that. The data is only visible within the same transaction and whenever data is COMMITed the table is truncated.
To read the data you need to make sure that there are no intervening COMMITs and that you use the same session.
Check your code and also check the JDBC settings so that it doesn't autocommit after each statement.
Also keep in mind that any DDL statement will force a commit.

Klas Lindbäck
- 33,105
- 5
- 57
- 82