Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Posts
    3

    Unanswered: Java and Global Temporary Tables

    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 no 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!

  2. #2
    Join Date
    Jan 2002
    Location
    India
    Posts
    15
    Connection and temporary data does not work well. Temporary table data will be available for an oracle session only. It is not sure JDBC driver uses same session.

    A better solution is to create a cached nologging table. By making the table no logging and cached, you will accomplish much of what you wanted with global tempoary tables.
    However, you may need to do periodic clean up.

  3. #3
    Join Date
    Jun 2003
    Location
    Czechia
    Posts
    1
    Originally posted by Rajeswari
    Connection and temporary data does not work well. Temporary table data will be available for an oracle session only. It is not sure JDBC driver uses same session.
    I've never seen that.
    I guess there are two things working together to make this trouble:
    1. the temporary table is created with "ON COMMIT DELETE ROWS" clause
    2. the connection object uses autocommit

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •