Comparing Oracle Blobs

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:

CREATE TABLE  "MYTABLE"
   (	"ID" NUMBER,
	"NAME" VARCHAR2(4000),
	"BLOB_COLUMN" BLOB
   )
/

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!!)

package net.pascalalma.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class BlobTester {

	private static void initValues(Connection conn) throws Exception {

	  PreparedStatement pstmt = conn.prepareStatement("delete from mytable");
	  pstmt.execute();

	  pstmt = conn.prepareStatement("insert into mytable(id,name, blob_column ) values (?,?,empty_blob())");
	  pstmt.setLong(1,1);
    	  pstmt.setString(2,"row1");
    	  pstmt.execute();

    	  pstmt.setLong(1,2);
    	  pstmt.setString(2,"row2");
    	  pstmt.execute();

    	  pstmt = conn.prepareStatement("update mytable set blob_column = ?");
    	  pstmt.setBytes(1,"just some text as blob".getBytes());

     	  pstmt.executeUpdate();

	}

       private static void showContent(Connection conn) throws Exception
       {
	 Statement stmt = conn.createStatement();

	 ResultSet rset = stmt.executeQuery("select id,name,blob_column from mytable");

         while (rset.next()) {
            System.out.print (rset.getString(1));
            System.out.print (" | " + rset.getString(2) + " | ");
            System.out.print (new String(rset.getBytes(3)));
            System.out.println("");
         }

         rset.close();
         stmt.close();
       }

       public static void main (String args []) throws Exception
       {
	  DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());

	  Connection conn = DriverManager.getConnection
	             ("jdbc:oracle:thin:@dbserver:1521:sid", "user", "password");

	  initValues(conn);

	  showContent(conn);

	  conn.close();
       }
  }

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:

  private static void showFilteredContent(Connection conn) throws Exception
  {
	System.out.println("--------- showFilteredContent -----------");
	PreparedStatement stmt = conn.prepareStatement("select id,name,blob_column from mytable where blob_column = ?");
	stmt.setBytes(1, "abc".getBytes());
	ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            System.out.print (rs.getString(1));
            System.out.print (" | " + rs.getString(2) + " | ");
            System.out.print (new String(rs.getBytes(3)));
            System.out.println("");
        }

        rs.close();
        stmt.close();
  }

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:

private static void showFilteredContent(Connection conn) throws Exception
{
  System.out.println("--------- showFilteredContent -----------");
  PreparedStatement stmt =
    conn.prepareStatement("select id,name,blob_column from mytable where dbms_lob.compare(blob_column,?) = 0");
  stmt.setBytes(1, "just some text as blob".getBytes());

  ResultSet rs = stmt.executeQuery();

  while (rs.next()) {
    System.out.print (rs.getString(1));
    System.out.print (" | " + rs.getString(2) + " | ");
    System.out.print (new String(rs.getBytes(3)));
    System.out.println("");
  }

  rs.close();
  stmt.close();
}

About Pascal Alma

Pascal is a senior IT consultant and has been working in IT since 1997. He is monitoring the latest development in new technologies (Mobile, Cloud, Big Data) closely and particularly interested in Java open source tool stacks, cloud related technologies like AWS and mobile development like building iOS apps with Swift. Specialties: Java/JEE/Spring Amazon AWS API/REST Big Data Continuous Delivery Swift/iOS
This entry was posted in Technical. Bookmark the permalink.

2 Responses to Comparing Oracle Blobs

  1. noon says:

    You can create a Hibernate UserType which converts the BLOBs to ByteArray and vice versa. I found the idea from Hibernate forum. The code is a bit too long to be pasted here…

  2. Pascal Alma says:

    Hi noon, thx for your comment. As you can see in this post I do use a Hibernate usertype for another type of conversion

Comments are closed.