Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-01722: invalid number error with Bulk collect

942902Jun 11 2012 — edited Jun 12 2012
Hi ,

I have been using the script to delete old seasonal data from my application DB tables. The stored procedure has been created successfully but when i try to run the proc it has been throwing 'ORA-01722: invalid number' exception at line 'FETCH C1_CUR BULK COLLECT INTO C1_TYPE_VAR LIMIT v_bulklimit;'.

Could you please help me here?

Below is the stored proc:

CREATE OR REPLACE PROCEDURE clean_old_season_data(P_SEASON VARCHAR2) AS

CURSOR C1_CUR IS SELECT ROWID RID,pro.* FROM PROPS pro where pro.ITEMPK IN
(SELECT sve.pk FROM SAVEDVALUEENTRY sve WHERE sve.p_parent IN
(SELECT s.pk FROM SAVEDVALUES s WHERE s.P_MODIFIEDITEM IN
(SELECT a.PK
FROM products a
WHERE a.p_season IN (select s.pk from Seasons s where s.P_code=P_SEASON)
) ) ) and rownum<5;

CURSOR C2_DEL IS SELECT RID FROM PROPS_HISTORY;


TYPE C1_TYPE IS TABLE OF C1_CUR%ROWTYPE;

C1_TYPE_VAR C1_TYPE;

TYPE C2_TYPE IS TABLE OF UROWID;

C2_TYPE_VAR C2_TYPE;

ex_dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);

l_error_count NUMBER;

err_num NUMBER;

err_msg VARCHAR2 (300);

COMMIT_VARIABLE PLS_INTEGER:=0;
v_bulklimit NUMBER:=2;


BEGIN

/*------------------ Data Selection and INSERTION IN HISTORY TABLE ---------------------------------------*/

OPEN C1_CUR;

LOOP
DBMS_OUTPUT.put_line('Cursor opend now in loop');


FETCH C1_CUR BULK COLLECT INTO C1_TYPE_VAR LIMIT v_bulklimit;//ERROR OCCURS HERE
DBMS_OUTPUT.put_line('Cursor count is'|| C1_TYPE_VAR.COUNT);
FORALL I IN 1..C1_TYPE_VAR.COUNT SAVE EXCEPTIONS
INSERT INTO PROPS_HISTORY VALUES C1_TYPE_VAR(I);

COMMIT_VARIABLE := COMMIT_VARIABLE + v_bulklimit;
DBMS_OUTPUT.put_line('Commit variable'|| COMMIT_VARIABLE.COUNT);

IF COMMIT_VARIABLE = v_bulklimit THEN

COMMIT;

COMMIT_VARIABLE := 0;

END IF;

EXIT WHEN C1_CUR%NOTFOUND;

END LOOP;
DBMS_OUTPUT.put_line('Cursor closed now in loop and data inserted in history table');

CLOSE C1_CUR;

/*------------------ Data Selection and DELETION IN Live TABLE ---------------------------------------*/

COMMIT_VARIABLE := 0;

OPEN C2_DEL;

LOOP

FETCH C2_DEL BULK COLLECT INTO C2_TYPE_VAR LIMIT 2;

FORALL I IN 1..C2_TYPE_VAR.COUNT SAVE EXCEPTIONS
DELETE FROM PROPS WHERE ROWID = C2_TYPE_VAR(I);

COMMIT_VARIABLE := COMMIT_VARIABLE + 2;

IF COMMIT_VARIABLE = 2 THEN

COMMIT;

COMMIT_VARIABLE := 0;

END IF;

EXIT WHEN C2_DEL%NOTFOUND;

END LOOP;

CLOSE C2_DEL;

END;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 10 2012
Added on Jun 11 2012
8 comments
3,645 views