PL/SQL tutorial 78: PL/SQL FORALL statement with INDICES OF clause in Oracle Database

------------------------------------------------------------------------
►►►LINKS◄◄◄

Bolg is online but you can still learn what is a DBMS

Previous Tutorial
► FORALL with Lower & Upper Bound

►PL/SQL FOR LOOP

►SELECT-INTO statement

►Nested Table

-------------------------------------------------------------------------
►Make sure you SUBSCRIBE and be the 1st one to see my videos!
-------------------------------------------------------------------------
►►►Find me on Social Media◄◄◄
Follow What I am up to as it happens on

___Facebook Official Page of Manish Sharma___

You can also Email me at
for E-mail address please check the About section

Please please LIKE and SHARE my videos it makes me happy.
Thanks for liking, commenting, sharing and watching more of our videos

♥ I LOVE ALL MY VIEWERS AND SUBSCRIBERS

[nalinaksheepanda]

To answer your question in this video, we can place the select count before the forall, First time there will be no record in the table. So we can check for the condiion, IF tot_rec = 0, Then insert the record, Else print you have already inserted some records.

[MohsinKhan-xbhe]

Thank you for all your videos!! I have learnt a lot of PL/SQL things through your channel, please upload a video on Pragma Autonomous_Transaction.

[Jonathancasado]

I just have to add a Primary Key contraint to the column of the table.

[swapnilp]

Hi Manish sir,
Thank you so much for your awesome work of providing us short and crispy videos on SQL and PL/SQL topics. These are extremely helpful in learning lot of concepts.

To answer your question, we can take a help of implicit cursor -> SQL%rowcount.
This will give us a count of affected rows only (instead of total count of records in a table).

Code will be as below-

DECLARE
TYPE my_nested_table IS TABLE OF number;
var_nt my_nested_table := my_nested_table (9, 18, 27, 36, 45, 54, 63, 72, 81, 90);
--Another variable for holding total number of record stored into the table
tot_rec NUMBER;
BEGIN
var_nt.DELETE(3, 7);

FORALL idx IN INDICES OF var_nt
INSERT INTO tut_78 (mul_tab) VALUES (var_nt(idx));

tot_rec:= SQL%rowcount;
dbms_output.put_line('Total records inserted are '||tot_rec);

--SELECT COUNT (*) INTO tot_rec FROM tut_78;
--DBMS_OUTPUT.PUT_LINE ('Total records inserted are '||tot_rec);
END;
/

[PuiuDR]

you do a select count before forall, then another after forall, then you subtract the first count from the final count and you have the ammount of rows insterted

[naveenragam]

Hi Manish...what if a collection is of varchar2 dataype and the collection is having two variables storing the string data...How to insert this collection data into another table using forall?...can you please explain

[nalinaksheepanda]

Is there any SQL playlist in your channel? If so, please provide the link.

[thesitedemo]

SET SERVEROUTPUT ON;
DECLARE
TYPE my nested_table IS TABLE OF number;
var_nt my_nested_table= my_nested_table (9, 18, 27, 36, 45, 54, 63, 72, 81, 90);
tot rec NUMBER;
BEGIN
var_nt.DELETE (3, 6); -- delete items from array index 3 to 6
FORALL idx IN INDICES OF var_nt
END;
/
INSERT INTO tut_78 (mul_tab) VALUES (var_nt (idx));
SELECT count (*) INTO tot_rec FROM tut_78;
DBMS_OUTPUT.PUT_LINE ('Total records inserted are '||tot_rec);