oracle插数,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();

}

}

}