• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

java.sql.SQLException: "ORA-06550: PLSQL-00306: wrong number or types of arguments in call to "

 
Subash Kumar Sahu
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am getting an error "PLSQL 00306: wrong number or types of arguments in call to...." while calling a Oracle stored procedure from java

following is my java code :

public ResultSet callShgLoansQry(String comCode,String mainCode,String unitCode,String shgno,String lsrid)
throws SQLException{
Array arr = null;
String query = "{call shg_loans_qry(?,?,?,?,?,?)}";
cstmt = con.prepareCall(query);
cstmt.setString(1, comCode );
cstmt.setString(2, mainCode );
cstmt.setString(3, unitCode);
cstmt.setString(4, shgno);
cstmt.setString(5, lsrid);
cstmt.registerOutParameter(6, OracleTypes.ARRAY,"LOAN_DTS_TAB");
cstmt.execute();
arr = cstmt.getArray(6);
return arr.getResultSet();
}

following is my stored procedure :

CREATE OR REPLACE PROCEDURE shg_loans_qry(V_COMP IN VARCHAR2,V_MAIN IN VARCHAR2,V_UNIT IN VARCHAR2,V_SHG IN VARCHAR2,V_EMP_ID IN VARCHAR2,loan_rec_Tab out shg_qry.loan_dts_tab) AS
cursor c1 is select A.COMP_CODE,A.MAIN_CODE,a.unit_code,a.loan_no,a.csa_code,b.client_name ,c.emp_id,b.regis_group,D.int_rate,D.INT_TYPE,
D.last_disbdate disbdt,D.total_disbamt disbamt,a.client_code,b.phone_nos
from bo119_prodsanction_mst a
,bo501_regis_hdr b
,cm010_emp_mst c
,BO301_LOAN_SUMM D where ..........


and this is my shg_qry.loan_dts_tab :

CREATE OR REPLACE PACKAGE shg_qry is
type loan_rec is Record (
comp_code varchar2(3),
main_code varchar2(3),
unit_code varchar2(3),
loan_no varchar2(10),
CLIENT_NAME Varchar2(100),
client_code varchar2(100),
shg_group_name varchar2(100),
mobile_no varchar2(20),
os_amt number,
TOBE_COLL number,
os_Prin number,
Od_Prin number,
Os_Int number,
Od_Int number,
od_icr number,
os_icr number,
inst_int number,
inst_prn number,
inst_icr number);
loan_dtls loan_rec;
type loan_dts_tab is table of loan_dtls%type index by binary_integer;
end shg_qry;
 
es kranthi
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There are six placeholders in
String query = "{call shg_loans_qry(?,?,?,?,?,?)}";
and your procedure just contains 5
CREATE OR REPLACE PROCEDURE shg_loans_qry(V_COMP IN VARCHAR2,V_MAIN IN VARCHAR2,V_UNIT IN VARCHAR2,V_SHG IN VARCHAR2,V_EMP_ID IN VARCHAR2,loan_rec_Tab out shg_qry.loan_dts_tab) AS
.

There is a mismatch in the number.
 
Subash Kumar Sahu
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
CREATE OR REPLACE PROCEDURE shg_loans_qry(V_COMP IN VARCHAR2, V_MAIN IN VARCHAR2, V_UNIT IN VARCHAR2, V_SHG IN VARCHAR2, V_EMP_ID IN VARCHAR2, loan_rec_Tab out shg_qry.loan_dts_tab) AS .....

It contain 5 IN parameter of type VARCHAR2 and 1 OUT parameter of type SHG_QRY.LOAN_DTS_TAB
 
Subash Kumar Sahu
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
CREATE OR REPLACE PROCEDURE shg_loans_qry(V_COMP IN VARCHAR2, V_MAIN IN VARCHAR2, V_UNIT IN VARCHAR2, V_SHG IN VARCHAR2, V_EMP_ID IN VARCHAR2, loan_rec_Tab out shg_qry.loan_dts_tab) AS .....


It contain 5 IN parameter of type VARCHAR2 and 1 OUT parameter of type SHG_QRY.LOAN_DTS_TAB
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The parameter count matches.
JDBC cannot handle PLSQL declared types, which is the cause of your problem. It is simply unaware of them.
You will need to declare them as OBJECTs in Oracle and a TABLE.

CREATE TYPE loan_rec AS OBJECT (your columns);
CREATE TYPE loan_dts_tab AS TABLE OF loan_rec;
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic