How to get Oracle Stored Prodcedure Out Parameter

, , ,
1

Hi

Iam trying to get the values of Oracle Stored procedure out paramter from Enterprise Integrator, though in type of paramaters are working fine.

But when iam trying to get values of out parameter, its throwing
error.

Any one knows how to get the out type of parameter value

2

You have to write a wrapping stored procedure which receive a ref cursor. The problem is that webMethods generate a refcursor in the parameterstring of the Stored procedure for retrieve data from the stored procedure.

I hope that the follow example give you an idea:

CREATE OR REPLACE PACKAGE DBL_TRANSFER_FX_WEBM AS
Type outputCursor is ref cursor;
PROCEDURE GetSPO(out_cursor IN OUT DBL_TRANSFER_FX_WEBM.outputCursor);
END DBL_TRANSFER_FX_WEBM;
/
CREATE OR REPLACE PACKAGE BODY DBL_TRANSFER_FX_WEBM AS
PROCEDURE GetSPO(out_cursor IN OUT DBL_TRANSFER_FX_WEBM.outputCursor) IS
lfkond QL54.LFKOND%TYPE;
action QL54.action%TYPE;
TXCPSN QL86.TXCPSN%TYPE;
TXFOSN QL86.TXFOSN%TYPE;
TXSPFO QL86.TXSPFO%TYPE;
TXCOFO VARCHAR2(50);
CDDVAS QL86.CDDVAS%TYPE;
CDIMOD QL86.CDIMOD%TYPE;
TXUSSN QL86.TXUSSN%TYPE;
CDTYEV QL86.CDTYEV%TYPE;
TXCUBU QL86.TXCUBU%TYPE;
BTBUY QL86.BTBUY%TYPE;
TXCUSE QL86.TXCUSE%TYPE;
BTSELL QL86.BTSELL%TYPE;
CAPAMT INTEGER;
DTHAND QL86.DTHAND%TYPE;
ZTHAND QL86.ZTHAND%TYPE;
DTVA04 QL86.DTVA04%TYPE;
KSDEV QL86.KSDEV%TYPE;
KSKUND QL86.KSKUND%TYPE;
CDFITY QL86.CDFITY%TYPE;
CDMASA QL86.CDMASA%TYPE;
TXKOCO QL86.TXKOCO%TYPE;
CDKODK QL86.CDKODK%TYPE;

BEGIN
dbl_transfer_fx.GetSPO(
lfkond, action, TXCPSN, TXFOSN, TXSPFO, TXCOFO, CDDVAS, CDIMOD, TXUSSN,
CDTYEV, TXCUBU, BTBUY , TXCUSE, BTSELL, CAPAMT, DTHAND, ZTHAND, DTVA04,
KSDEV , KSKUND, CDFITY, CDMASA, TXKOCO,CDKODK);

  open out_cursor for Select 
                 lfkond, action, TXCPSN, TXFOSN, TXSPFO, TXCOFO, CDDVAS, CDIMOD, TXUSSN, 
                 CDTYEV, TXCUBU, BTBUY , TXCUSE, BTSELL, CAPAMT, DTHAND, ZTHAND, DTVA04, 
                 KSDEV , KSKUND, CDFITY, CDMASA, TXKOCO,CDKODK 
        FROM DUAL;

END;

END DBL_TRANSFER_FX_WEBM;
/

You call in the config. operation the wrapping stored procedure in place of the original stored procedure.

3

Thank you very much your information was very useful in
implementing cursor and accessing them.

Thanks
Jagadesh

4

Hi,

I have a stored procedure that returns a reference cursor. The purpose of writing the stored procedure (SP) is that I don’t want to store the results of my select query in memory, which will slow down the other processes as I am expecting many rows.
The question I have is that do I need to explicitly close the cursor? In my SP, I only open the cursor but do not close as the cursor is a part of my signature. I created Stored procedure with signature adapter service.
If any of you a better sugggestion of avoiding memory consumption, please let me know.

Thanks in advance