ORA-01465: invalid hex number

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:ORA-01465: invalid hex number

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

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