PL/SQL Dynamic SQL with Collections

Example 1:

CREATE OR REPLACE PROCEDURE proc_dynamicsql_collection(source_tab VARCHAR2,

                                                       dest_tab   VARCHAR2,

                                                       comp_col   VARCHAR2) IS

  cur_statement VARCHAR2(2000);

  p_str         VARCHAR2(4000);

BEGIN

  cur_statement := 'SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY FROM ' ||

                   source_tab || ' WHERE SYSDATE - ' || comp_col || ' > 60';

  p_str := 'DECLARE

  err_code      NUMBER;

  err_msg       VARCHAR2(1000);

  TYPE tab_cur IS TABLE OF emp_test%ROWTYPE INDEX BY PLS_INTEGER;

  lv_tab_cur tab_cur;

  TYPE cur_source_tab IS REF CURSOR;

  ref_cur cur_source_tab;

BEGIN

  OPEN ref_cur FOR ' || cur_statement || ';

  LOOP

    FETCH ref_cur BULK COLLECT

      INTO lv_tab_cur LIMIT 20;

    EXIT WHEN lv_tab_cur.count = 0;

    FORALL i IN lv_tab_cur.first .. lv_tab_cur.last ';

  p_str := p_str || '

      INSERT INTO ' || dest_tab || '

      VALUES

        (lv_tab_cur(i).employee_id,

         lv_tab_cur(i).first_name,

         lv_tab_cur(i).last_name,

         lv_tab_cur(i).email,

         lv_tab_cur(i).phone_number,

         lv_tab_cur(i).hire_date,

         lv_tab_cur(i).job_id,

         lv_tab_cur(i).salary);

  END LOOP;

  COMMIT;

  CLOSE ref_cur;

EXCEPTION

  WHEN OTHERS THEN

    err_code := SQLCODE;

    err_msg  := SQLERRM;

    dbms_output.put_line(dbms_utility.format_error_backtrace() || err_code ||

                         '' | '' || err_msg);

END;

';

  dbms_output.put_line(p_str);

  EXECUTE IMMEDIATE p_str;

END;

Example 2:

DECLARE

  p_str   LONG;

  d_table VARCHAR2(30) := 'MYEMP';

BEGIN

  DELETE FROM myemp;

  p_str := 'Declare type l_table is table of emp%rowtype;';

  p_str := p_str || ' p_table l_table := l_table();';

  p_str := p_str || ' Begin select * bulk collect into p_table from emp;';

  p_str := p_str || ' forall i in 1..p_table.count';

  p_str := p_str || ' insert into ' || d_table ||' values p_table(i); commit; end;';

  EXECUTE IMMEDIATE p_str;

END;

Example 3:

DECLARE

  TYPE t_emp_id_tab IS TABLE OF emp.employee_id%TYPE;

  l_tab t_emp_id_tab;

  l_table_name VARCHAR2(30) := 'EMP';

BEGIN

  -- Populate collection use in forall.

  SELECT e.employee_id

    BULK COLLECT

    INTO l_tab

    FROM emp e

   WHERE e.employee_id BETWEEN 100 AND 113

   ORDER BY e.employee_id;

  FORALL i IN l_tab.first .. l_tab.last EXECUTE IMMEDIATE

                             'UPDATE ' || l_table_name ||

                             ' SET  employee_id = employee_id / 100 WHERE  employee_id = :1'

                             USING l_tab(i)

    ;

    COMMIT;

END;

Example 4:

DECLARE

  TYPE t_emp_id_tab IS TABLE OF EMP_TEST.EMPLOYEE_ID%TYPE;

  l_tab  t_emp_id_tab;

  l_table_name VARCHAR2(30) := 'EMP_TEST';

BEGIN

  EXECUTE IMMEDIATE

    'DELETE FROM  ' || l_table_name ||' RETURNING employee_id  INTO :1'

     RETURNING BULK COLLECT INTO l_tab;

  DBMS_OUTPUT.put_line('Deleted IDs : ' || l_tab.count || ' rows');

  ROLLBACK;

END;

Example 5:

  

DECLARE

  TYPE t_emp_id_tab IS TABLE OF EMP_TEST.EMPLOYEE_ID%TYPE;

  l_in_tab  t_emp_id_tab;

  l_out_tab t_emp_id_tab;

  l_table_name VARCHAR2(30) := 'EMP_TEST';

BEGIN

  -- Populate collection use in forall.

  EXECUTE IMMEDIATE 'SELECT e.employee_id

     FROM ' || l_table_name ||' e WHERE e.employee_id BETWEEN 100 AND 113' BULK COLLECT INTO l_in_tab;

  FORALL i IN l_in_tab.first .. l_in_tab.last

  EXECUTE IMMEDIATE 'DELETE FROM ' || l_table_name ||'  WHERE employee_id = :1 RETURNING employee_id INTO :2'

  USING l_in_tab(i) RETURNING BULK COLLECT INTO l_out_tab;

  dbms_output.put_line('Starting IDs : ' || l_in_tab.count || ' rows');

  dbms_output.put_line('Deleted IDs  : ' || l_out_tab.count || ' rows');

  ROLLBACK;

END;

I hope you all have enjoyed reading this article. Comments are welcome....