Home » RDBMS Server » Server Utilities » sqlloader continueif problem (oracle 9i)
| sqlloader continueif problem [message #404191] |
Wed, 20 May 2009 13:06  |
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 #404734 is a reply to message #404689] |
Sat, 23 May 2009 01:44  |
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 #404798 is a reply to message #404191] |
Sun, 24 May 2009 01:31 |
 |
Michel Cadot
Messages: 68636
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 |
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 |
|
Michel Cadot
Messages: 68636
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 |
|
|
|
Goto Forum:
Current Time: Tue Apr 16 10:57:32 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
