IBM Support

Transformation Extender Oracle adapter does not support SYS_REFCURSOR return type in an Oracle stored procedure / function

Technote (troubleshooting)


Problem(Abstract)

When using the IBM WebSphere Transformation Extender (WTX) / IBM Transformation Extender (ITX) Oracle adapter to call a stored procedure / function with a REF_SYSCURSOR return type, a failure of "Unsupported datatype returned is being treated as text" occurs.

Symptom

The Oracle database adapter log (.dbl) reports the following error:

Unsupported datatype returned is being treated as text.


Cause

SYS_REFCURSOR is not a valid datatype as a return from an Oracle stored procedure call using the WTX / ITX Oracle adapter.

Environment

IBM WebSphere TX / IBM TX Oracle adapter on any valid execution platform

Diagnosing the problem

LASTERRORMSG() reports 'Failed to execute the SQL statement' and the database adapter log reports 'Unsupported datatype returned is being treated as text.'

Resolving the problem

Redesign the Stored Procedure to NOT return a SYS_REFCURSOR datatype.

Document information

More support for: Transformation Extender
Database Adapter

Software version: 8.3, 8.4, 8.4.1, 9.0

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows, z/OS

Reference #: 1982008

Modified date: 28 April 2016