How to call a Oracle 4argument Stored procedure from wmAdapter service?

, ,
1

Hi wmUsers,

i’ve created a stored procedure in a package with four ref cursor out variables as follows.

Package declaration also done.
[highlight=sql]
CREATE OR REPLACE PACKAGE BODY ARCHIVE_PKG

AS

PROCEDURE ARCHIVEDATA_PROC(BIZDOCCURSOR OUT BIZDOCCUR, BIZDOCCONTENTCURSOR OUT BIZDOCCONTENTCUR, ACTIVITYLOGCURSOR OUT ACTIVITYLOGCUR, DELIVERYJOBCURSOR OUT DELIVERYJOBCUR)

IS

BEGIN
OPEN BIZDOCCURSOR FOR
SELECT * FROM BIZDOC;

  OPEN BIZDOCCONTENTCURSOR FOR       
     SELECT * FROM BIZDOCCONTENT;

  OPEN ACTIVITYLOGCURSOR FOR       
     SELECT * FROM ACTIVITYLOG;

  OPEN DELIVERYJOBCURSOR FOR       
     SELECT * FROM DELIVERYJOB;

END ARCHIVEDATA_PROC;

END ARCHIVE_PKG;

[/highlight]
I want to call this procedure using wm jdbc adapter services.
There i couldn’t find how to pass 4oracle cursors as input to this proc.

I could invoke this proc using normal Java program which works fine.

…

[highlight=java]
CallableStatement cstmt = conn.prepareCall(“BEGIN ARCHIVE_PKG.ARCHIVEDATA_PROC(?,?,?,?); END;”);

cstmt.registerOutParameter(1,OracleTypes.CURSOR);
cstmt.registerOutParameter(2,OracleTypes.CURSOR);
cstmt.registerOutParameter(3,OracleTypes.CURSOR);
cstmt.registerOutParameter(4,OracleTypes.CURSOR);
cstmt.executeQuery();
ResultSet rset1;
rset1 = (ResultSet)cstmt.getObject(1);
while(rset1.next()){
System.out.println(rset1.getString(1));

[/highlight]
ResultSet rset1;
…
…

Under stored proc adapter service, at ‘Call’ Tab
i’ve given

Procedure Name : ARCHIVE_PKG.ARCHIVEDATA_PROC
JDBC Type : ORACLECURSOR
Return field name : BIZDOCCONENTCURSOR

i’ve defined columns names under Resultset also.

When i try to run this, i’m getting the following error:

 Could not run 'archiveData_Proc1'.

com.wm.app.b2b.server.ServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service adapterServices:archiveData_Proc.
[ADA.1.316] Cannot execute the SQL statement "{? = call SCOTT.ARCHIVE_PKG.ARCHIVEDATA_PROC}". "
(65000/6550) ORA-06550: line 1, column 32:
PLS-00306: wrong number or types of arguments in call to 'ARCHIVEDATA_PROC'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
"
ORA-06550: line 1, column 32:
PLS-00306: wrong number or types of arguments in call to 'ARCHIVEDATA_PROC'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Could anybody help me out?
Any ideas please…

Thanks in advance…

Ganesh

2

You will have to define four Return Field Names in the Call tab, isn’t it?

-Pauly

3

Yes, but there i’m not getting any chance to declare 4return type oracle cursors except only one.
Even i tried to run one argument (ref cursor out variabe) stored procedure instead of 4argument sp. Even then i’m getting the same error.

Please let me know your ideas over this.

Thanks,
Ganesh

4

Did you type the Return Field Name or you select from drop down menu. I see
a spelling mistake in the name BIZDOCCONENTCURSOR. Should’nt it be BIZDOCCONTENTCURSOR.

-Pauly

5

Yes, Pauly
Here i’ve given wrong.
But in wm Developer, i’ve given it perfect.
Did anyone execute ref cursor type stored procedure?
Please let me know the solution for this.

Thanks in advance,
Ganesh

6

RMG/Ray/Mar/Chris,

Could you please answer this one?

-Pauly