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

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?

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!

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);
        }