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....
