Skip to Main Content
Forums

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!

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

User_H3J7U
Answer

~if c_emp is not null~
~index by pls_integer~
EDIT: returning initializes the collection even in the absence of deleted rows.

Marked as Answer by carl0021 · Nov 22 2021
Anton Scheffer

H, I can reproduce that with your code. Are you sure that you have 3 similar tables, employee, emp and t_emp?

BEDE

Do you really need to collect those id-s into a collection?
If not, then use sql%rowcount, like below:
dbms_output.put_line (sql%rowcount||' rows deleted');
Or, if you must have a collection, you may check the number of elements in the collection like below:
if c_emp.count>0 then
dbms_output.put_line (c_emp.count||' rows deleted');
for i in c_emp.first..c_emp.last loop
dbms_output.put_line(c_emp(i).id); --- this should output the ID that was deleted
end loop;
end if;
Anyway, it doesn't seem to me a good idea looping through the collection just to output for each collection element only "rows found".
Then, your delete has no where clause, which means all the rows in that table are to be deleted, which, in case there are many rows will give an out of memory error. That is why I'd avoid this bulk collect thing.

carl0021

@bede
I think you are completely missing the point. The simplified snippet of code in not about me mistakenly deleting all rows in a very large table or inefficiently outputting rows found.
Also c_emp.count or c_emp.first..c_emp.last will fail with ORA-06531 if c_emp is not initialised
@ascheffer
My mistake. All tables are emp. ...... not employee

@User_H3J7U
Thanks that did the trick.
TYPE t_emp IS TABLE OF employee%ROWTYPE INDEX BY PLS_INTEGER;

Solomon Yakobson

What trick? Returning always initializes collection regardless if it is nested table, varray or associative array:

SQL> CREATE TABLE empty_table(id NUMBER, fname VARCHAR2(50));

Table created.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2   TYPE t_emp IS TABLE OF empty_table%ROWTYPE;
  3   c_emp t_emp;
  4  BEGIN
  5   DELETE  FROM empty_table
  6    RETURNING id, fname BULK COLLECT INTO c_emp;
  7   FOR i IN 1..c_emp.COUNT LOOP
  8    dbms_output.put_line (' rows found');
  9   END LOOP;
 10  END;
 11  /


PL/SQL procedure successfully completed.


SQL> DECLARE
  2   TYPE t_emp IS VARRAY(10) OF empty_table%ROWTYPE;
  3   c_emp t_emp;
  4  BEGIN
  5   DELETE  FROM empty_table
  6    RETURNING id, fname BULK COLLECT INTO c_emp;
  7   FOR i IN 1..c_emp.COUNT LOOP
  8    dbms_output.put_line (' rows found');
  9   END LOOP;
 10  END;
 11  /


PL/SQL procedure successfully completed.


SQL> DECLARE
  2   TYPE t_emp IS TABLE OF empty_table%ROWTYPE INDEX BY PLS_INTEGER;
  3   c_emp t_emp;
  4  BEGIN
  5   DELETE  FROM empty_table
  6    RETURNING id, fname BULK COLLECT INTO c_emp;
  7   FOR i IN 1..c_emp.COUNT LOOP
  8    dbms_output.put_line (' rows found');
  9   END LOOP;
 10  END;
 11  /


PL/SQL procedure successfully completed.


SQL>

SY.

carl0021

'Trick' was just a figure of speech.
I did not realise RETURNING will initialise collection when the the sql returned no rows. ... writing a snippet without preceeding SQL this is what threw me off.
DECLARE
TYPE t_emp IS TABLE OF emp%ROWTYPE;
c_emp t_emp;
BEGIN
FOR i IN 1..c_emp.COUNT LOOP
dbms_output.put_line (' rows found');
END LOOP;
END;

Solomon Yakobson

In fact, I wish Oracle would introduce APPEND in RETURNING/INTO/BULK COLLECT... so we could append selected data to existing collection instead of initializing it.
SY.

1 person found this helpful
RogerT

That's where cardinality does the trick.
Cardinality can be used on a collection that has not been initialized as well as on a initialized one. If the collection was not initialized and cardinality therefor returns NULL this can be handled using NVL or COALESCE.

DECLARE
   TYPE t_emp IS TABLE OF emp%ROWTYPE;
   c_emp t_emp;
BEGIN
   FOR i IN 1..nvl(cardinality(c_emp),0) LOOP
      dbms_output.put_line (' rows found');
   END LOOP;
END;

hth

1 - 8

Post Details

Added on Nov 22 2021
8 comments
110 views
1 mention