oracle global temporary tables

Question

I created the global temp table. when I execute the code as an individual scripts it works fine. but when I execute it as a single script in TOAD then no record was created. there was just an empty global temp table.

eg.

CREATE GLOBAL TEMPORARY TABLE TEMP_TRAN
(
COL1 NUMBER(9),
COL2 VARCHAR2(30),
COL3 DATE
) ON COMMIT PRESERVE ROWS
/

INSERT INTO TEMP_TRAN VALUES(1,'D',sysdate);
/
INSERT INTO TEMP_TRAN VALUES(2,'I',sysdate);
/
INSERT INTO TEMP_TRAN VALUES(3,'s',sysdate);
/
COMMIT;

When I run the above code one statement at a time it works fine. But when I execute it as a script it runs fine but there was no records in temp table.

can anyone help me on this please?

Answer 1

Hi mrp,

since you explicitly stated ON COMMIT PRESERVE ROWS, the only reason why the rows would seem to "disappear" is that TOAD is using more than one session (i.e. you're executing the script with Session A and querying the table with Session B -- which would see an empty table).

I don't use Toad much, but I know you can open several independent sessions with this tool.

What happens if you put the query SELECT * FROM TEMP_TRAN; at the end of your script immediately after the commit ?

Answer 2

Sometimes, you will need to refresh the data display within Toad so that it shows the changes within the table under the tabpage Data.