Sunday, October 4, 2009

how to insert blob or image file to oracle table

To do apply this example in windows,
first create a os directory like "D:\halim_blob_dir"
and input a image file like 'c.jpg';

then begin

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 3 18:58:02 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL>
SQL> CONN / AS SYSDBA
Connected.
SQL>
SQL> grant dba to halim2 identified by halim2;

Grant succeeded.

SQL> conn halim2/halim2
Connected.
SQL>
SQL> drop directory halim_blob;
drop directory halim_blob
*
ERROR at line 1:
ORA-04043: object HALIM_BLOB does not exist


SQL> create directory halim_blob as 'D:\halim_blob_dir';

Directory created.

SQL> CREATE TABLE image_table (
2 dname VARCHAR2(30), -- directory name
3 sname VARCHAR2(30), -- subdirectory name
4 fname VARCHAR2(30), -- file name
5 iblob BLOB)
6 /

Table created.

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE halim_load_image_file (
2 dir_name VARCHAR2,
3 sub_dir_name VARCHAR2,
4 file_name VARCHAR2
5 ) IS
6 source_file BFILE;
7 destenation_file BLOB;
8 length_file BINARY_INTEGER;
9 BEGIN
10 source_file := BFILENAME ('HALIM_BLOB', file_name);
11
12 -- insert a NULL record to lock
13 INSERT INTO image_table
14 (dname, sname, fname, iblob
15 )
16 VALUES (dir_name, sub_dir_name, file_name, EMPTY_BLOB ()
17 )
18 RETURNING iblob
19 INTO destenation_file;
20
21 -- lock record
22 SELECT iblob
23 INTO destenation_file
24 FROM image_table
25 WHERE dname = dir_name AND sname = sub_dir_name AND fname = file_nam
26 FOR UPDATE;
27
28 -- open the file
29 DBMS_LOB.fileopen (source_file, DBMS_LOB.file_readonly);
30 -- determine length
31 length_file := DBMS_LOB.getlength (source_file);
32 -- read the file
33 DBMS_LOB.loadfromfile (destenation_file, source_file, length_file);
34
35 -- update the blob field
36 UPDATE image_table
37 SET iblob = destenation_file
38 WHERE dname = dir_name AND sname = sub_dir_name AND fname = file_name;
39
40 -- close file
41 DBMS_LOB.fileclose (source_file);
42 END halim_load_image_file;
43 /

Procedure created.

SQL> EXEC halim_load_image_file('HALIM_BLOB','Image_test','c.jpg');

PL/SQL procedure successfully completed.

SQL> SELECT *
2 FROM image_table;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
SQL>
SQL>
SQL> DECLARE
2 image_file BLOB;
3 BEGIN
4 SELECT iblob
5 INTO image_file
6 FROM image_table;
7
8 DBMS_OUTPUT.put_line (DBMS_LOB.getlength (image_file));
9 END;
10 /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL>
SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 image_file BLOB;
3 BEGIN
4 SELECT iblob
5 INTO image_file
6 FROM image_table;
7 DBMS_OUTPUT.put_line (DBMS_LOB.getlength (image_file));
8* END;
9 /

105542

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>

No comments: