Thanks for the question, Bala.

Asked: August 16, 2012 - 11:30 am UTC

Last updated: January 30, 2013 - 12:34 pm UTC

Version: 11.1

Viewed 1000+ times

You Asked

Hi,
I am using sql loader to load the data into tables from flat file.
sample data in file

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

File values are emmpno,empname,phone numbers and salary repectivly

I want to insert the data into emp & Emp_contact tables
the tables structures are

emp
-------
empno number(5) primary key
ename varchar2(10)
Sal number(10,2)

Emp_contact
---------------
empno number(5) --- this field refers the emp(empno)
phone_no number(10)

now i want to insert the data into emp and emp_contact tables
i don't know how many values for the phone_no the file contains(i.e the number of values for the phone_no is not fixed)

pls help me how to insert the the data into the tables

Thanks & Regards,
Bala Sake

and Tom said...

with an external table, the 21st century way to load data - we can do this.

with sqlldr, the 20th century legacy data loading tool for old people - you cannot really - you could fake it out with a trigger - but that is beyond a bad idea.

check it out:

ops$tkyte%ORA11GR2> create or replace directory my_dir as '/home/tkyte'
  2  /

Directory created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> CREATE TABLE et
  2  (
  3    "EMPNO" VARCHAR2(10),
  4    "ENAME" VARCHAR2(20),
  5    "TELNOS" VARCHAR2(50),
  6    "SAL" VARCHAR2(10)
  7  )
  8  ORGANIZATION external
  9  (
 10    TYPE oracle_loader
 11    DEFAULT DIRECTORY MY_DIR
 12    ACCESS PARAMETERS
 13    (
 14      RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
 15      BADFILE 'MY_DIR':'t.bad'
 16      LOGFILE 't.log_xt'
 17      READSIZE 1048576
 18      FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
 19      MISSING FIELD VALUES ARE NULL
 20      REJECT ROWS WITH ALL NULL FIELDS
 21      (
 22        "EMPNO" CHAR(255)
 23          TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 24        "ENAME" CHAR(255)
 25          TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 26        "TELNOS" CHAR(1000)
 27          TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 28        "SAL" CHAR(255)
 29          TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
 30      )
 31    )
 32    location
 33    (
 34      't.dat'
 35    )
 36  )
 37  /

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select * from et;

EMPNO      ENAME                TELNOS                                             SAL
---------- -------------------- -------------------------------------------------- ----------
12         smith                1234556@@1234567@@876556612                        1200
14         John                 1234@@4567@@56789@@12345@@45778@@34566@@23456      1345

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select empno, ename, sal, i,
  2         substr( tnos, instr( tnos, '@@', 1, i )+2, instr( tnos, '@@', 1, i+1 )-instr( tnos, '@@', 1, i) - 2 ) tno
  3    from (
  4  select to_number(et.empno) empno,
  5         et.ename,
  6         to_number(et.sal) sal,
  7         column_value i,
  8         '@@'||et.telnos||'@@' tnos
  9    from et,
 10         table( cast( multiset( select level
 11                           from dual
 12                        connect by level <= (length(et.telnos)-length(replace(et.telnos,'@@','')))/2+1 )
 13               as sys.odciNumberList ) )
 14         )
 15  /

     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.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table emp ( empno number primary key, ename varchar2(10), sal number );

Table created.

ops$tkyte%ORA11GR2> create table emp_contact( empno number references emp, phone_no number );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert all
  2  when (i = 1) then into emp (empno,ename,sal) values (empno,ename,sal)
  3  when (i > 0) then into emp_contact(empno,phone_no) values (empno,tno)
  4  select empno, ename, sal, i,
  5         substr( tnos, instr( tnos, '@@', 1, i )+2, instr( tnos, '@@', 1, i+1 )-instr( tnos, '@@', 1, i) - 2 ) tno
  6    from (
  7  select to_number(et.empno) empno,
  8         et.ename,
  9         to_number(et.sal) sal,
 10         column_value i,
 11         '@@'||et.telnos||'@@' tnos
 12    from et,
 13         table( cast( multiset( select level
 14                           from dual
 15                        connect by level <= (length(et.telnos)-length(replace(et.telnos,'@@','')))/2+1 )
 16               as sys.odciNumberList ) )
 17         )
 18  /

12 rows created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select * from emp;

     EMPNO ENAME             SAL
---------- ---------- ----------
        12 smith            1200
        14 John             1345

ops$tkyte%ORA11GR2> 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.

Rating

