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!

to insert collection data

681359Aug 2 2009 — edited Aug 3 2009
hi All,

I have created this procedure to get data from emp table and insert into dept table using associative array and record type, but i am getting following errror, i am sending code and error, hopefully it would help to understand where i am going wrong.
DECLARE

	TYPE EMP_RECORD IS RECORD
		(
		EMP_P_ID  	EMP.P_ID%TYPE,
		 EMP_FIRSTNAME	EMP.FIRSTNAME%TYPE,
		 EMP_ADDRESS	EMP.ADDRESS%TYPE
		);


	TYPE TB_ARRAY IS TABLE OF EMP_RECORD
	INDEX BY BINARY_INTEGER;
		
		ARRAY TB_ARRAY;

CURSOR CUR_EMP IS
	SELECT P_ID, FIRSTNAME, ADDRESS
 	FROM EMP;

BEGIN

	OPEN CUR_EMP;

	LOOP

		FETCH CUR_EMP BULK COLLECT INTO ARRAY LIMIT 10;

			FORALL I IN 1..ARRAY.COUNT
			INSERT INTO DEPT (P_ID, FIRSTNAME, ADDRESS)
			VALUES (ARRAY(I.EMP_P_ID), ARRAY(I.EMP_FIRSTNAME),
			ARRAY(I.EMP_ADDRESS));

			--DBMS_OUTPUT.PUT_LINE(ARRAY(I.EMP_P_ID));

		EXIT WHEN CUR_EMP%NOTFOUND;

	END LOOP;

	CLOSE CUR_EMP;

END; 

/
VALUES (ARRAY(I.EMP_P_ID), ARRAY(I.EMP_FIRSTNAME),
*
ERROR at line 30:
ORA-06550: line 30, column 18:
PLS-00487: Invalid reference to variable 'I'
ORA-06550: line 30, column 12:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 29, column 4:
PL/SQL: SQL Statement ignored

Best Regards,

Lee
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 31 2009
Added on Aug 2 2009
4 comments
556 views