Passing query result to stored procedure.

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Passing query result to stored procedure.

Postby Narayanan » Tue Jan 29, 2013 6:18 am

Hi,

I am using oracle 10g version. Can any one please tell me how to pass the query result to IN clause of oracle stored procedure..?

For Example: I have a Employee table which returns only employee names ( select employeeNames from Emplyoee -> this query returns more than 100 employee names).

EmployeeNames:
-------------------
Arul
Rahul
Raj

In my oracle stored procedure, I want to pass these values to comma seperated string....

I have stored the query result in one variable like employee_query="select employeeNames from Employee". Now my requirement is how to pass this query result as comma seperated strings to IN clause of my stored procedure?

Thanks in Advance.
Narayanan
Member
 
Posts: 1
Joined: Tue Jan 29, 2013 6:06 am

Re: Passing query result to stored procedure.

Postby venkat » Tue Jan 29, 2013 6:49 am

i hope u can use pl sql collections
venkat
Member
 
Posts: 16
Joined: Sat Jan 05, 2013 5:13 am

Re: Passing query result to stored procedure.

Postby Tim... » Tue Jan 29, 2013 7:10 am

Hi.

You can turn the results of the query into a comma separated string using string aggregation.

http://www.oracle-base.com/articles/mis ... niques.php

That string can then be passed into the procedure as a regular IN parameter.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 7 guests