0

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.

  • why not just truncate the GTT at the start of every run? – kevinsky Oct 27 '15 at 12:42
  • @kevinsky Thanks, but GTT is truncated automatically at the end of every session. by the way in my trial and errors found that GTTs can't be used as an external table container. – Cunning Oct 27 '15 at 14:24
  • 1
    If the files are in same format, you should just create one external table (not to be confused with GTT) and then every day do 'alter table xxx location('your_file_name'); before reading from the new file. so you have one table which will point to different files to read from and thus avoid dropping and re-creating the table each day. – Raj Oct 27 '15 at 18:40
  • Thanks @Raj that was really helpful! Though the external table is used to import data in a different settlement table so I need a proxy table anyway, ALTER TABLE fixed my problem. Thanks! – Cunning Oct 28 '15 at 14:35
1

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> 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> 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> 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
  • Thanks. It is much appreciated but SQL*loader can't be used in this case due to restrictions on production. There's no access to shell and it's a part of an autonomous procedure. – Cunning Oct 31 '15 at 11:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.