How to insert BLOB into Oracle 10g database

, ,
1

We are using JDBC driver ojdbc14.zip to connect oracle 10g

I want to insert a BLOB into table. Then first i insert sequence no into db then select the blog and update it.

In the flow service

 Insert seq_no into table1 (adapter 1)
 Select BLOB from table1 where seq_no =?  (adapter 2, Output Field Type : java.sql.Blob)

then it got error of " java.io.NotSerializableException: oracle.jdbc.driver.T4Connection"

Do anyone know how to solve it ?

Thanks !

2

Try Output Type “byte array” instead of “java.sql.Blob”. I don’t know the exact reason why “java.sql.Blob” doesn’t work, but there may be a reason, may be the driver.

3

if you bytes array as output from the select sql, then I cannot update the same record

I want to do insert, so first select then update the blob

4

The update and insert adapters take byte array as inputs too.

In JDBC world, java.sql.Blob is just a pointer to the Blob in the database table. Also, a Blob is logically nothing but a byte array. So you should be able to select, update, insert of Blob fields using “byte array” input/output data type of JDBC adapter templates.

Unless you are trying to read(stream) large Blobs efficiently, i don’t see a reason why you need to use java.sql.Blob.

Ofcourse, i know that i am not solving your original problem here. The other “byte array” option may just work for your requirement, if you never get to the root of the original problem.

5

/* as sysdba set permissions and create a directory
** you mus first create the directory physically */
create or replace directory datadump as ‘/home/oracle/datadump’;
GRANT read, write ON DIRECTORY datadump TO t4_islands;
GRANT EXECUTE ON UTL_FILE TO t4_islands;

If you have an BLOB image table that you want to dump all images to file for this routine will do the trick.
We have a table blob_image_table with the following columns:
image_name varchar2(128)
image_extension varchar2(128)
image_blob BLOB
CREATE OR REPLACE PROCEDURE T4_ISLANDS.unload_travelpack_images2 IS
v_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32000);
l_amount BINARY_INTEGER := 32000;
l_pos number := 1;
l_blob BLOB;
l_blob_len number;
v_name VARCHAR2(128);
CURSOR Cimage IS select image_name,image_extension,image_blob l_blob,dbms_lob.getlength(image_blob) l_blob_len from blob_image_table;
RecordsAffected PLS_INTEGER := 0;
v_CONTENT_LENGTH PLS_INTEGER := 0;
v_CONTENT_LENGTH2 PLS_INTEGER := 0;
v_content clob;
j PLS_INTEGER := 0;
k PLS_INTEGER;
l PLS_INTEGER;
vn_the_rest PLS_INTEGER :=0;
vn_the_rest2 PLS_INTEGER :=0;
v_image_count number;
x number;
BEGIN
DBMS_OUTPUT.PUT_LINE('Begin Time: '||to_char(sysdate,‘MM/DD/YYYY hh:mm:ss’));

FOR Rec IN Cimage LOOP
x := l_blob_len;
– Open the destination file.
v_name := Rec.image_name||‘.’||Rec.image_extension;
v_file := UTL_FILE.FOPEN(‘DATADUMP’,v_name,‘wb’,32767);
if Rec.l_blob_len < 32000 then
DBMS_LOB.read(Rec.l_blob, Rec.l_blob_len, l_pos, l_buffer);
utl_file.put_raw(v_file,l_buffer, TRUE);
utl_file.fflush(v_file);
else
WHILE l_pos < Rec.l_blob_len and l_amount > 0 LOOP
DBMS_LOB.read(Rec.l_blob, l_amount, l_pos, l_buffer);
UTL_FILE.put_raw(v_file, l_buffer, TRUE);
utl_file.fflush(v_file);
– set the start position for the next cut
l_pos := l_pos + l_amount;
– set the end position if less than 32000 bytes
x := x - l_amount;
IF x < 32000 THEN
l_amount := x;
END IF;
END LOOP;
l_pos := 1;
l_buffer := null;
l_blob := null;
l_amount := 32000;
end if;
RecordsAffected := RecordsAffected + 1;
UTL_FILE.FCLOSE(v_file);
end if;
END LOOP;
DBMS_OUTPUT.PUT_LINE('End Time ===> '||to_char(sysdate,‘MM/DD/YYYY hh:mm:ss’));
DBMS_OUTPUT.PUT_LINE('Image Records Affected: '||RecordsAffected);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, ‘Invalid path. Create directory or set UTL_FILE_DIR.’);
WHEN utl_file.WRITE_ERROR THEN
raise_application_error(-20001, ‘Operating system error occurred during the write operation.’);
WHEN utl_file.INTERNAL_ERROR THEN
raise_application_error(-20002, ‘Unspecified PL/SQL error.’);
WHEN utl_file.INVALID_OPERATION THEN
raise_application_error(-20003, ‘File could not be opened or operated on as requested.’);
WHEN utl_file.INVALID_FILEHANDLE THEN
raise_application_error(-20004, ‘File handle was invalid.’);
WHEN utl_file.INVALID_MODE THEN
raise_application_error(-20005, ‘The open_mode parameter in FOPEN was invalid.’);
WHEN utl_file.INVALID_MAXLINESIZE THEN
raise_application_error(-20006, ‘Specified max_linesize is too large or too small.’);
WHEN OTHERS THEN
– Close the file if something goes wrong.
IF UTL_FILE.is_open(v_file) THEN
UTL_FILE.fclose(v_file);
END IF;
RAISE;
END;
/

That should do the trick :slight_smile: