Re: Dynamic SQL causes infinite loop???

[Robert Buford <robertb@ABBASYS.COM>]

Hi Brannon,

I understand that, with Oracle, inserts will close the select cursor.  If
your insert is inside the select loop, you could try something like what I
did:

arrCount = 0                    !to count elements going into array
begin-select
  put $values into array(arrCount)
  arrCount = arrCount + 1
end-select

i = 0
while i < arrCount
   get $values from array(arrCount)
   do ProcB
end-while

HTH,
Bob

-----Original Message-----
From:   Fay, Brannon [SMTP:brannon.fay@PAETEC.COM]
Sent:   Thursday, May 06, 1999 1:16 PM
To:     Multiple recipients of list SQR-USERS
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