sqlldr multi table insert using external table

Needed to load a single file into two table inside the database

1,2,3,4,5,6,7,8
9,10,11,12,13,14,15,16
17,18,19,20,21,22,23,24
25,26,27,28,29,30,31,32

Some of the columns needed to be loaded into both tables (with a generate ref num between them)

Create needed directories

create directory demo as '/home/oracle';
create sequence test;

Create external table

CREATE TABLE "HARRY"
(
  "COL1" VARCHAR2(10),
  "COL2" VARCHAR2(10),
  "COL3" VARCHAR2(10),
  "COL4" VARCHAR2(10),
  "COL5" VARCHAR2(10),
  "COL6" VARCHAR2(10),
  "COL7" VARCHAR2(10),
  "COL8" VARCHAR2(10)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY DEMO
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    BADFILE 'DEMO':'a.bad'
    LOGFILE 'a.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "COL1" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "COL2" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "COL3" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "COL4" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "COL5" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "COL6" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "COL7" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "COL8" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    )
  )
  location
  (
    'a.txt'
  )
)REJECT LIMIT UNLIMITED;

Load data into the 2 tables directly from external table

insert all into testtab1 values(COL1,COL2,COL3,COL4,COL5,COL6,COL7,test.nextval)
into testtab2 values (COL5,COL6,COL7,COL8,test.nextval)
select * from harry;

0 comments:

Post a Comment