| Java and Global Temporary Tables [message #76732] |
Tue, 03 June 2003 10:18 |
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 * FROM TMP_TABLE");
while (resultSet.next ()) {
context.log(resultSet.getString(1));
}
Any ideas about how this could be happening would be greatly appreciated!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
