oracle hex,案例:Oracle报错ORA-01465: invalid hex number 插入blob字段报错

天萃荷净

开发DBA人员反馈，在blob插入一些字段报错，查询metalink，发现使用rawtohex处理即可

测试如下：

SQL> create table t_lob (t blob);

Table created.

SQL> insert into t_lob values('-------');

insert into t_lob values('-------')

*

ERROR at line 1:

ORA-01465: invalid hex number

SQL> insert into t_lob values(rawtohex('---------'));

1 row created.

SQL> insert into t_lob values('－－－－－－－－－－&');

insert into t_lob values('－－－－－－－－－－&')

*

ERROR at line 1:

ORA-01465: invalid hex number

SQL> insert into t_lob values(rawtohex('－－－－－－－－－－&'));

1 row created.

SQL> commit;

Commit complete.

网友提供java处理代码如下：

///插入

public void test()

{

conn=DBUtil.getActiveConnection();

String sqlStr="Set define off";

String ss="————";

String sql="insert into test1(names,btestname) values('12',rawtohex('"+ss+"'))";

System.out.println(sqlStr);

System.out.println(sql);

try {

pstmt=conn.prepareStatement(sqlStr);

pstmt.addBatch();

pstmt=conn.prepareStatement(sql);

pstmt.addBatch();

pstmt.executeBatch();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

finally

{

DBUtil.closeStatement(pstmt);

DBUtil.closeConnection(conn);

}

}

／／／／查询语句

public void getTest()

{

conn=DBUtil.getActiveConnection();

String sql="select * from test1 where names='12'";

//oracle.sql.BLOB blob = null;

InputStream inStream=null;

try {

pstmt=conn.prepareStatement(sql);

set=pstmt.executeQuery();

if(set!=null && set.next())

{

//接收blob类型

java.sql.Blob blob = (oracle.sql.BLOB)set.getBlob("btestname");

//注意

inStream = blob.getBinaryStream();

if(blob!=null) System.out.println("有值＝＝＝＝＝＝＝＝＝＝＝＝");

try {

byte[] data = new byte[200];

int length=0;//每次读取的实际字节长度

//is.read()方法：从buff缓中区的第0个位开始读取字节，每次最多读取200，

//方法会返回一个实际读取的长度，用length接收，当值为-1时，表示所有的字节全部读取完毕

while((length=inStream.read(data,0,200))!=-1)

{

//把字节以平台的默认编码方式转为字符，从buff的第0个位开始转换，实际要转换的长度是length

String str=new String(data,0,length);

System.out.println("最终结果==== "+str+" ====");

}

//关闭流

inStream.close();

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

finally

{

DBUtil.closeResultSet(set);

DBUtil.closeStatement(pstmt);

DBUtil.closeConnection(conn);

}

}

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之案例:Oracle报错ORA-01465: invalid hex number 插入blob字段报错