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 Go to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
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 #525301 is a reply to message #525296] Fri, 30 September 2011 14:15 Go to previous messageGo to next message
Kwon
Messages: 19
Registered: September 2011
Junior Member
Well the thing is the rows I want excluded will be spooled into the badfile but the outcome of the sqlldr will also give errors = xxxx
and I cannot have any errors or the entire framework with scripts etc. will stop.

I would like them in the discard file or is it possible to ignore the rows not imported so that the also are not reported as errors?
Re: SQL Loader discard non-numeric chars [message #525304 is a reply to message #525301] Fri, 30 September 2011 14:53 Go to previous messageGo to next message
BlackSwan
Messages: 26656
Registered: January 2009
Location: SoCal
Senior Member
use EXTERNAL TABLE, custom PL/SQL & CREATE NEW_TABLE with ERROR LOGGING enabled
Re: SQL Loader discard non-numeric chars [message #525444 is a reply to message #525304] Mon, 03 October 2011 02:52 Go to previous messageGo to next message
Kwon
Messages: 19
Registered: September 2011
Junior Member
How would that PL/SQL look?
Re: SQL Loader discard non-numeric chars [message #525558 is a reply to message #525444] Mon, 03 October 2011 13:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
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 #525600 is a reply to message #525558] Tue, 04 October 2011 01:48 Go to previous messageGo to next message
Kwon
Messages: 19
Registered: September 2011
Junior Member
This is almost perfectly working.
But it discards amounts with decimals and only includes integer even though the column is number.

inv_id and cust_id should be integer but amount have to be number and contains decimal values.
Re: SQL Loader discard non-numeric chars [message #525711 is a reply to message #525600] Tue, 04 October 2011 13:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
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>

Re: SQL Loader discard non-numeric chars [message #525746 is a reply to message #525711] Wed, 05 October 2011 01:53 Go to previous message
Kwon
Messages: 19
Registered: September 2011
Junior Member
My fault.

Ii is working. Smile

Thnaks for the help!!

[Updated on: Wed, 05 October 2011 07:25]

Report message to a moderator

Previous Topic: Error in Import
Next Topic: IMP-00010
Goto Forum:
  


Current Time: Wed Feb 19 13:39:46 CST 2020