How include array (or similar) into an in-clause SQL statement?

Question

I am new to oracle and PL SQL. Currently I struggle with the handling of arrays and "similar things" like i.e. collection. I am trying to build a procedure like:

procedure insert_by_array( my_array some_array_type)
begin

insert into table1 (some_column)
select some_column 
from table2
where column2 in my_array 
;

end;

However I could not make I did try some array types but I did not find the right one. The entries of the type must be varchar2 - a part of this criterium I am open to any array type. I.e when my array_type is

type array_of_strings is varray(100) of varchar2(40);

My error would be: "local collection types are not alowed in sql statements" I am using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0.

So at the end, this worked:

create type table_of_strings IS TABLE OF VARCHAR2(64); --define it global;

declare
my_table table_of_strings;

begin
my_table := table_of_strings('aaa', 'bb','c');

insert into table1 (some_column)
select some_column 
from table2
where column2 in (select * from table(my_table))
;

end;

Arkadiusz Łukasiewicz · Accepted Answer · 2019-06-13 10:29:58Z

2

First of all, you have to define sql level collection. (varray is not an option)

create type array_of_strings as table of varchar2(40);

Now you can use table or "member of" approach

declare 
c array_of_strings := new array_of_strings();
begin
c.extend();
c(c.count) := 'A';
c.extend();
c(c.count) := 'B';

for rec in (select * from dual where 'A' member of c ) loop
  dbms_output.put_line('Option with memeber of ');
end loop;


for rec in (select * from dual where 'A' in (select * from table(c))) loop
  dbms_output.put_line('Option with table');
end loop;

end;

answered Jun 13, 2019 at 10:29

Arkadiusz Łukasiewicz

6,3761 gold badge13 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ponder Stibbons · Accepted Answer · 2019-06-13 10:18:51Z

As error says, local collections, defined in procedure, function, code block, cannot be used in queries, at least in Oracle 11 which I use.

This does not work (PLS-00642):

declare 
  type strings is table of varchar2(5);
  v_i strings := strings('A', 'X', 'Q');
  v_o strings;
begin 
  select * bulk collect into v_o from dual where dummy in (select * from table(v_i));
end;

So... either use type defined at schema level (your own or predefined sys.odcivarchar2list):

declare 
  type strings is table of varchar2(5);
  v_i strings := strings('A', 'X', 'Q');
  v_o strings;
  v_so sys.odcivarchar2list := sys.odcivarchar2list();
begin 
  v_so.extend(v_i.count);
  for i in 1..v_i.count loop
    v_so(i) := v_i(i);
  end loop;
  select * bulk collect into v_o from dual where dummy in (select * from table(v_so));
end;

... either use dynamic sql:

declare 
  type strings is table of varchar2(5);
  v_i strings := strings('A', 'X', 'Q');
  v_o strings;
  v_str varchar2(4000);
begin 
  for i in 1..v_i.count loop
    v_str := v_str || case when i > 1 then ', ' end || ''''||v_i(i)||'''';
  end loop;
  execute immediate 'select * from dual where dummy in ('||v_str||')' bulk collect into v_o ;
end;

... either use loop (probably slowest):

declare 
  type strings is table of varchar2(5);
  v_i strings := strings('A', 'X', 'Q');
  v_o strings := strings();
begin 
  for rec in (select * from dual) loop
    if rec.dummy member of v_i then 
      v_o.extend();
      v_o(v_o.count) := rec.dummy;
    end if;
  end loop;
end;

Collectives™ on Stack Overflow

How include array (or similar) into an in-clause SQL statement?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related