Home » RDBMS Server » Server Utilities » sqlldr error
sqlldr error [message #224315] Tue, 13 March 2007 19:29 Go to next message
syang
Messages: 30
Registered: February 2007
Member
Hi All,

We experienced some errors when loading data using sqlldr. The error was on column HCPCS_DESC which has 800 bytes defined. The actual data was less than 700 bytes in length. But, sqlloader complaint that it exceeded the maximum length. Does anyone know what is going wrong?

1. Here is table structure:

SQL> desc hcpcs
Name Null? Type
----------------------------------------- -------- ----------------------------
HCPCS_CD NOT NULL CHAR(5)
HCPCS_SQNC_NUM CHAR(5)
HCPCS_DESC VARCHAR2(800)
LAST_CHG_USER_ID VARCHAR2(80)
LAST_CHG_DT DATE

2. Actual data:

E1390,,"Bid price includes HCPCS codes:E1391,E0424, E0439. E1390-Oxygen concentrator, single delivery port, capable of delivering 85 percent or greater oxygen concentration at the prescribed flow rate; E1391-Oxygen concentrator, dual delivery port, capable of delivering 85 percent or greater oxygen concentration at the prescribed flow rate, each; E0424-Stationary compressed gaseous oxygen system, rental; includes container, contents, regulator, flowmeter, humidifier, nebulizer, cannula or mask, and tubing; E0439-Stationary liquid oxygen system, rental; includes container, contents, regulator, flowmeter, humidifier, nebulizer, cannula or mask, and tubing",xxxx@yahoo.com,3/13/2007

3. The control file:

load data
infile hcpcs.dat
insert
into table cbssr.hcpcs
fields terminated by ',' optionally enclosed by '"'
TRAILING NULLCOLS
(
HCPCS_CD,
HCPCS_SQNC_NUM,
HCPCS_DESC,
LAST_CHG_USER_ID,
LAST_CHG_DT date "mm/dd/yyyy"
)

You help is greatly appreciated.

Thanks!
Re: sqlldr error [message #224318 is a reply to message #224315] Tue, 13 March 2007 20:22 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You may want to try to modify your control file to:

load data
infile hcpcs.dat
insert
into table cbssr.hcpcs
fields terminated by ',' optionally enclosed by '"'
TRAILING NULLCOLS
(
HCPCS_CD,
HCPCS_SQNC_NUM,
HCPCS_DESC CHAR(800),
LAST_CHG_USER_ID,
LAST_CHG_DT date "mm/dd/yyyy"
)
Re: sqlldr error [message #224319 is a reply to message #224318] Tue, 13 March 2007 20:40 Go to previous messageGo to next message
syang
Messages: 30
Registered: February 2007
Member
Thanks for your reply and suggestion.

Since the column HCPCS_DESC is a varchar2 field, I used VARCHAR2(800) instead of what you suggested CHAR(800) in my control file:

load data
infile hcpcs_fix.dat
append
into table cbssr.hcpcs
fields terminated by ',' optionally enclosed by '"'
TRAILING NULLCOLS
(
HCPCS_CD,
HCPCS_SQNC_NUM,
HCPCS_DESC VARCHAR2(800),
LAST_CHG_USER_ID,
LAST_CHG_DT date "mm/dd/yyyy"
)

I got a different error:

SQL*Loader-350: Syntax error at line 10.
Expecting "," or ")", found "VARCHAR2".
HCPCS_DESC VARCHAR2(800),
^
Any idea?

Thanks!
Re: sqlldr error [message #224324 is a reply to message #224319] Tue, 13 March 2007 21:00 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Yes, it's because you need to use the syntax I specified. VARCHAR2 is not correct syntax for SQLLDR.
Re: sqlldr error [message #224329 is a reply to message #224324] Tue, 13 March 2007 21:18 Go to previous messageGo to next message
syang
Messages: 30
Registered: February 2007
Member
Ok.
I used CHAR(800) in my control file as you suggested to load sinlge data record just for a test, and the log file says I am trying to load 6 records:

1. Result from the log file:

Record 1: Rejected - Error on table CBSSR.HCPCS, column HCPCS_DESC.
second enclosure string not present
Record 2: Rejected - Error on table CBSSR.HCPCS, column HCPCS_CD.
ORA-12899: value too large for column "CBSSR"."HCPCS"."HCPCS_CD" (actual: 110, maximum: 5)

Record 3: Rejected - Error on table CBSSR.HCPCS, column HCPCS_CD.
ORA-12899: value too large for column "CBSSR"."HCPCS"."HCPCS_CD" (actual: 6, maximum: 5)

Record 4: Rejected - Error on table CBSSR.HCPCS, column HCPCS_CD.
ORA-12899: value too large for column "CBSSR"."HCPCS"."HCPCS_CD" (actual: 34, maximum: 5)

Record 5: Rejected - Error on table CBSSR.HCPCS, column HCPCS_CD.
ORA-12899: value too large for column "CBSSR"."HCPCS"."HCPCS_CD" (actual: 9, maximum: 5)

Record 6: Rejected - Error on table CBSSR.HCPCS, column HCPCS_CD.
ORA-12899: value too large for column "CBSSR"."HCPCS"."HCPCS_CD" (actual: 9, maximum: 5)

2. Please see the actual data file in the attched hcpcs_fix.dat

Since the text is too long, one single data record became wrapped into 6 lines. What can I do to tell sqlldr that I am loading 1 single record?

Your suggestin is appreciated!
Thanks!
Re: sqlldr error [message #224330 is a reply to message #224329] Tue, 13 March 2007 21:42 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
There appears to be a hard return at the end of each line. You may want to try and remove the hard returns.
Re: sqlldr error [message #224639 is a reply to message #224330] Wed, 14 March 2007 20:46 Go to previous message
syang
Messages: 30
Registered: February 2007
Member
Thank you, Ebrian! Yes. The hard coded return was the root of cause.
Previous Topic: SQL Loader date time conversion problem
Next Topic: sqlldr: what's wrong?
Goto Forum:
  


Current Time: Fri Dec 09 01:41:05 CST 2016

Total time taken to generate the page: 0.26034 seconds