I have some questions regarding the Stored Procedure Adapter Service. If I have to use REF CURSOR, how can I assign values into the REF CURSOR if the values are not direct from a select statment.
Also, how can I send character string inputs to the stored procedure from Webmethods.
In the below example to say:
The result set is based on calculation and is available in the table of records tbl_order. This works at Oracle level. How do I get the results into Webmethods? How do I get the values in tbl_order into a REF CURSOR?
The query has orno hardcoded right now. The reason being I was not able to pass CHAR value successfully from Webmethods. How do I pass char values from Webmethods to Oracle SP as input? The error I get is unimplemented transformation requested or something to that effect.
Also is there a way to send in multiple inputs from WM to SP for an IN CLAUSE?
CREATE OR REPLACE PACKAGE TEST_PKG AS
TYPE type_rec IS RECORD (
TYPE type_tab is TABLE of type_rec
INDEX BY BINARY_INTEGER;
PROCEDURE TEST_SP(tbl_order OUT type_tab);
CREATE OR REPLACE PACKAGE BODY TEST_PKG AS
PROCEDURE TEST_SP(tbl_order OUT type_tab)
TYPE type_curvar IS REF CURSOR;
i NUMBER := 1;
OPEN cur_order FOR select T$orno, T$pono, T$cpva
where T$orno in (‘10000004’,‘10000005’);
FETCH cur_order INTO rec_ord;
EXIT WHEN cur_order%NOTFOUND;
tbl_order(i).cpva := rec_ord.pono + rec_ord.cpva;
tbl_order(i).ord := rec_ord.ord;
tbl_order(i).pono := rec_ord.pono;
i := i + 1;