want to pass multiple values into a procedure

[shravan]

want to pass multiple values into a procedure wherr the select query where condition is as ' select * from table name where param in (parameter)'

The parameter should be like 12,36,48 and so on.

I shall appreciate if anyone can help me in this.

Thank you
Shravan

[vbaskar]

since the number of parameters is not static u can do this by sending the parameters in to an varchar2 field.
variablein = '14,48,56,42,31,12'

replace the where clause with the variable...

[shravan]

create or replace procedure ProcedureName (Parametername in String) as

begin

Open Cursor for

SELECT * FROM tablename WHERE tablename.column IN
(parametername);

end;
----------------------------------------------------------------------
In the above example of stored procedure the paramter value I ma sending is like 24,36,54,68 and the column datatype in database is number.

So when I make parameter as string it will send it as '24,36,54' with quotes. Am i right? If I hardcode the parametervalue in the stored procedure it works fine but while passing it gives error.

The otherway round as you told to parameter as Varchar2. I also tried that but doesn't work.

Please help me.

Thank you
Shravan

[vbaskar]

Hi

I assume u r using oracle8i...use EXECUTE IMMEDIATE..

Example :

variable1 := 'SELECT * FROM tablename WHERE tablename.column IN ('||parametername||')';

open urcursor for variable1;
if urcorsuror%found then
--do necessary
null;
end if;
close urcursor;

[shravan]

Hi,

Thank you. I worked in the same manner and could solve it. but now I cannot return the values from that stored procedure as I am using cursor variables and opening the cursor as as follows
--------------------------------------------------------------------
create or replace procedure name (parameter1 in varchar2,
test_cur in out packagename.Reference_cursor)

Type test_cur_type is ref cursor;
test_cur test_cur_type;

begin

variableSQL:='Sql statement';

Open Test_cur for variableSQL;

end;
----------------------------------------------------------------------
here in the above example in the parameter list packagename refers toa package in which I defined a record and refernce.cursor means a referenced cursor in the package.

package is as follows
------------------------------------------------------------------
create or replace package packagename is

type test_rec is record (

varcolumn1 varchar2(100),
varcloumn2 varchar2(10),
varcolumn3 number(20));

type reference.cursor is ref cursor return test_rec;
end;
--------------------------------------------------------------------
Actually when I run this I need to get the output of stored procedures SQL statement from the Reference cursor I have used but it gives me error saying that "Duplicate in the Record, Table or the argument list not permitted"
--------------------------------------------------------------------
Note: the SQL statement in procedure is a dynamic one.
------
Please help me if any one knows how to solve this.

Thank you.
Shravan.