Oracle Bulk Collect Example Using Cursor Rowtype Type Object
In this post I am giving an example to perform an insert into using Oracle Bulk Collect and FORALL command. Created a PL SQL function which will take data from EMP table using a cursor then creates type array variable of that cursor row type and then it bulk collect and insert into BONUS table using FORALL.
Below is the example:
CREATE OR REPLACE FUNCTION f_currowtype
RETURN BOOLEAN
IS
CURSOR c_emp
IS
SELECT empno,
ename,
job,
sal
FROM emp;
TYPE t_emp IS TABLE OF c_emp%ROWTYPE;
e_rec t_emp;
BEGIN
OPEN c_emp;
FETCH c_emp
BULK COLLECT INTO e_rec;
CLOSE c_emp;
FORALL i IN e_rec.FIRST .. e_rec.LAST
insert into bonus (empno, amount)
values (e_rec(i).empno, e_rec(i).sal * 10 / 100);
DBMS_OUTPUT.put_line (‘Rows inserted: ‘ || SQL%ROWCOUNT);
COMMIT;
RETURN TRUE;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
RETURN FALSE;
END;[/code]
You can execute it as below:
BEGIN
IF f_currowtype
THEN
DBMS_OUTPUT.put_line ('Success');
ELSE
DBMS_OUTPUT.put_line ('Failed');
END IF;
END;
