CODEWITHSUNDEEP
sqlexcelimportoracle-sqldeveloper
drunkenvash
drunkenvash

Reputation: 37

Importing 360,000 rows into a Global Temporary Table

I'm having some issues importing a list of numbers into a Global Temporary Table that I created.

The Problem: When importing data into a Global Temporary Table, nothing will show up in the desired column even though there is a success message prompt.

Previous Effort:

I'm currently using the Data Import Wizard method to do this. I tried importing from a .txt, but it gave me a null error. I tried importing from .xlsx but it gives me the Java heap space error. I tried renaming my .txt file into .tsv file and it said it worked, but no actual data was imported. The Images below shows completion but the end result count in the desired column is still 0.

insert progress
success message

What I previously tried was doing insert statements inside of the worksheet using SQL Developer however, I can only copy and paste about 20,000 rows of insert statements into the worksheet at a time. I do not want to have to copy and paste multiple times. However, doing this it will update the table and preserve the data.

The question has now been answered. See below.

Upvotes: 0

Views: 1516

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

By default a global temporary table is created with an implicit on commit delete clause:

create global temporary table tblbc (bc number);

Global temporary TABLE created.

select duration from user_tables where table_name = 'TBLBC';

DURATION      
---------------
SYS$TRANSACTION

The import is committing, as the dialogue message states. That means that the data is then deleted from the GTT, as that's what the duration is set to. That's the equivalent of doing:

insert into tblbc(bc) values (42);

1 row inserted.

commit;

Commit complete.

select * from tblbc;

no rows selected

If you redefine the GTT then you will be able to see the imported data after that commit, at least within the same session:

drop table tblbc;

Table TBLBC dropped.

create global temporary table tblbc (bc number) on commit preserve rows;

Global temporary TABLE created.

select duration from user_tables where table_name = 'TBLBC';

DURATION      
---------------
SYS$SESSION    

insert into tblbc(bc) values (42);

1 row inserted.

commit;

Commit complete.

select * from tblbc;

        BC
----------
        42

You can now compare the data in the GTT with your other tables, again just within that session. Once you end the session the GTT data will be lost.

Upvotes: 2

Related Questions