Maximum global variables????

Our entire user interface is completed via X-Window and 95% of the user interface uses global Oracle Embedded SQL variables. (In 6.0, where the UI was written, I don't remember if local variables gave us problems, didn't function or were unreliable, but they were all global.)

Because of this, we have many columns that are used in many different files and hence many different routines, but they should all be the same instance. In RedHat 6.1, Oracle 8.1.5.0.0 (no patches applied, beyond the PROC sqlctx=1 patch), we use an embedded SQL command like:

EXEC SQL BEGIN DECLARE SECTION;

int     cell_no;
int     port_no;
int     betalink;
int     spindle;
int     operation;

EXEC SQL END DECLARE SECTION; int GetOpAddr(op, blink)

int op;
char *blink;

{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  EXEC SQL WHENEVER NOT FOUND GOTO not_found;

  operation = op;

  EXEC SQL SELECT CELL_NO, PORT_NO, BETALINK, SPINDLE   INTO :cell_no, :port_no, :betalink, :spindle   FROM SPINDLE
  WHERE OPERATION = :operation;

  printf("Received string %02d:%02d:%02d-%02d and error %d.\n",

         cell_no, port_no, betalink, spindle, sqlca.sqlcode);

  sprintf(blink, "%02d:%02d:%02d-%02d", cell_no, port_no,

          betalink, spindle);
}

This code ends up returning a TOO_MANY_ROWS error (ORA-2122???) and the address 01:17:01-01, which if you check the SPINDLE table really has operation 9999! OPERATION is NOT an index (an operation will be unique, except a spindle without an operation assigned will be 0, so there will be many zeros, but we make sure there is only one operation of a non-zero number.)

The first question on this is why?

The "solution" was to create a local SQL variable (o_operation or something similar) inside the DECLARE SECTION internal to the routine and change operation to o_operation. Did that, and it work. Why?

Any answers you can provide would be greatly appreciated.

Terry Steyaert
steyaert_at_my-deja.com

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Apr 06 2000 - 09:17:16 CDT