oracle插数出错处理

创建存放数据表：

create table MESSAGE_CODE
(
  v_name varchar2(5)
)

创建存放错误信息表：

create table MESSAGE_E
(
  ERR_TIME     DATE,
  ERRM_MESSAGE VARCHAR2(300)
)

创建存储过程：

create or replace procedure pro_insert_message(v_message varchar2) as
  v_sql varchar2(4000);
begin
  insert into message_code values (v_message);
  commit;
exception
  when others then
    v_sql := sqlcode || ':' || sqlerrm;
    insert into message_e values (sysdate, v_sql);
    commit;
end;

测试：

package com.dada.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

public class Test_exception {

	public static void main(String[] args) {
		new Test_exception().pro_add();
		
	}

	public Connection init_oracle() {
		Connection conn = null;
		try {
			// 建立连接
			String url = "jdbc:oracle:thin:@192.168.0.114:1521:orcl";
			String user = "test";
			String password = "hdx";
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(url, user, password);

		} catch (Exception e) {
			System.out.println("数据库连接异常!");
			e.printStackTrace();
		}
		return conn;
	}

	// 测试调用oracle的存储过程
	public void pro_add() {

		Connection conn = init_oracle();
		ResultSet rs = null;
		CallableStatement pstmt = null;

		try {
			// 调用存储过程
			pstmt = conn
					.prepareCall("{ call pro_insert_message(?)}");
			pstmt.setString(1, "1234567");
			System.out.println(pstmt.execute());

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
}