Search code examples
oraclestored-proceduresplsqluser-defined-typesvarray

PLSQL user defined table types and joins

Let's say I have a user defined table type:

create or replace type SrcCodeTbl is table of varchar(20);

I have a procedure that has a parameter of this type:

create or replace procedure Blah.MyProc( srcCodesIN in SrcCodeTbl )

Can I use srcCodesIn in a select/join statement with another table within the procedure? Been trying to get it to work, and the compiler keeps reporting:

select distinct someVal into outVal 
from OtherTable ot, srcCodesIn sc 
where ot.ID = sc.column_val;

Error(28,22): PL/SQL: ORA-00942: table or view does not exist

I'm sure its something simple with the syntax, I just haven't been able to figure it out. I did get something to work with a for-loop, but I'm interested if there's another way to do it. Thanks.

Solution

  • How about

    SELECT DISTINCT someVal 
INTO outVal 
FROM OtherTable ot, TABLE(srcCodesIn) sc 
WHERE ot.ID = sc.column_value