| passing parameters in a dynamic query [message #416298] |
Fri, 31 July 2009 08:47  |
cool_coder
Messages: 15
Registered: September 2008 |
Junior Member |
|
|
Hi,
I have a Pro*C program, which uses a dynamic query.
The dynamic query is opened using result of another static cursor( 5 fields say , :a, :b , :c, :c, :d).
I am modifying the dynamic query and adding UNION for some requirement , which makes this dynamic query exactly double in size. ( means 2 set of prev. queies are joined by UNION, with one extra condiition though).
The question is , Do I need to pass 2 set of variable to open the dynamic query now?
Like earlier , program was passed with (:a, :b , :c, :c, :d)
so now i should pass (:a, :b , :c, :c, :d , :a, :b , :c, :c, :d)?
Thanks a lot for your help.
[Updated on: Sat, 01 August 2009 03:09] by Moderator
|
|
|
|
|
| Re: passing parameters in a dynamic query [message #416301 is a reply to message #416298] |
Fri, 31 July 2009 09:05  |
cool_coder
Messages: 15
Registered: September 2008 |
Junior Member |
|
|
Thnaks for your quick responses.
I was wonderring as the program has been executing fine without providing extra set.
I ran it for some 5-6 data-set as well as for 200+ data-sets.
I initially thought of doubling the parameter but i missed it while coding and now in production.
Any reason , why its working now?..and potentila to fail sometime?
Thanks again.
|
|
|
| Re: passing parameters in a dynamic query [message #416315 is a reply to message #416301] |
Fri, 31 July 2009 11:50 |
ehegagoka
Messages: 493
Registered: July 2005 |
Senior Member |
|
|
What do you mean by dataset? Could you post sample code?
If one of the bind variables have not been bound, it will error out.
DECLARE
c SYS_REFCURSOR;
d NUMBER;
a NUMBER := 1;
b NUMBER := 2;
BEGIN
OPEN c FOR 'select 1 from dual where 1 = :a union select 1 from dual where 1 = :b'
USING a;
FETCH c INTO d;
CLOSE c;
END;
/
I forgot - its possible to not error out if it wasn't executed on your code.
[Updated on: Fri, 31 July 2009 11:58]
|
|
|
| Re: passing parameters in a dynamic query [message #416325 is a reply to message #416315] |
Fri, 31 July 2009 13:51 |
cool_coder
Messages: 15
Registered: September 2008 |
Junior Member |
|
|
Thanks for Looking into it.
My Pro*C program has something like that...
(can't paste program as its a big one so description below is a bit confusing ...but please read two times -that might make sense..
sorry abt that)
There is a Declation part ( for arrays,variables,structures,cursors)
Cursor CSR1 is
select a,b from table test1
----------
in the Process() function..(which gets called from main function)
open/fetch CSR1 into :AA , :BB;
and dynamic querry is build like
strcpy(qry,"SELECT sum(abc1),abc2,abc3
from table
where p = :variable1
and q = :variable2
and r <> 1
group by abc1
UNION
SELECT sum(abc1),abc2,abc3
from table
where p = :variable1
and q = :variable2
and r = 1
group by abc1")
declare CSR2 for qry;
Case 1-- OPEN CSR2 USING :AA , :BB ;
CAse 2 --OPEN CSR2 USING :AA , :BB , :AA , :BB ;
----question:
The cursor CSR2 was modified by UNION clause with duplicating the first one , though for same set of variables.
Finally the output of CSR2 is written into a FILE.
I tested with both these Cases for now and ( for different example data -- i mean setting up data in the tables to fullfill query) and creating same FILE so just wonderring if
Case 2 is not needed an program will execute fine without issues.
Thanks a lot for your help.
|
|
|
|
| Re: passing parameters in a dynamic query [message #416350 is a reply to message #416336] |
Fri, 31 July 2009 20:08 |
cool_coder
Messages: 15
Registered: September 2008 |
Junior Member |
|
|
Sorry for the confusion.
Yes , I tried in stage env. executing this program and observed that by passing one set of parameters provides same result as passing two set of parameters.
(however, executing stand alone query in parts -divided by UNION produce different result)
May be , as both parts of cursor ( sepererated by UNION) requires exact same set of parameters so its working good with both cases.
1, So Fundamentally, in a dynamic query , number of parameters passed i.e. bind variables , should be equal to the parameters required in the query, right ?
2. HOWEVER , if that query is added by UNION and same kind of query is added to that ( with some other condition but same set/order of bind varibles ) then providing one set (as was before) would work and no need to repeat variables in USING clause.
DOes this makes sense?
Thanks
|
|
|
| Re: passing parameters in a dynamic query [message #416358 is a reply to message #416350] |
Sat, 01 August 2009 01:24 |
Michel Cadot
Messages: 29434
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
It is clearly stated in the documentation (for instance http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14407/pc_13dyn.htm#sthref1816 but it is said at several places):
| Quote: | Every placeholder in the PREPAREd dynamic SQL statement must correspond to a different host variable in the USING clause. So, if the same placeholder appears two or more times in the PREPAREd statement, each appearance must correspond to a host variable in the USING clause.
|
However as far as I remember in my old Pro*C developer time, if the list is too short, Oracle restart at the beginning of the variable list to bind the next placeholders.
So if your statement "works" it is by luck because you have the same parameters at the same place but this is NOT the way to do it. You can easily get wrong answers just swapping p and q in the second query for instance.
Regards
Michel
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
