Hi,

We are using the webM Enterprise Integrator 4.5.1.
Using the DBAdapter we need to invoke a stored procedure having an in/out parameter. (No error in procedure as it runs perfect on the backend)
Can any tell me a fix to this pls? Or any pirticular configurations I need to make to my DBAdapter.

TIA
GK.

It exceptioned out this way:

(059) Could not process request document of type “Customer::CustomerData”
(108) Could not invoke script “OracleAdapterComponent4”
(108) Could not invoke script “StoredProcedureCallOperation”
(353) Could not execute SQL statement “BEGIN
TEST_PROC(:out_cursor);
END;”:
(308) ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to ‘TEST_PROC’
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

You should be able to get more info in the docs on this but the highlights are.

1. All your stored procs need to use a REF CURSOR type for the out parameters. That is all the out’s are passed in one REF CURSOR parameter.

2. All your stored procs must be in a package to comply with the above.

3. Use unicode_string for all the webMethods params to/from the stored proc, changing them from string if necessary.

Hope that helps.

Regards

Geoff

Sorry, forgot to mention that the REF CURSOR parameter must be the first one. So all your out parameters are returned via a single REF CURSOR which is the first parameter. Then any in parameters follow afterwards.

Geoff

Thank you Geoff. I’ll look into the points you have put down and let you know if those will solve my problem. I should be updating you soon on this.

until then,
GK

Yes Geoff,

Using REF CURSOR it works. Thanks again.

GK

I need to know how to call a stored procedure passing in a struct or an array of structs, but the proc must only return primitives.

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:

1. 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?

2. 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.

3. 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 (
ord scott.orderline.T$orno%TYPE,
pono scott.orderline.T$pono%TYPE,
cpva scott.orderline.T$cpva%TYPE
);
rec_ord type_rec;

TYPE type_tab is TABLE of type_rec
INDEX BY BINARY_INTEGER;
tbl_order type_tab;

PROCEDURE TEST_SP(tbl_order OUT type_tab);
END;

CREATE OR REPLACE PACKAGE BODY TEST_PKG AS

PROCEDURE TEST_SP(tbl_order OUT type_tab)
IS

TYPE type_curvar IS REF CURSOR;
cur_order type_curvar;

i NUMBER := 1;
BEGIN

OPEN cur_order FOR select T$orno, T$pono, T$cpva
from scott.orderline
where T$orno in (‘10000004’,‘10000005’);

LOOP
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;
DBMS_OUTPUT.PUT_LINE(tbl_order(i).cpva);

i := i + 1;

END LOOP;
CLOSE cur_order;
END TEST_SP;
END;