Dynamic SQL problem (Oracle 9<->8)

From: Rafal Wojnar <rafalw_at_softcomputer.com>
Date: Tue, 30 Jul 2002 07:20:22 -0800
Message-ID: <F001.004A5C89.20020730072022@fatcity.com>

Hello,

I have software compiled in Oracle 9 environment. And when I try to fetch data using
different versions of Oracle client/server I receive ORA-errors:

1. client 9 --> server 8: "ORA-00932: inconsistent datatypes"
2. client 8 --> server 9: "ORA-01455: converting column overflows integer datatype"

These errors do not occur when connecting client 9 to server 9.

I use the following example code (similar to example in Oracle 9 documentation):

      EXEC SQL BEGIN DECLARE SECTION;
         int     intn_b;
         VARCHAR buffer[6];
         VARCHAR cursor_def[500];
      EXEC SQL END DECLARE SECTION;

      strcpy((char*)cursor_def.arr, "SELECT tstintn,tstcode FROM test WHERE
tstcode LIKE 'AU%'");
      cursor_def.len = strlen((char*)cursor_def.arr);

      EXEC SQL PREPARE S FROM :cursor_def;
      EXEC SQL DECLARE My_cursor CURSOR FOR S;
      EXEC SQL OPEN My_cursor ;
      EXEC SQL WHENEVER NOT FOUND DO break;

      for (;;)
      {
         int i=0;
         EXEC SQL FETCH My_cursor INTO :intn_b, :buffer;
         buffer.arr[buffer.len] = '\0';
         printf("%6d %7s\n", intn_b, buffer.arr);
      }
      EXEC SQL CLOSE My_cursor;

When I declare cursor without using dynamic SQL:

EXEC SQL DECLARE My_cursor CURSOR FOR

       SELECT tstintn, tstcode FROM test WHERE tstcode LIKE 'AU%';

everything works fine.

Does anyone know how to solve this problem?

Regards,
Rafal Wojnar
e-mail: rafalw_at_softcomputer.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Rafal Wojnar
  INET: rafalw_at_softcomputer.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Jul 30 2002 - 10:20:22 CDT