Why Is Unsigned Packed Decimal Data Not Rejected By SQL*Loader?
(Doc ID 1557000.1)
Last updated on AUGUST 04, 2018
Applies to:
Oracle Database - Enterprise Edition - Version 10.2.0.1 and laterInformation in this document applies to any platform.
Goal
The goal of this document is to explain why SQL*Loader (contrary to certain non-Oracle environments such as DB2) doesn't reject unpacked decimal data, and how to work around this. The following example will demonstrate the problem occurring:
A test table with a numeric datatype column is being used:
SQL> DESCRIBE test
Name Null? Type
----------------------------------------- -------- ----------------------------
AC_CA_PREV_DEFR_PAYMT_BAL_A NUMBER(13,2)
Name Null? Type
----------------------------------------- -------- ----------------------------
AC_CA_PREV_DEFR_PAYMT_BAL_A NUMBER(13,2)
The following data file is to be loaded:
test.txt
-------------
012345678c4040404040
-------------
012345678c4040404040
This data file consists of 2 values (01 23 45 67 8C and 40 40 40 40 40, where the latter one is the unsigned packed decimal value).
The SQL*Loader control file used reads:
test.ctl
-----------
options ( silent=(discards) )
LOAD DATA
CHARACTERSET we8ebcdic500 BYTEORDER BIG ENDIAN
INFILE "test.txt" "fix 5"
BADFILE "test.bad"
DISCARDFILE "test.dsc"
REPLACE
INTO TABLE test -- when <> = '??'
TRAILING NULLCOLS
(
ac_ca_prev_defr_paymt_bal_a POSITION (1:5) DECIMAL(9,2)
)
-----------
options ( silent=(discards) )
LOAD DATA
CHARACTERSET we8ebcdic500 BYTEORDER BIG ENDIAN
INFILE "test.txt" "fix 5"
BADFILE "test.bad"
DISCARDFILE "test.dsc"
REPLACE
INTO TABLE test -- when <
TRAILING NULLCOLS
(
ac_ca_prev_defr_paymt_bal_a POSITION (1:5) DECIMAL(9,2)
)
The SQL*Loader run ends with:
sqlldr tc/tc control=test.ctl
SQL*Loader: Release 11.2.0.3.0 - Production on Tue May 28 16:00:40 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 2
SQL*Loader: Release 11.2.0.3.0 - Production on Tue May 28 16:00:40 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 2
The SQL*Loader log file shows:
Table TEST:
2 Rows successfully loaded.
0 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.
2 Rows successfully loaded.
0 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.
After the load finishes, the table contains:
SQL> select * from test;
AC_CA_PREV_DEFR_PAYMT_BAL_A
---------------------------
123456.78
40404040.4
AC_CA_PREV_DEFR_PAYMT_BAL_A
---------------------------
123456.78
40404040.4
The problem is that the 4040404040 unsigned packed decimal value is not rejected.
Solution
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |