This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes how to pass multiple BLOB objects to a PLSQL procedure? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to pass multiple BLOB objects to a PLSQL procedure?" Watch "how to pass multiple BLOB objects to a PLSQL procedure?" New topic
Author

how to pass multiple BLOB objects to a PLSQL procedure?

Sudheer Bhat
Ranch Hand

Joined: Feb 22, 2011
Posts: 75
Hi All,

DB: Oracle 11g.

I have a table type of BLOB defined in the DB.
.

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
Ranch Hand

Joined: Feb 22, 2011
Posts: 75
This is resolved.
I did the below instead of creating a new blob object.


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.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: how to pass multiple BLOB objects to a PLSQL procedure?
 
Similar Threads
passing arrays to oracle
Problem while setting multiple Oracle Arrays as input to a proc
Calling a stored procedure with an ARRAY
Array as input to stored procedure.
cannot create oracle.sql.ArrayDescriptor using connection from weblogice datasource