Re: Dynamic SQL causes infinite loop???

[Tim Green <Tim_Green@NAD.ADP.COM>]

We just ran into the same problem.  Apparently, as documented in the SQR manual,
executing DDL commands from within a Begin-Select loop forces a commit, which
resets the Begin-Select's cursor.  I'm not very happy about this either,
especially because the 'Exit-Select' idea of yours won't work in my situation.

I just posted a message about this myself and am hoping that someone knows how
to circumvent this little "feature".  The only other way I know how to deal with
it is to save the DDL to a file and fire off another program (sqlplus, SQR, etc)
that'll read the file and execute the DDL for you on another connection.

Good luck, and if you find another way around this, let me know.

Tim





"Fay, Brannon" <brannon.fay@PAETEC.COM> on 05/06/99 01:16:19 PM

Please respond to SQR-USERS@list.iex.net

To:   Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
cc:    (bcc: Tim Green/HWRD/ASG_Louisville/NAD)
Subject:  Dynamic SQL causes infinite loop???




SQR 4.3.2.1
NT 4
Oracle 7.3.4

I added some procedures to my program that create a table with the name set
dynamically, then I insert records into this table and select back out of
it.

A begin-select calls proc A which calls the create, insert procs.  The main
begin-select keeps looping.  I've confirmed there is only 1 record for this
select by converting and testing as straight sql.  I then add loops=1 to the
begin-select line, and it is still looping.  I tried loops=5 for the heck of
it and it looped more than 5 times.

The only thing I found in the sqr-users archives was the mentioning of all
cursors being reset when using commit.  I commented out my commits for the
inserts, but I am assuming the commits are implicit.  Any ideas??? (please)

I can hardcode the begin-select to exit-select at the end, but I'd like to
know why this is happening.

Thanx,
Brannon

^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*

Brannon J. Fay
Junior Software Engineer
PaeTec Communications, Inc.
(716)340-2629
(877)472-3832

^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*^*

There are exceptions to every rule
And I am one of them