Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL Loader invalid packed decimal

794356Aug 27 2010 — edited Sep 3 2010
Hi. Could someone please help me with a couple of errors I am getting from SQL Loader? After examining the data and definitions, I can't see why these errors would come up. I have reduced the data down to 2 rows with 2 fields each to make is easier. We are running Linux 2.6.5-7.283-smp #1 (HOSTTYPE = x86_64) on a server model HL-DT-STCD-RW/DVD-ROM GCC-4244N with Oracle 10g and SQL*Loader 10.2.0.3.0

Here is what the input data file looks like in the pico editor and hex dump:
(the ^L is actually one character)
$ pico loadtest.csv
"^L","A"
" 7&|","A "

$ hd loadtest.csv
00000000 22 0C 22 2C 22 41 22 0A 22 20 20 20 37 26 7C 22 " ","A" " 7&|"
00000010 2C 22 41 20 22 0A ,"A "

-- This is the SQL to create the target table:
create table loadtest
(FLD1 NUMBER(12,2)
,FLD2 VARCHAR2(2) );

-- This is the control file used by SQL*Loader:
options (errors=9999999999)
LOAD DATA APPEND
into table LOADTEST
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( FLD1 decimal(12,2) "trim(:fld1)"
, FLD2 terminated by whitespace
)

-- $inputdb is the schema-name/password
-- The command below is used to load the data from the CSV into the schema.
$ sqlldr $inputdb control=loadtest.ctl data=loadtest.csv log=loadtest.log

-- Both records are rejected with these errors:
Record 1: Rejected - Error on table LOADTEST, column FLD1.
Invalid packed decimal nibble.
Record 2: Rejected - Error on table LOADTEST, column FLD2.
no terminator found after TERMINATED and ENCLOSED field

-- The "terminated by whitespace" clause makes no difference.
-- If the trim is left off of FLD1 in the control, the second message is
"Value larger than specified precision allows for this column"

The "invalid packed decimal nibble" makes no sense to me because the hex dump clearly shows that the value in the first record inside the quotes for FLD1 is 0C which is ascii hex for the form feed (FF). In packed decimal terms this is valid because the first 4 bits (x0 = 0000) represent the zero value, and the second 4 bits (xC = 1100) represent the positive sign.

The "no terminator found" makes no sense to me because the hex dump clearly shows each data line being terminated by (hex) 0A which is ascii hex for line feed (LF). According to documentation, this is the default terminator for stream data.

It is worth noting that this exact CSV file was copied to a VAX/VMS server and run through a COBOL program, which correctly translated the data as
"+000000.00","A"
"+000372.67","A "

(VAX/VMS is not where the data originated, but is convenient for COBOL programs)

Can anyone see why these errors are being thrown?
Thanks in advance.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 1 2010
Added on Aug 27 2010
7 comments
4,655 views