Use an ORACLE External table to load complex data

Use an ORACLE External table to load complex data

I am using SQL Loader to load data into tables from a flat file. Some sample data in the file might be:

I plan to use SQL Loader to load data from flat files. The sample data is as follows:

12, smith, 1234556 @ 1234567 @ 876556612,1200
14, John, 1234 @ 4567 @ 56789 @ 12345 @ 45778 @ 34566 @ 23456,1345


The field values are empno, empname, phone numbers, and salary. The table structures are

The table structure is as follows:

Create table emp (empno number (5) primary key, ename varchar2 (10), Sal number (10, 2 ))
Create table emp_contact (empno references emp, phone_no number (10 ))


I want to insert the data into the emp and emp_contact tables. I don't know how many values for phone_no the file contains (the number of values for phone_no is not fixed ). how do I insert the data into the tables?
I want to load the above data into two tables: emp and emp_contact.
The problem is: I cannot determine the number of the third column (phone number). This column is not fixed. How can I load data into a table?


This problem is tricky. It seems that we have to split the third column into multiple rows for insertion.
For 21 st century magic data loading Tool"-- SQL Loader cannot be directly implemented either !!!
Here, we recommend that you use the 21st century data loading tool-External tables. The idea is: load the flat file into an external table, write SQL statements to split the file, and insert the specified table at one time according to the specific insert rules.
Next, let's see my demo:
-- 1) load data

create or replace directory my_dir as '/home/tkyte'/CREATE TABLE et( "EMPNO" VARCHAR2(10),"ENAME" VARCHAR2(20),"TELNOS" VARCHAR2(1000),"SAL" VARCHAR2(10) )ORGANIZATION external(TYPE oracle_loaderDEFAULT DIRECTORY MY_DIRACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCIIBADFILE 'MY_DIR':'t.bad'LOGFILE 't.log_xt'READSIZE 1048576FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIMMISSING FIELD VALUES ARE NULLREJECT ROWS WITH ALL NULL FIELDS("EMPNO" CHAR(255)TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',"ENAME" CHAR(255)TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',"TELNOS" CHAR(1000)TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',"SAL" CHAR(255)TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'))location('t.dat'))/SQL> select * from et;EMPNO ENAME TELNOS SAL————— ————— ————————————————————————————————————————————— ————12 smith 1234556@@1234567@@876556612 120014 John 1234@@4567@@56789@@12345@@45778@@34566@@23456 1345

-- 2) Write a split SQL statement

SQL> select empno, ename, sal, i,substr( tnos,instr( tnos, '@@', 1, i )+2,instr( tnos, '@@', 1, i+1 )-instr( tnos, '@@', 1, i) - 2 ) tnofrom (select to_number(et.empno) empno,et.ename,to_number(et.sal) sal,column_value i,'@@'||et.telnos||'@@' tnosfrom et,table( cast( multiset(select levelfrom dualconnect by level <=(length(et.telnos)-length(replace(et.telnos,'@@','')))/2+1 )as sys.odciNumberList ) ))/EMPNO ENAME SAL I TNO————— ————— ———— —— ———————12 smith 1200 1 123455612 smith 1200 2 123456712 smith 1200 3 87655661214 John 1345 1 123414 John 1345 2 456714 John 1345 3 5678914 John 1345 4 1234514 John 1345 5 4577814 John 1345 6 3456614 John 1345 7 2345610 rows selected.

-- Note: The cast multiset syntax is used here. column_value is a column in TABLE (odciNumberList ).




-- 3) write an insert SQL statement

SQL> create table emp2 ( empno number primary key,3 ename varchar2(10),4 sal number5 );Table created.SQL> create table emp_contact2 ( empno number references emp,3 phone_no number4 );Table created.\SQL> insert allwhen (i = 1) then into emp (empno,ename,sal) values (empno,ename,sal)when (i > 0) then into emp_contact(empno,phone_no) values (empno,tno)select empno, ename, sal, i,substr( tnos,instr( tnos, '@@', 1, i )+2,instr( tnos, '@@', 1, i+1 )-instr( tnos, '@@', 1, i) - 2 ) tnofrom (select to_number(et.empno) empno,et.ename,to_number(et.sal) sal,column_value i,'@@'||et.telnos||'@@' tnosfrom et,table( cast( multiset(select levelfrom dualconnect by level <=(length(et.telnos)-length(replace(et.telnos,'@@','')))/2+1 )as sys.odciNumberList ) ))/12 rows created.SQL> select * from emp;EMPNO ENAME SAL————— —————— —————12 smith 120014 John 1345SQL> select * from emp_contact;EMPNO PHONE_NO——————— —————————12 123455612 123456712 87655661214 123414 456714 5678914 1234514 4577814 3456614 2345610 rows selected.------------------------------------Dylan Presents.