Method 1 (Standard way):
First we need to create directory on the database server to store files on it.
command:
Create or replace directory as TEMP_DIR '<database server path>/temp'
Now copy all the files to that directory.
Sql>create table reader(reader_id varchar2(10),signature BLOB);
Sql>Insert into reader (reader_id) values ('01');
Create Procedure to update table with signature file.
CREATE OR REPLACE procedure user_signature(v_id IN number)
IS
BEGIN
DECLARE
v_bfile bfile := BFILENAME('TEMP_DIR', 'sign.JPG'); --first parameter in all caps, second is case sensitive
v_lob blob;
BEGIN
select signature into v_lob from reader where reader_id = v_id for update;
DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADFROMFILE ( v_lob, v_bfile, DBMS_LOB.GETLENGTH(v_bfile));
DBMS_LOB.CLOSE(v_bfile);
commit;
END;
END;
Now run it with sql plus
SQL> exec user_signature('01')
or in TOAD (sql editor)
begin
user_signature('01');
end;
Method 2 (Easiest way): Need TOAD !!!
Select table from schema browser and click on data tab.
Double click on signature data (HugeBlob) (Hint: Not all cap in BLOB data)
Now you'll see following window. See data length is displayed as zero.
At the top-left corner of window you can see "Load a File" icon. Just click on it and it will ask you for file location.
Once file is loaded you can see data length is displayed as 4664
(Hint: now BLOB data dispalys as in all caps letters i.e HUGEBLOB)
Ohhh, It's done !!! now what ?
If you want to copy file on your local hard disk which is stored in oracle database then just do double-click on BLOB data and click on "Save to File" icon at top-left side of window (second icon).
First we need to create directory on the database server to store files on it.
command:
Create or replace directory as TEMP_DIR '<database server path>/temp'
Now copy all the files to that directory.
Sql>create table reader(reader_id varchar2(10),signature BLOB);
Sql>Insert into reader (reader_id) values ('01');
Create Procedure to update table with signature file.
CREATE OR REPLACE procedure user_signature(v_id IN number)
IS
BEGIN
DECLARE
v_bfile bfile := BFILENAME('TEMP_DIR', 'sign.JPG'); --first parameter in all caps, second is case sensitive
v_lob blob;
BEGIN
select signature into v_lob from reader where reader_id = v_id for update;
DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADFROMFILE ( v_lob, v_bfile, DBMS_LOB.GETLENGTH(v_bfile));
DBMS_LOB.CLOSE(v_bfile);
commit;
END;
END;
Now run it with sql plus
SQL> exec user_signature('01')
or in TOAD (sql editor)
begin
user_signature('01');
end;
Method 2 (Easiest way): Need TOAD !!!
Select table from schema browser and click on data tab.
Double click on signature data (HugeBlob) (Hint: Not all cap in BLOB data)
Now you'll see following window. See data length is displayed as zero.
At the top-left corner of window you can see "Load a File" icon. Just click on it and it will ask you for file location.
Once file is loaded you can see data length is displayed as 4664
(Hint: now BLOB data dispalys as in all caps letters i.e HUGEBLOB)
Ohhh, It's done !!! now what ?
If you want to copy file on your local hard disk which is stored in oracle database then just do double-click on BLOB data and click on "Save to File" icon at top-left side of window (second icon).
0 comments