This discussion is archived
1 Reply Latest reply: Feb 16, 2012 6:05 AM by 916952 RSS

Pro*C Issue: Hanging

916952 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points