try to call your stored procedure as below:
In the example I used a stored procedure with 3 input parameters and 4
output parameters.
______________________________________________
OracleConnection connection = null;
OracleStatement statement = null;
try {
connection = getConnection();
statement = (OracleStatement) connection.createStatement();
CallableStatement cstmnt;
query.append("{ call YOUR_STORED_PROCEDURE(?,?,?,?,?,?,?) }");
cstmnt = connection.prepareCall(query.toString());
cstmnt.setString(1, firstInputVariable);
cstmnt.setString(2, seconfInputVariable);
cstmnt.setString(3, thirdInputVariable);
cstmnt.registerOutParameter(4, firstOutputVariable);
cstmnt.registerOutParameter(5, secondOutputVariable);
cstmnt.registerOutParameter(6, thirdOutputVariable);
cstmnt.registerOutParameter(7, fourthOutputVariable);
cstmnt.execute();
output1 = cstmnt.getString(4);
output2 = cstmnt.getInt(5);
output3 = cstmnt.getDate(6);
output4 = cstmnt.getLong(7);
......
____________________________________________________________
Hope it's useful.
"Panayotou, Michael" <[EMAIL PROTECTED]>@JAVA.SUN.COM> on 24/07/2001
10.14.03
Please respond to A mailing list about Java Server Pages specification and
reference <[EMAIL PROTECTED]>
Sent by: A mailing list about Java Server Pages specification and
reference <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
cc:
Subject: Re: Need your help on stored procedure in oracle
Here is an example
private void prepareData(
Connection p_dbConnection
, String p_invr_cd
)
throws AccountStockBalanceException{
String all = new String("%");
int i;
CallableStatement cstmt = null;
try{
/*
function prepare_rpt_hldgs_data(
in_invr_cd in varchar2
-- , in_acc_cd in varchar2
-- , in_prcp_cd in varchar2
-- , in_sec_isin_cd in varchar2
-- , in_usr_id in varchar2
-- , in_mdl in varchar2
-- , in_sessionid in number
, out_last_upd_date out date
, out_invr_chk_dgt out varchar2
, out_acc_cd out varchar2
, out_acc_chk_dgt out varchar2
, out_invr_dsc out varchar2
, out_invr_nm out varchar2
, out_invr_fth out varchar2
, out_results out varchar2
*/
cstmt = p_dbConnection.prepareCall(
"{? = call
axwebpgk.prepare_rpt_hldgs_data(?,?,?,?,?,?,?,?,?)}"
);
cstmt.registerOutParameter(1,
java.sql.Types.VARCHAR); // TRUE/FALSE
cstmt.setString(2,p_invr_cd); // Investor_cd
// cstmt.setString(3,all); //
ACC_CD
// cstmt.setString(4,all); //
PRCP_CD
// cstmt.setString(5,all); //
SEC_ISIN_CD
// cstmt.setString(6,new
String("AXIAWEB")); // USER_ID
// cstmt.setString(7,new
String("WEB")); // MDL
// cstmt.setString(8,p_invr_cd); //
MERIDA
cstmt.registerOutParameter(3, java.sql.Types.DATE);
cstmt.registerOutParameter(4, java.sql.Types.VARCHAR);
cstmt.registerOutParameter(5, java.sql.Types.VARCHAR);
cstmt.registerOutParameter(6, java.sql.Types.VARCHAR);
cstmt.registerOutParameter(7, java.sql.Types.VARCHAR);
cstmt.registerOutParameter(8, java.sql.Types.VARCHAR);
cstmt.registerOutParameter(9, java.sql.Types.VARCHAR);
cstmt.registerOutParameter(10, java.sql.Types.VARCHAR);
i = cstmt.executeUpdate();
if(cstmt.getString(1).equals("FALSE")){
//System.out.println("[axwebpgk.prepare_rpt_hldgs_data]:" +
cstmt.getString(10));
StringUtil.reportError("ERROR",getModuleInfo()+".prepareData()","1:"+
cstmt.
getString(10));
throw new
AccountStockBalanceException(cstmt.getString(10));
}
this.setInvr_cd(p_invr_cd);
this.setLast_upd_dt(cstmt.getTimestamp(3));
this.setInvr_chk_dgt(cstmt.getString(4));
this.setAcc_cd(cstmt.getString(5));
this.setAcc_chk_dgt(cstmt.getString(6));
this.setInvr_dsc(cstmt.getString(7));
this.setInvr_nm(cstmt.getString(8));
this.setInvr_fth(cstmt.getString(9));
} catch(Exception e){
//System.out.println("[axwebpgk.prepare_rpt_hldgs_data]:" +
e);
StringUtil.reportError("ERROR",getModuleInfo()+".prepareData()","2:"+
e.toString());
throw new AccountStockBalanceException(e.toString());
} finally{
try{
if(cstmt != null){
cstmt.close();
}
} catch(Exception e){
//System.out.println(e);
StringUtil.reportError("ERROR",getModuleInfo()+".prepareData()","3:"+
e.toString());
}
}
}
-----Original Message-----
From: lancelot [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 24, 2001 10:33 AM
To: [EMAIL PROTECTED]
Subject: Need your help on stored procedure in oracle
Excuse me,
who know how to use a sql clause to excute a stored procedure(in oracle)
with out parameter and display the out variable?
thanks
Lancelot
===========================================================================
To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff
JSP-INTEREST".
For digest: mailto [EMAIL PROTECTED] with body: "set JSP-INTEREST
DIGEST".
Some relevant FAQs on JSP/Servlets can be found at:
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.html
http://www.jguru.com/jguru/faq/faqpage.jsp?name=JSP
http://www.jguru.com/jguru/faq/faqpage.jsp?name=Servlets
===========================================================================
To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff
JSP-INTEREST".
For digest: mailto [EMAIL PROTECTED] with body: "set JSP-INTEREST
DIGEST".
Some relevant FAQs on JSP/Servlets can be found at:
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.html
http://www.jguru.com/jguru/faq/faqpage.jsp?name=JSP
http://www.jguru.com/jguru/faq/faqpage.jsp?name=Servlets
===========================================================================
To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST".
For digest: mailto [EMAIL PROTECTED] with body: "set JSP-INTEREST DIGEST".
Some relevant FAQs on JSP/Servlets can be found at:
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.html
http://www.jguru.com/jguru/faq/faqpage.jsp?name=JSP
http://www.jguru.com/jguru/faq/faqpage.jsp?name=Servlets
