join a plsql nested table type with another regaular sql table

debugcn Published at Dev

jhon.smith

I would like to select from a nested table type using regular sql.

create table invoices(invoice_id number);
insert into invoices values(100);
insert into invoices values(200);
insert into invoices values(300);
insert into invoices values(500);
create or replace type invoice_obt
as object (
invoice_id number
);
/

create type invoices_ntt
as table of invoice_obt;
/

Here is my plsql for that

declare                                                        
l_invoices invoices_ntt := invoices_ntt();                     
begin                                                          
l_invoices.extend(3);                                          
l_invoices(1) := invoice_obt(100);                             
l_invoices(2) := invoice_obt(200);                             
l_invoices(3) := invoice_obt(200);                          
select invoice_id from invoices where invoice_id in (select * from table(l_invoices));                                
end;

and I run into an error saying

select invoice_id from table(l_invoices);
*
ERROR at line 8:
ORA-06550: line 8, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement

I would like to join this table l_invoices with my regular invoice table . Any ideas on how do I do that ?

Aleksej

The issue is not in the way you use types, but in the fact that you are trying to do a select query from within a Pl/SQL block without fetching the result INTO any variable.

You code could be:

DECLARE
    l_invoices   invoices_ntt := invoices_ntt ();

    /* define a variable to host the result of the query */
    TYPE tIdList IS TABLE OF NUMBER;
    vIdList      tIdList;
BEGIN
    l_invoices.EXTEND (3);
    l_invoices (1) := invoice_obt (100);
    l_invoices (2) := invoice_obt (200);
    l_invoices (3) := invoice_obt (200);

    SELECT invoice_id
      BULK COLLECT INTO vIdList  /* BULK COLLECT because you can have more than one row */
      FROM invoices
     WHERE invoice_id IN (SELECT * FROM TABLE (l_invoices));
END;

Collected from the Internet

Please contact [email protected] to delete if infringement.