Why Is Unsigned Packed Decimal Data Not Rejected By SQL*Loader?

(Doc ID 1557000.1)

Last updated on JUNE 03, 2013

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information 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)


The following data file is to be loaded:

test.txt
-------------
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)
)


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


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.


After the load finishes, the table contains:

SQL> select * from test;

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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms