EXECUTE IMMEDIATE - How to use BULK COLLECT INTO with or without Bind Variable

 
--====================================================
• If the dynamic SQL statement is a SELECT statement that returns "Multiple rows" :
    • Use the EXECUTE IMMEDIATE statement with the "BULK COLLECT INTO" clause .
    • Use the "OPEN FOR", "FETCH", and "CLOSE" statements
   
    
--====================================================
• Use the EXECUTE IMMEDIATE statement with the "BULK COLLECT INTO" clause .
--====================================================
 
SELECT ENAME FROM XX_EMP
 
DECLARE
V_SQL VARCHAR2(500);
 
Type  V_EMP_NAME IS TABLE OF VARCHAR2(10); -- Nasted table type collection is declare here --
V_E_NAME      V_EMP_NAME;
 
BEGIN
V_SQL := 'SELECT ENAME FROM XX_EMP';
EXECUTE IMMEDIATE V_SQL BULK COLLECT INTO V_E_NAME;
 
FOR I In 1..V_E_NAME.COUNT  LOOP
Dbms_output.put_line('Employee Name | '||V_E_NAME(I));
END LOOP;
 
END;
 
OUTPUT:-
Employee Name | SMITH
Employee Name | ALLEN
Employee Name | WARD
Employee Name | JONES
Employee Name | MARTIN
Employee Name | BLAKE
Employee Name | SCOTT
Employee Name | TURNER
Employee Name | ADAMS
Employee Name | JAMES
Employee Name | FORD
Employee Name | CLARK
Employee Name | KING
Employee Name | MILLER
Employee Name | Manoj
 
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@   
   
DECLARE
V_SQL VARCHAR2(500);
 
Type  V_EMP_NAME IS TABLE OF VARCHAR2(10);
Type  V_EMP_SAL IS TABLE OF NUMBER;
 
V_E_NAME      V_EMP_NAME;
V_E_SAL        V_EMP_SAL;
 
BEGIN
V_SQL := 'SELECT ENAME, SAL FROM XX_EMP';
EXECUTE IMMEDIATE V_SQL BULK COLLECT INTO V_E_NAME,V_E_SAL;
 
FOR I In 1..V_E_NAME.COUNT  LOOP
Dbms_output.put_line('Employee Name | '||V_E_NAME(I)||' | and its Salary is  |  '||V_E_SAL(I));
END LOOP;
END;
 
OUTPUT:-
Employee Name | SMITH | and its Salary is  |  800
Employee Name | ALLEN | and its Salary is  |  1600
Employee Name | WARD | and its Salary is  |  1250
Employee Name | JONES | and its Salary is  |  2975
Employee Name | MARTIN | and its Salary is  |  1250
Employee Name | BLAKE | and its Salary is  |  2850
Employee Name | SCOTT | and its Salary is  |  3000
Employee Name | TURNER | and its Salary is  |  1500
Employee Name | ADAMS | and its Salary is  |  1100
Employee Name | JAMES | and its Salary is  |  950
Employee Name | FORD | and its Salary is  |  3000
Employee Name | CLARK | and its Salary is  |  2450
Employee Name | KING | and its Salary is  |  5000
Employee Name | MILLER | and its Salary is  |  1300
Employee Name | Manoj | and its Salary is  |  1000
 
 
• If the dynamic SQL statement is a SELECT statement that returns "Multiple rows" :
    • Use the EXECUTE IMMEDIATE statement with the "BULK COLLECT INTO" clause and binbind variables in the "USING" clause.
 
--=========================
Use the EXECUTE IMMEDIATE statement with the "BULK COLLECT INTO" clause and binbind variables in the "USING" clause.
--=========================
 
 
DECLARE
V_SQL VARCHAR2(500);
Type  V_EMP_NAME IS TABLE OF VARCHAR2(100); -- Nasted table type collection is declare here --
 
V_E_NAME      V_EMP_NAME;
 
BEGIN
V_SQL := 'SELECT ENAME FROM XX_EMP WHERE DEPTNO = :DEPARTMENT_NO';
EXECUTE IMMEDIATE V_SQL BULK COLLECT INTO V_E_NAME USING 10;
 
FOR I In 1..V_E_NAME.COUNT  LOOP
Dbms_output.put_line('Employee Name | '||V_E_NAME(I));
END LOOP;
 
END;
 
OUTPUT:-
Employee Name | CLARK
Employee Name | KING
Employee Name | MILLER
 
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@   
   
SELECT ENAME, SAL FROM XX_EMP WHERE DEPTNO = :DEPARTMENT_NO   
 
DECLARE
V_SQL VARCHAR2(500);
Type  V_EMP_NAME IS TABLE OF VARCHAR2(100);
Type  V_EMP_SAL IS TABLE OF NUMBER;
 
V_E_NAME      V_EMP_NAME;
V_E_SAL        V_EMP_SAL;
 
BEGIN
V_SQL := 'SELECT ENAME, SAL FROM XX_EMP WHERE DEPTNO = :DEPARTMENT_NO AND SAL > :SALARY';
EXECUTE IMMEDIATE V_SQL BULK COLLECT INTO V_E_NAME,V_E_SAL USING 10, 1000;
 
FOR I In 1..V_E_NAME.COUNT  LOOP
Dbms_output.put_line('Employee Name | '||V_E_NAME(I)||' | and its Salary is  |  '||V_E_SAL(I));
Dbms_output.put_line('*********************************************************');
END LOOP;
END;
 
OUTPUT:-
Employee Name | CLARK | and its Salary is  |  2450
*********************************************************
Employee Name | KING | and its Salary is  |  5000
*********************************************************
Employee Name | MILLER | and its Salary is  |  1300
*********************************************************
x