Re: SQL-Loader-00555 : unreferenced error code

Frederic Payant <fpayant_at_club-internet.fr> wrote in message news:<v0aejuo11i47h9jvd93mltlr475o8575ie_at_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
>
> (c) Copyright 2000 Oracle Corporation. All rights reserved.
>
> 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
>
> (c) Copyright 2000 Oracle Corporation. All rights reserved.
>
> 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
>
> (c) Copyright 2000 Oracle Corporation. All rights reserved.
>
> 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

Per the version 8.1.7 Utilities manual both the bindsize and readsize limits are OS dependent. The limit should be listed, hopefully, in your OS specific documentation. If not then you will probably need to open a tar to get the correct answer. I seem to remember hitting an error like then when we moved from version 8.0 to 8.1 or 7.3 to 8 and we found that the limit was 2M and larger numbers were just ignored for one of these parameters. But we were on Sequent back then so the answer may vary.

HTH -- Mark D Powell -- Received on Fri Jul 19 2002 - 08:21:59 CDT