Needed to load a single file into two table inside the database
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;
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;
Comments
Post a Comment