Invoking a stored procedure in Enterprise Integrator.

, ,
1

Hi,

I am 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 particular configurations I need to make to my DBAdapter. (The procedure takes one single in/out parameter and I have passed it while calling)

TIA
GK.

Rob: Can you help me with the above pls?

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

Hi all,

Just in case if any of you may want to know how it works. We can use the REF CURSORS to achieve this.

and use the package name.proc name to invoke it at webMethods Enterprise DB adapter at proc operation.

eg:

CREATE OR REPLACE PACKAGE p_ora_pkg_counter AS
v_Counter integer := 0;
CURSOR ccount IS
SELECT
v_Counter
FROM
DUAL;

TYPE ctcount IS REF CURSOR RETURN ccount%rowtype;

procedure docount (p_cursor in out ctcount,
                       p_ename VARCHAR2);

END;
/
SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY p_ora_pkg_counter AS
procedure docount (p_cursor in out ctcount,
p_ename VARCHAR2)
IS
v_Counter integer;
v_Temp integer;
BEGIN
v_Counter := 0;

	select count(*) into v_Temp
	from bonus where ename=p_ename;
	
	v_Counter := v_Counter + v_Temp;
	
	select count(*) into v_Temp
	from bonus_2 where ename=p_ename;

	v_Counter := v_Counter + v_Temp;

	select count(*) into v_Temp
	from bonus_3 where ename=p_ename;

	v_Counter := v_Counter + v_Temp;

	OPEN p_cursor FOR
	SELECT
		v_Counter
	FROM 
		DUAL;
END ;

END p_ora_pkg_counter;
/
SHOW ERRORS

3

I’m interested in calling stored procedures in WM4.5, and I also used cursors and packages to achieve it, but I still don’t manage to configure the configured operation in WM … how to pass a cursor param as parameter … I tried a unicode String but I always have the same error message:

java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to ‘TST_BSCS_POC’
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Thanks for your help

4

All I can suggest is simplify until it works and then add the complexity back. Start with the solution posted earlier and remember to use “unicode_string” and all the cunning you have when working with Enterprise. If necessary go as far back as a paramterless call which does something, add in the ref cursor or a numeric input and build from there.

Anyone else any ideas?

Regards

Geoff

5

Our procedure call doesn’t require any parameter, the procedure only perform a select in a table.
We tried to call the configured operation with a string argument representing the cursor, but we still ahve the same error … “wrong number or types of arguments” …

here is the procedure:

package tst_nico as

CURSOR out_cursor is select CUSTOMER_FIRSTNAME, CUSTOMER_LASTNAME from BSCS_POC;

TYPE t_cursor_bscs_poc is ref cursor return out_cursor%rowtype;

procedure TST_BSCS_POC(p_cursor in out t_cursor_bscs_poc);
end;
/

package body tst_nico as
procedure TST_BSCS_POC(p_cursor in out t_cursor_bscs_poc) is
BEGIN
open p_cursor for
SELECT CUSTOMER_FIRSTNAME, CUSTOMER_LASTNAME
FROM BSCS_POC;
END;
end tst_nico;

Thanks for your help … We know it works … people on this forum managed to do it … HELP !!!

6

I can’t see anything wrong there. Though I cannot be certain as I am not able to test a solution out for you right now. I would suggest you contact webMethods Technical Support as they will be able to help you very quickly and specifically with your version of the software.

Regards

Geoff

7

Geoff, thank you for your help …
We didn’t understand why it doesn’t work with JDBC Adapter …
But the same test with the Oracle adapter works without any problem !
Our The WM Support is already working on the problem … W