Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.5K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 401 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
How do you automatically initialise collections?

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
Best Answer
-
if c_emp is not nullindex by pls_integer
EDIT: returning initializes the collection even in the absence of deleted rows.
Answers
-
if c_emp is not nullindex by pls_integer
EDIT: returning initializes the collection even in the absence of deleted rows.
-
H, I can reproduce that with your code. Are you sure that you have 3 similar tables, employee, emp and t_emp?
-
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.
-
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
My mistake. All tables are emp. ...... not employee
Thanks that did the trick.
TYPE t_emp IS TABLE OF employee%ROWTYPE INDEX BY PLS_INTEGER;
-
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.
-
'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;
-
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.
-
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