Oracle Stored Procedure Error.

, ,
1

Hi all,

Though calling stored procedures through the JDBC stored procedure template is not new to me, I have been struggling with this one SP I am trying to call. I am sure I am missing some mundane detail. Hoping that someone here will set me straight and point out the mistake I am making.

So, here we go.

The description of the SP in Oracle is as follows.

Desc GetEmployerInfo.GetManualInfo gives me the following:

 PROCEDURE GETMANUALINFO
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 IN_POLICY_NUM                  VARCHAR2                IN
 IN_BUS_SEQ_NUM                 VARCHAR2                IN
 IN_PERIOD_END_DATE             DATE                    IN
 OUT_MANUAL_CURSOR              REF CURSOR              OUT
PROCEDURE GETMANUALINFO
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 IN_POLICY_NUM                  VARCHAR2                IN
 IN_BUS_SEQ_NUM                 VARCHAR2                IN
 OUT_MANUAL_CURSOR              REF CURSOR              OUT

Notice that when I did the desc, it returned two sets. This is new to me. The person who wrote this in on vacation. So, Can’t really ask him what’s going on here. So, now when I create a JDBC Adapter service in developer using the StoredProcedureWithSignature template, it fills in the parameters (refer to 1.jpg). The input/output tab only lists 3 input fields. When I run this, I get an error

 DORSBP00.GETEMPLOYERINFO.GETMANUALINFO( ?, ?, ?, ?, ?, ?, ?)}". "
(65000/6550) ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GETMANUALINFO'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
"
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GETMANUALINFO'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Please help!

Zafar.
1.JPG

2

It looks like whoever wrote the stored procedure has actually written 2 overriden procedures with different input parameters. The first SP has 3 input paramters and the second has 2 input parameters. It looks like they are making the IN_PERIOD_END_DATE an optional parameter

You a trying to make a call with 7 parameters which doesnt match either of these 2 stored procedures. I guess you have to pass an object to be filled in OUT_MANUAL_CURSOR so you either need to call the SP with 3 or 4 parameters

I havent dealt with getting WM to deal with passing back reference cursors from a SP so thats the best I can help with

Chris