Thanks for the question, Don.

Asked: February 21, 2017 - 6:57 pm UTC

Last updated: September 07, 2020 - 2:15 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

I need to build a procedure that will accept a collection of numbers that I need to then find matches in a table. If the elements of the collection were in another table, then it would be a simple case to join the tables. How can I accomplish this with a collection?

create or replace type numberList 
    is table of number;
/

create table COLLECTION_TEST (
    I number, 
    J number, 
    A varchar2(100) 
    );
/

insert into COLLECTION_TEST 
    (I, J, A) 
select 1, 1, 'Type One1' from dual
union
select 1, 2, 'Type One2' from dual
union
select 2, 1, 'Type Two1' from dual
union
select 2, 2, 'Type Two2' from dual
union
select 3, 1, 'Type Three1' from dual
union
select 3, 3, 'Type Three3' from dual
;
commit; 
/

declare 
    COLLECTION_TEST2 numberList;
begin 
    COLLECTION_TEST2 := numberList(); 
    
    COLLECTION_TEST2.extend;
    COLLECTION_TEST2(1) := 1;
    COLLECTION_TEST2.extend;
    COLLECTION_TEST2(2) := 2; 
    
/*
    select ct.A 
        collect or bulk collect into a collection 
    from COLLECTION_TEST ct 
    join COLLECTION_TEST2 ct2 
        on ct2 = ct.j;
*/
end;
/

drop table COLLECTION_TEST;
/

Note that the numberList can have 1 to N elements, and the join in the comment is just for illustration.

Thanx in advance,
Don

with LiveSQL Test Case:

https://livesql.oracle.com/apex/livesql/file/content_ELZXRGB53EWH8UBMXJCKLA1AO.html

and Connor said...

Awesome test case...makes our life so much easier !

And you're incredibly close to the answer already. The TABLE clause lets you "relational-ise" a nested table collection

SQL> create or replace type numberList
  2      is table of number;
  3  /

Type created.

SQL>
SQL> create table COLLECTION_TEST (
  2      I number,
  3      J number,
  4      A varchar2(100)
  5      );

Table created.

SQL>
SQL> insert into COLLECTION_TEST
  2      (I, J, A)
  3  select 1, 1, 'Type One1' from dual
  4  union
  5  select 1, 2, 'Type One2' from dual
  6  union
  7  select 2, 1, 'Type Two1' from dual
  8  union
  9  select 2, 2, 'Type Two2' from dual
 10  union
 11  select 3, 1, 'Type Three1' from dual
 12  union
 13  select 3, 3, 'Type Three3' from dual
 14  ;

6 rows created.

SQL> commit;

Commit complete.

SQL> /

Commit complete.

SQL>
SQL> set serverout on
SQL> declare
  2      n numberList;
  3  begin
  4      n := numberList();
  5
  6      n.extend;
  7      n(1) := 1;
  8      n.extend;
  9      n(2) := 2;
 10
 11      for i in (
 12      select ct.A
 13      from COLLECTION_TEST ct
 14      join table(n) ct2
 15          on ct2.column_value = ct.j
 16      )
 17      loop
 18        dbms_output.put_line(i.a);
 19      end loop;
 20
 21  end;
 22  /
Type One1
Type One2
Type Two1
Type Two2
Type Three1

PL/SQL procedure successfully completed.

Rating

(11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Don Simpson, February 22, 2017 - 11:58 pm UTC

That flat out rocks!

February 23, 2017 - 2:53 am UTC

:-)

Jess, November 15, 2018 - 2:10 pm UTC

Hi Connor,
Is this doable without a loop?

I'd like to do delete from a table where id matches values in a collection (more of an 'in' than a 'join' but still). For example:

DECLARE
  TYPE t_my_type IS TABLE OF t1.col1%TYPE;
  v_coll_tab t_my_type;

BEGIN
  delete from t1 where <xyz>
  RETURNING col1 BULK COLLECT INTO v_coll_tab;

  delete from t2 where col1 in
    (select ct2.??? from table(v_lectid_tab) ct2);

  commit;

END;
/

Is there a way to indicate the column? Thank you!

November 15, 2018 - 5:46 pm UTC

You can use forall:

DECLARE
  TYPE t_my_type IS TABLE OF t1.col1%TYPE;
  v_coll_tab t_my_type;

