Oracle Java: Error casting java.sql.Blob datatype to Oracle BLOB datatype

Question

I am getting the following error upon executing Oracle Java procedure that accepts and returns BLOB data,

Error report - ORA-00932: inconsistent datatypes: expected a return value that is an instance of a user-defined Java class convertible to an Oracle type got an object that could not be converted ORA-06512: at "", line 86 ORA-06512: at line 7 00932. 00000 - "inconsistent datatypes: expected %s got %s" *Cause:
*Action:

Java Code

public static java.sql.Blob Convert_Image(java.sql.Blob srcBlob) {

java.sql.Blob desBlob = null;

try {
    Document document = new Document();

    ByteArrayOutputStream pdfDocumentOutputStream = new ByteArrayOutputStream();
    PdfWriter pdfDocumentWriter = PdfWriter.getInstance(document, pdfDocumentOutputStream);                        
    document.open();

    if (document.newPage()) {

        int indentation = 0;
        Image img = Image.getInstance(srcBlob.getBytes(1, (int) srcBlob.length()));
        float scaler = document.getPageSize().getWidth() - document.leftMargin() - document.rightMargin() - indentation;
        img.scalePercent((scaler / img.getWidth()) * 100);

        document.newPage();
        document.add(Image.getInstance(img));
        document.close();

        desBlob = new SerialBlob(pdfDocumentOutputStream.toByteArray());

        pdfDocumentWriter.close();
        pdfDocumentOutputStream.close();

    }   
}
catch (Exception e) {
    Show_Message(e);
}

return desBlob;

}

Oracle Code

FUNCTION CONVERT_IMAGE(
    P_BLOB IN DOCUMENTS.BLOB_CONTENT%TYPE)
  RETURN BLOB
AS
  LANGUAGE JAVA NAME 'egift.Util.Convert_Image (java.sql.Blob) return java.sql.Blob';  

Trigger Implementation

...
DECLARE

    v_blob_content DOCUMENTS.BLOB_CONTENT%TYPE;

BEGIN

    IF :NEW.BLOB_CONTENT IS NOT NULL AND 
      (
        NVL(:NEW.MIME_TYPE,'#') = 'image/png' OR 
        NVL(:NEW.MIME_TYPE,'#') = 'image/jpeg' OR 
        NVL(:NEW.MIME_TYPE,'#') = 'image/gif' OR
        NVL(:NEW.MIME_TYPE,'#') = 'image/tiff' OR
        NVL(:NEW.MIME_TYPE,'#') = 'image/bmp'
      ) THEN
      v_blob_content := EGIFT_UTIL.CONVERT_IMAGE(:NEW.BLOB_CONTENT);
      IF v_blob_content is not null then
        :NEW.BLOB_CONTENT := v_blob_content;  
        :NEW.MIME_TYPE := 'application/pdf';
        :NEW.NAME := substr(:NEW.NAME,0,instr(:NEW.NAME,'.',-1)) || 'pdf';
      END IF;
    END IF;
...

Answer 1

You need to return an instance of oracle.sql.BLOB or oracle.jdbc2.Blob from your Java procedure in order to create a trigger that calls the Java procedure and returns a BLOB. ORACLE actually has a table where they compare their datatypes to the Java instances they can accept:

the legal data type mappings. Oracle Database converts between the SQL types and Java classes automatically

Update 1: I actually tested passing java.sql.Blob and returning the same type in the function and it worked as expected:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "Util" as
public class Util {
  public static java.sql.Blob Convert_Image(java.sql.Blob srcBlob) {

    return srcBlob;

    }
}
/

CREATE OR REPLACE FUNCTION CONVERT_IMAGE(
    P_BLOB BLOB)
  RETURN BLOB
AS
  LANGUAGE JAVA NAME 'Util.Convert_Image (java.sql.Blob) return java.sql.Blob';


select utl_raw.cast_to_varchar2(convert_image(utl_raw.cast_to_raw('test'))) from dual;
-- test

Can you try to run the above code and see if you get the same error?

Answer 2

This is a temporary solution that I implemented since I was cutting close to my deadline, I am still in search of a solution wherein I don't have to use deprecated classes and would like to avoid reference of oracle.sql.BLOB and use java.sql.Blob.

Workaround involved creating a oracle.sql.BLOB object instead of SerialBlob and then populating bytearray from output stream as follows,

conn = new OracleDriver().defaultConnection();
desBlob = BLOB.createTemporary(conn, false, BLOB.DURATION_SESSION);
desBlob.setBytes(1, pdfDocumentOutputStream.toByteArray());

And suppressed deprecation warnings using,

@SuppressWarnings("deprecation")

Final Java Code

@SuppressWarnings("deprecation")
public static java.sql.Blob Convert_Image(java.sql.Blob srcBlob) {

    java.sql.Blob desBlob = null;

    try {
        Document document = new Document();

        ByteArrayOutputStream pdfDocumentOutputStream = new ByteArrayOutputStream();
        PdfWriter pdfDocumentWriter = PdfWriter.getInstance(document, pdfDocumentOutputStream);
        document.open();

        if (document.newPage()) {

            int indentation = 0;
            Image img = Image.getInstance(srcBlob.getBytes(1, (int) srcBlob.length()));
            float scaler =
                document.getPageSize().getWidth() - document.leftMargin() - document.rightMargin() - indentation;
            img.scalePercent((scaler / img.getWidth()) * 100);

            document.newPage();
            document.add(Image.getInstance(img));
            document.close();

            //desBlob = new SerialBlob(pdfDocumentOutputStream.toByteArray());
            conn = new OracleDriver().defaultConnection();
            desBlob = BLOB.createTemporary(conn, false, BLOB.DURATION_SESSION);
            desBlob.setBytes(1, pdfDocumentOutputStream.toByteArray());

            pdfDocumentWriter.close();
            pdfDocumentOutputStream.close();

        }
    } catch (Exception e) {
        Show_Message(e);
    }
    return desBlob;

}

I had setup a bounty to get a solution to resolve this issue w/o using deprecated class and I was unable to find one, although I received attention to this question. This is an open issue for me until I find the right solution. Thanks to all who made an effort.

Regards!

Answer 3

I found this solution without deprecated code:

    public static Blob bytes2Blob(byte[] b) throws Exception {
        if (System.getProperty("oracle.server.version") != null) {
            Connection con = DriverManager.getConnection("jdbc:default:connection");
            CallableStatement cStmt = con.prepareCall ("{ call DBMS_LOB.createtemporary(?,true,DBMS_LOB.SESSION) }");
            cStmt.registerOutParameter(1, OracleTypes.BLOB);
            cStmt.execute();
            Blob blob = ((OracleCallableStatement)cStmt).getBLOB(1);
            cStmt.close();
            OutputStream out = blob.setBinaryStream(1L);
            out.write(b);
            out.flush();
            return blob;
        } else {
            return new javax.sql.rowset.serial.SerialBlob(b);
        }