Most Efficient BLOB Insert
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.