Geek asks and answers

Oracle collection in the where section

I use the collection in the oracle code block because there is no table variable (for example, in MS SQL Server).

DECLARE
    TYPE I_NAME IS TABLE OF NVARCHAR2(512);     
    I_ITEMNAME      I_NAME := I_NAME(); 
BEGIN

I use "BULK COLLECT INTO I_ITEMNAME" to populate the collection.
I want to use this collection in a WHERE clause in a SELECT query, but cannot find a way to do this. I am currently using the FOR loop and getting the element one by one.
How can I use the collection directly in the WHERE clause, for example,

CHOOSE * FROM TBL WHICH IS GOING TO I_ITEMNAME?

Thank,

+5
source share
1 answer

You cannot use a locally declared collection in an SQL statement:

declare
    type i_name is table of nvarchar2(512);
    i_itemname i_name := i_name();
    c number;
begin
    select distinct owner bulk collect into i_itemname from all_objects;
    dbms_output.put_line(i_itemname.count);
    select count(*) into c
    from all_tables
    where owner in (select * from table(i_itemname));
    dbms_output.put_line(c);
end;
/

    where owner in (select * from table(i_itemname));
                                        *
ERROR at line 10:
ORA-06550: line 10, column 41:
PLS-00642: local collection types not allowed in SQL statements
ORA-06550: line 10, column 35:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
ORA-06550: line 8, column 5:
PL/SQL: SQL Statement ignored

, , , SQL , PL/SQL:

create type i_name is table of nvarchar2(512);
/

Type created.

declare
    i_itemname i_name := i_name();      
    c number;
begin 
    select distinct owner bulk collect into i_itemname from all_objects;
    dbms_output.put_line(i_itemname.count);
    select count(*) into c from all_tables
    where owner in (select * from table(i_itemname));
    dbms_output.put_line(c);
end;
/

No errors.
18
128

PL/SQL procedure successfully completed.

table, :

...
    select count(*) into c
    from table(i_itemname) t
    join all_tables at on at.owner = t.column_value;
...

, . ( , , , - ).

@haki, :

...
    select count(*) into c
    from all_tables
    where owner member of (i_itemname);
...

... i_name , . , nvarchar2 varchar2, , i_name varchar2(512). tab.col nvarchar2.

+10

More articles:


All Articles