Fill a GTT with external data each session I have to import an external csv file into a table in database each day. The file is encoded in 'UCS2-LE BOM'. I have found a solution using external tables and bulk insertion, but the problem is to re-use the GTT made as an external table to be filled by the new daily file, each day in a job.

Since the GTT already exists from previous session, I can't use CREATE GLOBAL TEMPORARY TABLE ... ORGANIZATION EXTERNAL .... I can however drop the table each time and re create it but I think there has to be a way I'm not aware of.

Additionally, It is much appreciated if the importing could be done entirely without the need for GTTs and loading a file straight into the destination table. I have done that before with custom tagged hierarchial XML files (Oracle is so powerful in XML files) but couldn't find many resources and references on csv files, since.

Thanks!

Edit:

GTT's can't be used as external tables

Error report -
SQL Error: ORA-30658: attempt was made to create a temporary table with EXTERNAL organization
30658.0000 -  "attempt was made to create a temporary table with EXTERNAL organization"
*Cause:    An attempt was made to create an External Organized Temporary table.
           This is not supported.
*Action:   Don't do that!


Edit2:

I found out that organization external doesn't create the table though it says on the log it does. The table is parsed each time from the blueprint defined in organization external on access.
I have to import an external csv file into a table in database each
  day.
  
  ...
  
  Additionally, It is much appreciated if the importing could be done
  entirely without the need for GTTs and loading a file straight into
  the destination table.


And that is what SQL*Loader was made for.

SQL&gt; create table employee
(
  id integer,
  name varchar2(10),
  dept varchar2(15),
  salary integer
);

$ cat employee.csv
100,Thomas,Sales,5000
200,Jason,Technology,5500
300,Mayla,Technology,7000
400,Nisha,Marketing,9500
500,Randy,Technology,6000
501,Ritu,Accounting,5400

$ cat load.ctl
load data
 infile 'employee.csv'
 append
 into table employee
 fields terminated by ","
 ( id, name, dept, salary )


Load data into empty table:

$ sqlldr user/password@database control=load.ctl

SQL&gt; select * from employee;

        ID NAME       DEPT                SALARY
---------- ---------- --------------- ----------
       100 Thomas     Sales                 5000
       200 Jason      Technology            5500
       300 Mayla      Technology            7000
       400 Nisha      Marketing             9500
       500 Randy      Technology            6000
       501 Ritu       Accounting            5400


Append data (notice the append keyword in load.ctl):

$ sqlldr user/password@database control=load.ctl

SQL&gt; select * from employee;

        ID NAME       DEPT                SALARY
---------- ---------- --------------- ----------
       100 Thomas     Sales                 5000
       200 Jason      Technology            5500
       300 Mayla      Technology            7000
       400 Nisha      Marketing             9500
       500 Randy      Technology            6000
       501 Ritu       Accounting            5400
       100 Thomas     Sales                 5000
       200 Jason      Technology            5500
       300 Mayla      Technology            7000
       400 Nisha      Marketing             9500
       500 Randy      Technology            6000
       501 Ritu       Accounting            5400