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

    Unanswered: Calling Stored Procedures Through JDBC - Behavior That Defies Logic!

    OK, I have seen some weird thing before, but this tops it all. I have a very simple stored procedure that looks like this:

    SELECT *
    FROM CUSTOMER, TMP_TABLE
    WHERE CUSTOMER.CUST_ID = TMP_TABLE.CUST_ID AND
    ...
    CUSTOMER.SOME_INDICATOR <> 'Y';

    Keep in mind that the TMP_TABLE is defined as a global temporary table. I invoke this procedure in my Java code; however even though data should be returned, the result set I get back is empty. By trial and error, I discovered that it is the condition involving SOME_INDICATOR that is causing the result set to be empty. The strange thing is that all the values of SOME_INDICATOR in the CUSTOMER table are 'N'. To show this, I executed the following query which actually returns the expected results:

    SELECT *
    FROM CUSTOMER, TMP_TABLE
    WHERE CUSTOMER.CUST_ID = TMP_TABLE.CUST_ID AND
    ...
    CUSTOMER.SOME_INDICATOR = 'N';


    So if something is equal to 'N' would that something also not equal to 'Y'?! (BTW, SOME_INDICATOR is defined as CHAR(1) )

    But here is the real kicker - if I change the GTT to a regular Oracle table, the whole problem goes away!

    I also need to mention that the stored procedure from which the above queries are being invoked is being used successfully from another client application written in PowerBuilder; it is only when I try using it trough the JDBC that I have a problem. Although this would seem to indicate a problem with JDBC, I thought that, since I am using a stored procedure and not straight SQL, JDBC should not be an issue (or should it?).

    Any ideas?!

  2. #2
    Join Date
    Jan 2002
    Location
    India
    Posts
    15
    Problem is with Global temporary table, GTT data will persist for a oracle session. It is not guranted that JDBC uses same session.
    Actual problem source is not with SOME_INDICATOR field, it is with
    CUSTOMER.CUST_ID = TMP_TABLE.CUST_ID, global temporary table data which created in a session will not be visible for different session

  3. #3
    Join Date
    Jun 2003
    Posts
    3
    Originally posted by Rajeswari
    Problem is with Global temporary table, GTT data will persist for a oracle session. It is not guranted that JDBC uses same session.
    Actual problem source is not with SOME_INDICATOR field, it is with
    CUSTOMER.CUST_ID = TMP_TABLE.CUST_ID, global temporary table data which created in a session will not be visible for different session
    That's what I thought too at first, but after some debugging I realized that the GTT is actually being populated correctly. How else would the second query (the one in which CUSTOMER.SOME_INDICATOR = 'N') work on a consistent basis?!

Posting Permissions

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