Can anyone help?
I have embedded SQL in the ProC program.
I am trying to retrieve data via a cursor then use this information to get data from another cursor.
Goes like this
Code:
EXEC SQL AT :dbname
DECLARE cur1 CURSOR FOR
SELECT details from 1 table using :host varable/bind variable
EXEC SQL AT :dbname
DECLARE cur2 CURSOR FOR
SELECT select details from 2 tables via join using host variable
EXEC SQL WHENEVER SQLERROR DO SPOTS_oracle_error ( ) ;
EXEC SQL OPEN cur1;
for ( ; ; )
{
EXEC SQL WHENEVER NOT FOUND DO break ;
EXEC SQL FETCH cur1
INTO :hv_var1 etc,
EXEC SQL OPEN cur2 ;
if(sqlca.sqlcode == 0) {
EXEC SQL FETCH cur2
INTO :var2;
}
EXEC SQL CLOSE cur2;
EXEC SQL CLOSE cur1;
However the above does not work - I have it logging into a log file so I can see where it goes. It retrieves the first row from cur1 but then fails on the fetch of cur2 (text in italics) - no errors - not even a no data found error are reported and nothing else is logged. The job finishes in error but nothing is logged anywhere about this errror - I have removed logs I have to make it easier to read. Am I doing something wrong?
I have also tried to move the declare of cur2 to after cur1 has been fetched but the samething happens - is there a problem with proc using nested cursors. Strangely it works if I use a straight select instead of cur2 ie
Code:
EXEC SQL AT :dbname
SELECT value INTO :value FROM table WHERE x = :hv_id
Thanks