Storing Images in Oracle tables

Images 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 xx_images_tbl (

  file_id number,

  file_desc varchar2(30),

  file_loc bfile,

  file_type varchar2(15))

 /

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.

INSERT INTO xx_images_tbl VALUES(1,'Test_file',bfilename('XX_DIR_NAME','TEST.JPG'),'JPEG');

commit;

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 xx_insert_images_proc(
   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 xx_images_tbl '
            || CHR(10)
            || 'VALUES (xx_images_tbl_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 file_id
           , file_desc
           , file_type
        FROM xx_images_tbl;
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 file_loc
        INTO LOCATOR
        FROM xx_images_tbl
       WHERE file_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 xx_images_tbl
           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.