ORA-01008: not all variables bound

Cause :- In select statement when we using bind variable same name in more place. There is no problem in run time if we pass one values for this bind variable. But when we using sql statement with in pl/sql block with BULK COLLECT we need to pass value more time for bind variable. Like for 3 times using pass same value in 3 times.

SQL> CONN RND/rnd
Connected.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE RND.DPR_BULK_DATA_TEST
  2  (P_EMP_ID NUMBER,
  3   P_DEPT_ID NUMBER
  4   )
  5  IS
  6  V_SQL VARCHAR2(3000);
  7  TYPE TYP_EMP_ALL IS TABLE OF EMP%ROWTYPE;
  8  REC_EMP_ALL  TYP_EMP_ALL;
  9  BEGIN
 10  V_SQL :='SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID
 11                FROM E
 12                WHERE EMPLOYEE_ID=:TEST AND DEPARTMENT_ID=:2 AND DEPARTMENT_ID=:2';
 13
 14        EXECUTE IMMEDIATE V_SQL  BULK COLLECT
 15        INTO REC_EMP_ALL
 16        USING  P_EMP_ID,     P_DEPT_ID;
 17
 18        FOR I IN REC_EMP_ALL.FIRST .. REC_EMP_ALL.LAST LOOP
 19
 20         INSERT INTO EMP(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
 21         VALUES (REC_EMP_ALL(I).EMPLOYEE_ID, REC_EMP_ALL(I).FIRST_NAME, REC_EMP_ALL(I).LAST_NAME, REC_EMP_ALL(I).EMAIL, REC_EMP_ALL(I).PHONE_NUMBER, REC_EMP_ALL(I).HIRE_DATE, REC_EMP_ALL(I).JOB_ID,
 REC_EMP_ALL(I).SALARY, REC_EMP_ALL(I).COMMISSION_PCT, REC_EMP_ALL(I).MANAGER_ID, REC_EMP_ALL(I).DEPARTMENT_ID);
 22        END LOOP;
 23
 24
 25        FORALL J IN REC_EMP_ALL.FIRST .. REC_EMP_ALL.LAST
 26
 27       INSERT INTO EMP(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
 28         VALUES (REC_EMP_ALL(J).EMPLOYEE_ID, REC_EMP_ALL(J).FIRST_NAME, REC_EMP_ALL(J).LAST_NAME, REC_EMP_ALL(J).EMAIL, REC_EMP_ALL(J).PHONE_NUMBER, REC_EMP_ALL(J).HIRE_DATE, REC_EMP_ALL(J).JOB_ID,
 REC_EMP_ALL(J).SALARY, REC_EMP_ALL(J).COMMISSION_PCT, REC_EMP_ALL(J).MANAGER_ID, REC_EMP_ALL(J).DEPARTMENT_ID);
 29
 30        COMMIT;
 31
 32  END;
 33  /

Procedure created.

SQL> BEGIN
  2  DPR_BULK_DATA_TEST(100,90);
  3  END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-01008: not all variables bound
ORA-06512: at "RND.DPR_BULK_DATA_TEST", line 14
ORA-06512: at line 2


SQL> CREATE OR REPLACE PROCEDURE RND.DPR_BULK_DATA_TEST
  2  (P_EMP_ID NUMBER,
  3   P_DEPT_ID NUMBER
  4   )
  5  IS
  6  V_SQL VARCHAR2(3000);
  7  TYPE TYP_EMP_ALL IS TABLE OF EMP%ROWTYPE;
  8  REC_EMP_ALL  TYP_EMP_ALL;
  9  BEGIN
 10  V_SQL :='SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID
 11                FROM E
 12                WHERE EMPLOYEE_ID=:TEST AND DEPARTMENT_ID=:2 AND DEPARTMENT_ID=:2';
 13
 14        EXECUTE IMMEDIATE V_SQL  BULK COLLECT
 15        INTO REC_EMP_ALL
 16        USING  P_EMP_ID,     P_DEPT_ID, P_DEPT_ID;
 17
 18        FOR I IN REC_EMP_ALL.FIRST .. REC_EMP_ALL.LAST LOOP
 19
 20         INSERT INTO EMP(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
 21         VALUES (REC_EMP_ALL(I).EMPLOYEE_ID, REC_EMP_ALL(I).FIRST_NAME, REC_EMP_ALL(I).LAST_NAME, REC_EMP_ALL(I).EMAIL, REC_EMP_ALL(I).PHONE_NUMBER, REC_EMP_ALL(I).HIRE_DATE, REC_EMP_ALL(I).JOB_ID,
 REC_EMP_ALL(I).SALARY, REC_EMP_ALL(I).COMMISSION_PCT, REC_EMP_ALL(I).MANAGER_ID, REC_EMP_ALL(I).DEPARTMENT_ID);
 22        END LOOP;
 23
 24
 25        FORALL J IN REC_EMP_ALL.FIRST .. REC_EMP_ALL.LAST
 26
 27       INSERT INTO EMP(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
 28         VALUES (REC_EMP_ALL(J).EMPLOYEE_ID, REC_EMP_ALL(J).FIRST_NAME, REC_EMP_ALL(J).LAST_NAME, REC_EMP_ALL(J).EMAIL, REC_EMP_ALL(J).PHONE_NUMBER, REC_EMP_ALL(J).HIRE_DATE, REC_EMP_ALL(J).JOB_ID,
 REC_EMP_ALL(J).SALARY, REC_EMP_ALL(J).COMMISSION_PCT, REC_EMP_ALL(J).MANAGER_ID, REC_EMP_ALL(J).DEPARTMENT_ID);
 29
 30        COMMIT;
 31
 32  END;
 33  /

Procedure created.

SQL>  BEGIN
  2   DPR_BULK_DATA_TEST(100,90);
  3   END;
  4  /

PL/SQL procedure successfully completed.

SQL>