INSERT not working on GLOBAL TEMPORARY TABLE in a PL/SQL block

Question

I'm new to Oracle scripting and I'm having problems with inserting data to a global temporary table. Here's the script I created in Toad:

    SET SERVEROUTPUT ON;

    DECLARE
        tempTwwIDExist NUMBER;
        v_sql LONG; 
    BEGIN
        SELECT COUNT(*) INTO tempTwwIDExist FROM USER_TABLES WHERE table_name = UPPER('tempTwwID');
        DBMS_OUTPUT.PUT_LINE(tempTwwIDExist);

        IF (tempTwwIDExist > 0) THEN 
        BEGIN
                EXECUTE IMMEDIATE 'TRUNCATE TABLE tempTwwID';
                EXECUTE IMMEDIATE 'DROP TABLE tempTwwID';
        END;
        END IF;

        EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tempTwwID (id NUMBER NOT NULL, SITEID NUMBER) ON COMMIT DELETE ROWS';

        EXECUTE IMMEDIATE 'INSERT INTO tempTwwID (id, SITEID) VALUES (1,123)';

    END;
    /

However, when I run a SELECT statement to get all data from tempTwwID, now rows are returned despite the INSERT statement this script runs using EXECUTE IMMEDIATE (last line of code before END;).

I hope you can help me with this. Thanks in advance.

How do you run your script? Are you executing the script and querying the table in the same session? — pablomatico, Aug 24 '15 at 05:46
Hi Tarun! Yes, I do so I write a 'SELECT * FROM tempTwwID' (without quotes) below the / and it seems not getting executed. — Christian Soliven, Aug 24 '15 at 08:16
Hi Pablomatico! I run the script by hitting F5 in a toad editor. I even had a EXECUTE IMMEDIATE 'COMMIT'; just below the insert code but it's does not seems to work. — Christian Soliven, Aug 24 '15 at 08:19
Hi Jeffrey Kemp! Could you explain further? This code does not give me any error. I tried it running with the gtt existing and not existing. I always get an empty gtt. — Christian Soliven, Aug 24 '15 at 08:21
@ChristianSoliven - you shouldn't be creating any schema objects at runtime really. Some people think they need to create GTTs at runtime because it's sort of how they do things in other databases, but it isn't necessary or efficient in Oracle. However, what you have shown works if you select immediately after the anonymous block. Do you have [autocommit turned on in Toad](http://www.toadworld.com/products/toad-for-oracle/f/10/t/9053)? That would cause the rows to be deleted, from the way you've defined the GTT. — Alex Poole, Aug 24 '15 at 09:29
@AlexPoole - my toad is on auto commit off setting. You are right. I work on TSQL for a few years and temp tables should be declared on run time. You mean this should work after the insert into statement? EXECUTE IMMEDIATE 'SELECT * FROM tempTwwID'; — Christian Soliven, Aug 24 '15 at 10:10
If you create the GTT first, once, then you wouldn't need `execute immediate` for the insert or select. Either way, within the PL/SQL block you have to select *into* something. You could select the table data or row count into a local variable and use `dbms_output` to display that for debugging purposes. Something is committing (or rolling back) between your block and your query. What do you see if you use `on commit preserve rows`? — Alex Poole, Aug 24 '15 at 10:16

score 5 · Accepted Answer · edited May 23 '17 at 11:58

You have defined the global temporary table with ON COMMIT DELETE ROWS. If you explicitly commit inside your anonymous block - with or without that being through execute immediate, which is pointless - or after the block then subsequent queries in that session won't see the inserted data any more.

What might be less obvious is that Toad can be configured to auto-commit. If that is set then the changes in your block will be automatically committed as soon as it is run, which means the row inserted in the block will be deleted before you can query it. If you change the GTT to ON COMMIT PRESERVE ROWS you'll see the data when you query.

This isn't restricted to Toad; you can see the same thing in SQL Developer or SQL*Plus with set autocommit on. If you don't have a good reason to auto-commit then you can just turn that off.

As Jeffrey Kemp said you shouldn't be creating your GTT (or any making any schema modifications) at runtime; the schema should be controlled and static. Oracle's GTTs are not the same as local temporary tables you define on the fly in other databases, and should be created once. It is the data that is temporary, not the table object.

Defining things at runtime does implicit commits you may not be expecting (since DDL commits), is expensive, risks clashes between sessions, and forces you to use dynamic SQL where it shouldn't be needed; which in turn prevents that code being checked at compile time, meaning syntax errors won't be seen until runtime.

Hi @Alex Poole! Followed all your advises and I got it all working. It behaves like temp table in T-SQL but doesn't need to be declared every time you run your script. Thanks a lot. — Christian Soliven, Aug 26 '15 at 17:50

score 0 · Answer 2 · answered Mar 16 '21 at 06:34

In my case this article work for me: https://community.oracle.com/tech/developers/discussion/632411/temporary-tables-insert

I use Toad for Oracle, and for example, there're 2 sql, one insert and one select from temp table, called TempTable:

Insert into TempTable select '123' from dual;
Select * from TempTable ;

and if I use keyboard F5 seperately execute these 2 sqls, first insert show one row created, and second select show no rows.

but if I put these 2 sqls together and highlight them, use F5 execute them continuously in one time, will successfully show select 1 rows from TempTable.

I guess when highlight multiple sql together to execute, Toad will seem them as in same session.

INSERT not working on GLOBAL TEMPORARY TABLE in a PL/SQL block

2 Answers2