Embedded SQL

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

The code above is run automatically by a batch process picking up jobs from the Oracle table.

Have found that it works when we enter a single job but when we use a repeating job it fails. Might be something to do with table locking - will post on a DB forum.

Thanks