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