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 Make it so: Java DB Connections & Transactions this week in the JDBC 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!