Using pl/sql tables parameters in stored procedures

[torely]

I've looked throw your help topic of using PL/SQL Tables and I've investigated the sample. But it is not exactly what I am looking for. I can't understand how to pass parameters of the PL/SQL Tables type into stored procedure.

Code: Select all

create or replace procedure TEST1(TXT string_list) is 
  cnt number; 
begin 
  cnt := 0; 
  for v in (select * from table(TXT)) loop 
    cnt := cnt + 1; 
  end loop;  
end TEST1; 

create type string_list is table of varchar2(100);

I need it for the following purposes. I have the SP with the select query which has where Field1 in (Param1) clause. I want to use such a pattern:

Code: Select all

select ... from ... where Field1 in (select * from table(Param1))

If there are other ways of solving my problem, I'm very much appreciated to learn them but using my pattern is more preferable.

Regards, torely.

[AndreyR]

We have corrected your procedure a bit.

Code: Select all

PROCEDURE TEST(TXT string_list) IS 
  cnt NUMBER; 
BEGIN 
  cnt := 0; 
  FOR v IN (SELECT * FROM TABLE(CAST(TXT AS string_list))) LOOP 
    cnt := cnt + 1; 
  END LOOP;  
END TEST; 

To pass the parameter to this SP you can use the following code:

Code: Select all

oracleCommand1.Parameters.Add(new CoreLab.Oracle.OracleParameter("TXT", CoreLab.Oracle.OracleDbType.Table, 0, System.Data.ParameterDirection.Input, false, ((byte)(0)), ((byte)(0)), "", System.Data.DataRowVersion.Current, null, 0, "SCOTT.STRING_LIST"));
oracleCommand1.Parameters["TXT"].Value = new string[] { "aa", "bb"};