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.

Oracle Bulk Collect, Forall and cursor rowtype example

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;

You can execute it as below:

SET SERVEROUTPUT ON;

BEGIN
IF f_currowtype
THEN
DBMS_OUTPUT.put_line ('Success');
ELSE
DBMS_OUTPUT.put_line ('Failed');
END IF;
END;