Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Using Clause (merged 3)
Dynamic Using Clause (merged 3) [message #388390] Tue, 24 February 2009 09:47 Go to next message
Messages: 46
Registered: March 2005
Karma: 0

We have dynamic SQL using the USING clause. A recent change requires us to now make the USING clause dynamic eg. different list of bind variables depending on how the WHERE clause is constructed.

Treating the USING clause as a string and concatenating it to the rest of the SQL statement is not working.

This is a snippet of the original code:

   WHERE ' || v_WhereClause
   USING p_customer_id,p_order_id;

I tried this:
   v_USING := ' p_parameter_one,p_parameter_two';
   WHERE ' || v_WhereClause || v_USING;

   ORA-00933: SQL Command not properly ended

Any ideas? Thx

Re: Dynamic Using Clause [message #388392 is a reply to message #388390] Tue, 24 February 2009 10:07 Go to previous messageGo to next message
Messages: 25578
Registered: January 2009
Location: SoCal
Karma: 0
Senior Member
Do not cross/multi-post
Re: Dynamic Using Clause (merged 3) [message #388397 is a reply to message #388390] Tue, 24 February 2009 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 65153
Registered: March 2007
Location: Nanterre, France, http://...
Karma: 0
Senior Member
Account Moderator
Post a REAL thing and not a piece of nothing.
Of course, "_USING := ' p_parameter_one,p_parameter_two';
WHERE ' || v_WhereClause || v_USING;" is not a SQL command.


[Updated on: Tue, 24 February 2009 10:23]

Report message to a moderator

Re: Dynamic Using Clause (merged 3) [message #388491 is a reply to message #388390] Wed, 25 February 2009 00:45 Go to previous message
Messages: 7880
Registered: March 2000
Karma: 0
Senior Member
If the number of bind-variable start to vary, it is time to stop using USING.
See this thread for an alternative.
Previous Topic: return xml in sys_refcursor
Next Topic: Get_Hash_Value_collisions (merged)
Goto Forum:

Current Time: Tue Aug 22 13:51:11 CDT 2017

Total time taken to generate the page: 0.01681 seconds