Oracle Database Discussions

DG4ODBC is configured and working for XE 11gR2, most remote tables can be queried just fine from Oracle but I'm receiving ORA-28500 apparently as a result of some discrete data anomaly in the case of one particular remote table. How can I isolate particular row(s) containing value(s) from remote data source causing this error?

Configuration: DG4ODBC for Oracle 11gR2 using ODBC datasource based on IBM UniVerse ODBC Driver 4.00.04.7346 UVODBC.DLL (8/25/2009) all on same 32-bit Windows host for purpose of retrieving data from remote AIX based IBM/Rockit UniVerse version 10.1 database.

I receive the following ORA-28500 error apparently as a result of an incorrectly formatted numeric value within remote table:

13:52:31 SQL> create table prod_det as (select * from PROD_DET@DBLINK);
create table prod_det as (select * from PROD_DET@DBLINK)
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Rocket U2][UVODBC][2701131]Error ID: 23 Severity: ERROR Facility: DATUMERR -
Incorrectly formatted number.Error noticed at the character or token ending at
column 65 of line 65535 of the input SQL statement. {S1000}
ORA-02063: preceding 2 lines from DBLINK

There are 60,566 records/rows in remote file/table:

I performed level 255 gateway trace for operation causing ORA-28500 error above. DG4ODBC settings:

HS_FDS_CONNECT_INFO = DBLINK.ODBC
HS_FDS_TRACE_LEVEL = ON
HS_FDS_TRACE_LEVEL = 255
HS_TRANSACTION_MODEL = READ_ONLY_AUTOCOMMIT
HS_FDS_FETCH_ROWS=1
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P1

I allowed operation to run for over 2 hours before killing it. Trace file produced up to that point is approximately 350MB and it seems only about 1/6 of records had been scanned based on a count of references to string "Exiting hgoftch" in trace file. Cost of performing a level 255 gateway trace certainly can be quite expensive it seems!

I turned trace off and similar but limited version of erroneous operation above was executed which completed successfully:

13:55:54 SQL> create table prod_det as (select * from PROD_DET@DBLINK where rownum < 11);
Table created.

Error message identifies that a value in column 65 is responsible for the error. Column 65 is a numeric value named OH_VALUE. I executed initial operation excluding only this column which completed successfully in under 2 minutes (with trace turned off):

14:56:54 SQL> create table prod_det_temp as (
14:57:32 2 select
14:57:32 3 Z_ID,
14:57:32 4 ACC_PHYS_CNT,
14:57:32 5 ADJ_MTD,
14:57:32 6 ADJ_YTD,
<< intervening values omitted >>
14:57:32 140 WLOC1,
14:57:32 141 WLOC2,
14:57:32 142 WLOC3,
14:57:32 143 WLOC4,
14:57:32 144 YR_BEG_INV
14:57:32 145 from PROD_DET@DBLINK);

Table created.

Elapsed: 00:01:46.89
14:59:20 SQL> select count (*) from PROD_DET_TEMP;

COUNT(*)
----------
60566


I tried HS_FDS_TRACE_LEVEL value 'DEBUG' which appears to represent the same level of trace as value '255' and I can't see anything in output file of 255/debug gateway trace referencing erroneous or other data values that may be useful to identify specific problematic row(s).

So from what I've observed and outlined above it seems that issue causing ORA-28500 in this case is some kind of data integrity or formatting problem with value(s) of a single column OH_VALUE on remote table. Is there a particular type of gateway trace or some other diagnostic/programming technique associated with DG4ODBC use that can be enabled to pinpoint the particular row(s) causing this error?

Thanks in advance for any advice anyone may be able to provide!

Glenn

Edited by: WileyCoyote on Mar 8, 2012 1:59 PM