• Post author:
  • Post category:PLSQL / SQL
  • Reading time:2 mins read
You are currently viewing Select Bulk Collect into Oracle Example

In the previous blog post, I have given an example to use Oracle Bulk Collect Using Cursor Rowtype Type Object. Which first creates the cursor then type object of cursor type and then open the cursor, fetching into bulk collect and then finally process them using Forall. But sometimes you may be required to prepare some data for just one time with a temporary anonymous PLSQL block, and you don't want to write too much code to perform that task. For this kind of scenarios, you can use Select Bulk Collect into an option to perform any task quickly without wasting too much time to write code.

Below is the example of PLSQL block which will take the records from EMP table and update the BONUS table and also prints on the screen.

SET SEVEROUTPUT ON;
DECLARE
TYPE t_emp IS TABLE OF emp%ROWTYPE;

e_rec   t_emp;
BEGIN
SELECT *
BULK COLLECT INTO e_rec
FROM emp;

FORALL i IN e_rec.FIRST .. e_rec.LAST
update bonus set amount = e_rec(i).sal * 15 / 100
where empno = e_rec(i).empno;

Commit;

FOR i IN e_rec.FIRST .. e_rec.LAST
LOOP
DBMS_OUTPUT.
put_line (
'Bonus Updated For Employee: '
|| e_rec (i).ename
|| ' '
|| 'Bonus: '
|| e_rec(i).sal * 15 / 100);
END LOOP;
END;
Select bulk collect into example Oracle

 

Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of foxinfotech.in and orclqa.com a question and answer forum for developers.

This Post Has 2 Comments

  1. Mauricio

    Thank you so much for this so great and useful guide to use BULK COLLECT and FORALL.

    I've wanted to learn how to use for make massive updates, and now I know how to use thanks to you.

    I just have a question... I'm updating 86000 records with all columns for VARCHAR type, It's normal that my BULK COLLECT spends 20 minutes to do that? What am I do wrong?

    God bless you and greetings from vzla.

    1. Vinish Kapoor

      Actually, 20 minutes time is too much for just 86000 rows.

      If you can share a little of your code then maybe I can help you. Or ask it with more details at my Q&A forum orclqa.com.

      Please let me know.

Comments are closed.