Invoking a stored procedure in Enterprise Integrator.

, ,
1

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

2

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

3

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

4

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

5

Yes Geoff,

Using REF CURSOR it works. Thanks again.

GK

6

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.

7

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;