1

try to parse array parameter to oracle for use it in select where in statement:

c#:

string[] arr = new string { "aaa", "bbb" };

Oracle:

type str_table is table of varchar2(20);
procedure sp_test ( p_arr in str_table )
is
begin
select *
  from table 
 where field in (select column_value
                   from table(p_arr))
end;

Its not work. Error is cant access table not initialized...

MT0
  • 143,790
  • 11
  • 59
  • 117
flap13
  • 379
  • 3
  • 14
  • Please post the exact error message and the code you are using to call the procedure. Also, is the Oracle code being create in the SQL scope or in a package? – MT0 Jul 11 '16 at 11:34
  • This is how to do it in Java: [How to pass List from java to Oracle Procedure](http://stackoverflow.com/a/37161584/1509264) – MT0 Jul 11 '16 at 11:38
  • Possible duplicate: [Calling an Oracle procedure with a PL/SQL collection type parameter via .NET](http://stackoverflow.com/a/29608014/1509264) – MT0 Jul 11 '16 at 11:42
  • 1
    http://stackoverflow.com/questions/9700173/odp-net-how-to-pass-array-of-strings-to-an-oracle-stored-procedure – Mottor Jul 11 '16 at 11:44
  • Also - if you are using an Oracle collection you do not need to do `WHERE column_name IN ( SELECT COLUMN_VALUE FROM TABLE( array ) )` instead you can simply do `WHERE column_name MEMBER OF array`. – MT0 Jul 11 '16 at 12:05
  • Show us your c# code – Mottor Jul 11 '16 at 12:07
  • Before c# code the package is not compiled :) when changed to member of p_arr get error PLS-00382 expression is of wrong type, but type is same, varchar2. – flap13 Jul 11 '16 at 12:39
  • Show us than the pl/sql code. Where are the 'CREATE' , where is the select into or this select is open for cursor – Mottor Jul 11 '16 at 16:01

0 Answers0