| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: recursion in Pro*C
A copy of this was sent to "Alan D. Mills" <alanmNOSPAM_at_uk.europe.mcd.mot.com>
(if that email address didn't require changing)
On Tue, 27 Oct 1998 13:27:33 -0000, you wrote:
>In my history of Pro*C I've not come up against this before so I'm not sure
>if it's actually possible. The question is about dynamic SQL.
>
>My dynamic SQL statement is picked up from another table and executed as a
>cursor. For each record retrieved I may then want to build another dynamic
>cursor for it and so on. My question is. What is the scope of a cursor
>(dynamic or otherwise) within the Pro*C program. I thought it was global
>nomatter where the cursor is actually defined. Can I define a C function
>which builds and executes a dynamic cursor and for each record retrieved
>call the same function to open the next level of dynamic cursor, returning
>to the correct record of the previous on completion?
>
>Is this possible as a recursive call. If not, can anyone suggest any
>alternative designs for this sort of thing.
>
>I hope this is clear. Any help appreciated.
You'll not be able to do this in a straightforward way. Cursors in PRO*C are global to the file they are contained in -- they do not obey subroutine stacks and such (their definitions are really at the top of the file as a bunch of data structures -- outside of any subroutine).
As soon as one recursive call is made, it will be really using the 'parent' routines cursor and when it opens it -- it will close the parents by default.
Suggestions for ways around it
1 - look at connect by, it does recursion for you. You may very well be able to use connect by to get the entire result set in one query.
2 - consider embedding OCI (you can mix oci and pro*c in the same application, you have to logon using pro*c but then can use sqllda() to get an OCI login data area and do oci stuff). OCI can use stack variables as cursors (and hence, normal C scoping rules come into play).
3 - look into the dbms_sql package and use pl/sql to actually do the dynamic sql. dbms_sql treats cursors much in the same way OCI does so you can declare them as stack variables once again.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Oct 27 1998 - 08:54:45 CST
 |
 |