jeebukarthikeyan
asked on
IN operator usage in stored procedure
hi,
I'm developing a simple form which will show some records, user can select more than one record at a time.
Now my query is to how to give all the selected records (say PK's) in the query which is put up in a stored procedure.
For E.g.
select * from user u where u.Pk in (1,2,3,4)
user will select 1,2,3,4 from front end but how to pass these pk's to the query.
I've tried by giving array but i was not able to.
Please give solution for above or suggest any other way of doing this.
Thanks,
I'm developing a simple form which will show some records, user can select more than one record at a time.
Now my query is to how to give all the selected records (say PK's) in the query which is put up in a stored procedure.
For E.g.
select * from user u where u.Pk in (1,2,3,4)
user will select 1,2,3,4 from front end but how to pass these pk's to the query.
I've tried by giving array but i was not able to.
Please give solution for above or suggest any other way of doing this.
Thanks,
ASKER
Thanks mate,
But I was looking for a solution where I need not go for a dynamic query.
Thanks
But I was looking for a solution where I need not go for a dynamic query.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
1. one fast solution could be - pass parameter as a string, i.e. "1,2,3,4"
2. transform your select as
PROCEDURE test ( PARAM_ID IN VARCHAR2 ) AS
begin
execute immediate 'select * from user u where u.Pk in (' || PARAM_ID || ')';
end;
HTH
I