Forum Stats

  • 3,817,447 Users
  • 2,259,334 Discussions
  • 7,893,776 Comments

Discussions

Categories

How do you automatically initialise collections?

carl0021
carl0021 Member Posts: 31 Blue Ribbon
in SQL & PL/SQL


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

· Share on TwitterShare on Facebook

Best Answer

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy
    edited Nov 22, 2021 11:26AM Answer ✓
    1. if c_emp is not null
    2. index by pls_integer

    EDIT: returning initializes the collection even in the absence of deleted rows.

    · Share on TwitterShare on Facebook
  • Anton Scheffer
    Anton Scheffer Member Posts: 1,950 Gold Trophy

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

    · Share on TwitterShare on Facebook
  • BEDE
    BEDE Oracle Developer Member Posts: 2,405 Gold Trophy

    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.

    · Share on TwitterShare on Facebook
  • carl0021
    carl0021 Member Posts: 31 Blue Ribbon

    @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;

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,336 Red Diamond

    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.

    · Share on TwitterShare on Facebook
  • carl0021
    carl0021 Member Posts: 31 Blue Ribbon
    edited Nov 22, 2021 12:33PM


    '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;

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,336 Red Diamond
    edited Nov 22, 2021 12:37PM

    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.

    BEDE
    · Share on TwitterShare on Facebook
  • RogerT
    RogerT Member Posts: 1,857 Gold Trophy

    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

    · Share on TwitterShare on Facebook