Home » Developer & Programmer » Precompilers, OCI & OCCI » passing parameters in a dynamic query
passing parameters in a dynamic query [message #416298] Fri, 31 July 2009 08:47 Go to next message
cool_coder
Messages: 17
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

Report message to a moderator

Re: passing parameters in a dynamic query [message #416299 is a reply to message #416298] Fri, 31 July 2009 08:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, you have to double them.

Regards
Michel
Re: passing parameters in a dynamic query [message #416300 is a reply to message #416298] Fri, 31 July 2009 08:51 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
If I understand it correctly you have 2 queries different in just a filter but same set of bind variables. Then as I know, you need to specify one value per bind variable you specify in the dynamic sql.
Re: passing parameters in a dynamic query [message #416301 is a reply to message #416298] Fri, 31 July 2009 09:05 Go to previous messageGo to next message
cool_coder
Messages: 17
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 Go to previous messageGo to next message
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]

Report message to a moderator

Re: passing parameters in a dynamic query [message #416325 is a reply to message #416315] Fri, 31 July 2009 13:51 Go to previous messageGo to next message
cool_coder
Messages: 17
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 #416336 is a reply to message #416325] Fri, 31 July 2009 16:55 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
I didn't quite understand it. But if you're trying to test if the second cursor ( union ) will run fine, can't you test it in your stage environment? Make sure that it's really executing that cursor your trying to test.
Re: passing parameters in a dynamic query [message #416350 is a reply to message #416336] Fri, 31 July 2009 20:08 Go to previous messageGo to next message
cool_coder
Messages: 17
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
Re: passing parameters in a dynamic query [message #416364 is a reply to message #416358] Sat, 01 August 2009 02:33 Go to previous message
cool_coder
Messages: 17
Registered: September 2008
Junior Member
Thanks a lot for your time and valuable insight.
Previous Topic: Data is wrong for char
Next Topic: ORA-12541: TNS:No listener
Goto Forum:
  


Current Time: Thu Mar 28 17:01:49 CDT 2024