-
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
-
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...
Vijay.s
-
I read your message. here is my stored procedure
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
-
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;
Vijay.s
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|