How to load image file in Oracle database?

Photographs and pictures and Oracle BLOB data are easy to add to a Oracle table. Uses PL/SQL and the DBMS_LOB package and the BFILE datatype to transfer external LOB files into the database internal LOB structures.

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).

Below are the steps:

1. create a directory where you will find the image files:

CREATE OR REPLACE DIRECTORY ORCL_IMG_FILE as '/orcl/data/img_file';

GRANT READ, WRITE ON DIRECTORY test_dir TO abc;

If  it is windows the select your windows directory.

2.Table which hold the image

CREATE TABLE EXTRACT_FILE_IMG_TBL

(

  FILE_NAME       VARCHAR2(100 BYTE),

  FILE_SIZE       VARCHAR2(100 BYTE),

  EXTRACT_FILE    BLOB,

  LOAD_DATE_TIME  DATE

);

3.Processing the image file to the table,below is the procedure:

CREATE OR REPLACE PROCEDURE PR_IMAGE_UPLOAD(pfname VARCHAR2)--pfname is the image file name which we want to load into db table.

AS

  upload_file_name    VARCHAR2(100);

  src_file            BFILE;

  dst_file            BLOB;

  lgh_file                                     INTEGER;

  pdname              VARCHAR2(100);

  v_file_size_chk     BOOLEAN       :=TRUE;

  g_sqlcode           VARCHAR2(50)  := NULL;

  g_sqlerrm           VARCHAR2(250) := NULL;

  g_customized_msg    VARCHAR2(500) := NULL;

  v_proc_start_time   DATE;

  v_proc_end_time     DATE;

  v_elapsed_time      NUMBER;

  v_lineno                              NUMBER :=0;

BEGIN

v_lineno :=1;

v_elapsed_time := 0;

v_proc_start_time := SYSDATE + (1 / 86380);

  BEGIN

  v_lineno :=2;

    pdname   :='ORCL_IMG_FILE';--My directory where through source system images will store

    src_file := bfilename(pdname, pfname);

                lgh_file := NVL(dbms_lob.getlength(src_file),0);

-- +-----------------------------------------------------------------------------------------------------------+

 -- | ASSIGN AN EMPTY VALUE (WITH 0 LENGTH) TO A BLOB COLUMN. ALTHOUGH THE LENGTH IS 0, THE VALUE IS NOT NULL   |

-- +-----------------------------------------------------------------------------------------------------------+

  v_lineno :=3;

     INSERT

      /* + APPEND */

       INTO EXTRACT_FILE_IMG_TBL

      (

        FILE_NAME,

        FILE_SIZE,

        EXTRACT_FILE,

        load_date_time

      )

      VALUES

      (

        pfname  ,

        lgh_file,

        EMPTY_BLOB(),

        sysdate

      )

    RETURNING EXTRACT_FILE

       INTO dst_file;

    COMMIT;

               

    v_lineno :=4;

               

    /*lock record till update */

     SELECT EXTRACT_FILE

       INTO dst_file

       FROM EXTRACT_FILE_IMG_TBL

      WHERE FILE_NAME = pfname and file_size=lgh_file FOR UPDATE;

                 

    /* open the file */

    dbms_lob.fileopen(src_file, dbms_lob.file_readonly);

               

    /* determine the length of file */

    /* Read the file and intiate dst_file varible with input file */

    dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

                   

    v_lineno :=5;

               

                /* update the blob field */

                UPDATE EXTRACT_FILE_IMG_TBL

                SET EXTRACT_FILE  = dst_file

                WHERE FILE_NAME = pfname

                and FILE_SIZE=lgh_file;

                COMMIT;

               

    /* close file */

    dbms_lob.fileclose(src_file);

    COMMIT;

v_proc_end_time := SYSDATE + (1 / 86380);

v_elapsed_time := v_proc_end_time-v_proc_start_time;

 dbms_output.put_line('Total Elapsed_time for image processing into database'||' = '||v_elapsed_time); 

   

  EXCEPTION

  WHEN OTHERS THEN

    g_customized_msg := 'Error while inserting data into extract_file_tprr_tbl';

    g_sqlcode        := SQLCODE;

    g_sqlerrm        := sqlerrm;

--   

                DELETE FROM EXTRACT_FILE_IMG_TBL WHERE FILE_NAME = pfname and FILE_SIZE=lgh_file;

                COMMIT;

    RAISE_APPLICATION_ERROR

    (

      -20420, 'ERROR WHILE EXECUTING PR_IMAGE_UPLOAD'||' line no: '|| v_lineno||' '|| g_sqlcode||' '|| g_sqlerrm

    )

    ;

  END;

END PR_IMAGE_UPLOAD;