how to pass multiple BLOB objects to a PLSQL procedure?

[Sudheer Bhat]

Hi All,

DB: Oracle 11g.

I have a table type of BLOB defined in the DB.
create or replace my_type as table of blob.

I have a UI where users can upload multiple files as attachment to an service order getting created. I use Oracle BC4J, hence these documents are stored as BlobDomain.

In my java class, I am trying to create an ARRAY of these blobs and it invariably fails.

What I have tried so far:
1. Create a ArrayDescriptor for my_type. Try to create the ARRAY using the descriptor and BlobDomain Array. Code fails with "Fail to convert to internal representation"
2. Create a ArrayDescriptor for my_type. Try to create the ARRAY using descriptor and Object[] or byte[] (byte array is derived by making call to getByteArray(long offset, int length) method on the BlobDomain. Same error as above.
3. Create a ArrayDescriptor for my_type. Try to create the ARRAY using descriptor and BLOB[]. BLOB array created from byte[] derived from BlobDomain. Here the call goes to DB, but DB throws ORA-22927: invalid LOB locator specified.

I tried to use oracle.sql.STRUCT with the combination of ArrayDescriptor but that approach also failed with "Fail to convert to internal representation"

I am not sure how to use ARRAY for BLOB tables. I have been successfully using for all other native DB types and also user defined ADT's.
Any pointers for the same?

Thanks in advance.

[Sudheer Bhat]

This is resolved.
I did the below instead of creating a new blob object.

      BLOB blob = BLOB.createTemporary(txn.getJdbcConnection(),false,BLOB.DURATION_SESSION);
                       outputStream = blob.setBinaryStream(0L);
                       inputStream = new ByteArrayInputStream(byteArray);
                       byte[] buffer = new byte[blob.getBufferSize()];
                       int bytesRead = 0;
                       while((bytesRead = inputStream.read(buffer)) != -1){
                           outputStream.write(buffer,0,bytesRead);
                       }
                       outputStream.close();
                       inputStream.close();

I added this to the BLOB array and used the BLOB array to create the sql ARRAY.
Works great now!.

Note: I got a lead to this in stackoverflow when I was searching the web.