CODEWITHSUNDEEP
oracle-databaseglobaltoadtemporary
Christian Soliven
Christian Soliven

Reputation: 33

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

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.

Upvotes: 2

Views: 7267

Answers (2)

yu yang Jian
yu yang Jian

Reputation: 7171

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.

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191425

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.

Upvotes: 5

Related Questions