Home » RDBMS Server » Server Utilities » sqlloader continueif problem (oracle 9i)
sqlloader continueif problem [message #404191] Wed, 20 May 2009 13:06 Go to next message
mikeverkimpe
Messages: 30
Registered: April 2007
Location: Belgium
Member
Hy,

I have the following problem ...

My input file looks like this :

packet00001;
t_customers;1;thomson;
t_books;1;1;lord of the rings;tolkien;
t_fines;1;200;
t_fines;1;360;
packet00002;
t_customers;2;collin;
t_books;2;1;lord of the rings;tolkien;
t_books;2;2;red dragon;jones
packet000003;
t_customers;3;roberts;
t_books;3;2;red dragon;jones;
t_books;3;4;camping out;farrel;
t_books;3;5;delphi;reisdorph;
t_fines;3;500;
t_fines;3;450;
t_fines;3;600;

I need to upload it to a database and I have thousands of these packets. They all have a customers record, some have books, others have multiple books. Some customers have paid fines, others haven't.

I only want to commit each packet, and not each record. So I should use the continueif command. I do it this way

continueif this preserve (1:1) = 't'

The first name is the entity in where I should insert the data, so I should use a when clause.

But somehow sqlloader evaluates the first when clause (the one on the t_customers table) and for the second record sqlloader advances to the next when clause instead of evaluating the first when clause again.
My real problem is on the entities where I have multiple records, my clause on the record t_books gets evaluated and my record gets inserted, but the next record will be skipped as sqlloader doesn't re-evaluate all when clauses again.

Does anyone have an idea how to tell sqlloader to evaluate all when clauses for each record ???

thx.


[Updated on: Wed, 20 May 2009 13:09]

Report message to a moderator

Re: sqlloader continueif problem [message #404689 is a reply to message #404191] Fri, 22 May 2009 16:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Your problem needs clarification. Please provide create table statements and an example of what data should be in which tables after the load of the sample data and the control file that you have tried with the when clauses.
Re: sqlloader continueif problem [message #404734 is a reply to message #404689] Sat, 23 May 2009 01:44 Go to previous messageGo to next message
mikeverkimpe
Messages: 30
Registered: April 2007
Location: Belgium
Member


Create table t_customers (cus_id number, name varchar2(50))
/
Create table t_books (book_id number,cus_id number,title varchar2(150, author varchar2(100))
/
Create table t_fines (cus_id number, fin_id number, amount number, fin_date date)
/


so the first object in the record specifies the table name, the rest is in fact all items of the record of that table.

This is just a small example, I have 15 of these tables for every packet. The tables are sorted as master, detail. So inserting the first, then the second and so on will not give any constraint problems.

It is very important that we give a commit for each packet (that's why I need the continueif).

Not all record have a record in every entity, for example it is possible that a customer no books but has had fines or a customer has 2 books and 4 fines and another has 15 books and 1 fine ...

When I use the WHEN clause to do this, the WHEN clause is not validated for every record.

for example, with the continueif I have a new logical record like this

t_customers;1;thomson;t_books;1;1;lord of the rings;tolkien;t_fines;1;200;t_fines;1;360;

It would be easy just to write 3 when clauses, one for the table t_customers, one for the table t_books and one for the table t_fines. But after the the when clause on t_customers is validated (and te record inserted) the next record (the books record) is not validated on the when clause on t_customers but on the next when clause.

My controle file looks like this:

load data

append

continueif this preserve (1:1) = 'T'

into table t_customers

WHEN object = 't_customers'

fields terminated by '|' optionally enclosed by '"'

TRAILING NULLCOLS

( object FILLER TERMINATED BY '|',
cus_id number,
name
)

into table t_books

when object = 't_books'

...

hope this helps...

Mike.
Re: sqlloader continueif problem [message #404773 is a reply to message #404734] Sat, 23 May 2009 10:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Sorry, but I don't see any way to combine the CONTINUEIF with the WHEN. I think you will probably need to load your data into a staging table, then use SQL to parse it out from there and insert it into your tables.
Re: sqlloader continueif problem [message #404789 is a reply to message #404773] Sat, 23 May 2009 17:57 Go to previous messageGo to next message
mikeverkimpe
Messages: 30
Registered: April 2007
Location: Belgium
Member
I was thinking the same thing (staging)... but I hoped that it would work directly using sql loader as that would be a lot faster.

Thanks,

Mike.
Re: sqlloader continueif problem [message #404798 is a reply to message #404191] Sun, 24 May 2009 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know if this will help you but you can use an external table and one insert statement:
SQL> Create table t_customers (cus_id number, name varchar2(20))
  2  /

Table created.

SQL> Create table t_books (book_id number, cus_id number, title varchar2(20), author varchar2(20))
  2  /

Table created.

SQL> Create table t_fines (cus_id number, fin_id number, amount number, fin_date date)
  2  /

Table created.

SQL> create table t_data_ext (
  2    f1 varchar2(20),
  3    f2 varchar2(20),
  4    f3 varchar2(20),
  5    f4 varchar2(20),
  6    f5 varchar2(20)
  7    )
  8  organization external (
  9    type oracle_loader
 10    default directory WORK_DIR
 11    access parameters (
 12      records delimited by X'0A'
 13      nobadfile
 14      nologfile
 15      nodiscardfile
 16      fields terminated by ';' optionally enclosed by '"'
 17      missing field values are null
 18      (f1, f2, f3, f4, f5)
 19    )
 20    location ('books.txt')
 21  )
 22  reject limit unlimited
 23  /

Table created.

SQL> select * from t_data_ext;
F1                   F2                   F3                   F4                   F5
-------------------- -------------------- -------------------- -------------------- --------------------
packet00001
t_customers          1                    thomson
t_books              1                    1                    lord of the rings    tolkien
t_fines              1                    200
t_fines              1                    360
packet00002
t_customers          2                    collin
t_books              2                    1                    lord of the rings    tolkien
t_books              2                    2                    red dragon           jones
packet000003
t_customers          3                    roberts
t_books              3                    2                    red dragon           jones
t_books              3                    4                    camping out          farrel
t_books              3                    5                    delphi               reisdorph
t_fines              3                    500
t_fines              3                    450
t_fines              3                    600

17 rows selected.

SQL> insert first 
  2    when f1='t_customers' then into t_customers (cus_id, name) values (to_number(f2), f3)
  3    when f1='t_books'     then into t_books     (book_id, cus_id, title, author) 
  4                                      values (to_number(f3), to_number(f2), f4, f5)
  5    when f1='t_fines'     then into t_fines     (cus_id, fin_id, amount, fin_date)
  6                                      values (to_number(f2), to_number(f3), f4, f5)
  7  select * from t_data_ext
  8  where f1 not like 'packet%'
  9  /

14 rows created.

SQL> select * from t_customers;
    CUS_ID NAME
---------- --------------------
         1 thomson
         2 collin
         3 roberts

3 rows selected.

SQL> select * from t_books;
   BOOK_ID     CUS_ID TITLE                AUTHOR
---------- ---------- -------------------- --------------------
         1          1 lord of the rings    tolkien
         1          2 lord of the rings    tolkien
         2          2 red dragon           jones
         2          3 red dragon           jones
         4          3 camping out          farrel
         5          3 delphi               reisdorph

6 rows selected.

SQL> select * from t_fines;
    CUS_ID     FIN_ID     AMOUNT FIN_DATE
---------- ---------- ---------- -----------
         1        200
         1        360
         3        500
         3        450
         3        600

5 rows selected.

Regards
Michel
Re: sqlloader continueif problem [message #404818 is a reply to message #404798] Sun, 24 May 2009 08:38 Go to previous messageGo to next message
mikeverkimpe
Messages: 30
Registered: April 2007
Location: Belgium
Member

Thanks for your help, but I can't do the transaction in 1 pass. This is just a small example of a very big file that has approx 1000000 records in it for 20+ tables. So commiting in 1 pass is not an option.

I think I will go with the staging area.

thx.

Mike.
Re: sqlloader continueif problem [message #404822 is a reply to message #404818] Sun, 24 May 2009 10:40 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you need to commit each N packets you can use a PL/SQL loop with the following query:
SQL> with 
  2    data as (select f1, f2, f3, f4, f5, rownum rn from t_data_ext where f1 not like 'packet%' ),
  3    flagged as (
  4      select f1, f2, f3, f4, f5, rn, 
  5             decode(f1, 't_customers', rn) flg
  6      from data
  7    )
  8  select f1, f2, f3, f4, f5, 
  9         max(flg) over (order by rn) grp
 10  from flagged
 11  /
F1                   F2                   F3                   F4                   F5                          GRP
-------------------- -------------------- -------------------- -------------------- -------------------- ----------
t_customers          1                    thomson                                                                 1
t_books              1                    1                    lord of the rings    tolkien                       1
t_fines              1                    200                                                                     1
t_fines              1                    360                                                                     1

t_customers          2                    collin                                                                  5
t_books              2                    1                    lord of the rings    tolkien                       5
t_books              2                    2                    red dragon           jones                         5

t_customers          3                    roberts                                                                 8
t_books              3                    2                    red dragon           jones                         8
t_books              3                    4                    camping out          farrel                        8
t_books              3                    5                    delphi               reisdorph                     8
t_fines              3                    500                                                                     8
t_fines              3                    450                                                                     8
t_fines              3                    600                                                                     8

14 rows selected.

The last column has the same value for each line of a same packet.

Regards
Michel

[Updated on: Sun, 24 May 2009 10:42]

Report message to a moderator

Previous Topic: Import Views and Functions from dump file
Next Topic: Error while loading table ( Using OWB )
Goto Forum:
  


Current Time: Sat Dec 10 14:57:40 CST 2016

Total time taken to generate the page: 0.09874 seconds