I am trying to insert values in a table which has a LONG datatype column.
There are 20,000 records to be inserted, PL/SQL procedure works fine for rownum < 3000 rows without any errors and then I get a numeric or value error (ORA-06502: PL/SQL: numeric or value error).
I tried to trap the exact record where error occurred and inserted the
same record by hardcoding it in the procedure, it works fine and the
same error repeats again.
Declared a cursor which select LONG column besides couple of other columns.
In the body, for all the records fetched by the above cursor, inserting the values in the same table for different ID.
set long 500000
CURSOR c1 is
a.name -- LONG
FROM type_class a, body_class b
WHERE a.id = b.id;
dbms_output.put_line('Start time '|| to_char(sysdate,'dd/mon/yyyy hh24:mi:ss'));
DELETE FROM type_class where id IN (select id from body_class);
for c1rec in c1 loop
dbms_output.put_line('Length is '|| length(c1rec.name));
dbms_output.put_line('Inserting ID in type_class: '||to_char(c1rec.id));
INSERT INTO type_class(id,value,name)
dbms_output.put_line('End time '|| to_char(sysdate,'dd/mon/yyyy hh24:mi:ss'));
when others then
Is there any size restriction or parameter setting on the retrieved result set of a cursor with LONG column?
I am just trying to figure out why I am able to insert records in chunks not in one shot.
Any thought shared would be highly appreciated.
PS: I get the same error ORA-06502: PL/SQL: numeric or value error inspite of commenting the whole INSERT statement in the code. Looks like its something to do in the cursor selection.
Originally posted by Littlefoot
Just an idea ... what happens if you put a COMMIT into the loop, right after an INSERT statement? And enhance EXCEPTION handler such as
INSERT INTO type_class
(ID, VALUE, NAME
VALUES (c1rec.to_be_inserted_id, c1rec.VALUE, c1rec.NAME
('End time ' || TO_CHAR (SYSDATE, 'dd/mon/yyyy hh24:mi:ss'));
('Error on to_be_inserted_id ' || TO_CHAR c1rec.to_be_inserted_id)); );
You said it works allright if inserting in chunks of some 3000 records. Oracle error doesn't seem to be this, but - 3000 * something_of_a_long_type perhaps extends rollback segment to its limit ...
Thanks for your reply Little foot. I tried to put a COMMIT and the variable declared to capture the value in the exception handler. I still get the same numeric or value error. As mentioned if I try to insert the erroneous record or as long as the result set is less than or equal to 3000 there is no problem.