ORA-28562 data error truncation in oracle heterogenous?

Question

I have created an Oracle heterogenous service to db2 using DG4ODBC.

try login using isql and select using this query

select *from workorder@maximo 

I get a result set all of the data

but when I am login using sqlplus Oracle I get this error

this is my heterogenous init

and I try using HS_FDS_TRACE_LEVEL=DEBUG and get some column have a sql_null_value

This the sample of the log from debugging:

if any columns have null value then the data cannot be showing.

how I can show the data even though have a null value?? I am using Oracle 12c and db2 10.5

Answer 1

Try using:

HS_FDS_SQLLEN_INTERPRETATION = 32

(From Ocacle docs) This parameter is only valid for 64 bit platforms. ODBC standard specifies SQLLEN (of internal ODBC construct) being 64 bit on 64 bit platforms, but some ODBC driver managers and drivers violate this convention, and implement it as 32 bit. In order for the gateway to compensate their behavior, you need to specify HS_FDS_SQLLEN_INTERPRETATION=32 if you use these types of driver managers and driver.

It is unclear from your question the bitness of the Db2 odbc client, or whether the driver-manager or driver is using 32-bit or 64-bit internally for this. After changing this parameter you may need to re-initialise. A CLI trace of Db2 (google for instructions) may also expose this.

Answer 2

I've got this error when I was trying to load a TEXT data type from Postgres to Oracle VARCHAR2(4000) and the source data in the TEXT column was bigger than 4000 bytes. One of available workarounds could be to map TEXT column to PL/SQL's VARCHAR2(32767) variable or maybe LONG, CLOB data type.