在java中调用procedure,procedure中往往会进行异常处理,这样会带来一个问题,当存储过程出错,其实程序是不知道的。通常的做法是讲错误的信息写入到日志表中。但对于我现在的环境,一是系统非常庞大,二是并无专业的DBA监控,数据库第三方维护,但拿到weblogic日志很容易,所以把存储过程的错误抛到中间件这一级,相对来说是个不错的选择。
package com.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
public class Test {
public static final String drive = "oracle.jdbc.driver.OracleDriver";
public static final String url = "jdbc:oracle:thin:@10.10.29.150:1522:ordb10";
public static final String DBUSER="test";
public static final String password="test";
public static void main(String args[]){
Connection conn = null;
CallableStatement cstmt = null;
try{
Class.forName (drive).newInstance();
conn = DriverManager.getConnection(url, DBUSER, password);
String strSQL = "update test set object_id=1 where";
cstmt = conn.prepareCall("{call proce_test(?,?)}");
cstmt.setString(1, strSQL);//输入参数
cstmt.registerOutParameter(2, java.sql.Types.VARCHAR);//输出参数
cstmt.execute();
System.out.println(cstmt.getString(2));//打印错误信息
}catch(Exception ex){
ex.printStackTrace();
}finally{
try{
if(cstmt != null){
cstmt.close();
}
}catch(Exception ex){
ex.printStackTrace();
}
try{
if(conn != null){
conn.close();
}
}catch(Exception ex){
ex.printStackTrace();
}
}
}
}
CREATE OR REPLACE PROCEDURE proce_test
(intputsql in string, v_error_message out string)
AS
BEGIN
EXECUTE IMMEDIATE intputsql;
Exception
WHEN OTHERS Then
v_error_message :=SQLCODE || ':' || SQLERRM || substr(dbms_utility.format_error_backtrace, 1, 200);
END proce_test;
输出的错误结果为:-936:ORA-00936: 缺失表达式ORA-06512: 在 "TEST.PROCE_TEST", line 5