SQL-Loader-00555 : unreferenced error code

From: Frederic Payant <fpayant_at_club-internet.fr>
Date: Thu, 18 Jul 2002 23:14:58 +0200
Message-ID: <v0aejuo11i47h9jvd93mltlr475o8575ie@4ax.com>

Hi,
I've experimented this afternoon a strange comportment of sqlloader (Oracle 817 with Solaris8).

For tests purpoise I need to load some millions of rows in a table so I used awk to generate data and sqlloader to load it.

My table is defined as follow :
CREATE TABLE X_EVENT_ACT_CONTENT_AUTH_T (

  OBJ_ID0                 INTEGER, 
  AAC_PACKAGE             VARCHAR2 (2), 
  AAC_SOURCE              VARCHAR2 (2), 
  ACCOUNT_NO              VARCHAR2 (7), 
  DELIVERING_MEDIA        VARCHAR2 (3), 
  EVENT_NO                VARCHAR2 (10), 
  LAST_NAME               VARCHAR2 (30), 
  LOGIN                   VARCHAR2 (9), 
  MERCHANT_CODE           VARCHAR2 (6), 
  ORDERING_MEDIA          VARCHAR2 (3), 
  PROGRAM_NAME            VARCHAR2 (4), 
  RATING_TYPE             VARCHAR2 (1), 
  TAX_CODE                VARCHAR2 (5), 
  TITLE                   VARCHAR2 (10), 
  CONTPROVIDER_CODE       VARCHAR2 (10), 
  DATE_AND_TIME_1         INTEGER, 
  DELIVERY_MSISDN         VARCHAR2 (9), 
  MERCHANT_CONTRACT_CODE  VARCHAR2 (8), 
  RATING_CLASS            INTEGER, 
  TRANSACTION_STATUS      INTEGER )

PARTITION BY RANGE (OBJ_ID0)
  (
PARTITION PARTITION_HISTORIC VALUES LESS THAN (35184372088832) PCTFREE 10
   PCTUSED 40
   INITRANS 1
   MAXTRANS 255
STORAGE (
   INITIAL 204800
   NEXT 204800
   PCTINCREASE 0
   MINEXTENTS 1
   MAXEXTENTS 2147483645
),
PARTITION PARTITION_LAST VALUES LESS THAN (MAXVALUE) PCTFREE 10
   PCTUSED 40
   INITRANS 1
   MAXTRANS 255
STORAGE (
   INITIAL 204800
   NEXT 204800
   PCTINCREASE 0
   MINEXTENTS 1
   MAXEXTENTS 2147483645
)
);
my flat file is a semicolon separated one my control file is :
LOAD DATA
APPEND
INTO TABLE X_EVENT_ACT_CONTENT_AUTH_T
FIELDS TERMINATED BY ";"
(

OBJ_ID0,
AAC_PACKAGE,
AAC_SOURCE,

ACCOUNT_NO,
DELIVERING_MEDIA,
EVENT_NO,
LAST_NAME,
LOGIN,
MERCHANT_CODE,
ORDERING_MEDIA,
PROGRAM_NAME,
RATING_TYPE,
TAX_CODE,
TITLE,
CONTPROVIDER_CODE,

DATE_AND_TIME_1,
DELIVERY_MSISDN,
MERCHANT_CONTRACT_CODE,

RATING_CLASS,
TRANSACTION_STATUS
)

and I call sqlloader :
sqlldr \

        bindsize=20000000 \
        readsize=20000000 \
        rows=10000 \
        errors=1000 \
        userid=pin/pin \
        bad=$FICBAD \
        log=$FICLOG \
        data=$FICOUT \
        control=PopXEVTWithSQLLoader.ctl \
        direct=false

($FICBAD $FICLOG and $FICOUT are valids file names)
If I call sqlloader like this, all is OK and it treats 3875 rows at one time :
SQL*Loader: Release 8.1.7.0.0 - Production on Je Jul 18 15:41:28 2002

