SQL & PL/SQL

HI ALL,

I am using ref cursor and bulk collect in my procedure .
Whenever I execute this I am getting error saying that ORA-
01001: invalid cursor . I didnt understand why. Please help me
ASAP.


Here is my code:

CREATE OR REPLACE PROCEDURE TEST(P_ID1 IN NUMBER,P_ID2 IN
NUMBER) IS
TYPE Ref_STP IS REF CURSOR ;

vSqlStatement VARCHAR2(3000);
C_DATA Ref_STP;

TYPE SS_ID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE SS_COL IS TABLE OF MYTAB.MYCOL%TYPE INDEX BY
BINARY_INTEGER;

nss_id SS_ID;
pk1 ss_col;
pk2 ss_col;
pk3 ss_col;
pk4 ss_col;
pk5 ss_col;
pm1 ss_col;
pm2 ss_col;
pm3 ss_col;
pm4 ss_col;
pm5 ss_col;


vSqlStatement := 'SELECT SD, '|| L_COL1 || ' , '
|| L_COL2 ||' , '
|| L_COL3 ||' , '
|| L_COL4 ||' , '
|| L_COL5 ||' , '
|| L_PARM1 ||' , '
|| L_PARM2 ||' , '
|| L_PARM3 ||' , '
|| L_PARM4 ||' , '
|| L_PARM5
|| ' FROM MY_TAB WHERE
id1 = '||P_ID1
|| ' AND id2
= '||P_ID2 || ' ORDER BY sd ';

OPEN C_DATA FOR vSqlStatement;
LOOP
FETCH C_DATA BULK COLLECT INTO nss_id,
pk1,
pk2,
pk3,
pk4,
pk5,
pm1,
pm2,
pm3,
pm4,
pm5;

FOR I IN 1..C_STP_DATA%ROWCOUNT LOOP
INSERT INTO TEMP VALUES (NSS_ID(I));
commit;
END LOOP;
EXIT WHEN C_STP_DATA%NOTFOUND;
END LOOP;
CLOSE C_DATA;
END;

****************
WHEN I RAN vSqlStatement alone it is working perfect. I MEAN I
AM GETTING ROWS FORM THAT TABLE.

L_COL AND L_PRAM VARIABLE ARE POPULATED BY BASED ON OTHER TABLE.



THANKS,
SWETHA.