| Java and Global Temporary Tables [message #91867] |
Tue, 03 June 2003 10:08  |
Mihajlo
Messages: 9
Registered: October 2002
|
Junior Member |
|
|
I have observed a very strange behavior dealing with global temporary tables and was wondering if you could provide at least a possible explanation. I have a stored procedure which takes as one of its parameters a list of values. Instead of passing the list as an array, the original developers chose to first populate a global temporary table with the parameter values, and then, in the stored procedure, do a join with that temporary table to produce the result set.
So, in my Java code, I first insert parameter values into the global temporary table and then call the stored procedure. However even though data should be returned, the result set I get back is empty. First I made sure that the global temporary table is being populated correctly, by invoking a simple select statement from my Java code right before calling the stored procedure. I then invoked the code from the stored procedure directly from Java as another select statement performing a join with the temporary table, but still not results. However when I changed the global temporary table to a regular Oracle table, the process worked. As I understand, data stored in a global temporary table is visible only to a particular connection - however I am using the same connection object for all the transactions!
// First query: shows that there is data in the global temporary table TMP_TABLE
Statement st = connection.createStatement();
resultSet = st.executeQuery("SELECT * FROM TMP_TABLE");
while (resultSet.next ()) {
context.log(resultSet.getString(1));
}
// Second query: performs a join with CUSTOMER table that should return results but does not
st = connection.createStatement();
resultSet = st.executeQuery("SELECT * " +
"FROM CUSTOMER, TMP_TABLE " +
"WHERE CUSTOMER.CUST_ID = TMP_TABLE.CUST_ID");
while (resultSet.next ()) {
context.log(resultSet.getString(1));
}
// Third query: a modified version of the second query that still does not return results
st = connection.createStatement();
resultSet = st.executeQuery("SELECT * " +
"FROM CUSTOMER " +
"WHERE CUST_ID IN (SELECT CUST_ID FROM TMP_TABLE)");
while (resultSet.next ()) {
context.log(resultSet.getString(1));
}
// Forth query: same as the first one, demonstrating that there is still data in the global temporary table TMP_TABLE
st = connection.createStatement();
resultSet = st.executeQuery("SELECT CRITERIA_CATEGORY_IND, BANK_NBR , MARKET_NBR " +
"FROM TMP_MGMT_RPT_CRITERIA");
context.log("[[CLASS DB]] Table tmp_mgmt_rpt_criteria(second time):");
context.log("CRITERIA_CATEGORY_IND BANK_NBR MARKET_NBR");
while (resultSet.next ()) {
context.log(resultSet.getString(1));
}
Any ideas about how this could be happening would be greatly appreciated!
|
|
|
|
| Re: Java and Global Temporary Tables [message #92653 is a reply to message #91867] |
Mon, 08 November 2004 01:00  |
Ajit Surendran
Messages: 1
Registered: November 2004
|
Junior Member |
|
|
Hi,
Hope by now you must have got the solution. But incase you happen to slog, here is the fix. Just disable autocommit before executing the stored procedure and the following queries. Insert the following code.
if (con.getAutoCommit())
con.setAutoCommit(false);
Hope this helps
Ajit
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