BEGIN
  delete from t1 where <xyz>
  RETURNING col1 BULK COLLECT INTO v_coll_tab;

  forall i in 1 .. v_coll_tab.count
    delete from t2 
    where col1 = v_coll_tab(i).id;

  commit;

END;
/

Jess, November 16, 2018 - 12:27 am UTC

Oh that's a really neat trick! Thanks Chris, this is super handy!

November 16, 2018 - 3:33 pm UTC

Yep, if you want to know more about this, read:

https://blogs.oracle.com/oraclemagazine/bulk-processing-with-bulk-collect-and-forall

Jess, March 11, 2019 - 6:22 pm UTC

Hi guys,

I am trying to combine the original answer with the subsequent responses, but running into a "local collection types not allowed in SQL statements" error...

I have a bit of PL/SQL code that returns several strings from a cursor. I opted to store them in a nested table like so:

declare
   type t_object_names is table of varchar2(30);
   v_tables t_object_names := t_object_names();
begin
...
   v_tables.extend;
   v_tables(v_tables.last) := 'A'; -- actual variable value is assigned in a loop;
   v_tables.extend;
   v_tables(v_tables.last) := 'B'; -- actual variable value is assigned in a loop;
...

This works, and I can print the nested table and see correct results.
What I want to do is select from a table where columnA contains these values and return columnB into a collection (presumably also a nested table since I only need the returned values).

Can't get it to work.... My original thinking was going without a collection and putting the values into a string and passing it into a cursor to use in "in", but that produces no results (suspecting it's being treated as 1 value rather than a list of values).

Tried declaring a second nested table and doing something like this

forall i in 1 ..v_tables.count
   select myTab.colB from myTab, table(v_tables) tbl 
   where myTab.colA = tbl.column_value; -- into v_second_nested_table;

but it's failing with "local collection types not allowed in SQL statements" and "cannot access rows from a non-nested table item".

What's the right way of doing something like this (i.e., putting some values into a collection, joining collection to a table, and selecting values from a column for matching rows into another collection)? Or is there a different way of passing a list to a cursor where it would treat it correctly in an "in"?

Thank you as always!

March 13, 2019 - 2:42 pm UTC

You can't use forall with select...

Jess, March 11, 2019 - 6:44 pm UTC

realized the for all mistake...

select myTab.colB bulk collect into v_triggers from myTab; --, table(v_tables) tbl where myTab.colA = tbl.column_value;

fine against the table, but trying to join to the collection is back go "local collection types not allowed in SQL statements"...

March 13, 2019 - 2:42 pm UTC

So... are you still stuck on this? If so, what exactly is your complete example?

Returning

Lolo, March 13, 2019 - 6:11 pm UTC

From tour code above :

delete from t1 where <xyz> RETURNING col1 BULK COLLECT INTO v_coll_tab;

Q. My requirements is to got something like Returning DISTINCT COL1 bulk collect ...etc.

How ?

March 15, 2019 - 8:55 am UTC

Nope:

create table t as 
  select level c1, mod ( level, 2 ) c2 
  from   dual
  connect by level <= 100;
  
declare
  vals dbms_sql.number_table;
begin
  delete t
  returning distinct c2
  bulk collect into vals;
end;
/
ORA-06550: line 5, column 13:
PL/SQL: ORA-00936: missing expression

Making it distinct

Peter G, March 15, 2019 - 4:35 pm UTC

Maybe using a schema level collection will do the trick, something like this:

create table t as 
  select level c1, mod ( level, 2 ) c2 
  from   dual
  connect by level <= 100;
  
declare
  --vals dbms_sql.number_table;
  vals sys.odcinumberlist;
begin
  delete t
  returning c2
  bulk collect into vals;
  
  vals := SET(vals);
end;
/

March 15, 2019 - 4:46 pm UTC

Yep, that's a way to do it!

Jess, August 30, 2020 - 6:34 am UTC

Hi Connor and Chris,

What is the most efficient way of making forall work with dynamic sql?
Say we have a list of tables and IDs that needs deleting that's not being hardcoded:

create table t1 (id number);
create table t2 (id number);
create table meta (tab_name varchar2(10), pk_column varchar2(10));
create table myids (id number);

insert into t1 values (1, null); 
insert into t1 values (2, null);
insert into t1 values (3, null);
insert into t1 values (4, null);
insert into t2 values (2, null);
insert into t2 values (3, null);
insert into meta values ('t1', 'id');
insert into meta values ('t2', 'id');
insert into myids values (1);
insert into myids values (2);

