Transformation Extender Oracle adapter does not support SYS_REFCURSOR return type in an Oracle stored procedure / function
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.
The Oracle database adapter log (.dbl) reports the following error:
Unsupported datatype returned is being treated as text.
SYS_REFCURSOR is not a valid datatype as a return from an Oracle stored procedure call using the WTX / ITX Oracle adapter.
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.
More support for:
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