Originally posted by: Kepa_Screen

Hi, i'm having some trouble calling a pl-sql procedure from a WTX Map.
This was working ok at mercator 6.7.1, but does not work well when migrated to 8.1.0.3

This the log:

code<81978-001>: Validating the adapter command...
<81978-001>: Database type is Oracle
<81978-515>: Connecting...
<81978-515>: Host string: PGREDFAC
<81978-515>: UserID : APPS
<81978-515>: Password : *****
<81978-515>: DBLOOKUP Function, Transaction scope: Map
<81978-515>: TRACE command specified, file: /home/itf/exec/mercator/mmc/traza_2001.txt
<81978-515>: Connection to Oracle has been established.
<81978-515>: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
<81978-515>: Returned status: (0) Success
<81978-515>: Interface library version 8.1(37)
<81978-515>: Map: /home/itf/exec/mercator/mmc/itf_159_2001_60.mmc, Timestamp: Thu Oct 9 15:38:47 2008.
<81978-515>: Data being retrieved for DBLOOKUP function.
<81978-515>: Database adapter: Oracle9i Version 8.1(37)
<81978-515>: Starting a database unload...
<81978-515>: Query : CALL APPS.PK_ITF_159_GXXX.P_ACTUALIZAR_2001([[]],0,[[]],0,[[563|563|563|564|564|564]],1
,C016_2008100710191119974,?,?)
<81978-515>: DBLOOKUP Function, Transaction scope: Map
<81978-515>: TRACE command specified, file: /home/itf/exec/mercator/mmc/traza_2001.txt
<81978-515>: The columns are of the following types:
<81978-515>: Column 1 (P_T_FPROVR_ACT) type is ADT.
<81978-515>: The columns are of the following types:
<81978-515>: Column P_T_FPROVR_ACT - 1 (FAC_T_T_FPRO) type is ADT.
<81978-515>: The columns are of the following types:
<81978-515>: Column FAC_T_T_FPRO - 1 (COD_DOC_COT) type is NUMBER(10).
<81978-515>: Column 2 (P_FLG_HAY_FACT) type is VARCHAR(4000).
<81978-515>: Column 3 (P_T_CORREC_ACT) type is ADT.
<81978-515>: The columns are of the following types:
<81978-515>: Column P_T_CORREC_ACT - 1 (FAC_T_T_CORR) type is ADT.
<81978-515>: The columns are of the following types:
<81978-515>: Column FAC_T_T_CORR - 1 (COD_DOC_COT) type is NUMBER(10).
<81978-515>: Column 4 (P_FLG_HAY_CORREC) type is VARCHAR(4000).
<81978-515>: Column 5 (P_T_PROVCOM_ACT) type is ADT.
<81978-515>: The columns are of the following types:
<81978-515>: Column P_T_PROVCOM_ACT - 1 (FAC_T_T_PROV) type is ADT.
<81978-515>: The columns are of the following types:
<81978-515>: Column FAC_T_T_PROV - 1 (COD_DOC_PROVIS) type is NUMBER(10).
<81978-515>: Column 6 (P_FLG_HAY_PROVCOM) type is VARCHAR(4000).
<81978-515>: Column 7 (P_COD_FICHERO_INT) type is VARCHAR(4000).
<81978-515>: Column 8 (P_COD_ERROR) type is NUMBER(5).
<81978-515>: Column 9 (P_MSG_ERROR) type is VARCHAR(32000).
<81978-515>: Number of buffers in fetch array = 1
<81978-515>: The procedure statement to be executed is:
<81978-515>: begin APPS.PK_ITF_159_GXXX.P_ACTUALIZAR_2001(:a00,:a01,:a02,:a03,:a04,:a05,:a06,:a07,:a08); end;
<81978-515>: Error (-1) returned by OCINumberFromText, errcode=22061
OCI-22061: invalid format text [999
<81978-515>: *ERR: in CAdp::_OraStreamToField (-2)
<81978-515>: *ERR: in CAdp::_ReadStream (-2)
<81978-515>: *ERR: in CAdp::_ReadStream (-2)
<81978-515>: *ERR: in CAdp::ProcessStream (-2)
<81978-515>: *ERR: in adpProcessStream (-2)
<81978-515>: Returned status: (-1097) Failed_to_create_an_object
<81978-515>: Cleaning up and closing the transaction...
<81978-515>: The transaction was successfully committed.
<81978-515>: Returned status: (0) Success
[/code]

There is some kind of error with a number that can not be converted from text....
But i'm not sure what of them it's causing that trouble.

Any kind of info is welcomed.
Thanks
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: Kepa_Screen

<81978-001>: Validating the adapter command...
<81978-001>: Database type is Oracle
<81978-515>: Connecting...
<81978-515>: Host string: PGREDFAC
<81978-515>: UserID : APPS
<81978-515>: Password : *****
<81978-515>: DBLOOKUP Function, Transaction scope: Map
<81978-515>: TRACE command specified, file: /home/itf/exec/mercator/mmc/traza_2001.txt
<81978-515>: Connection to Oracle has been established.
<81978-515>: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
<81978-515>: Returned status: (0) Success
<81978-515>: Interface library version 8.1(37)
<81978-515>: Map: /home/itf/exec/mercator/mmc/itf_159_2001_60.mmc, Timestamp: Thu Oct 9 15:38:47 2008.
<81978-515>: Data being retrieved for DBLOOKUP function.
<81978-515>: Database adapter: Oracle9i Version 8.1(37)
<81978-515>: Starting a database unload...
<81978-515>: Query : CALL APPS.PK_ITF_159_GXXX.P_ACTUALIZAR_2001([[]],0,[[]],0,[[563|563|563|564|564|564]],1
,C016_2008100710191119974,?,?)
<81978-515>: DBLOOKUP Function, Transaction scope: Map
<81978-515>: TRACE command specified, file: /home/itf/exec/mercator/mmc/traza_2001.txt
<81978-515>: The columns are of the following types:
<81978-515>: Column 1 (P_T_FPROVR_ACT) type is ADT.
<81978-515>: The columns are of the following types:
<81978-515>: Column P_T_FPROVR_ACT - 1 (FAC_T_T_FPRO) type is ADT.
<81978-515>: The columns are of the following types:
<81978-515>: Column FAC_T_T_FPRO - 1 (COD_DOC_COT) type is NUMBER(10).
<81978-515>: Column 2 (P_FLG_HAY_FACT) type is VARCHAR(4000).
<81978-515>: Column 3 (P_T_CORREC_ACT) type is ADT.
<81978-515>: The columns are of the following types:
<81978-515>: Column P_T_CORREC_ACT - 1 (FAC_T_T_CORR) type is ADT.
<81978-515>: The columns are of the following types:
<81978-515>: Column FAC_T_T_CORR - 1 (COD_DOC_COT) type is NUMBER(10).
<81978-515>: Column 4 (P_FLG_HAY_CORREC) type is VARCHAR(4000).
<81978-515>: Column 5 (P_T_PROVCOM_ACT) type is ADT.
<81978-515>: The columns are of the following types:
<81978-515>: Column P_T_PROVCOM_ACT - 1 (FAC_T_T_PROV) type is ADT.
<81978-515>: The columns are of the following types:
<81978-515>: Column FAC_T_T_PROV - 1 (COD_DOC_PROVIS) type is NUMBER(10).
<81978-515>: Column 6 (P_FLG_HAY_PROVCOM) type is VARCHAR(4000).
<81978-515>: Column 7 (P_COD_FICHERO_INT) type is VARCHAR(4000).
<81978-515>: Column 8 (P_COD_ERROR) type is NUMBER(5).
<81978-515>: Column 9 (P_MSG_ERROR) type is VARCHAR(32000).
<81978-515>: Number of buffers in fetch array = 1
<81978-515>: The procedure statement to be executed is:
<81978-515>: begin APPS.PK_ITF_159_GXXX.P_ACTUALIZAR_2001(:a00,:a01,:a02,:a03,:a04,:a05,:a06,:a07,:a08); end;
<81978-515>: Error (-1) returned by OCINumberFromText, errcode=22061
OCI-22061: invalid format text [999
<81978-515>: *ERR: in CAdp::_OraStreamToField (-2)
<81978-515>: *ERR: in CAdp::_ReadStream (-2)
<81978-515>: *ERR: in CAdp::_ReadStream (-2)
<81978-515>: *ERR: in CAdp::ProcessStream (-2)
<81978-515>: *ERR: in adpProcessStream (-2)
<81978-515>: Returned status: (-1097) Failed_to_create_an_object
<81978-515>: Cleaning up and closing the transaction...
<81978-515>: The transaction was successfully committed.
<81978-515>: Returned status: (0) Success
sorry, something was corrupting the log in previous message
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: janhess

At a guess I'd think it was to do with the datetime formatting.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: Kepa_Screen

Hi Jahness, first of all , thank you for your quick reply...allways at the foot of the gun.... ;)

C016_2008100710191119974
Column 7 (P_COD_FICHERO_INT) type is VARCHAR(4000).

This is not a date item, its a varchar containing the name of a file.

I think that it's something related to [563|563|564] ... this is a table of "cod_doc_cot" ( number )
Type is table of cod_doc_cot's , where cod_doc_cot is number(10)
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: Kepa_Screen

This is the functional map rule:

=F_EjecutarSQLDB( VALID(DBLOOKUP("CALL APPS.PK_ITF_159_GXXX.P_ACTUALIZAR_2001(" +
package(ListaPKFPROVR) + ","+
IF(CodigoFPROVR ="0","1","0") + ","+
package(ListaPKCORREC) + ","+
IF(CodigoCORREC ="0","1","0") + ","+
package(ListaPKPROVCOM) + ","+
IF(CodigoPROVCOM ="0","1","0") + ","+
NombreFicheroDestino + "," +
"?,?"+ ")",
" -MDQ %mdq_file% -DBNAME GREDFAC -TRACE traza_2001.txt"),
LASTERRORCODE() + "|" + "ERROR GREDFAC : CALL APPS.PK_ITF_159_GXXX.P_ACTUALIZAR_2001 - " + LASTERRORMSG()))
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

I've never used Abstract Data Types before, but I would suspect the null sets you are passing may be the cause. You seem to be passing two of these ([[]]).
Just a guess. I also notice you are using an Oracle 9i client to call Oracle 10g; no idea if that has any impact.

I'd also debug the call in SQL*PLUS. Assuming you can call it without harming data, of course! I've used the following to run stored procedures in SQL*PLUS.

I don't have your definitions, but it would look something like the following. Again, never using ADT's before, I'm not sure if the literal values below can be passed as I'm demonstrating:

set serveroutput on;
declare
P_COD_ERROR NUMBER(5);
P_MSG_ERRORVARCHAR(32000);
begin
APPS.PK_ITF_159_GXXX.P_ACTUALIZAR_2001([[]],0,[[]],0,[[563|563|563|564|564|564]],1
,C016_2008100710191119974,P_COD_ERROR,P_MSG_ERRORVARCHAR);
DBMS_OUTPUT.PUT_LINE(P_COD_ERROR);
DBMS_OUTPUT.PUT_LINE(P_MSG_ERRORVARCHAR);
end;
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: Kepa_Screen

Obviously, this can not be done.
Brackets "[]" are put by Oracle Connector, but are not "real", are not part of the data.

The brackets are used always at DBLOOKUPS, but they are not put by us, are put by the connector to database....
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

I think you are passing text to number filed. remove package to the numeric field.
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

According to the OCINumberFromText document (http://www.cs.uvm.edu/oracle9doc/appdev.901/a89857/oci18m81.htm):

This function returns an error if there is an invalid format, an invalid multibyte format, or an invalid
input string, if number or str is null, or if str_length is zero.

The way I'm reading the trace, it looks like the ADT's for parms 1 and 3 are eventually converted to some sort of number.
It looks like parameter 1 and 3 are zero length strings ([[]]), so there's a failure because Oracle doesn't know
how to turn a zero length string into a number?

What happens if 'ListaPKFPROVR' and 'ListaPKCORREC' have some data?
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: Kepa_Screen

Hi, finally it was solved. We didn't use "PACKAGE" for ListaXXXXX items at the call and the problem was solved.

There were more brackets than neccesary.

Now it works well.

Thank you to all for your replies.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender