Originally posted by: SystemAdmin

Hello - to anyone familiar with Oracle packages and WTX (I'm on 8.2.0.3)....

I've got a simple package:
create PACKAGE PM AS
FUNCTION fn_getpid (
i_exec_name IN VARCHAR2,
i_app_id IN VARCHAR2) RETURN NUMBER;
END;

Then I have a simple package body (both compile successfully and this body can be run and inserts a row):
create or replace PACKAGE BODY PM AS
FUNCTION fn_getpid(
i_exec_name in VARCHAR2,
i_app_id in VARCHAR2) RETURN NUMBER
IS l_pid NUMBER;
BEGIN
SELECT PROCESS_INFO_SEQ.nextval
INTO l_pid
FROM dual;
insert into procmon.process_info (process_id, executable_name, application_id, return_code)
values (l_pid,i_exec_name,i_app_id,0);
RETURN l_pid;
END;
END;

I keep getting the following in my DB trace no matter what I seem to do for a query call:

<3740-1272>: Query : Call ?= FN_GETPID(?,'f','t',?)
<3740-1272>: DBLOOKUP Function, Transaction scope: Map
<3740-1272>: TRACE command specified, file: C:\wtx_ws\wtx\utilities\maps\testGetPid.dbl
<3740-1272>: Error returned by OCIDescribeAny, errcode=4043
ORA-04043: object FN_GETPID does not exist

Can anyone point me in the right direction of trying to call a function within an Oracle Package from an output card? I need to at least return the l_pid for further processing.

My latest rule attempt is:
DbLookup("Call ?= FN_GETPID(?,'f','t',?)","-t -dbtype Oracle -connect xx -user yy -password zz")
I've also tried using the schema (such as PM.FN_GETPID) without any luck.

Any ideas would be greatly appreciated.
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: janhess

I've always had problems with packages - they only ever worked if qualified with the schema so the correct permissions are needed. I've found it much simpler to use functions or procedures.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: Kepa_Screen

You must provide schema owner, and put all in uppercase:

Example

=DBLOOKUP(
'CALL *APPS*.PK_ITF_M.F_GET_PID()',
'-MDQ %mdq_file%')

#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: Kepa_Screen

=DBLOOKUP('CALL APPS.PK_ITF_M.F_GET_PID()','-MDQ %mdq_file%')
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

Thank you for the suggestion - I gave it a shot, however it is still not working. I am connecting to the database as the schema owner AND putting the schema name prefix (the schema is PROCMON) in the string, and it's all uppercase:

=DbLookup("Call ?= PROCMON.FN_GETPID(?,'f','t')","-t -DBTYPE ORACLE -CONNECT DBDEV -USER procmon -PASSWORD zz")

And yet I'm still getting:

<3740-3884>: Query : Call ?= PROCMON.FN_GETPID(?,'f','t')
<3740-3884>: DBLOOKUP Function, Transaction scope: Map
<3740-3884>: TRACE command specified, file: C:\wtx_ws\wtx\utilities\maps\testGetPid.dbl
<3740-3884>: Error returned by OCIDescribeAny, errcode=4043
ORA-04043: object PROCMON.FN_GETPID does not exist

Anything else I might try?
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: Kepa_Screen

I would try using a procedure instead of a function.

Make one of the parameters of the procedure OUT and put there your "function" returned value. ( last one )

Then create a PUBLIC synonym for that procedure and give grant execute to public.

Call with schema name....

=DBLOOKUP('Call PROCMON.P_GET_FN( x,y, ? )','-MDQ %mdq_file%')

Are you using a mdq file for connecting to the database ?
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

I removed the package, created a procedure, granted execute and viola - we're working.

Thank you!
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange