| |
 |
|
Storing pictures and photos in Oracle tables
Oracle Database Tips by Donald Burleson |
 |
Photograph picture capture devices are now inexpensive
($33.99) such as the Creative Webcam NoteBook
camera that employs a CMOS color sensor that enables still
images captures of 640 x 480 resolutions and live video up
to 640 x 480resolutions at 15 fps. Its captures ultra
close-up digitized images and uses a standard USB port. |
Storing Pictures in Oracle Tables
Photographs and pictures and Oracle BLOB data
are easy to add to a Oracle table. There are two ways to load BLOBs
and CLOBs into the database. The first method uses PL/SQL and the
DBMS_LOB package and the BFILE datatype to transfer external LOB
files into the database internal LOB structures. The second uses the
Oracle Call Interface (OCI) to perform the same function. Let's look
at the first method.
To load external LOB data into internal LOB storage using PL/SQL
alone you must first use a table containing BFILE locators for the
files to allow Oracle to access them.
create
table graphics_table (
bfile_id number,
bfile_desc varchar2(30),
bfile_loc bfile,
bfile_type
varchar2(4))
TABLESPACE appl_data
storage (initial 1m next 1m pctincrease 0)
/
Listing 3: Example Use of BFILE datatype in a
Table
Notice in the above code that no LOB storage
clause is specified. This is because all that is stored in the
database is a locator value for the BFILE consisting of an internal
DIRECTORY specification and a file name. The BFILE locators are
loaded into the table using the BFILENAME function and a standard
insert statement.
An example of this process is shown here in
Listing 4.
SQL>
INSERT INTO graphics_table
2
VALUES(4,'April Book of Days
Woodcut',bfilename('GIF_FILES','APRIL.JPG'),'JPEG');
1 row
created.
SQL>
INSERT INTO graphics_table
2
VALUES(8,'August Book of Days
Woodcut',bfilename('GIF_FILES','AUGUST.JPG'),'JPEG');
1 row
created.
SQL>
INSERT INTO graphics_table
2
VALUES(13,'Benzene Molecule',bfilename('GIF_FILES','BENZNE.GIF'),'GIF');
1 row
created.
.
.
.
SQL>
INSERT INTO graphics_table
2
VALUES(30,'',bfilename('GIF_FILES','SHAPIROS.GIF'),'GIF');
1 row
created.
SQL>
INSERT INTO graphics_table
2
VALUES(31,'',bfilename('GIF_FILES','SODF5.GIF'),'GIF');
1 row
created.
SQL>
INSERT INTO graphics_table
2
VALUES(32,'',bfilename('GIF_FILES','WAVRA-CL.GIF'),'GIF');
1 row
created.
SQL>
commit;
Commit
complete.
Listing 4: Example Set of INSERT Commands to
Load BFILE Locators Manually
TIP:
Using a host command to perform a single column
directory listing into a file (for example on NT: dir /B >file.lis),
then using the UTL_FILE package to read the contents of the created
file into the DBMS_SQL package to build the INSERT commands on the
fly, an entire directory of LOB datafiles can be loaded at one time
into a BFILE table and then on into the internal LOB storage table.
For an example see Listing 5.
CREATE OR REPLACE PROCEDURE get_bfiles(
bfile_dir in VARCHAR2,
bfile_lis in VARCHAR2,
bfile_int_dir VARCHAR2)
AS
cur INTEGER;
bfile_int
VARCHAR2(100);
sql_com
VARCHAR2(2000);
file_proc
INTEGER;
file_hand
utl_file.file_type;
file_buff
VARCHAR2(1022);
file_type
VARCHAR2(4);
BEGIN
bfile_int:=UPPER(bfile_int_dir);
file_hand:=utl_file.fopen(bfile_dir,bfile_lis,'R');
LOOP
BEGIN
utl_file.get_line(file_hand,file_buff);
cur:=dbms_sql.open_cursor;
file_type:=SUBSTR(file_buff,INSTR(file_buff,'.')+1,3);
file_type:=UPPER(file_type);
IF
file_type='GIF'
THEN
file_type:='GIF';
ELSIF file_type='JPG'
THEN file_type:='JPEG';
END
IF;
sql_com:= 'INSERT INTO graphics_table '||CHR(10)||
'VALUES (graphics_table_seq.NEXTVAL,'||CHR(39)||CHR(39)||
', bfilename('||
CHR(39)||bfile_int||CHR(39)||','
||CHR(39)||file_buff||CHR(39)||
') ,'||CHR(39)||file_type||CHR(39)||')';
dbms_output.put_line(sql_com);
dbms_sql.parse(cur,sql_com,dbms_sql.v7);
file_proc:=dbms_sql.execute(cur);
dbms_sql.close_cursor(cur);
EXCEPTION
WHEN no_data_found THEN
EXIT;
END;
END
LOOP;
utl_file.fclose(file_hand);
END;
/
Listing 5: Example Procedure for Loading
BFILE Locators based on an External File List
Once the BFILE locators are set in the BFILE
table we can use the DBMS_LOB package to read the external LOB (BFILE)
into an internal LOB (BLOB, CLOB or NCLOB). This is shown in Listing
6. The SELECT from the TEMP_BLOB table initializes the internal LOB
values so they can be used, otherwise an error will be returned.
CREATE OR REPLACE PROCEDURE load_lob AS
id NUMBER;
image1 BLOB;
locator BFILE;
bfile_len NUMBER;
bf_desc VARCHAR2(30);
bf_name VARCHAR2(30);
bf_dir VARCHAR2(30);
bf_typ VARCHAR2(4);
ctr
integer;
CURSOR get_id IS
SELECT bfile_id,bfile_desc,bfile_type FROM graphics_table;
BEGIN
OPEN get_id;
LOOP
FETCH get_id INTO id, bf_desc, bf_typ;
EXIT WHEN get_id%notfound;
dbms_output.put_line('ID: '||to_char(id));
SELECT bfile_loc INTO locator FROM graphics_table WHERE bfile_id=id;
dbms_lob.filegetname(
locator,bf_dir,bf_name);
dbms_output.put_line('Dir: '||bf_dir);
dbms_lob.fileopen(locator,dbms_lob.file_readonly);
bfile_len:=dbms_lob.getlength(locator);
dbms_output.put_line('ID: '||to_char(id)||' length: '||to_char(bfile_len));
SELECT temp_blob INTO image1 FROM temp_blob;
bfile_len:=dbms_lob.getlength(locator);
dbms_lob.loadfromfile(image1,locator,bfile_len,1,1);
INSERT INTO internal_graphics VALUES (id,bf_desc,image1,bf_typ);
dbms_output.put_line(bf_desc||' Length: '||TO_CHAR(bfile_len)||
'
Name: '||bf_name||' Dir: '||bf_dir||' '||bf_typ);
dbms_lob.fileclose(locator);
END
LOOP;
END;
/
Listing 6: Example Procedure to Load BFILE
values into Internal LOBs
By enforcing a naming standard on the external
LOB files the loading procedure in Listing 6.6 could be modified to
place BLOB types into BLOBs, CLOB types into CLOBs and NCLOB types
into NCLOBs based on the file type values that are parsed from the
file extensions in Listing 5. Between the procedure in Listing 5 and
the one shown in Listing 6 the example tables in Listings 1 and 3
are populated with LOB values located in a specific directory.
|
and include the URL for the page.
