SET LONG 200000
SET LINESIZE 2000



-- Demonstrate the usage of getBFileContent() subprogram. This PL/SQL block reads the content of a file named
-- 'XMLDB_Utilities_Overview.htm' available in the user specified directory and stores it into the database 

-- table named 'demoTable'.

DECLARE

  -- OS file whose content needs to be read.
  fileName      VARCHAR2(50) := 'XMLDB_Utilities_Overview.html'; 


  -- Directory Object specifying the file location.
  directoryName VARCHAR2(50) := 'SOURCE_DIR';        

  -- BFILE Handle for the file on the OS.            

  fileOnOS      BFILE;    

  -- The CLOB object that will hold the content of the file.                                       
  bFileContent  CLOB;                                            

BEGIN

  -- Get the BFILE object from the filename and the directory specified.

  fileOnOS:= bfilename(directoryName , fileName);

  -- Get the BFILE content as CLOB.
  bfileContent := xdb_utilities.getBFileContent (fileOnOS);

  -- Delete any existing data from the table 'demoTable' and 
  -- insert the CLOB value obtained into the 'demoTable' table for
  -- viewing the file contents later.
  DELETE FROM demoTable;

  INSERT INTO demoTable VALUES( bfileContent );
  COMMIT;

END;
/
  
-- View the file content.
SELECT content FROM demoTable;