(7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

bala sake, August 20, 2012 - 5:19 am UTC

Thanks lot for ur response.
But we are not using external tables. We have to do this using sqlloader.
Please help me how to achive this using sql loader

can we call user defined function or procedure in control file?

Thanks & Regards,
Bala Sake

August 28, 2012 - 12:47 pm UTC

you do not have to do this with sqlldr.

you do not *have* to.

what is true, the only true thing you can say here is:

we have chosen not to use the tool that would allow us to do this.

sqlldr isn't going to cut it, sqlldr was a 20th century tool, it was very good in its day, but its day is long past. We are in the 21st century.

calling a function isn't going to do anything for you. You need to take a single row and turn it into MANY rows. A scalar function isn't going to do that.

bala sake, August 20, 2012 - 6:34 am UTC

I have tested. we can call user defined functions in control file like below

no1 "aaa(:no1)"

but needs to know how to achive the OP's problem with sql loader

Thanks & Regards
Bala Sake

August 28, 2012 - 12:49 pm UTC

you won't.

as I said, you could use a trigger - load into a view with an instead of trigger, trigger has access to the data values, trigger parses the input data and inserts it into the real table.

but I will not recommend that, you are free to write the code if you like - the approach is rather straightforward (I already mentioned it above)

INSERT ALL and ORA-02291: ... parent key not found

Stew Ashton, January 21, 2013 - 7:26 am UTC

Hi Tom,

You recently wrote about this subject in Oracle Magazine. When I try to scale up your example, I get
ORA-2291: ... parent key not found.

To insert more than a few hundred lines, I have to DEFER the foreign key constraint. Has this behavior changed with 11.2.0.3 (or later?)

Here's a test case using a real table instead of an external table, just to go faster:

select * from v$version;

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

define numlines = 85

drop table et;
 CREATE TABLE et (
  EMPNO number,
  ENAME VARCHAR2(20),
  TELNOS VARCHAR2(50),
  SAL number
);
insert all
into et values(rn,'smith','1234556@@1234567@@876556612',1200)
select level rn from dual connect by level <= &numlines;

drop table emp_contact;
drop table emp;
create table emp (
  empno number primary key,
  ename varchar2(10),
  sal number
);

create table emp_contact(
  empno number references emp,
  phone_no number
);

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 et.empno, et.ename, et.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 ) )
);

340 rows inserted.

Now run exactly the same code with NUMLINES = 86

Error report:
SQL Error: ORA-02291: integrity constraint (XXX.YYYY) violated - parent key not found
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
*Cause:    A foreign key value has no matching primary key value.
*Action:   Delete the foreign key or add a matching primary key.

January 22, 2013 - 1:46 am UTC

I will file a bug on this...

Statement level constraint consistency with MTI

Toon Koppelaars, January 22, 2013 - 2:43 am UTC

Could this have anything to do with the issue I describe at http://rulegen.blogspot.co.uk/2012/01/statement-level-constraint-validation.html

I show there that for MERGE and MTI, foreign keys aren't allowed to get violated during a single statement's execution, even if the violation is 'fixed' by the end of the statement's execution.

January 22, 2013 - 2:52 am UTC

I think so, yes. I'm traveling this week - I'll be filing a bug next week when I get back home and can sit down and put together something really small and convincing (I'll probably borrow your very simple P and C multi-table insert)

Clarification

Doug, January 22, 2013 - 5:31 pm UTC

>sqlldr, the 20th century legacy data loading tool for old >people 

Tom, 
Are you saying that external tables are preferable for loading data to sqlldr in this example or all scenarios?
Regards
Doug

January 23, 2013 - 1:06 am UTC

pretty much.

the only time you might consider sqlldr is when you are forced by someone else against your will to load the data over the network - rather than making the file to be loaded available over the network via NFS or some similar technology.

external tables require the file to be available to the database server - as long as as that can be achieved - external tables are the way to go!

not necessarily

Sokrates, January 23, 2013 - 5:58 am UTC

...external tables require the file to be available to the database server...

not necessarily

if not possible, one could also consider the following workaround:
- let the file be available to any server
- let an XE-instance run on this server ( probably just for this purpose )
- create an external table on this XE mounting the file
- use a database link from the database server to this XE to load the data

I would prefer this way over using sqlldr

January 30, 2013 - 12:34 pm UTC

ummm

the file is on a database server then isn't it. oracle XE is a database server.

the file MUST be on a machine that the database server accessing that file is on.

can you use a database link? sure, but that would be like using an atom bomb to crack a walnut and doesn't remove the necessary fact that "the file is going to be on a machine where the database server is" - you have just introduced yet another database server into the equation.

I would myself prefer sqlldr over this personally.

+ regexp

vktl, January 28, 2013 - 3:12 am UTC

Hello Tom,

This solution was a real hit. I could use it "in battle" the very next day after I have read it in the Magazine. Thanks!

Speaking of 21th century, there are some minor improvements:

select empno, ename, sal, i,
       Regexp_SubStr(tnos, '[^@][0-9]*[^@]', 1, i) tno
  from (
select to_number(et.empno) empno,
       et.ename,
       to_number(et.sal) sal,
       column_value i,
       et.telnos
  from et,
       table( cast( multiset( select level
                         from dual
                      connect by level <= Regexp_Count(et.telnos, '@@')+1 )
             as sys.odciNumberList ) )
       )
/

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

INSERT ALL and ORA-02291: ... parent key not found

Statement level constraint consistency with MTI

Clarification

not necessarily

+ regexp

More to Explore

PL/SQL demos

PL/SQL docs

Loading data into multiple tables with multiple terminators

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

INSERT ALL and ORA-02291: ... parent key not found

Statement level constraint consistency with MTI

Clarification

not necessarily

+ regexp

More to Explore

PL/SQL demos

PL/SQL docs