The idea is to grab everything, shove into collections and then execute a delete such that we delete records n all tables in meta where id matches what's in myids (meaning we'd delete ids 1 and 2 in t1 and id 2 in t2).

Originally we were just going to do
-- select from meta into v_meta, then loop through it and for every table value
v_sql := 'delete from ' || v_meta.tab_name || ' where ' || v_meta.col_name ' in...

but a). weren't quite sure how to tie it all together so a table gets done in one go rather than one id at a time, and
b). because you could have errors on delete (say fk constraint), we wanted to go the blk collect route, so we could save exceptions, but that complicated it even more, and now we're completely stuck.

Would you be able to help make this mess sensible and efficient?
Thank you!

August 31, 2020 - 4:26 am UTC

Maybe I'm losing something here but isn't this just:


SQL>
SQL>
SQL> create table t1 (id number);

Table created.

SQL> create table t2 (id2 number);

Table created.

SQL> create table meta (tab_name varchar2(10), pk_column varchar2(10));

Table created.

SQL> create table myids (id number);

Table created.

SQL>
SQL> insert into t1 values (1);

1 row created.

SQL> insert into t1 values (2);

1 row created.

SQL> insert into t1 values (3);

1 row created.

SQL> insert into t1 values (4);

1 row created.

SQL> insert into t2 values (2);

1 row created.

SQL> insert into t2 values (3);

1 row created.

SQL> insert into meta values ('t1', 'id');

1 row created.

SQL> insert into meta values ('t2', 'id2');

1 row created.

SQL> insert into myids values (1);

1 row created.

SQL> insert into myids values (2);

1 row created.

SQL>
SQL> select 'delete from '||tab_name||' where '||pk_column||' in ( select id from myids);'
  2  from meta;

'DELETEFROM'||TAB_NAME||'WHERE'||PK_COLUMN||'IN(SELECTIDFROMMYIDS);
-------------------------------------------------------------------
delete from t1 where id in ( select id from myids);
delete from t2 where id2 in ( select id from myids);

SQL>
SQL>

Jess, August 31, 2020 - 8:46 am UTC

Hi Connor,

I think I was less clear than I thought. Although we're probably overcomplicating it too!

The counts of records deleted from some of the tables are in the millions, which is why we thought we'd select from "myids" into a cursor/variable and drive the bulk collect off it (plus we need to save exception records but continue processing).. So there would be an outer loop of table names and an inner loop of updates (updating each table, say, 200K records at a time).

That's why it wasn't just create dynamic sql string and run it stand-alone.
Given that's what we're after, we're struggling with the right syntax to do that...

September 02, 2020 - 1:12 am UTC

It will just be case of building the appropriate anonymous block dynamically.

For example, I can extend mine to loops, eg

SQL> select
  2    'begin '||chr(10)||
  3    'loop '||chr(10)||
  4    'delete from '||tab_name||' where '||pk_column||chr(10)||
  5    ' in ( select id from myids) and rownum <= 100000;'||chr(10)||
  6    'exit when sql%rowcount < 100000; '||chr(10)||
  7    'commit;'||chr(10)||
  8    'end loop;'||chr(10)||
  9    'commit;'||chr(10)||
 10    'end;' anon_blk
 11  from meta;

ANON_BLK
------------------------------------------------------------------------------
begin
loop
delete from t1 where id
 in ( select id from myids) and rownum <= 100000;
exit when sql%rowcount < 100000;
commit;
end loop;
commit;
end;

begin
loop
delete from t2 where id2
 in ( select id from myids) and rownum <= 100000;
exit when sql%rowcount < 100000;
commit;
end loop;
commit;
end;

A quick "hack" to constructing these things is to write a *static* version of what you want and then just substitute in variables. For example, rather than "build" the string like I did above, I could do this:

SQL> variable the_block varchar2(1000);
SQL> begin
  2    :the_block :=
  3  'begin
  4     loop
  5       delete from @@TAB@@
  6       where @@COL@@ in
  7         ( select id
  8           from myids)
  9       and rownum <= 100000;
 10       exit when sql%rowcount < 100000;
 11       commit;
 12    end loop;
 13    commit;
 14  end;';
 15  end;
 16  /

PL/SQL procedure successfully completed.

SQL>
SQL> select
  2    replace(replace(:the_block,'@@TAB@@',tab_name),'@@COL@@',pk_column) x
  3  from meta;

