CREATE TABLE FT_GALVA (GALV_NO VARCHAR2(2),GALV_APPR_ST NUMBER,img_name varchar2(30),PIC_GALV BLOB)


Create or Replace directory image_dir as 'e:\image_dir';

Grant all on directory image_dir to public


/* Formatted on 2013/04/25 23:50 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE insert_image_file (
   p_id                NUMBER,
   p_status            NUMBER,
   p_image_name   IN   VARCHAR2
)
IS
   src_file   BFILE;
   dst_file   BLOB;
   lgh_file   BINARY_INTEGER;
BEGIN
   src_file := BFILENAME ('IMAGE_DIR', p_image_name);

   -- insert a NULL record to lock
   INSERT INTO ft_galva
               (galv_no, galv_appr_st, img_name, pic_galv
               )
        VALUES (p_id, p_status, p_image_name, EMPTY_BLOB ()
               )
     RETURNING pic_galv
          INTO dst_file;

   -- lock record
   SELECT     pic_galv
         INTO dst_file
         FROM ft_galva
        WHERE galv_no = p_id
          AND img_name = p_image_name
          AND galv_appr_st = p_status
   FOR UPDATE;

   -- open the file
   DBMS_LOB.fileopen (src_file, DBMS_LOB.file_readonly);
   -- determine length
   lgh_file := DBMS_LOB.getlength (src_file);
   -- read the file
   DBMS_LOB.loadfromfile (dst_file, src_file, lgh_file);

   -- update the blob field
   UPDATE ft_galva
      SET pic_galv = dst_file
    WHERE galv_no = p_id
      AND img_name = p_image_name
      AND galv_appr_st = p_status;

   -- close file
   DBMS_LOB.fileclose (src_file);
END insert_image_file;

execute insert_image_file(1,3,'a.jpg');

return (:status = 3);