You Asked
I want to input data from a file using sql loader.I have read through the available samples on the site but my file seems quite a challenging one
The data is in this fomart order
A,HJ00034,Thu Dec 7 17:39:18 2000,,,,,,,,,
B,john,smith,admin@mywebsite.com,1 beacon House,Staltan Street,London,(null),~GB,SE5 0XS,,
P,Credit,CyberCash,Visa,4.11E+15,Jan-00,,,,,,
X,message,Hello Hello, this is a message for you,mailchoice,Yes,,,,,,
S,Express delivery shipping,My other name,My second name,123 House Number,IN a City,(null),~DZ,SE4 9TY,,,
I,J5409,1,£8.79,£8.79,,,,,,,
I,J5400,1,£33.89,£33.89,,,,,,,
T,£42.68,,,,,£42.68,,,,,
A,B,P,X,S,I,I,T are column identifiers.and are not required in the destined tables.
The A record contains the invoice number and the date of the order.
The B record contains customer name and bill to address information.
The P record contains payment information. There are actually two types of P records
depending on whether the customer used a check or credit card as a payment method
The X record contains any comments.
The S record contains customer name and ship to address information.
The I record contains information about the product(s) being ordered. There will be
as many I records as there are items ordered for each order.
The T record contains information about the orders total monetary value.
The begining of every 'A' will indicate the begining of the transaction and likewise 'T' will indicate the end of this same unique transaction. I will need to
put the results in tables as follows
A - customers Table(HJ00034 -will act as primary key) and order Table (HJ00034 will be foreign key in table)
B - customer Table
P - Payments table
x - Payments table
S - Shipping Table
I - Invoice Table
T - Invoice Table
I have tried using positions but this wouldnt workable as I will need to skip and go to the next row. A Pointer in the right direction will be appreciated
James
and Tom said...
You won't be able to do that as a transaction in sqlldr. SQLLdr can load this file but the A...T records will NOT be considered "together". It might insert an A record, commit and then insert the B, P, fail on the X, succeed on the S, I, I and T records and then commit -- placing the X record into a BAD file.
If transactional integrity is meaningful to you across these records, you'll NOT be using SQLLDR.
Suggest you create a table like:
create table temp_load
( SEQ int primary key,
record_type char(1),
c1 varchar2(4000),
c2 varchar2(4000),
....
cN varchar2(4000)
)
organization index
/
and use a ctl file like:
LOAD DATA
INFILE *
into table temp_load
replace
fields terminated by ","
trailing NULLCOLS
( seq RECNUM,
record_type,
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,
c11,c12,c13,c14,c15,c16,c17,c18,c19,c20
)
begindata
A,HJ00034,Thu Dec 7 17:39:18 2000,,,,,,,,,
B,john,smith,admin@mywebsite.com,1 beacon House,Staltan Street,London,
....
and then post process the loaded data using a plsql loop:
begin
for x in ( select * from temp_load order by SEQ )
loop
if ( x.record_type = 'A' ) then commit; end; -- new transaction;
if ( x.record_type = 'A' )
then
insert into table_a values ( x.c1, x.c2, .... x.c? );
elsif ( x.record_type = 'B' )
then
insert into table_b values ( x.c1, x.c2, ... x.c? );
.....
end if;
end loop;
commit; -- the last transaction
end;
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment