Home » SQL & PL/SQL » SQL & PL/SQL » BULK COLLECT LIMIT generate ORA-01401 (9.2.0.7 Enterprise Edition on 5.3 AIX)
BULK COLLECT LIMIT generate ORA-01401 [message #356613] |
Fri, 31 October 2008 08:56  |
spinbains
Messages: 1 Registered: October 2008 Location: Cincinnati, OH
|
Junior Member |
|
|
Oracle 9.2.0.7
300G size instance
MLER_BULK = empty table
MLER_BULK_ARCH=s table with data and batchnumber key
MLHEADER_HEADER=main table with batchnumber key
MLER_BULK will be populated with data from MLER_BULK_ARCH when it's batchnumber_key matches batchnumber_key of MLHEADER_HEADER.
I'm attempting to save time by inserting a large number of rows that are also large in size by using BULK COLLECT LIMIT. When executing, I receive the error: O
ORA-01401: inserted value too large for column.
Below is the procedure:
PROCEDURE BULK_COLLECT_INTO_MLER
IS
TYPE MLER_BULK_REC IS RECORD
(R_BATCHNUM MLER_BULK.BATCHNUMBER%TYPE,
R_LOADTRK MLER_BULK.LOADTRACKDETAILKEY%TYPE,
R_STATEID MLER_BULK.MLFINITESTATEID%TYPE,
R_STATEDE MLER_BULK.MLFINITESTATEDESC%TYPE,
R_ALLPLAN MLER_BULK.MLAPPLIESTOALLPLANS%TYPE,
R_RECMEM MLER_BULK.MLLOADRECORDTOMEM%TYPE,
R_CRDATE MLER_BULK.CREATIONDATE%TYPE,
R_CRUSER MLER_BULK.CREATIONUSER%TYPE,
R_CRPGM MLER_BULK.CREATIONPROGRAM%TYPE,
R_MDATE MLER_BULK.MAINTENANCEDATE%TYPE,
R_MUSER MLER_BULK.MAINTENANCEUSER%TYPE,
R_MPGM MLER_BULK.MAINTENANCEPROGRAM%TYPE);
TYPE MLER_BULK_TAB IS TABLE OF MLER_BULK_REC;
V_MLER_BULK MLER_BULK_TAB;
V_ARRAY_SIZE NUMBER := 10000;
V_TOT_CNT NUMBER(20) :=0;
CURSOR C1 IS
SELECT
BATCHNUMBER,
LOADTRACKDETAILKEY,
MLFINITESTATEID,
MLFINITESTATEDESC,
MLAPPLIESTOALLPLANS,
MLLOADRECORDTOMEM,
CREATIONDATE,
CREATIONUSER,
CREATIONPROGRAM,
MAINTENANCEDATE,
MAINTENANCEUSER,
MAINTENANCEPROGRAM
FROM mler_bulk_arch m
WHERE m.batchnumber IN
(SELECT h.batchnumber FROM MLHEADER_ARCH h);
PROCEDURE writelog(pMSG IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN -- writelog
INSERT INTO POST_BULK_MLER_LOG
VALUES ('BULK_COLLECT_INS_MLER','MAIN',pMsg, SYSDATE, 0);
COMMIT;
END writelog;
BEGIN
writelog('Started');
OPEN C1;
LOOP
FETCH C1 BULK COLLECT
INTO
V_MLER_BULK LIMIT V_ARRAY_SIZE;
BEGIN
INSERT INTO mler_bulk
(BATCHNUMBER,
LOADTRACKDETAILKEY,
MLFINITESTATEID,
MLFINITESTATEDESC,
MLAPPLIESTOALLPLANS,
MLLOADRECORDTOMEM,
CREATIONDATE,
CREATIONUSER,
CREATIONPROGRAM,
MAINTENANCEDATE,
MAINTENANCEUSER,
MAINTENANCEPROGRAM)
SELECT
BATCHNUMBER,
LOADTRACKDETAILKEY,
MLFINITESTATEID,
MLFINITESTATEDESC,
MLAPPLIESTOALLPLANS,
MLLOADRECORDTOMEM,
CREATIONDATE,
CREATIONUSER,
CREATIONPROGRAM,
MAINTENANCEDATE,
MAINTENANCEUSER,
MAINTENANCEPROGRAM
FROM mler_bulk_arch;
EXIT WHEN V_MLER_BULK.COUNT=0;
V_TOT_CNT:=V_TOT_CNT+V_MLER_BULK.COUNT;
COMMIT;
writelog('Inserted into MLER_BULK so far: '|| V_TOT_CNT);
END LOOP;
CLOSE C1;
COMMIT;
writelog('Finished - Total Rows: '|| V_TOT_CNT);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
writelog(SQLERRM);
END;
|
|
|
|
Re: BULK COLLECT LIMIT generate ORA-01401 [message #356701 is a reply to message #356613] |
Sat, 01 November 2008 03:31  |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Looks to me you are duplicating your data in mler_bulk_arch several times: you fetch data into V_MLER_BULK, yet you use the complete table as source for insertion.
Regarding your error:
What's the max length of the message-column in your log table?
|
|
|
Goto Forum:
Current Time: Mon Feb 17 19:04:29 CST 2025
|