Originally posted by: Kepa_Screen

Hi:

I'm trying to call to a PL-SQL stored Function from WTX 6.7.1, our rule for the field is next one :

=VALID(DBLOOKUP("CALL ?=APPS.PK_ARQ_MENSAJE.F_ARQ_INSERTA_EST_PROC_BATCH(200015,?,?,?)",
"-MDQ %mdq_file% -DBNAME GREDAPR -T"),
"0| " +
LASTERRORCODE() +
"|Error en la llamada a PK_ITF_FAC_BATCH.P_INI_EST_PROC_BATCH para insertar el estado del proceso - " + LASTERRORMSG())

and the trace file for the DB Adapter that we are getting is this :

<149504-001>: Validating the adapter command...
<149504-001>: Database type is Oracle
<149504-258>: Connecting...
<149504-258>: Host string: DAOL
<149504-258>: UserID : apps
<149504-258>: Password : ****
<149504-258>: Connection to Oracle has been established.
<149504-258>: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
<149504-258>: Returned status: (0) Success
<149504-258>: Interface library version 6.7(306_t07feb2006)
<149504-258>: Map: /home/entornos/desarrollo/itf/exec/mercator/mmc/itf_60_1050
_154.mmc, Timestamp: Fri Apr 27 12:33:42 2007.
<149504-258>: Data being retrieved for DBLOOKUP function.
<149504-258>: Database adapter: Oracle9i Version 6.7(306_t07feb2006)
<149504-258>: Starting a database unload...
<149504-258>: Query : CALL ?=APPS.PK_ARQ_MENSAJE.F_ARQ_INSERTA_EST_PROC_B
ATCH(200015,?,?,?)
<149504-258>: The columns are of the following types:
<149504-258>: Column 0 (RETURN_VALUE) type is NUMBER(40,0).
<149504-258>: Column 1 (P_COD_PROGRAMA) type is NUMBER(6).
<149504-258>: Column 2 (P_COD_PARTICION) type is VARCHAR(4000).
<149504-258>: Column 3 (P_FEC_EJECUCION) type is DATE.
<149504-258>: Column 4 (P_NUM_REPETICION) type is NUMBER(3).
<149504-258>: Column 5 (P_INFO_ADICIONAL) type is VARCHAR(4000).
<149504-258>: Unsupported datatype returned is being treated as text.
<149504-258>: Column 6 (P_R_ERROR) type is <Unknown>.
<149504-258>: Number of buffers in fetch array = 5
<149504-258>: The procedure statement to be executed is:
<149504-258>: begin :z99 := APPS.PK_ARQ_MENSAJE.F_ARQ_INSERTA_EST_PROC_BATCH(:
a00,:a01,:a02,:a03,:a04,:a05); end;

<149504-258>: Returned status: (-1034) The supplied statement was syntacticall
y invalid

<149504-258>: Cleaning up and closing the transaction...
<149504-258>: The transaction was successfully committed.
<149504-258>: Returned status: (0) Success
<149504-258>: Disconnecting...
<149504-258>: Database disconnect succeeded.
<149504-258>: Returned status: (0) Success
There is no problem if we change Function with Procedure, when we use a procedure instead of a function there is no problem with the sintax of the command.

Any advice on how to call to a Stored Function ??

Thanks in advance.
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

Can you provide the details of how your PL-SQL function is declared? Are you using packages? What is the return type of the function? Is it a complex type, such as a table? The reason I'm asking is the following line in your trace: "Unsupported datatype returned is being treated as text."

WTX opened a problem report against Oracle regarding an issue that sounds similar. Oracle was returning values associated with complex data types that were undocumented in their OCI interface. If your problem is being caused by the same issue, there may be a work-around available, but it would mean a little rework to your SQL. (Oracle was not interested in fixing this problem for us.)

Some of the things we found to help the problem were what you're seeing. Instead of returning the value from the function, you can return it as an OUT parameter via a store procedure. We also saw difference when moving declarations outside of packages.
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender