Home » RDBMS Server » Server Utilities » SQL LOADER error during import some packed value
| SQL LOADER error during import some packed value [message #119852] |
Mon, 16 May 2005 06:55  |
hite
Messages: 4
Registered: May 2005
|
Junior Member |
|
|
During sqlldr test I encoutered a very big bug I hope. I want to load different kind of data, char, integer, decimal ...
This is my table :
CREATE TABLE CICCIO (
CARAT_NULL CHAR (10) NULL,
CARAT_NONULL CHAR (10) NOT NULL,
INT_NULL INTEGER NULL,
INT_NONULL INTEGER NOT NULL,
DEC_NULL DECIMAL (5, 0) NULL,
DEC_NONULL DECIMAL (5, 0) NOT NULL,
DEC_VIR_NULL DECIMAL (8, 3) NULL,
DEC_VIR_NONULL DECIMAL (8, 3) NOT NULL
);
when I try to load into DEC_VIR_NULL or DEC_VIR_NONULL this packed value
00000
0010C
sqlldr load with no problem, but insert 0 into DEC_VIR_NULL or DEC_VIR_NONULL instead of 1.
When I run select on the table field value is 0 instead of 1.000
Anyone know why???
this is my ctl
LOAD DATA
INFILE ciao " fix 48 "
REPLACE
PRESERVE BLANKS
INTO TABLE CICCIO
(
CARAT_NULL POSITION ( 1 ) CHAR ( 10) NULLIF (11) = '?',
CARAT_NONULL POSITION ( 12 ) CHAR ( 10) ,
INT_NULL POSITION ( 22 ) INTEGER NULLIF (26) = '?',
INT_NONULL POSITION ( 27 ) INTEGER ,
DEC_NULL POSITION ( 31 ) DECIMAL ( 5, 0) NULLIF (34) = '?',
DEC_NONULL POSITION ( 35 ) DECIMAL ( 5, 0) ,
DEC_VIR_NULL POSITION ( 38 ) DECIMAL ( 8, 3) NULLIF (43) = '?',
DEC_VIR_NONULL POSITION ( 44 ) DECIMAL ( 8, 3) )
~
~
|
|
|
|
| Re: SQL LOADER error during import some packed value [message #119898 is a reply to message #119852] |
Mon, 16 May 2005 10:56  |
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
|
Senior Member |
|
|
when I try to load into DEC_VIR_NULL or DEC_VIR_NONULL this packed value
00000
0010C
Try using this format for decimal fields:
DEC_VIR_NONULL char terminated by whitespace "to_number(:DEC_VIR_NONULL,'99999.999')"
instead of
DEC_VIR_NONULL POSITION ( 44 ) DECIMAL ( 8, 3)
Eg below will make it clear:
SQL> desc x
Name Null? Type
----------------------------------------- -------- ---------------------
A NUMBER(5,2)
sam.dat:
001C
12C
123
123.56
1.ctl:
load data
infile 'sam.dat'
append into table x
trailing nullcols
(a char terminated by whitespace "to_number(:a,'999.99')")
Data inserted:
SQL> select * from x;
A
----------
123
123.56
2.ctl:
load data
infile 'sam.dat'
append into table x
trailing nullcols
(a decimal (5,2))
Data inserted:
SQL> select * from x;
no rows selected
|
|
|
|
| Re: SQL LOADER error during import some packed value [message #120347 is a reply to message #119898] |
Thu, 19 May 2005 04:00 |
hite
Messages: 4
Registered: May 2005
|
Junior Member |
|
|
The value I want to load into DEC_VIR_NONULL column is
000001000C
It is not terminated by whitespace.
The problem is with this value, if I try to load
001002030C
I load it correctly.
My data comes from db2 so I have a sequential file to load. I can load data only by position.
I haven't character separator.
Thanks
|
|
|
|
| Re: SQL LOADER error during import some packed value [message #120702 is a reply to message #120347] |
Mon, 23 May 2005 01:55 |
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
|
Senior Member |
|
|
HI Hite
I want to know 1 thing:
How can u load a value terminating with character into a decimal column, that too without any error, say a value 001002030C ?
I am having doubts over this. Pls do clarify. As when I tested with same values, given ur sql*loader code, everytime I got error stating Inavlid number value.
Awaiting ur comments on this for my knowledge sake...
Attached is my test.log for your reference.
As for the workaround my test control file is below (assumption: Last character is digit):
LOAD DATA
INFILE *
INTO TABLE testing
( a POSITION (1:5) NULLIF (5) = '?' "to_number(:a,'99999.999')",
b POSITION (6:10) "to_number(:b,'99999.999')"
)
BEGINDATA
001002030C
000001000C
1000020000
0010020300
0000010000
0000?10000
Table desc:
Name Null? Type
----------------------------------------- -------- -------------
A NUMBER(8,3)
B NUMBER(8,3)
Data loaded:
A B
---------- ----------
10000 20000
100 20300
0 10000
10000
Summary of load process (from .log file)
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
A 1:5 5 CHARACTER
NULL if 5:5 = 0X3f(character '?')
SQL string for column : "to_number(:a,'99999.999')"
B 6:10 5 CHARACTER
SQL string for column : "to_number(:b,'99999.999')"
Record 1: Rejected - Error on table TESTING, column B.
ORA-01722: invalid number
Record 2: Rejected - Error on table TESTING, column B.
ORA-01722: invalid number
Table TESTING:
4 Rows successfully loaded.
2 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Regds
Girish
-
Attachment: test.log
(Size: 2.15KB, Downloaded 1455 times)
|
|
|
|
| Re: SQL LOADER error during import some packed value [message #120756 is a reply to message #120702] |
Mon, 23 May 2005 09:56 |
hite
Messages: 4
Registered: May 2005
|
Junior Member |
|
|
00 00 01 00 0C
This is the hex rappresentation of a PACKED value. Those value came from DB2 stored in MVS system. When you download PACKED value you have to considered data stored as COBOL data type. A Packed field is a COMP-3 field.
Your control file is ok if you upload display data, but if you load packed data you have a control file like my example.
The value rapresented from
00 00 01 00 0C is 1000 but it is stored 5 bytes so it can be loaded into a NUMBER ( 8 ) or NUMBER 8 with decimal.
When I load this kind of data I have the error specified in my first ask.
Do you have already loaded this kind of data?
Bye
|
|
|
|
| Re: SQL LOADER error during import some packed value [message #120775 is a reply to message #120756] |
Mon, 23 May 2005 12:55 |
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
|
Senior Member |
|
|
Hi
Pls check with this control file syntax:
LOAD DATA
INFILE *
INTO TABLE x
( a POSITION (1:5) NULLIF (5) = '?' "to_number(:a,'XXXXXXXXXXXXXXXX')",
b POSITION (6:10) "to_number(:b,'XXXXXXXXXXXXXXXX')"
)
BEGINDATA
001002030C
000001000C
1000020000
0010020300
0000010000
0000?10000
I Tried loading data using this, and it loaded perfectly. All these Hexa values were converted to decimal values.
The changes I made to table str are (increase in size of field from (8,3) to (9,3):
SQL> desc x
Name Null? Type
----------------------------------------- -------- ---------------
A NUMBER(9,3)
B NUMBER(9,3)
The following values got loaded:
SQL> select * from x;
A B
---------- ----------
256 131852
0 65548
65536 131072
256 131840
0 65536
65536
Also the decimal value represented by 00 00 01 00 0C
is 65548. So, if its stored in decimal field, shouldn't this value 65548 be stored instead of 1000 as mentioned by u earlier.
Pls do clarify..
Regds
Girish
|
|
|
|
Goto Forum:
Current Time: Mon Feb 03 12:35:34 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
