Oracle Gateway limit on lengthy text fields

From: UnixDBA <unixdba_at_aol.com>
Date: 27 Aug 1998 04:18:18 GMT
Message-ID: <1998082704181900.AAA07598@ladder03.news.aol.com>

     Using the Oracle Transparent Gateway for Sybase and an Oracle database link, we are migrating data, table by table, from Sybase to Oracle. For each table, we create a stored procedure which declares a cursor which selects a batch of rows from the Sybase table. We then insert the data into its counterpart Oracle table row by row using a FOR loop cursor.

     A problem arises when we attempt to select Sybase columns of datatype TEXT (a BLOB datatype) with a datalength longer than 32760 characters. The text data is destined for an Oracle column of datatype LONG(32767). When the text data is less than 32760 on the Sybase side, all works fine. However when the text data reaches 32761 on the Sybase side, the FOR loop cursor fetch raises the exception

                      ORA-06502 numeric or value error

     Does anyone know of a workaround for this problem? Is the Gateway adding

invisible control characters which eat up the expected addtional 17 characters? Thanks in advance.

• Bill Mullen -- UnixDBA_at_aol.com

Received on Wed Aug 26 1998 - 23:18:18 CDT