Re: Quick (hopefully) pl/sql cursor question

The order in which procedures happen to appear in the package has no bearing whatsoever on when cursors are opened, if that is what is meant by "initialized".

Perhaps tracing execution via the debugger would help set their minds at rest.

William

On 27 Sep 2019, at 21:20, Andy Sayer <andysayer_at_gmail.com> wrote:

That doesn’t really indicate any sort of problem, just a fear of something with no evidence that it’s happening.

Perhaps they have more evidence, but I’m tempted to say “works on my machine”.

Whether something reinitializes doesn’t matter unless you see a problem occurring due to it. PL/SQL is a great programming language, people use it with huge success. It’s unlikely that this vague problem is anything to do with the language if it does exist.

Hope that helps,
Andy

> On Fri, 27 Sep 2019 at 20:15, Sweetser, Joe <JSweetser_at_icat.com> wrote:
> I am not sure of the exact problem, but the message I got from the developer was something like this:
>
>
>
> --- if the cursors are initialized when ‘procedure3a’ is called, that's good. If they are initialized when ‘procedure3’ is called, that's bad.
>
>
>
> Taking that with Lothar’s previous explanation(s), I will go back to developer and make sure they understand that c2 is reinitialized every time the c1 loop iterates and that it doesn’t really have anything to do with the procedure order, per se.
>
>
>
> Thanks again to all,
>
> -joe
>
>
>
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Andy Sayer
> Sent: Friday, September 27, 2019 1:03 PM
> To: l.flatz_at_bluewin.ch
> Cc: oracle-l_at_freelists.org
> Subject: Re: Quick (hopefully) pl/sql cursor question
>
>
>
> What weirdness is being seen exactly?
>
>
>
> On Fri, 27 Sep 2019 at 19:58, Lothar Flatz <l.flatz_at_bluewin.ch> wrote:
>
> Would c2 still be initialized at the start of the ‘For y’ loop?? I think so but want to confirm.
>
> C2 will reinitialize for every iteration of the c1 loop. "Still" does not apply here.
>
>
>
>
> Am 27.09.2019 um 20:52 schrieb Sweetser, Joe:
>
> Thanks!
>
>
>
> To be clear, procedure3a has 2 cursor ‘definitions’ in the declaration section. My tabs didn’t work for spacing last time. I apologize if that happens again.
>
>
>
> Procedure 3a is
>
>
>
> Cursor c1 is select ….
>
> Cursor c2 is select….
>
>
>
> Begin
>
> For x in c1 loop
>
> Declare
>
> -set some constants
>
> Begin
>
> For y in c2 loop
>
> Do some stuff
>
> End loop;
> End loop;
>
> End;
>
>
>
> Would c2 still be initialized at the start of the ‘For y’ loop?? I think so but want to confirm.
>
>
>
> Thanks again,
>
> -joe
>
>
>
> From: Jacek Gębal <jgebal_at_gmail.com>
> Sent: Friday, September 27, 2019 12:32 PM
> To: Sweetser, Joe <JSweetser_at_icat.com>
> Cc: oracle-l <oracle-l_at_freelists.org>
> Subject: Re: Quick (hopefully) pl/sql cursor question
>
>
>
> If the cursor is implicit in procedure3a like:
>
>
>
> FOR x IN (SELECT * FROM DUAL) LOOP
>
> --do some stuff here
>
> NULL;
>
> END LOOP;
>
>
>
> The cursor is initialized when the PLSQL engine goes into FOR ...LOOP statement.
>
>
>
> Cheers,
>
> Jacek
>
>
>
> On Fri, 27 Sep 2019 at 18:27, Sweetser, Joe <JSweetser_at_icat.com> wrote:
>
> Greetings,
>
> Does anyone know exactly when an explicit cursor is initialized in pl/sql? I understand I may need to supply more info, but I am no pl/sql wizard and don't know exactly what info is needed.
>
> I have a package with multiple procedures. One those procedures contains additional procedures. And one of those additional procedures has some cursors. Sorta like this:
>
> Package
> - procedure1
> - procedure2
> - procedure3
> - procedure3a
> - cursor 1
> - cursor 2
>
> Would the cursors be initialized when procedure3 is called or when procedure3a is called? I would guess procedure3a but the developer is seeing some weirdness where it looks like it's when procedure3 is called.
>
> There is no explicit "open cursor" call. It is used in a for loop, so I think oracle opens it automagically. But, again, I am trying to determine the initialization. When is the cursor loaded with data?
>
> Any/all ideas/suggestions welcome.
>
> Thanks,
> -joe
>

--
http://www.freelists.org/webpage/oracle-l