Use ORACLE External tables to load complex data and oracle External tables
Original article: http://www.oracle.com/technetwork/issue-archive/2013/13-jan/o13asktom-1886639.html
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_loader
Default directory MY_DIR
ACCESS PARAMETERS
(
Records delimited by newline characterset US7ASCII
BADFILE 'my _ dir': 't. bad'
LOGFILE 't. log_xt'
READSIZE 1048576
Fields terminated by "," optionally enclosed by '"'ldrtrim
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
"EMPNO" CHAR (255)
Terminated by "," optionally enclosed '"',
& Quot; ENAME & quot; CHAR (255)
Terminated by "," optionally enclosed '"',
"TELNOS" CHAR (1000)
Terminated by "," optionally enclosed '"',
"SAL" CHAR (255)
Terminated by "," optionally enclosed '"'
)
)
Location
(
'T. dat'
)
)
/
SQL> select * from et;
EMPNO ENAME TELNOS SAL
-----------------------------------------------------------
12 smith 1234556 @ 1234567 @ 876556612 1200
14 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) tno
From (
Select to_number (et. empno) empno,
Et. ename,
To_number (et. sal) sal,
Column_value I,
'@' | Et. telnos | '@' tnos
From et,
Table (cast (multiset (
Select level
From dual
Connect by level <=
(Length (et. telnos)
-Length (replace (et. telnos, '@', '')/2 + 1)
As sys. odciNumberList ))
)
/
EMPNO ENAME SAL I TNO
-----------------------
12 smith 1200 1 1234556
12 smith 1200 2 1234567
12 smith 1200 3 876556612
14. John 1345 1 1234
14. John 1345 2 4567
14. John 1345 3 56789
14 John 1345 4 12345
14. John 1345 5 45778
14. John 1345 6 34566
14 John 1345 7 23456
10 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 emp
2 (empno number primary key,
3 ename varchar2 (10 ),
4 sal number
5 );
Table created.
SQL> create table emp_contact
2 (empno number references emp,
3 phone_no number
4 );
Table created.
\
SQL> insert all
When (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) tno
From (
Select to_number (et. empno) empno,
Et. ename,
To_number (et. sal) sal,
Column_value I,
'@' | Et. telnos | '@' tnos
From et,
Table (cast (multiset (
Select level
From dual
Connect 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 1200
14 John 1345
SQL> select * from emp_contact;
EMPNO PHONE_NO
----------------
12 1234556
12 1234567
12 876556612
14 1234
14 4567
14 56789
14 12345
14 45778
14 34566
14 23456
10 rows selected.
------------------------------------
Dylan Presents.
