Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Most Efficient BLOB Insert

Avi AbramiMay 6 2013 — edited Oct 15 2014
java 1.5
latest Oracle JDBC driver - ojdbc5.jar
Oracle 11g Release 2 enterprise database

I am only allowed to insert into a table using a stored routine, i.e. procedure or function, as this is a restriction placed upon me by my superiors at work.
Please, there is absolutely NO way that this restriction will be removed.
From my java program, I need to read a file containing an image and insert it as a BLOB into a database table using a stored routine.
Specifically the database table contains precisely two (2) BLOB columns as well as other columns whose data types are VARCHAR2 or NUMBER or DATE
I have searched the Internet and the Oracle documentation and I am not clear on the best way to achieve this.

Currently I have a PL/SQL function that has a parameter for each table column, i.e. the function parameter list includes two (2) BLOBs.
My java code uses CallableStatement.setBinaryStream(int, InputStream, int) for passing the image file contents to the PL/SQL function.
Note also that the java code resides on my PC while the Oracle database resides on a remote server and the two are connected via our in-house network.

The PL/SQL function performs a straight insert of the function parameters into the table, i.e.

create or replace function F (p1 varchar2, p2 number, p3 BLOB, p4 BLOB) return number is
begin
insert into T (c1, c2, c3, c4) values (p1, p2, p3, p4);
return 0;
end;

I am measuring the time using System.nanoTime() and the average time to read the file and perform the insert is about 0.3 seconds.

I would appreciate any suggestions on how to reduce the overall time taken.

Thanks,
Avi.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 5 2013
Added on May 6 2013
8 comments
2,002 views