Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic bind variables (Oracle 9i, any OS)
icon5.gif  Dynamic bind variables [message #422098] Mon, 14 September 2009 07:11 Go to next message
bjvad
Messages: 3
Registered: September 2009
Junior Member
Hi,

Having a bit of trouble getting around dynamic queries.

In a pl/sql proc, I'm trying to create a dynamic query based on some conditions and return the results to a cursor using "Open xx for yy using a,b,c,d;

The problem that I have is the strings that I am adding dynamically has the bind variables in it, so I want to vary the bind variables when executing. For example:

IF p_taskID IS NOT NULL THEN
	v_dynWhere := v_dynWhere||'AND T.CALLID LIKE :p_taskId ';
END IF;
		
IF p_CTID IS NOT NULL THEN
	v_dynWhere := v_dynWhere||'AND E.ID LIKE :p_CTID ';
END IF;

IF p_input IS NOT NULL THEN
	v_dynWhere := v_dynWhere||'AND T.''|| p_criteria || '' LIKE :p_input ';
END IF;

IF p_assignDate IS NOT NULL THEN
	v_dynWhere := v_dynWhere||'AND TRUNC(A.STARTTIME) = :p_assignDate ';
END IF;

V_QUERY := 'SELECT T.CALLID AS callid,  FROM W6ADMIN.W6TASKS T, W6ADMIN.W6ASSIGNMENTS_ENGINEERS AE, W6ADMIN.W6ENGINEERS E, W6NATYPE NA WHERE AE.TASK= T.W6KEY  '||v_dynWhere||'; 


OPEN p_cursor FOR V_QUERY USING p_taskId,p_CtId,p_input,p_assignDate;


So v_query is the base query and I am concatenating v_dynWhere which is built based on the IF statements.

If one of the variables in the IF statements is NULL, then I don't have enough bind variables.

So basically, is there any way I can dynamically define the bind variables?

Thanks in advance!
Re: Dynamic bind variables [message #422100 is a reply to message #422098] Mon, 14 September 2009 07:24 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
You can use this method suggested by Tom Kyte


IF p_taskID IS NOT NULL THEN
	v_dynWhere := v_dynWhere||'AND T.CALLID LIKE :p_taskId ';
else
v_dynWhere := v_dynWhere||'AND (:p_taskID is null or 1=1)'
END IF;
		
IF p_CTID IS NOT NULL THEN
	v_dynWhere := v_dynWhere||'AND E.ID LIKE :p_CTID ';
else
v_dynWhere := v_dynWhere||'AND (:p_CTID is null or 1=1)'
END IF;

IF p_input IS NOT NULL THEN
	v_dynWhere := v_dynWhere||'AND T.''|| p_criteria || '' LIKE :p_input ';
else
v_dynWhere := v_dynWhere||'AND (:p_input is null or 1=1)'
END IF;

IF p_assignDate IS NOT NULL THEN
	v_dynWhere := v_dynWhere||'AND TRUNC(A.STARTTIME) = :p_assignDate ';
else
v_dynWhere := v_dynWhere||'AND (:p_assignDate is null or 1=1)'
END IF;

V_QUERY := 'SELECT T.CALLID AS callid,  FROM W6ADMIN.W6TASKS T, W6ADMIN.W6ASSIGNMENTS_ENGINEERS AE, W6ADMIN.W6ENGINEERS E, W6NATYPE NA WHERE AE.TASK= T.W6KEY  '||v_dynWhere||'; 


OPEN p_cursor FOR V_QUERY USING p_taskId,p_CtId,p_input,p_assignDate;

[Updated on: Mon, 14 September 2009 07:25]

Report message to a moderator

Re: Dynamic bind variables [message #422104 is a reply to message #422100] Mon, 14 September 2009 07:41 Go to previous messageGo to next message
bjvad
Messages: 3
Registered: September 2009
Junior Member
Thanks for the reply.

I can't set the variable (like p_taskID) to NULL as it still might have values.

The reason why I'm checking if p_taskID IS NOT NULL is because I am retrieving it from the GUI and assigning the value to that. So if the user did not enter a task ID, the p_taskID variable would be empty, so I just want to exclude it from my search criteria.

What does 1=1 mean?
Re: Dynamic bind variables [message #422105 is a reply to message #422098] Mon, 14 September 2009 07:45 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
T. Kyte answered to this question in the following article:
http://www.oracle.com/technology/oramag/oracle/09-jul/o49asktom.html

Regards
Michel
icon6.gif  Re: Dynamic bind variables [message #422137 is a reply to message #422098] Mon, 14 September 2009 10:30 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
You might also look at something that is deprecated by Oracle (and I am using that in my project). DBMS_SQL offers a way of binding variables by the variable name.

Cons:
- functionality that tends to become deprecated but is needed for some purposes (Oracle tends to prefer EXECUTE IMMEDIATE since it is easier to use) - thanks Michel for pointing that out Smile
- probably performance might be impacted... I'm just guessing
- use is much more complex
- by using DBMS_SQL your code would look like a cheese with something that does not look like PL/SQL
- you still need to determine when the variable is going to be used (unless you go with that trick sugested by bonker)

Pros:
- You might bind variables by name and one-by-one
- You might bind variables by name and one-by-one
- You might bind variables by name and one-by-one

[Updated on: Tue, 15 September 2009 02:30]

Report message to a moderator

Re: Dynamic bind variables [message #422139 is a reply to message #422137] Mon, 14 September 2009 11:05 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Cons:
- deprecated functionality

No, it is not.
It is in some case the ony way to do what you want and Oracle even enhances it in 11g adding the possibility to use ref cursor.

Regards
Michel
Re: Dynamic bind variables [message #422164 is a reply to message #422098] Mon, 14 September 2009 22:39 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
If I understand you, then there is also this approach, using dynamic sql that builds dynamic sql. I am not saying it is a better approach, just an alternative. Then again I may have mis-understood. Even this approach has its limits.
declare
   v1 number := 1;
   v2 number := null;
   v3 number := 3;
   rc1 sys_refcursor;
   sql_v varchar2(32000);
   rdual dual%rowtype;
begin
   sql_v := 'declare'||
            ' v1 number := :1;'||
            ' v2 number := :2;'||
            ' v3 number := :3;'||
            ' sql_v varchar2(32000) := ''select * from dual where 0 = 0';
   if v1 is not null then sql_v := sql_v||' and 1 = ''||'':''||''v1'; end if;
   if v2 is not null then sql_v := sql_v||' and 2 = ''||'':''||''v2'; end if;
   if v3 is not null then sql_v := sql_v||' and 3 = ''||'':''||''v3'; end if;
   sql_v := sql_v||'''; begin open :4 for sql_v using v0';
   if not (v1 is null and v2 is null and v3 is null)
   then
      if v1 is not null then sql_v := sql_v||',in v1'; end if;
      if v2 is not null then sql_v := sql_v||',in v2'; end if;
      if v3 is not null then sql_v := sql_v||',in v3'; end if;
      sql_v := replace(sql_v,'v0,');
      sql_v := sql_v||';';
   end if;
   sql_v := sql_v||' end;';
--   raise_application_error(-20999,sql_v);
   execute immediate sql_v using in v1, in v2,in v3, in out rc1;
   fetch rc1 into rdual;
   close rc1;
   raise_application_error(-20999,'it works, found this:'||rdual.dummy);
end;
/

Notice the primary code generates secondary code. The primary code always passes all variables to the secondary code. The primary code evaluates its variables in generating certain portions of the secondary code. The primary code then executes the generated secondary code from which is returned a refcursor. Notice the refcursor is a IN OUT parameter not an OUT parameter. Do not ask me why, I only know this works and using OUT only does not compile.

Here is the result of a run. I am using RAISE_APPLICATION_ERROR simply as a shortcut to show results.
declare
*
ERROR at line 1:
ORA-20999: it works, found this:X
ORA-06512: at line 32

As you can see, a sql statement was generated based on the NULLNESS of various variables. It was executed and its results fetched and used. Not your simple code generator but then again your's is not a simple problem. After you have done this a few times it gets easier.

Again, it is just an alternative to other approaches. Not necessarily better.

Good luck, Kevin
icon10.gif  Re: Dynamic bind variables [message #422299 is a reply to message #422164] Tue, 15 September 2009 07:14 Go to previous message
bjvad
Messages: 3
Registered: September 2009
Junior Member
Thanks so much guys. I used Tom Kyte's method, worked like a charm. Brilliant idea
Previous Topic: insert a dummy(?) row to the query result
Next Topic: Problems that we face when migrate from oracle9i to 10g
Goto Forum:
  


Current Time: Tue Sep 27 16:07:20 CDT 2016

Total time taken to generate the page: 0.09351 seconds