1 Reply Latest reply: Feb 16, 2012 8:05 AM by 916952 RSS

    Pro*C Issue: Hanging

    916952
      Hello everybody, I'm very new to this forum and I would like to submit you a question.
      I got some big problem on fetching values - inserting values from an oracle Db (input) to another (output).
      I'm using Pro*C, and the tables both in Dbin and DbOut, have the same features.

      Here it is a simplified code:

      --------------------------------------------------------------------------------------------------------

      EXEC SQL AT dbInput CONNECT :connIN;
      EXEC SQL AT dbOutput CONNECT :conn;

      EXEC SQL BEGIN DECLARE SECTION;
      here i declare the variables to store datas:
      struct cc_struct, struct cc_ind,
      int Other_value etc.
      EXEC SQL END DECLARE SECTION;

      ...

      for (;;) {

           EXEC SQL AT dbInput
                DECLARE cursor_input CURSOR FOR
                SELECT ...

           EXEC SQL AT dbInput OPEN cursor_input
           
           */Starts fetching values*/

           EXEC SQL AT dbInput
      FETCH cursor_input
      INTO :cc_struct indicator :cc_ind;

      for (;;) */ Here the inner loop: writes on destination DB values taken from source DB*/
      {

                EXEC SQL AT dbOutput
      INSERT INTO TABLE
                     (coumn1, column2 ...)
                VALUES(cc_struct.value1, cc_struct.value2...)
                RETURNING Other_values into Other_value

                //here another fetch

                
                EXEC SQL AT dbInput
           FETCH cursor_input
           INTO :cc_struct indicator :cc_ind;

                */ TERMINATES when sqlca.sqlcode is 1403, or when retrieves an error
           }

           */ TERMINATES when sqlca.sqlcode is 1403, or when retrieves an error
      }


      ------------------------------------------------------------------------------------------------

      The issue comes when i'm trying to insert the values at dbOutput: it seems to run fine when retrieves/writes
      the first record, then it HANGS, without returning me any useful information, such as runtime errors or Oracle error.
      Any ideas?
      I thought it could be that, when i'm tring to retrieve the datas for the second time (inner-loop fetch), i'm
      doing it wrong, perhaps i need to clean the struct before inserting new values on it.
      The second idea is that i'm suddenly loosing connection towards the output Db, in fact, when i put the connection
      string (EXEC SQL AT dbOutput CONNECT :conn) before the INSERT operation, it works well.
      and, oh, forgot to mention that the system is SUN/SOLARIS.
      Thank you very much,
      cheers,
      Archduke