:-/ ORA-01026 in SQL*Loader
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