File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes inserting blob to oracle Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Head First Android this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "inserting blob to oracle" Watch "inserting blob to oracle" New topic

inserting blob to oracle

shobhar gupta

Joined: Mar 30, 2006
Posts: 4
I am trying to insert blobs for a file to oracle, tried everything nothing working, i found out there are two methods:

one is to get a reference to blob in database by getting a result set and then updating the reference to blob by output stream, its not working i don't know why, also this is long way as we have to first insert data, then get reference to it and then again update it by output streams, so 3 calls to database in place of 1.
Another easy way is to use callableStatement and do setBinaryStream like this: byteStream = new;
int numBytes = byteData.length;
cstmt.setBinaryStream(parmIndex, byteStream, numBytes);

but i am not able to send bigger files, it is not allowing something more than 15-20 Kb, even failing at 31 kb i tried, says Data size bigger than max size for this type: , trying to set it into BLob datatype in oracle, i checked oracle specification, it says 4 GB is allowed for BLOB, and setBinaryStream is ok for BLOB as i am able to add smaller files
I got this piece of code from this link:

Can you please help me on this, i am getting really frustrated by this blob thing, help will be really appreciated
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
See Oracle's sample code, a complete working program that inserts and retrieves CLOBs and BLOBS, here:
(The LOB example)
shobhar gupta

Joined: Mar 30, 2006
Posts: 4
I have also tried as in this oracle link, but when i try to update by using an output stream i get a error, ORA-00942: table or view does not exist, can u explain why i am getting this error. Here is my full code.

oracle.sql.BLOB bl = null;
statement.setDate(5, messageDate);

CallableStatement statement1 = getStatement();
statement1.registerOutParameter(1, OracleTypes.CURSOR);


resultSet = (ResultSet) statement1.getObject(1);

// Get the Blob locator and open output stream for the Blob

if( )

bl = (BLOB)resultSet.getBlob("FILE_ATTACHMENT");
OutputStream byteOutputStream = bl.getBinaryOutputStream();
// byteInputStream;
// here file data is byte[] i got from my file i want to save

byteOutputStream.write(fileData, 0, byteInputStream.available());
// Write to Blob

Please help urgently !!!
dnyan ginde
Ranch Hand

Joined: Jan 17, 2006
Posts: 68
hi Shobhar,

try using the schema name along with your table name. For example,

insert into schema_name.table values (?,?);

say your schema name is shobhar and the table name is employee, your query would be:

insert into shobhar.employee values (?,?);

are u converting your blob into byte[]? please try doing this.

try this, if you still face some problems then let us know.
[ April 02, 2006: Message edited by: dnyan ginde ]
pvk Punugoti

Joined: May 10, 2006
Posts: 2
Example available in:

I agree. Here's the link:
subject: inserting blob to oracle
It's not a secret anymore!