X
----------------------------------------------------------------------------------------
begin
   loop
     delete from t1
     where id in
       ( select id
         from myids)
     and rownum <= 100000;
     exit when sql%rowcount < 100000;
     commit;
  end loop;
  commit;
end;

begin
   loop
     delete from t2
     where id2 in
       ( select id
         from myids)
     and rownum <= 100000;
     exit when sql%rowcount < 100000;
     commit;
  end loop;
  commit;
end;

Much easier than concatenating strings etc

Jess, August 31, 2020 - 6:47 pm UTC

This is a full example of the logic

create table ids (id number);
create table tabs (tab_name varchar2(30));
create table records (id number, col2 number);

insert into ids values (1);
insert into ids values (2);
insert into ids values (3);
insert into tabs values ('RECORDS');
insert into records values (1, 1);
insert into records values (2, 1);
insert into records values (3, 1);
insert into records values (4, 1);

create type typ_my_ids is table of number;

declare
    v_ids_tab typ_my_ids 
    v_sql varchar2(1000);
    cursor cur_ids is select id from ids;
    cursor cur_tabs is select tab_name from tabs;

begin
    open cur_ids 
    loop
    fetch cur_ids bulk collect into v_ids_tab limit 2;
    exit when v_ids_tab .count = 0;

    for i in cur_tabs loop

        delete from records where exists (select null from table(v_ids_tab) e where e.column_value = records.id);
    
        --v_sql := 'delete from ' || i.tab_name || ' x where exists (select null from table(' || v_ids_tab || ') e where e.column_value = x.id)';
        -- dbms_output.put_line(v_sql);
        -- execute immediate v_sql;
        dbms_output.put_line('deleted ' || sql%rowcount);

    end loop; -- of table names    
    end loop; -- of ids
end;
/

The straight up delete obviously works, and the code above runs.
However, we want to have a collection of IDs and a collection of tables.
Then for every table in cursor, delete from it in bulk (by 'limit' chunks).
Can't get converting that to dynamic sql to work, "v_ids_tab" is the wrong type of course...
How does one do this kind of thing?

(By the way, the code above is the reverse of the ideal logic because it grabs all IDs first and deletes from each table.
But swapping the loops around like so

    for rec in cur_tabs loop
        open cur_ids;
        loop
        fetch cur_ids bulk collect into v_ids_tab limit 2;
        exit when v_ids_tab.count = 0;
           <delete>
      end loop;
  end loop;

fails with "attempt made to open already open cursor", so doubting it's doable in this order...

Could you please help with doing this properly?
Thank you as always!

September 02, 2020 - 1:13 am UTC

See my previous review.

Write a static version of the code you want to run for ONE table with known name and columns. Then its easy to just extend that out with REPLACE to let it work for any number of tables.

Jess, September 06, 2020 - 11:54 pm UTC

Hi Connor,
Thank you for your advice!
Pre-generating the blocks and then simply swapping out the metadata from the cursor values will certainly work and keep things streamlined.

We've replaced blk collect's save exceptions with log errors into, so that's covered. Given your examples though, it does not seem like there is a way to select the ids (contents of "myids" tables in our example) into a cursor and use it for a join in a way that can be processed using dynamic sql.... It is upwards of 500K values every time and we have >100 DML statements, so that's a lot of re-selecting of the same data instead of having it quietly sat in memory.

If there really is no way of combining having these values in a cursor/collection and being able to use them as a sub-query in dynamic sql to delete in bulk, we will go with the approach you're suggesting and will keep re-selecting the data...

(Well, there is "forall i ... delete where id = :1 using collection(i).id", but it's still a row-by-row delete sans context switching, which we don't want.... It'd be great to have "where ids in (select ids from myids)" have "myids" not be a table select every time but a static set kept in memory...)

Thanks again for the tips!

September 07, 2020 - 2:15 am UTC

forall is not row-by-row :-)

The benefit of forall is not the context switching but the array processing.

If "select ids from myids" is an issue (I don't see why it is) then you can always bulk collect them into a nested table, and then do:

where ids in (select ids from table(my_nested_table))

You bind nested table data types if they are SQL types (ie, you need to do a "create type")

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Returning

Making it distinct

More to Explore

PL/SQL demos

PL/SQL docs

Join (or equivalent) a collection to a table.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Returning

Making it distinct

More to Explore

PL/SQL demos

PL/SQL docs