Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How do you automatically initialise collections?

carl0021Nov 22 2021

With the scenario below, the FOR LOOP will fail (... collection initialisation ...) if there are no records to delete.
What is the best way to deal with this without changing the delete-bulk collect statement?
... apart from wrapping the for loop in an exception block...

CREATE TABLE t_emp(id NUMBER, fname VARCHAR2(50));
DECLARE
TYPE t_emp IS TABLE OF employee%ROWTYPE;
c_emp t_emp;
BEGIN
DELETE FROM emp
RETURNING id, fname BULK COLLECT INTO c_emp;
FOR i IN 1..c_emp.COUNT LOOP
dbms_output.put_line (' rows found');
END LOOP;
END;

Thanks

This post has been answered by User_H3J7U on Nov 22 2021
Jump to Answer

Comments

Post Details

Added on Nov 22 2021
8 comments
105 views