Loading complex data with Oracle external tables

Original: 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 is:

I intend to mount the data from the flat file using SQL loader. The sample data is as follows:

12,smith,[email protected]@1234567 @@876556612,1200
14,john,[email protected]@[email protected]@56789 @@[email protected]@[email protected]@[email protected]@23456,1345


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

The table structure is as follows:

CREATE TABLE EMP (empno number (5) primary key, Ename VARCHAR2 (Ten), Sal number (10,2))
CREATE TABLE Emp_contact (Empno references emp, phone_no number (10))


I want to inserts 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 are not fixed). How does I insert the data into the tables?
I want to load the above data into 2 tables: EMP and Emp_contact
The problem is: I can't determine how many third columns (phone numbers) There are, and this column is not fixed. How will I load the data into a table?


This is a tricky question, and it looks like we have to split the third column into multiple rows.
For the " 21st century Magic Data loading tool"--sql Loader can not be directly realized!!!"
Here, I recommend using the 21st Century Data loading tool-external table resolution. The idea is to load a flat file into an external table, then split it by writing SQL, and then insert the specified table one at a time by a specific insert rule.
Below, see my Demo:
--1) for data loading
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 is NULL
REJECT 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
————— ————— ————————————————————————————————————————————— ————
Smith [email protected]@[email protected]@876556612 1200
John [email protected]@[email protected]@[email protected]@[email protected]@[email protected]@[email protected] @23456 1345


--2) write split sql
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
————— ————— ———— —— ———————
Smith 1200 1 1234556
Smith 1200 2 1234567
Smith 1200 3 876556612
John 1345 1 1234
John 1345 2 4567
John 1345 3 56789
John 1345 4 12345
John 1345 5 45778
John 1345 6 34566
John 1345 7 23456


Rows selected.


-Note: The cast multiset syntax is used here, and Column_value is a column in table (odcinumberlist)




--3) Writing Insert SQL
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))
)
/
Rows created.


Sql> select * from EMP;


EMPNO ename SAL
————— —————— —————
1200 Smith
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

Rows selected.

------------------------------------

Dylan presents.

Loading complex data with Oracle external tables