singh.neerajin

 Profile Answers by singh.neerajin 

• May 28th, 2008

 

It is always better to store the images in the BLOB or CLOB datatype.......

PROCEDURE apply_changes_to_illust
(
path varchar2
)
IS

  f_lob BFILE;
  b_lob BLOB;
  image_name VARCHAR2(30);
  dot_pos NUMBER;
  x varchar2(100);
  f utl_file.file_type;
  FileChk VARCHAR2(10) := NULL;
  SCHEMAUSER VARCHAR2(2000);
  Cur VARCHAR2(4000) := NULL;
  IN_PATH varchar2(2000);
  TYPE IMG_NAME IS REF CURSOR;
  IMG_DIFF IMG_NAME;

  z_IMGNAME  ILLUST.ILLUSNAME%TYPE;
  z_IllusID  ILLUST.ILLUSID%TYPE;
 

BEGIN

  execute immediate (' ALTER TRIGGER ILLUST DISABLE ');
  execute immediate ('create or replace directory SOURCE_DIR as '''|| path ||'''');
  CUR:='SELECT ILLUSNAME,ILLUSID FROM ILLUST';

  OPEN IMG_DIFF FOR CUR;
   LOOP
    Fetch IMG_DIFF INTO z_IMGNAME,z_IllusID;
    EXIT When IMG_DIFF%NOTFOUND;
      dot_pos := INSTR(z_IMGNAME,'.');

      image_name := z_IMGNAME || '.tif';
       SELECT file_exists(image_name) INTO FileChk from dual;
       IF FileCHk = 'TRUE' THEN
                  
          
           UPDATE ILLUST SET ILLUSIMAGE = empty_blob() WHERE ILLUSNAME = z_IMGNAME AND ILLUSID= z_IllusID
           RETURN ILLUSIMAGE INTO b_lob;
           f_lob := BFILENAME('SOURCE_DIR', image_name);
           dbms_lob.fileopen(f_lob,dbms_lob.file_readonly);
           dbms_lob.loadfromfile(b_lob,f_lob,dbms_lob.getlength(f_lob) );
           dbms_lob.fileclose(f_lob);
           commit;
           END IF;
    END LOOP;
    execute immediate (' ALTER TRIGGER ILLUST ENABLE ');
END apply_changes_to_illust;


In this code there is a directory where images had been located . This code first checks the existence of images . If  images found then its is loaded in the table.

SOURCE_DIR : it is the directory path where the images are residing. path is a parameter throug which directory path is coming.

image_name : is the name of the image coming through  the cursor.

File_exists : is a function which checks the existence of image_name  under in the root directory:

If image is found then its ia lodaed in the ILLUST table in the;

ILLUSIMAGE : is the column name where image is laoded

flob: is the file name of the image
b_lob: paramneter of BLOB
dbms_lob.fileopen: opens the image file.
dbms_lob.loadfromfile: load the image file .
dbms_lob.fileclose: closes the image file opened.

finally all the images are laoaded.....