0

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.

APC
  • 144,005
  • 19
  • 170
  • 281
MonkeyWrench
  • 1,809
  • 2
  • 24
  • 48

1 Answers1

2

How about

SELECT DISTINCT someVal 
INTO outVal 
FROM OtherTable ot, TABLE(srcCodesIn) sc 
WHERE ot.ID = sc.column_value
phil
  • 707
  • 5
  • 19
  • Yup, that'll work. I tried your original (before edit) answer, and that spit out a nebulous error message. Interesting that I need to cast a UDT table type to a table before using it as a... table. Gave you the answer since the other guy has a bazillion points already. 8) – MonkeyWrench Oct 28 '11 at 12:08
  • Yeah, I tried it by simulating it in my local oracle so had to typecast it before converting it to table and then forgot to remove it afterwards :) Thanks for the points :) – phil Oct 28 '11 at 12:09
  • Computers are all about precise definitions. A UDT "table" is a TYPE not a TYPE as far as the data dictionary is concerned. That's why you need to cast it in order to use it in a SQL statement. – APC Oct 30 '11 at 05:11
  • which version of oracle are you guys using? – Zeus Jun 03 '16 at 21:53