Re: recursion in Pro*C

You've backed up nicely what I thought. I haven;t used Pro*C since V1.6 and wondered if anything had changed regarding scope of cursors.

In reply to your suggestions.

1 - CONNECT BY. The data structures I have are not recursive. It's the process that need to be. I don't think this fits my problem. However, nice idea. I hadn't considered it.

2- I don't know OCI and suspect that for this task the learning curve would be too great. I happy to be correct on this though.

3 - The dbms_sql package. Now there's an idea. I haven't done a lot with this before. Certainly not for querying data. My select list is dynamic also (Pro*C dynamic SQL method 4) so will this idea still work? Also, can I use DBMS_SQL to perform a single array fetch. I don't have to worry about fetching in a loop as I have a theoretical maximum record size. Is there any change in syntax or procedure for this or do I just fire off my

SELECT col, col, col INTO :HostArrayvar1, :hostarrayvar2 etc

and be done with it? I'm simply not familiar with host arrays and embedded PL/SQL (as opposed to simple embedded SQL). I guess I need to dig out the manuals on that one.

Thanks for the help though. Definitely food for thouight. Ta.

--
Alan D. Mills

Thomas Kyte wrote in message <3635ddca.3569903_at_192.86.155.100>...

>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.
Received on Tue Oct 27 1998 - 10:03:20 CST