Create java.sql.blob instance in java stored procedure

Question

This is my first time posting a question in stackoverflow, I need to write a java stored procedure that creates an excel file and returns a blob containing the file data in bytes.

My pl/sql function is in the following form

function test_create_excel(i_username IN varchar2) return BLOB
   AS LANGUAGE JAVA NAME 'NTO.Excel.ExcelFunctions.PushToExcel( java.lang.String ) return java.sql.Blob';

my Java method is as follows

public static java.sql.Blob TestPushToExcel(String username) throws IOException, SQLException{
        //create excel file, read content to byte array and set to a blob
    }

My problem is that I cannot find any way to create an instance of java.sql.Blob so that i can use the blob.setBinaryStream(..) method to write the file data byte array.

I tried to use the SerialBlob implementation but it results in the following oracle error

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

has anyone come across this issue and if so can you share on how you got through it.

Thank you in Advance.

EDIT JAVA

public static oracle.sql.BLOB getBlob(byte[] data) throws SQLException, IOException{
        oracle.jdbc.OracleConnection conn = (oracle.jdbc.OracleConnection)new OracleDriver().defaultConnection();

        oracle.sql.BLOB retBlob = oracle.sql.BLOB.createTemporary(conn, true, oracle.sql.BLOB.DURATION_SESSION);

        java.io.OutputStream outStr = retBlob.setBinaryStream(0);
        outStr.write(data);
        outStr.flush();

        return retBlob;
  }


public static ExcelFileStore PushToExcel(String userId) throws IOException, SQLException{

        ExcelFileStore fileStore = new ExcelFileStore();        
        fileStore.NU_USERID = userId;
        fileStore.CreatedTime = new java.sql.Date(new Date().getTime());
        fileStore.Last_Updated = new java.sql.Date(new Date().getTime());        
        fileStore.FileSize = fileData.length;
        fileStore.FileData = getBlob(fileData);        
        return fileStore;
    }

PL/SQL

 function test_create_excel(i_username IN varchar2) return EXCELFILESTORE    AS LANGUAGE JAVA NAME 'NTO.Excel.ExcelFunctions.PushToExcel( java.lang.String, ) return OracleObjects.ExcelFileStore';

OracleObject.ExcelfileStore is a class that implements java.sql.SqlData and EXCELFILESTORE is a UDT in oracle.

I loaded the reference jars and jar created for my code using 'sys.dbms_java.loadjava'

I hope you understand my question, as i'm quite new to pl/sql programming

Answer 1

I was wrong. It can be done. Took me a while to get it to work, but, finally, here is a working example:

Java class

import oracle.jdbc.driver.*;

public class TestBlob {
  public static oracle.sql.BLOB getBlob(String username) throws Exception {
    oracle.jdbc.OracleConnection conn = 
      (oracle.jdbc.OracleConnection)new OracleDriver().defaultConnection();

    oracle.sql.BLOB retBlob =
      oracle.sql.BLOB.createTemporary(conn,
                                      true,
                                      oracle.sql.BLOB.DURATION_SESSION);

    java.io.OutputStream outStr = retBlob.setBinaryStream(0);
    outStr.write(username.getBytes());
    outStr.flush();

    return retBlob;
  }
}

As you can see, I have used the oracle.sql.BLOB for the result. I create it with the static createTemporary method of the BLOB class, specifying that it should be created for the duration of the session (oracle.sql.BLOB.DURATION_SESSION parameter).

Then, I obtain the OutputStream and write the data. Flush was needed.

Database side

create or replace FUNCTION getBlobWrp (username IN VARCHAR2) RETURN BLOB
  AS LANGUAGE JAVA NAME
              'TestBlob.getBlob(java.lang.String) return oracle.sql.BLOB';

Test:

DECLARE
  l_blob BLOB;
BEGIN
  l_blob := getBlobWrp('test');

  dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(l_blob));
END;

Output:

test

(previous answer)

I think you should have an IN OUT BLOB parameter in your test_create_excel function (change it to a procedure), and operate on that parameter inside your Java stored method. I saw that approach once.

Before calling the test_create_excel, you should create a BLOB object:

DECLARE
  l_blob BLOB;
BEGIN
  DBMS_LOB.createtemporary(l_blob, TRUE);
  test_create_excel('username', l_blob);
END;

Edit

I don't think what you're trying to do is possible. However, you could wrap the above code in another function. It's a bit messy, but then you'll have a function which returns the blob:

CREATE OR REPLACE FUNCTION get_excel_blob(p_username VARCHAR2) RETURN BLOB
AS
  l_blob BLOB;
BEGIN
  DBMS_LOB.createtemporary(l_blob, TRUE);
  test_create_excel(p_username, l_blob);
  RETURN l_blob;
END;