网友反馈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);
}
}