Home » RDBMS Server » Server Utilities » SQL Loader discard non-numeric chars (11.1.0.7.0)
| SQL Loader discard non-numeric chars [message #525273] |
Fri, 30 September 2011 08:18  |
 |
Kwon
Messages: 19
Registered: September 2011
|
Junior Member |
|
|
Creating a table
CREATE TABLE NEW_DATA
(INK_DATE DATE,
INV_ID NUMBER,
CUST_ID NUMBER,
AMOUNT NUMBER)
Loading data into the table with
LOAD DATA
CHARACTERSET WE8MSWIN1252
INFILE 'input/NEW_DATA.dat' "str '\r\n'"
BADFILE 'log/NEW_DATA.bad'
DISCARDFILE 'log/NEW_DATA.dsc'
TRUNCATE INTO TABLE NEW_DATA
FIELDS TERMINATED BY '|'
(INK_DATE date "YYYY-MM-DD",
INV_ID,
CUST_ID,
AMOUNT)
My problem is that there are some rows (about 1%) where the columns INV_ID, CUST_ID, AMOUNT are containing non numeric characters and they end up in the BAD-file as errors.
Is there a way to make them end up in the discardfile instead so I don't end up with errors but discards?
Or even better load then into another table looking like this
(INK_DATE DATE,
INV_ID varchar2,
CUST_ID varchar2,
AMOUNT varchar2)
Do I need to have a WHEN-clause?
|
|
|
|
| Re: SQL Loader discard non-numeric chars [message #525296 is a reply to message #525273] |
Fri, 30 September 2011 13:50  |
 |
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You can do a second load using a second control file that uses the badfile from the first load as the infile and loads into a table of discards. Please see the demonstration below.
-- new_data.dat:
20110930|1|2|3|
20110930|a|b|c|
-- test1.ctl:
LOAD DATA
CHARACTERSET WE8MSWIN1252
INFILE 'NEW_DATA.dat' "str '\r\n'"
BADFILE 'discards.dat'
DISCARDFILE 'NEW_DATA.dsc'
TRUNCATE INTO TABLE NEW_DATA
FIELDS TERMINATED BY '|'
(INK_DATE date "YYYY-MM-DD",
INV_ID,
CUST_ID,
AMOUNT)
-- first load:
SCOTT@orcl_11gR2> CREATE TABLE NEW_DATA
2 (INK_DATE DATE,
3 INV_ID NUMBER,
4 CUST_ID NUMBER,
5 AMOUNT NUMBER)
6 /
Table created.
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test1.ctl log=test1.log
SCOTT@orcl_11gR2> select * from new_data
2 /
INK_DATE INV_ID CUST_ID AMOUNT
--------- ---------- ---------- ----------
30-SEP-11 1 2 3
1 row selected.
-- test2.ctl:
LOAD DATA
CHARACTERSET WE8MSWIN1252
INFILE 'discards.dat' "str '\r\n'"
BADFILE 'NEW_DATA.bad'
DISCARDFILE 'NEW_DATA.dsc'
TRUNCATE INTO TABLE discards
FIELDS TERMINATED BY '|'
(INK_DATE date "YYYY-MM-DD",
INV_ID,
CUST_ID,
AMOUNT)
-- second load:
SCOTT@orcl_11gR2> CREATE TABLE discards
2 (INK_DATE DATE,
3 INV_ID varchar2(10),
4 CUST_ID varchar2(10),
5 AMOUNT varchar2(10))
6 /
Table created.
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test2.ctl log=test2.log
SCOTT@orcl_11gR2> select * from discards
2 /
INK_DATE INV_ID CUST_ID AMOUNT
--------- ---------- ---------- ----------
30-SEP-11 a b c
1 row selected.
|
|
|
|
|
|
|
|
|
|
| Re: SQL Loader discard non-numeric chars [message #525558 is a reply to message #525444] |
Mon, 03 October 2011 13:43 |
|
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following demonstrates accessing the text data via an external table, inserting the data from the external table into the new data table, logging the errors into a discards table.
-- new_data.dat:
20110930|1|2|3|
20110930|a|b|c|
-- new_data and discards tables:
SCOTT@orcl_11gR2> CREATE TABLE new_data
2 (ink_date DATE,
3 inv_id NUMBER,
4 cust_id NUMBER,
5 amount NUMBER)
6 /
Table created.
SCOTT@orcl_11gR2> CREATE TABLE discards
2 (ora_err_number$ NUMBER,
3 ora_err_mesg$ VARCHAR2(2000),
4 ora_err_rowid$ ROWID,
5 ora_err_optyp$ VARCHAR2(2),
6 ora_err_tag$ VARCHAR2(2000),
7 ink_date DATE,
8 inv_id VARCHAR2(10),
9 cust_id VARCHAR2(10),
10 amount VARCHAR2(10))
11 /
Table created.
-- external table, insert into new_data logging errors into discards:
SCOTT@orcl_11gR2> CREATE OR REPLACE DIRECTORY my_dir AS 'c:\my_oracle_files'
2 /
Directory created.
SCOTT@orcl_11gR2> CREATE TABLE external_table
2 (ink_date DATE,
3 inv_id VARCHAR2(10),
4 cust_id VARCHAR2(10),
5 amount VARCHAR2(10))
6 ORGANIZATION external
7 (
8 TYPE oracle_loader
9 DEFAULT DIRECTORY my_dir
10 ACCESS PARAMETERS
11 (
12 RECORDS DELIMITED BY NEWLINE
13 CHARACTERSET WE8MSWIN1252
14 LOGFILE 'test.log'
15 FIELDS TERMINATED BY "|" LDRTRIM
16 REJECT ROWS WITH ALL NULL FIELDS
17 (
18 "INK_DATE" CHAR(8) DATE_FORMAT DATE MASK "YYYYMMDD",
19 "INV_ID",
20 "CUST_ID",
21 "AMOUNT"
22 )
23 )
24 location ('new_data.dat')
25 )REJECT LIMIT UNLIMITED
26 /
Table created.
SCOTT@orcl_11gR2> INSERT INTO new_data SELECT * FROM external_table
2 LOG ERRORS INTO discards REJECT LIMIT UNLIMITED
3 /
1 row created.
-- results:
SCOTT@orcl_11gR2> SELECT * FROM new_data
2 /
INK_DATE INV_ID CUST_ID AMOUNT
--------- ---------- ---------- ----------
30-SEP-11 1 2 3
1 row selected.
SCOTT@orcl_11gR2> SELECT ink_date, inv_id, cust_id, amount FROM discards
2 /
INK_DATE INV_ID CUST_ID AMOUNT
--------- ---------- ---------- ----------
30-SEP-11 a b c
1 row selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
| Re: SQL Loader discard non-numeric chars [message #525711 is a reply to message #525600] |
Tue, 04 October 2011 13:16 |
|
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It works for me, as demonstrated below, so either your are doing something different or the result you are getting is due to something else or there is a difference between settings or versions. You need to post a copy and paste as I have done below, but showing the different results that you are getting. In the run below, I changed the value of amount from 3 to 3.5 and it loaded as such. It may be that the manner in which you are displaying it is obscuring the decimal or some such thing. I also changed inv_id and cust_id to integer.
20110930|1|2|3.5|
20110930|a|b|c|
SCOTT@orcl_11gR2> -- tables:
SCOTT@orcl_11gR2> CREATE TABLE new_data
2 (ink_date DATE,
3 inv_id INTEGER,
4 cust_id INTEGER,
5 amount NUMBER)
6 /
Table created.
SCOTT@orcl_11gR2> CREATE TABLE discards
2 (ora_err_number$ NUMBER,
3 ora_err_mesg$ VARCHAR2(2000),
4 ora_err_rowid$ ROWID,
5 ora_err_optyp$ VARCHAR2(2),
6 ora_err_tag$ VARCHAR2(2000),
7 ink_date DATE,
8 inv_id VARCHAR2(10),
9 cust_id VARCHAR2(10),
10 amount VARCHAR2(10))
11 /
Table created.
SCOTT@orcl_11gR2> -- load:
SCOTT@orcl_11gR2> CREATE OR REPLACE DIRECTORY my_dir AS 'c:\my_oracle_files'
2 /
Directory created.
SCOTT@orcl_11gR2> CREATE TABLE external_table
2 (ink_date DATE,
3 inv_id VARCHAR2(10),
4 cust_id VARCHAR2(10),
5 amount VARCHAR2(10))
6 ORGANIZATION external
7 (
8 TYPE oracle_loader
9 DEFAULT DIRECTORY my_dir
10 ACCESS PARAMETERS
11 (
12 RECORDS DELIMITED BY NEWLINE
13 CHARACTERSET WE8MSWIN1252
14 LOGFILE 'test.log'
15 FIELDS TERMINATED BY "|" LDRTRIM
16 REJECT ROWS WITH ALL NULL FIELDS
17 (
18 "INK_DATE" CHAR(8) DATE_FORMAT DATE MASK "YYYYMMDD",
19 "INV_ID",
20 "CUST_ID",
21 "AMOUNT"
22 )
23 )
24 location ('new_data.dat')
25 )REJECT LIMIT UNLIMITED
26 /
Table created.
SCOTT@orcl_11gR2> INSERT INTO new_data SELECT * FROM external_table
2 LOG ERRORS INTO discards REJECT LIMIT UNLIMITED
3 /
1 row created.
SCOTT@orcl_11gR2> -- results:
SCOTT@orcl_11gR2> SELECT * FROM new_data
2 /
INK_DATE INV_ID CUST_ID AMOUNT
--------- ---------- ---------- ----------
30-SEP-11 1 2 3.5
1 row selected.
SCOTT@orcl_11gR2> SELECT ink_date, inv_id, cust_id, amount FROM discards
2 /
INK_DATE INV_ID CUST_ID AMOUNT
--------- ---------- ---------- ----------
30-SEP-11 a b c
1 row selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Jan 12 18:01:11 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
