At my current project, we are replacing the Postgres database with an Oracle one (finally :-)). After putting the first raw version of our Oracle DB in place, one of our existing pieces of code gave an error. It was caused by a query that was processed by Hibernate. The query was comparing the content of a bytearray field (containing a X.509 certificate) with that of a byterarray supplied as bindparameter. This worked fine with Postgres but not with Oracle (we are using as JDBC driver 'ojdbc14.jar' and the 10g version as database).

The exception that was thrown:
ORA-00932: inconsistent datatypes: expected - got BLOB

Since I had never seen this issue before, I started to investigate it. The first I did was starting a simple testcase to reproduce the problem. Since we are using Hibernate3 and Spring, I wanted to make sure the problem wasn't in those layers.
So I created a simple table in Oracle DB:

PLAIN TEXT
SQL:
  1. CREATE TABLE  "MYTABLE"
  2.    (    "ID" NUMBER,
  3.     "NAME" VARCHAR2(4000),
  4.     "BLOB_COLUMN" BLOB
  5.    )
  6. /

And created a simple Test class in which I filled some rows in the table like this:
(Please note that this class is only used for ad-hoc testing!!)

PLAIN TEXT
JAVA:
  1. package net.pascalalma.db;
  2.  
  3. import java.sql.Connection;
  4. import java.sql.DriverManager;
  5. import java.sql.PreparedStatement;
  6. import java.sql.ResultSet;
  7. import java.sql.Statement;
  8.  
  9. public class BlobTester {
  10.  
  11.     private static void initValues(Connection conn) throws Exception {
  12.  
  13.       PreparedStatement pstmt = conn.prepareStatement("delete from mytable");
  14.       pstmt.execute();
  15.  
  16.       pstmt = conn.prepareStatement("insert into mytable(id,name, blob_column ) values (?,?,empty_blob())");
  17.       pstmt.setLong(1,1);
  18.           pstmt.setString(2,"row1");
  19.           pstmt.execute();
  20.  
  21.           pstmt.setLong(1,2);
  22.           pstmt.setString(2,"row2");
  23.           pstmt.execute();
  24.  
  25.           pstmt = conn.prepareStatement("update mytable set blob_column = ?");
  26.           pstmt.setBytes(1,"just some text as blob".getBytes());
  27.  
  28.           pstmt.executeUpdate();
  29.  
  30.     }
  31.  
  32.        private static void showContent(Connection conn) throws Exception
  33.        {
  34.      Statement stmt = conn.createStatement();
  35.  
  36.      ResultSet rset = stmt.executeQuery("select id,name,blob_column from mytable");
  37.  
  38.          while (rset.next()) {
  39.             System.out.print (rset.getString(1));
  40.             System.out.print (" | " + rset.getString(2) + " | ");
  41.             System.out.print (new String(rset.getBytes(3)));
  42.             System.out.println("");
  43.          }
  44.  
  45.          rset.close();
  46.          stmt.close();
  47.        }
  48.  
  49.        public static void main (String args []) throws Exception
  50.        {
  51.       DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
  52.  
  53.       Connection conn = DriverManager.getConnection
  54.                  ("jdbc:oracle:thin:@dbserver:1521:sid", "user", "password");
  55.  
  56.       initValues(conn);
  57.  
  58.       showContent(conn);
  59.  
  60.       conn.close();
  61.        }
  62.   }

If you run this class you should get some output containing the two rows added to the table. After this was in place, I added the following method:

PLAIN TEXT
JAVA:
  1. private static void showFilteredContent(Connection conn) throws Exception
  2.   {
  3.     System.out.println("--------- showFilteredContent -----------");
  4.     PreparedStatement stmt = conn.prepareStatement("select id,name,blob_column from mytable where blob_column = ?");
  5.     stmt.setBytes(1, "abc".getBytes());
  6.     ResultSet rs = stmt.executeQuery();
  7.  
  8.         while (rs.next()) {
  9.             System.out.print (rs.getString(1));
  10.             System.out.print (" | " + rs.getString(2) + " | ");
  11.             System.out.print (new String(rs.getBytes(3)));
  12.             System.out.println("");
  13.         }
  14.  
  15.         rs.close();
  16.         stmt.close();
  17.   }

And added a call to this method in the main() method. When you run the class this time you will get a stacktrace like:

Exception in thread "main" java.sql.SQLException: ORA-00932: inconsistent datatypes: expected - got BLOB
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:810)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:850)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1134)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3384)
at net.pascalalma.db.BlobTester.showFilteredContent(BlobTester.java:55)
at net.pascalalma.db.BlobTester.main(BlobTester.java:101)

After trying several things, we soon found out that you just can't compair blobs like this in Oracle. Although it works for Postgres , in Oracle you have to use the DBMS_LOB package to make this work. So after rephrasing the used query to use this package , it worked. The method then looks like:

PLAIN TEXT
JAVA:
  1. private static void showFilteredContent(Connection conn) throws Exception
  2. {
  3.   System.out.println("--------- showFilteredContent -----------");
  5.     conn.prepareStatement("select id,name,blob_column from mytable where dbms_lob.compare(blob_column,?) = 0");
  6.   stmt.setBytes(1, "just some text as blob".getBytes());
  7.  
  8.   ResultSet rs = stmt.executeQuery();
  9.  
  10.   while (rs.next()) {
  11.     System.out.print (rs.getString(1));
  12.     System.out.print (" | " + rs.getString(2) + " | ");
  13.     System.out.print (new String(rs.getBytes(3)));
  14.     System.out.println("");
  15.   }
  16.  
  17.   rs.close();
  18.   stmt.close();
  19. }