OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » sqlldr error

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sqlldr error [message #224315]

Tue, 13 March 2007 19:29

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!

Report message to a moderator

Re: sqlldr error [message #224318 is a reply to message #224315]

Tue, 13 March 2007 20:22

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"
)

Report message to a moderator

Re: sqlldr error [message #224319 is a reply to message #224318]

Tue, 13 March 2007 20:40

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!

Report message to a moderator

Re: sqlldr error [message #224324 is a reply to message #224319]

Tue, 13 March 2007 21:00

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.

Report message to a moderator

Re: sqlldr error [message #224329 is a reply to message #224324]

Tue, 13 March 2007 21:18

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!