:-/ ORA-01026 in SQL*Loader

From: Chris <Chris_member_at_newsguy.com>
Date: 23 Feb 2001 11:14:35 -0800
Message-ID: <976cqr0j1f_at_drn.newsguy.com>


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 Fri Feb 23 2001 - 20:14:35 CET

Original text of this message