Commit point reached - logical record count 3875
Commit point reached - logical record count 7750
Commit point reached - logical record count 11625
Commit point reached - logical record count 15500
Commit point reached - logical record count 19375

... etc ...

I tried to increment the number of rows treated at one time by modifying the parameters bindsize and readsize. But if I type :
sqlldr \

        bindsize=25000000 \
        readsize=25000000 \
        rows=10000 \
        errors=1000 \
        userid=pin/pin \
        bad=$FICBAD \
        log=$FICLOG \
        data=$FICOUT \
        control=PopXEVTWithSQLLoader.ctl \
        direct=false

I get the following error :
SQL*Loader: Release 8.1.7.0.0 - Production on Je Jul 18 15:36:53 2002

SQL*Loader-500: Unable to open file
(/tmp/PopXEVTWithSQLLoader_20020718_153652.ldr)
SQL*Loader-555: unrecognized processing option

The first error (500) has no interest, the file is OK, readable, correctly formatted, no special caracters (and works well if I change bindsize and readsize). the second one is not referenced in error messages and I did'nt find it nor in group archives, nor with google.

Log file just says :
SQL*Loader: Release 8.1.7.0.0 - Production on Je Jul 18 15:37:19 2002

Control File: PopXEVTWithSQLLoader.ctl

Data File:      /tmp/PopXEVTWithSQLLoader_20020718_153718.ldr
  Bad File:     /tmp/PopXEVTWithSQLLoader_20020718_153718.bad

Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 1000
Bind array:     10000 rows, maximum of 25000000 bytes
Continuation:    none specified
Path used:      Conventional

Table X_EVENT_ACT_CONTENT_AUTH_T, loaded from every logical record.
Insert option in effect for this table: APPEND

   Column Name                  Position   Len  Term Encl

Datatype

------------------------------ ---------- ----- ---- ---- -----
----------------
OBJ_ID0                             FIRST     *   ;       
CHARACTER            
AAC_PACKAGE                          NEXT     *   ;       
CHARACTER            
AAC_SOURCE                           NEXT     *   ;       
CHARACTER            
ACCOUNT_NO                           NEXT     *   ;       
CHARACTER            
DELIVERING_MEDIA                     NEXT     *   ;       
CHARACTER            
EVENT_NO                             NEXT     *   ;       
CHARACTER            
LAST_NAME                            NEXT     *   ;       
CHARACTER            
LOGIN                                NEXT     *   ;       
CHARACTER            
MERCHANT_CODE                        NEXT     *   ;       
CHARACTER            
ORDERING_MEDIA                       NEXT     *   ;       
CHARACTER            
PROGRAM_NAME                         NEXT     *   ;       
CHARACTER            
RATING_TYPE                          NEXT     *   ;       
CHARACTER            
TAX_CODE                             NEXT     *   ;       
CHARACTER            
TITLE                                NEXT     *   ;       
CHARACTER            
CONTPROVIDER_CODE                    NEXT     *   ;       
CHARACTER            
DATE_AND_TIME_1                      NEXT     *   ;       
CHARACTER            
DELIVERY_MSISDN                      NEXT     *   ;       
CHARACTER            
MERCHANT_CONTRACT_CODE               NEXT     *   ;       
CHARACTER            
RATING_CLASS                         NEXT     *   ;       
CHARACTER            
TRANSACTION_STATUS                   NEXT     *   ;       
CHARACTER

SQL*Loader-500: Unable to open file
(/tmp/PopXEVTWithSQLLoader_20020718_153718.ldr)
SQL*Loader-555: unrecognized processing option

My questions are :
-- Is there an undocumented limit for bindsize or readsize ? -- Is there any other way to rise up the # of rows treated at each commit ?

Have a very nice day

Regards

Frederic PAYANT
Unices system administrator Received on Thu Jul 18 2002 - 16:14:58 CDT