Win a copy of TDD for a Shopping Website LiveProject this week in the Testing forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • paul wheaton
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Frits Walraven
Bartenders:
  • Piet Souris
  • Himai Minh

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

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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;
 
Ranch Hand
Posts: 36
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Rancher
Posts: 4801
50
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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;
 
What is that? Is that a mongol hoarde? Can we fend them off with this tiny ad?
Free, earth friendly heat - from the CodeRanch trailboss
https://www.kickstarter.com/projects/paulwheaton/free-heat
reply
    Bookmark Topic Watch Topic
  • New Topic