rem -----------------------------------------------------------------------
rem Filename:   ctxload.sql
rem Purpose:    Load a document from a file into the database for later 
rem             indexing.
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set serveroutput on

-- Create a table to load file into...
drop table cv_emp;
create table cv_emp(id number, cv clob);

-- Create a directory pointing to the files we need to load...
create or replace directory CV_TEXT as '/tmp/';

declare
  v_text_loc  CLOB;
  v_file_loc  BFILE;
  v_file_len  number;
  v_file_name varchar2(30) := 'mycv.txt';
begin
  insert into cv_emp values(1, EMPTY_CLOB())
	 returning cv into v_text_loc;

  v_file_loc := BFILENAME('CV_TEXT', v_file_name);
  v_file_len := DBMS_LOB.GETLENGTH(v_file_loc);
  dbms_output.put_line('File size: '||v_file_len||' bytes.');

  -- Open the file and read it in...
  dbms_lob.fileopen(v_file_loc, dbms_lob.file_readonly); 
  dbms_lob.loadfromfile(v_text_loc, v_file_loc, v_file_len); 
  dbms_lob.fileclose(v_file_loc); 

  commit;
end;
/

-- 
set long 32000 pages 5000
select * from cv_emp where id = 1;