Ref Cursor query containing IN operator as parameter

[hazelsisson]

Hello,

I have a ref cursor report which works fine apart from one line of the query:

and    e.org_id in (:p_orgs)

After asking on the Oracle 8i forum I understand that this is not possible, and that I need to concatenate the bind variable with the rest of the query.

http://www.tek-tips.com/viewthread.cfm?qid=1395504&page=1

thanks to Dagon.

However after creating a varchar "l_query" to hold my query as a string (concatenated with my bind variables), when I try to open the ref cursor I get an error.

open temp_evdesc for l_query;

Error: Encountered the symbol "L_QUERY" when expecting one of the following: 
Select

Is it not possible to do this?

I've also tried to use a function to produce my IN list rather than the bind variable, but that didn't work either:

and    e.org_id in (getOrgs(1))

I'm using reports 6i. Let me know if there's any other information that would be useful.

Many thanks in advance,
Hazel

 

[hazelsisson]

I managed to solve the problem using lexical parameters, replacing the statement

and    e.org_id in (:p_orgs)

with

&org

and passing the parameter &org as a string constructed using a function in my application.

So in the end I didn't need to use a ref cursor query.