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 Go to next message
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 #356618 is a reply to message #356613] Fri, 31 October 2008 09:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: BULK COLLECT LIMIT generate ORA-01401 [message #356701 is a reply to message #356613] Sat, 01 November 2008 03:31 Go to previous message
Frank
Messages: 7880
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?
Previous Topic: with clause query
Next Topic: metadata about tables
Goto Forum:
  


Current Time: Tue Dec 06 04:41:01 CST 2016

Total time taken to generate the page: 0.06041 seconds