Re: :-/ ORA-01026 in SQL*Loader

Change the buffer size to be less than 4000 for the bind variable bound to a normal column.

On 23 Feb 2001 11:14:35 -0800, Chris <Chris_member_at_newsguy.com> wrote:

>I am trying to load data into an Oracle database in unix (Oracle 7.3) using
>Oracle SQL*Loader. The data is a table dump from a DB2 (mainframe) database. I
>have 400other tables that work fine, but this is the only table that has VARCHAR
>in it. Any ideas?
>
>DB2 definition:
> COL1 DECIMAL(10,0)
> COL2 TIMESTMP
> COL3 VARCHAR(2000)
> COL4 VARCHAR(2000)
> COL5 CHAR(2)
> COL6 DATE
>
>Oracle 7.3 definition:
> COL1 NOT NULL NUMBER(10)
> COL2 NOT NULL CHAR(26)
> COL3 NOT NULL VARCHAR2(2000)
> COL4 NOT NULL VARCHAR2(2000)
> COL5 NOT NULL CHAR(2)
> COL6 NOT NULL CHAR(10)
>
>SQL*Loader control card:
> OPTIONS (ROWS=10)
> LOAD DATA
> -- The following lines are added for SQL*Load
> CHARACTERSET WE8EBCDIC500
> INFILE 'TABLE.EBC' "fix 4048"
> BADFILE 'TABLE.bad'
> DISCARDFILE 'TABLE.dsc'
> TRUNCATE
> PRESERVE BLANKS
> INTO TABLE TABLENAME
> (
> COL1 POSITION(001:006) DECIMAL (10,0)
> ,COL2 POSITION(007:032) CHAR(26)
> ,COL3 POSITION(033:2034) VARCHAR(2000)
> ,COL4 POSITION(2035:4036) VARCHAR(2000)
> ,COL5 POSITION(4037:4038) CHAR(02)
> ,COL6 POSITION(4039:4048) CHAR(10)
> )
>
>I receive the following errors:
>SQL*Loader: Release 7.3.2.2.0 - Production on Fri Feb 23 14:03:04 2001
>
>Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.
>
>Control File: /TABLE.ctl
>Character Set WE8EBCDIC500 specified for all input.
>
>Data File: TABLE.EBC
> File processing option string: "fix 4048"
> Bad File: TABLE.bad
> Discard File: TABLE.dsc
> (Allow all discards)
>
>Number to load: ALL
>Number to skip: 0
>Errors allowed: 50
>Bind array: 10 rows, maximum of 65536 bytes
>Continuation: none specified
>Path used: Conventional
>
>Table TABLENAME, loaded from every logical record.
>Insert option in effect for this table: TRUNCATE
>
> Column Name Position Len Term Encl Datatype
>------------------------------ ---------- ----- ---- ---- ---------------------
>COL1 1:6 6 PACKED DECIMAL (10, 0)
>COL2 7:32 26 CHARACTER
>COL3 33:2034 2002 VARCHAR
>COL4 2035:4036 2002 VARCHAR
>COL5 4037:4038 2 CHARACTER
>COL6 4039:4048 10 CHARACTER
>
>Record 1: Rejected - Error on table TABLENAME.
>ORA-01026: multiple buffers of size > 2000 in the bind list
>
>Record 2: Rejected - Error on table TABLENAME.
>ORA-01026: multiple buffers of size > 2000 in the bind list
>.
>.(all records from 1-51)
>.
>Record 50: Rejected - Error on table TABLENAME.
>ORA-01026: multiple buffers of size > 2000 in the bind list
>
>Record 51: Rejected - Error on table TABLENAME.
>ORA-01026: multiple buffers of size > 2000 in the bind list
>
>
>MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.
>
>Table TABLENAME:
> 0 Rows successfully loaded.
> 51 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.
>
>
>Space allocated for bind array: 40780 bytes(10 rows)
>Space allocated for memory besides bind array: 96585 bytes
>
>Total logical records skipped: 0
>Total logical records read: 58
>Total logical records rejected: 51
>Total logical records discarded: 0
>
>Run began on Fri Feb 23 14:03:04 2001
>Run ended on Fri Feb 23 14:05:08 2001
>
>Elapsed time was: 00:02:03.40
>CPU time was: 00:00:00.14
>
>
>
>********
>I tried various combinations of data definition with the control file. I also
>tried defining the max number of rows (as you see here), and without the rows
>definition - no difference. I don't understand the error. It almost looks as
>though it is complaining that there is more than 1 VARCHAR in the table.
>
>Any ideas or help would be greatly appreciated!
Received on Mon Mar 05 2001 - 19:28:09 CET