SQL> create table test
  2    (post   varchar2(10),
  3  	loc    varchar2(20),
  4  	resume blob)
  5  /

Table created.

SQL> create or replace directory my_files as 'c:\test'
  2  /

Directory created.

SQL> select owner, directory_name, substr (directory_path, 1, 30) as path
  2  from   all_directories
  3  where  directory_path like '%test%'
  4  /

OWNER               DIRECTORY_NAME                 PATH
------------------- ------------------------------ ------------------------------
SYS                 MY_FILES                       c:\test

SQL> create or replace procedure load_file is
  2    l_clob BLOB;
  3    l_bfile BFILE:=BFILENAME('MY_FILES','metalinksv.pdf');
  4  begin
  5    insert into res_det values('TEST','TEST1',EMPTY_BLOB())
  6    returning resume into l_clob;
  7    dbms_lob.fileopen(l_bfile);
  8    dbms_lob.loadfromfile(l_clob,l_bfile,dbms_lob.getlength(l_bfile));
  9    dbms_lob.fileclose(l_bfile);
 10  end;
 11  /

Procedure created.

SQL> show errors
No errors.
SQL> exec load_file
BEGIN load_file; END;

*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
No such file or directory
ORA-06512: at "SYS.DBMS_LOB", line 475
ORA-06512: at line 7

SQL> create table demo (id number, theblob blob);

SQL> create directory my_files as 'c:\test';

SQL> ed
Wrote file afiedt.buf

  1  declare
  2        l_clob BLOB;
  3        l_bfile BFILE:=BFILENAME('MY_FILES','metalinksv.pdf');
  4  begin
  5    insert into demo values(1,EMPTY_BLOB())
  6        returning theblob into l_clob;
  7        dbms_lob.fileopen(l_bfile);
  8        dbms_lob.loadfromfile(l_clob,l_bfile,dbms_lob.getlength(l_bfile));
  9        dbms_lob.fileclose(l_bfile);
 10*    end;
SQL> /
declare
*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
No such file or directory
ORA-06512: at "SYS.DBMS_LOB", line 475
ORA-06512: at line 7

create table demo (id number, theblob blob)
/
declare
  l_blob  BLOB;
  l_bfile BFILE := BFILENAME ('c:\test', 'metalinksv.pdf');
begin
  insert into demo values (1, EMPTY_BLOB())
  returning theblob into l_blob;
  dbms_lob.fileopen (l_bfile);
  dbms_lob.loadfromfile (l_blob, l_bfile, dbms_lob.getlength (l_bfile));
  dbms_lob.fileclose (l_bfile);
end;
/

create or replace package image_get
as
    procedure pdf( p_id in demo.id%type );
end;
/

create or replace package body image_get
as

procedure pdf( p_id in demo.id%type )
is
    l_lob    blob;
    l_amt    number default 30;
    l_off   number default 1;
    l_raw   raw(4096);
begin
    select theBlob into l_lob
        from demo 
         where id = p_id;

    owa_util.mime_header( 'image/pdf' );

        begin
           loop
              dbms_lob.read( l_lob, l_amt, l_off, l_raw );

              htp.prn( utl_raw.cast_to_varchar2( l_raw ) );
              l_off := l_off+l_amt;
              l_amt := 4096;
           end loop;
        exception
           when no_data_found then 
              NULL;
        end;
end;
end;

scott@ORA92> create table demo
  2    (id	number,
  3  	theblob blob)
  4  /

Table created.

scott@ORA92> -- skip next line for 8i:
scott@ORA92> create or replace directory my_files as 'c:\test'
  2  /

Directory created.

scott@ORA92> declare
  2    l_blob	     blob;
  3    l_bfile	     bfile;
  4  begin
  5    l_bfile := bfilename ('MY_FILES', 'clip.gif');
  6    insert into demo (id, theblob)
  7    values (1, empty_blob())
  8    returning theblob
  9    into l_blob;
 10    dbms_lob.fileopen (l_bfile);
 11    dbms_lob.loadfromfile
 12  	  (l_blob,
 13  	   l_bfile,
 14  	   dbms_lob.getlength (l_bfile));
 15    dbms_lob.fileclose (l_bfile);
 16    commit;
 17    l_bfile := bfilename ('MY_FILES', 'metalinksv.pdf');
 18    insert into demo (id, theblob)
 19    values (2, empty_blob())
 20    returning theblob
 21    into l_blob;
 22    dbms_lob.fileopen (l_bfile);
 23    dbms_lob.loadfromfile
 24  	  (l_blob,
 25  	   l_bfile,
 26  	   dbms_lob.getlength (l_bfile));
 27    dbms_lob.fileclose (l_bfile);
 28    commit;
 29  end;
 30  /

PL/SQL procedure successfully completed.

scott@ORA92> create or replace procedure get_blob
  2    (p_id in demo.id%type)
  3  as
  4    l_clob clob;
  5  begin
  6    for r in
  7  	 (select theblob
  8  	  from	 demo
  9  	  where  id = p_id)
 10    loop
 11  	 dbms_lob.createtemporary (l_clob, true);
 12  	 ctx_doc.ifilter (r.theblob, l_clob);
 13  	 l_clob := p_id ||' ' || l_clob;
 14  	 for i in 0 .. ceil (length (l_clob) / 200) loop
 15  	   dbms_output.put_line (substr (l_clob, (i * 200) + 1, 200));
 16  	 end loop;
 17    end loop;
 18  end get_blob;
 19  /

Procedure created.

scott@ORA92> show errors
No errors.
scott@ORA92> set serveroutput on
scott@ORA92> execute get_blob (1)
1 <HTML><BODY>



    <H1>Image 1</H1>

    <BR>

  



</BODY></HTML>



PL/SQL procedure successfully completed.

scott@ORA92> execute get_blob (2)
2 <HTML><BODY>






Stuffed Peppers
Servings: 6
Preparation Time: 1 hours 10 minutes
... truncated to save space
. Bake in
preheated 350-degree oven for 1 hour. Serve as is or with heated tomato sauce, especially
sauce with herbs
added.
Per Serving (excluding unknown items): 592 Calories; 39g Fat (58.6% calories
from fat); 34g Protein; 27g Carbohydrate; 4g Dietary Fiber;
131mg Cholesterol; 342mg Sodium.
Exchanges: 1 Grain(Starch); 4 Lean Meat; 1 1/2 Vegetable; 0 Non-Fat Milk; 5 Fat.
</BODY></HTML>

PL/SQL procedure successfully completed.

SQL> DESC DOCUMENTS;
 Name                            Null?    Type
 ------------------------------- -------- ----
 PERSONALNO                               CHAR(4)
 DOCUMENTID                      NOT NULL CHAR(10)
 REFERENCENO                              VARCHAR2(30)
 ISSUEDATE                                DATE
 DOCUMENTTYPEID                           CHAR(2)
 SUBJECT                                  VARCHAR2(50)
 DESIGNATIONID                            CHAR(3)
 FILENO                                   VARCHAR2(30)
 DEPARTMENTID                             NUMBER
 ATTACHEMENT                              BLOB

DECLARE
  filename VARCHAR2(4000) ;
 BEGIN
 	filename := GET_FILE_NAME('','','JPEG FILES/"(*JPG.)|*.JPG|',
 	'select a image', open_file,TRUE);
 read_image_file('X:\Rustam_Satti\Files\'||:documents.Documentid||'.jpg','any','DOCUMENTS.ATTACHEMENT');
 
 END;