You Asked
Hi,
I am having issue loading a simple file with Header and Trailer Record.
I am able to skip the header record - but not able to use the WHEN logic to skip the Trailer Record - it gives error as REJECTED record and comes out in the bad file.
The file is like this :
"H",20120112
"100370","ASH","SYS","O",,19.99," 7400224"
"100374","ASH","SYS","O",,103.07," 7400223"
"100371","ASH","SYS","O",,47.33," 7400222"
"100375","ASH","SYS","O",,67.74," 7400221"
"100372","ASH","SYS","O",,77.07," 7400220"
"100377","ASH","SYS","O",,474.61," 7400219"
"100376","ASH","SYS","O",,465.33," 7400218"
"100373","ASH","SYS","O",,333.21," 7400217"
"T",8,1588.35,802988
The control file is :
OPTIONS (ERRORS = 999999, BINDSIZE = 1000000, SKIP = 1)
LOAD DATA
INFILE 'Ash.CSV'
BADFILE 'Ash.BAD'
DISCARDFILE 'Ash.DSC'
DISCARDMAX 999
TRUNCATE
INTO TABLE ash_test
WHEN (01) <> 'T'
FIELDS TERMINATED BY ',' Optionally enclosed by '"'
TRAILING NULLCOLS
(
check_number "TRIM(:check_number)",
payer_id "TRIM(:payer_id)",
payment_type "TRIM(:payment_type)",
check_status "TRIM(:check_status)",
posting_date "to_date(:posting_date,'YYYYMMDD')",
received_amount "TRIM(:received_amount)",
Notes "TRIM(:Notes)",
status "TRIM(:status)",
result "TRIM(:result)",
date_timestamp "SYSDATE"
)
-- Create table
create table ASH_TEST
(
check_number VARCHAR2(16),
payer_id VARCHAR2(16),
payment_type VARCHAR2(16),
check_status CHAR(1),
posting_date DATE,
received_amount NUMBER(9,2),
notes VARCHAR2(16),
status CHAR(1),
result VARCHAR2(200),
date_timestamp TIMESTAMP(6) default SYSTIMESTAMP
);
I have the 'T' in the beginning to differtiate the record - but it is giving REJECTED RECORD ERROR and saying REJECTED RECORD 9 - "value too large for CHECK_STATUS" .
So it is still trying to insert the 9th record and failing on the 4th column.
My understanding is that it should just DISCARD that record based on the WHERE condition.
Let me know if I can do anything here.
I tried to update the file and put 'D' for details in front of the other records and it worked. BUT that is something the client might not give us.
I tried to do the LOAD = 8 with SKIP = 1 and that worked. BUT that too I have to know ahead of time HOW MANY records i the file and then change this everytime.
Any pointers for this would be appreciated.
Thanks
Ashwin
and Tom said...
use an external table - sqlldr is so 1990, so last century.
Using an external table, you can just say "where check_number not in ('H', 'T' )"
ops$tkyte%ORA11GR2> !cat test.dat
"H",20120112
"100370","ASH","SYS","O",,19.99," 7400224"
"100374","ASH","SYS","O",,103.07," 7400223"
"100371","ASH","SYS","O",,47.33," 7400222"
"100375","ASH","SYS","O",,67.74," 7400221"
"100372","ASH","SYS","O",,77.07," 7400220"
"100377","ASH","SYS","O",,474.61," 7400219"
"100376","ASH","SYS","O",,465.33," 7400218"
"100373","ASH","SYS","O",,333.21," 7400217"
"T",8,1588.35,802988
ops$tkyte%ORA11GR2> !cat test.ctl
LOAD DATA
INFILE 'test.dat'
INTO TABLE T
FIELDS TERMINATED BY ',' Optionally enclosed by '"'
TRAILING NULLCOLS
(
check_number ,
payer_id ,
payment_type ,
check_status ,
posting_date ,
received_amount ,
Notes ,
status ,
result
)
ops$tkyte%ORA11GR2> !sqlldr / test external_table=generate_only
SQL*Loader: Release 11.2.0.3.0 - Production on Wed Aug 1 08:47:57 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace directory my_dir as '/home/tkyte';
Directory created.
<b>t.sql was generated from test.log which was created by sqlldr, I edited it a bit for datatypes and lengths</b>
ops$tkyte%ORA11GR2> @t.sql
ops$tkyte%ORA11GR2> CREATE TABLE et
2 (
3 "CHECK_NUMBER" varchar2(10),
4 "PAYER_ID" VARCHAR2(10),
5 "PAYMENT_TYPE" VARCHAR2(10),
6 "CHECK_STATUS" VARCHAR2(10),
7 "POSTING_DATE" varchar2(10),
8 "RECEIVED_AMOUNT" varchar2(10),
9 "NOTES" VARCHAR2(10),
10 "STATUS" VARCHAR2(10),
11 "RESULT" VARCHAR2(10)
12 )
13 ORGANIZATION external
14 (
15 TYPE oracle_loader
16 DEFAULT DIRECTORY MY_DIR
17 ACCESS PARAMETERS
18 (
19 RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
20 BADFILE 'MY_DIR':'test.bad'
21 LOGFILE 'test.log_xt'
22 READSIZE 1048576
23 FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
24 MISSING FIELD VALUES ARE NULL
25 REJECT ROWS WITH ALL NULL FIELDS
26 (
27 "CHECK_NUMBER" CHAR(255)
28 TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
29 "PAYER_ID" CHAR(255)
30 TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
31 "PAYMENT_TYPE" CHAR(255)
32 TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
33 "CHECK_STATUS" CHAR(255)
34 TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
35 "POSTING_DATE" CHAR(255)
36 TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
37 "RECEIVED_AMOUNT" CHAR(255)
38 TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
39 "NOTES" CHAR(255)
40 TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
41 "STATUS" CHAR(255)
42 TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
43 "RESULT" CHAR(255)
44 TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
45 )
46 )
47 location
48 (
49 'test.dat'
50 )
51 )REJECT LIMIT UNLIMITED
52 /
Table created.
ops$tkyte%ORA11GR2> select * from et;
CHECK_NUMB PAYER_ID PAYMENT_TY CHECK_STAT POSTING_DA RECEIVED_A NOTES STATUS RESULT
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
H 20120112
100370 ASH SYS O 19.99 7400224
100374 ASH SYS O 103.07 7400223
100371 ASH SYS O 47.33 7400222
100375 ASH SYS O 67.74 7400221
100372 ASH SYS O 77.07 7400220
100377 ASH SYS O 474.61 7400219
100376 ASH SYS O 465.33 7400218
100373 ASH SYS O 333.21 7400217
T 8 1588.35 802988
10 rows selected.
ops$tkyte%ORA11GR2> select * from et where check_number not in ('H','T');
CHECK_NUMB PAYER_ID PAYMENT_TY CHECK_STAT POSTING_DA RECEIVED_A NOTES STATUS RESULT
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
100370 ASH SYS O 19.99 7400224
100374 ASH SYS O 103.07 7400223
100371 ASH SYS O 47.33 7400222
100375 ASH SYS O 67.74 7400221
100372 ASH SYS O 77.07 7400220
100377 ASH SYS O 474.61 7400219
100376 ASH SYS O 465.33 7400218
100373 ASH SYS O 333.21 7400217
8 rows selected.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment