Home » Developer & Programmer » JDeveloper, Java & XML » Using global temporary tables with java jdbc
Using global temporary tables with java jdbc [message #479355] Sat, 16 October 2010 05:54 Go to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
I am encountering a strange problem. I am using a global temp table created as such

DROP TABLE PF.PF_ANOMALIES_TMP CASCADE CONSTRAINTS;

CREATE GLOBAL TEMPORARY TABLE PF.PF_ANOMALIES_TMP
(
  ID                   VARCHAR2(10 BYTE)        NOT NULL,
  FVD_TYPE             VARCHAR2(10 BYTE)        NOT NULL,
  CLIENT_ID            VARCHAR2(10 BYTE),
  SEDOL_CODE           VARCHAR2(7 BYTE),
  PLAN_ID              VARCHAR2(10 BYTE),
  LTY_ID               VARCHAR2(10 BYTE),
  ORIG_PRICE_CCY       VARCHAR2(3 BYTE),
  CUSTOM_ANOMALY_DESC  VARCHAR2(300 BYTE),
  ANOMALY_ERROR_CODE   NUMBER(9)
)
ON COMMIT PRESERVE ROWS
NOCACHE;


DROP PUBLIC SYNONYM PF_ANOMALIES_TMP;

CREATE PUBLIC SYNONYM PF_ANOMALIES_TMP FOR PF.PF_ANOMALIES_TMP;


GRANT DELETE, INSERT, SELECT, UPDATE ON PF.PF_ANOMALIES_TMP TO PF_USER;


The front end app is programmed in java, and at some point this global temp table is getting populated (java calls an oracle proc that populates this tmp table, using jdbc)

Now at a later point, I am trying to query this global temp table using the same connection that was originally used to populate the table in the first place.. but i am getting no rows back

Below is the backend query im testing that is callewd by front end java to populate a dialog, but i get nothing back
            OPEN anomalies_cursor
            FOR
                SELECT      pat.client_id
                FROM        pf_anomalies_tmp pat;

        RETURN anomalies_cursor


Can anyone indicate what is possibly going wrong here?
Re: Using global temporary tables with java jdbc [message #479357 is a reply to message #479355] Sat, 16 October 2010 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 58852
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This proves you are not using the same session (or you did not populate the table or you are not refering the table or... anyway one of your asuumptions is wrong).

Regards
Michel

[Updated on: Sat, 16 October 2010 06:06]

Report message to a moderator

Re: Using global temporary tables with java jdbc [message #479360 is a reply to message #479357] Sat, 16 October 2010 06:18 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
Thanks for the reply. What do you mean by:

or you are not refering the table
Re: Using global temporary tables with java jdbc [message #479375 is a reply to message #479360] Sat, 16 October 2010 07:57 Go to previous messageGo to next message
Michel Cadot
Messages: 58852
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I mean the table you think you refer but another one.

Regards
Michel
Re: Using global temporary tables with java jdbc [message #479402 is a reply to message #479375] Sat, 16 October 2010 13:14 Go to previous message
pyscho
Messages: 134
Registered: December 2009
Senior Member
Thanks Michael. I got it working in the end - I was not using the same connection that was initially used to populate the temp table
Previous Topic: Problem with namespaces in XML
Next Topic: How to extract namespace from XML document
Goto Forum:
  


Current Time: Thu Aug 21 05:52:45 CDT 2014

Total time taken to generate the page: 0.08304 seconds