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…

Accepted Answer

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;
```

Create java.sql.blob instance in java stored procedure

Solution

Problem

Related problems