OraFAQ Forum: SQL & PL/SQL » using variable as part of where clause

Home » SQL & PL/SQL » SQL & PL/SQL » using variable as part of where clause

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

using variable as part of where clause [message #18989]

Mon, 25 February 2002 04:02

Brian Kelly
Messages: 4
Registered: February 2002

Junior Member

Hi,
I have a where clause passed to a client extension in oracle. Here is an example of my problem:
select project_id from pa_projects_all x
where to_char(x.project_id) in (v_new_where)

lets say v_new_where holds the following:'SELECT P.PROJECT_ID FROM PA_PROJECTS P'.

So when the query runs it seems that the variable is passing the single quotes along with the string. This means that it is trying to compare the project_id's with the actual string and not the values that it should be returning. hence always returning nothing. I have v_new_where declared as varchar2. Has anyone had a similar problem and do they know a solution. thanks, Brian.

Report message to a moderator

Re: using variable as part of where clause [message #18990 is a reply to message #18989]

Mon, 25 February 2002 04:17

pratap kumar tripathy
Messages: 660
Registered: January 2002

Senior Member

use dynamic sql instead of static

e.g.

a ref cursor;

open a for
'select project_id from pa_projects_all x
where to_char(x.project_id) in ('||v_new_where||')';

Report message to a moderator

Re: using variable as part of where clause [message #18992 is a reply to message #18989]

Mon, 25 February 2002 04:38

Brian Kelly
Messages: 4
Registered: February 2002

Junior Member

thanks Pratap

Report message to a moderator

Previous Topic:	Counter
Next Topic:	Urgent! IDEPTREE and DEPTREE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 05:32:55 CDT 2024