Storing Pictures in Oracle Tables

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.