aspose file tools*
The moose likes JDBC and the fly likes stored procedure mismatch Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "stored procedure mismatch" Watch "stored procedure mismatch" New topic
Author

stored procedure mismatch

Dharmesh Chheda
Ranch Hand

Joined: May 08, 2001
Posts: 204
Hi ..

I have a Stored Proc which has input params as

p_papa_user_party_id_in IN --> Integer
p_pasu_user_version_nr_in IN --> Number
p_user_login_tx_in IN --> VARCHAR
p_cdt_display_language_cd_in IN --> VARCHAR
p_cdt_time_zone_cd_in IN --> VARCHAR
p_cdt_date_format_cd_in IN --> VARCHAR
p_cdt_number_format_cd_in IN --> VARCHAR
p_cdt_currency_cd_in IN --> VARCHAR
p_local_timestamp_in_in IN --> VARCHAR
p_adj_daylight_savings_in_in IN --> VARCHAR
p_comment_tx_in IN --> VARCHAR
p_system_user_attributes_xt_in IN --> XMLTYPE
p_latest_in_in IN --> VARCHAR
p_audit_update_user_name_tx_in IN --> VARCHAR
p_last_audit_update_dt_in INdate,
p_err_code_out out number,
p_err_mesg_out out varchar2


I am calling the stored proc and setting the params as


String updateStr = "{ call party_owner.px_co_pa_system_user_pg.SPT_UPDATE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
callableStmt = conn.prepareCall(updateStr);
int papaPartyid= 2329147;
int versionNumb = 1;
callableStmt.setInt(1,papaPartyid);
callableStmt.setInt(2,versionNumb);
callableStmt.setNull(3,Types.VARCHAR);
callableStmt.setNull(4,Types.VARCHAR);
callableStmt.setNull(5,Types.VARCHAR);
callableStmt.setNull(6,Types.VARCHAR);
callableStmt.setNull(7,Types.VARCHAR);
callableStmt.setNull(8,Types.VARCHAR);
callableStmt.setNull(9,Types.VARCHAR);
callableStmt.setNull(10,Types.VARCHAR);
callableStmt.setNull(11,Types.VARCHAR);
callableStmt.setObject(12,getCLOB("<hey></hey>",conn));
callableStmt.setString(13,"Y");
callableStmt.setString(14,"dchheda");
callableStmt.setNull(15,Types.DATE);
callableStmt.registerOutParameter(16,Types.INTEGER);
callableStmt.registerOutParameter(17,Types.VARCHAR);


Here getCLOB is a method which is forming the clob and returning the Clob object.

The problem is that when i fire the executeUpdate method i get the error as
java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SPT_UPDATE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


I checked all the types correctly.. nothing wrong with the stored proc .. any tips ...?

[ April 09, 2007: Message edited by: Dharmesh Chheda ]

[ April 09, 2007: Message edited by: Dharmesh Chheda ]

[edited to use a meaningful subject - was "HELP!!!"]
[ April 10, 2007: Message edited by: Jeanne Boyarsky ]

regards,<br />Dharmesh Chheda
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30752
    
156

Dharmesh,
It could be a number of things. For example, maybe the driver doesn't support XML, maybe it doesn't like nulls, etc.

I don't know that anyone is going to be able to point to the problem by just looking at it. You could try making the problem smaller by creating a stored proc with half of the parameters to see in which half the problem resides. And then keep doing that until you have one parameter causing a problem. Repost that and someone is more likely to know.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: stored procedure mismatch