Using pl/sql tables parameters in stored procedures

Using pl/sql tables parameters in stored procedures

Postby torely » Mon 28 Jul 2008 01:44

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.
torely
 
Posts: 7
Joined: Thu 24 Jul 2008 04:24

Postby AndreyR » Tue 29 Jul 2008 09:53

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"};
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle