Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Storing pictures and photos in Oracle tables

Oracle Tips by Burleson Consulting

Creative Labs : 73PD117000000 : Creative WebCam Notebook Camera USB  

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.


 

 

��  
 
 
 
 

 
 
 

 
 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright 1996 -  2014

All rights reserved by Burleson

Oracle is the registered trademark of Oracle Corporation.