Oracle RAW id列和JDBC [英] Oracle RAW id columns and JDBC

问题描述

我正在尝试开发一个调用Oracle 11g XE数据库的简单Java servlet。我提供的数据库使用RAW列类型作为所有表的ID。

I am trying to develop a simple Java servlet that calls an Oracle 11g XE database. The database I was provided uses RAW column types for the IDs on all tables.

最初我试图从JDBC获取数据，而base64编码VARBINARY字节数组正在返回，所以我可以通过JSON将结果传递给前端系统。但是，在base64解码并尝试将字节数组设置回列之后，Oracle / JDBC没有返回数据。

Initially I tried to just get the data back from JDBC and base64 encode the VARBINARY byte arrays that were being returned so I could pass the results via JSON to the front end system. However, after base64 decoding and trying to set the byte array back to the column, Oracle/JDBC was not returning data.

我决定尝试让Oracle进行编码/ decode并且能够使用SQL Developer 2在Oracle中触发以下查询。

I decided to try and have Oracle do the encoding/decoding and was able to get the following queries to fire in Oracle using SQL Developer 2.

SELECT utl_encode.base64_encode(IDCOLUMN), FIRST_NAME FROM TABLENAME
SELECT utl_encode.base64_encode(IDCOLUMN), FIRST_NAME FROM TABLENAME WHERE IDCOLUMN = utl_encode.base64_decode('BASE64STRING')

然而，当使用JDBC执行这些queires时：

However when executing these queires using JDBC:

conn = isConnSupplied ? userConn : ResourceManager.getConnection();
Statement stmt = conn.createStatement();
 try {
   ResultSet rset = stmt.executeQuery("SELECT utl_encode.base64_encode(IDCOLUMN), FIRST_NAME FROM TABLENAME");
   try {
     while (rset.next())
       System.out.println (rset.getString(1));   // Print col 1
   } 
   finally {
      try { rset.close(); } catch (Exception ignore) {}
   }
 } 
 finally {
   try { stmt.close(); } catch (Exception ignore) {}
 }

我收到以下错误：

java.sql.SQLException: ORA-29261: bad argument
ORA-06512: at "SYS.UTL_ENCODE", line 8
ORA-06512: at "SYS.UTL_ENCODE", line 243

oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:866)
oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1491)
oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406)
com.pearson.familyportal.servlet.WebController.doPost(WebController.java:97)
com.pearson.familyportal.servlet.WebController.doGet(WebController.java:171)
javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
javax.servlet.http.HttpServlet.service(HttpServlet.java:722)

问题：什么是最好的方法当我必须在客户端传递这些时，处理RAW ID列字节数组？如果让Oracle进行转换，那么JDBC不喜欢我做错了什么？

Question: What is the best way to deal with the RAW ID column byte arrays when I have to pass these around on the client side? If letting Oracle do the conversion, what am I doing wrong that JDBC isn't liking?

提前致谢。

推荐答案

尝试使用RAWTOHEX和HEXTORAW。

Try using RAWTOHEX and HEXTORAW.

这篇关于Oracle RAW id列和JDBC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！