SET LONG 200000
SET LINESIZE 2000



-- Demonstrate the usage of getFileContent() subprogram.This PL/SQL block reads the content of a file named
-- 'XMLDB_Utilities_Overview.html' 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';        

  -- CLOB Object to hold the content of the file from the user specified directory.
  fileContent         CLOB;        


BEGIN
  -- Get the content of the file.
  -- Get the content of the file specified above and located at the directory mentioned above as CLOB.
  -- Note: The below mentioned subprogram call will throw an error if Directory Object 
  -- specified  does not exists.

  fileContent := xdb_utilities.getFileContent(fileName, directoryName);

  -- Delete any existing data from the table 'demoTable' and 
  -- insert the CLOB value obtained in the 'demoTable' table for

  -- viewing the file contents later.
  DELETE FROM demoTable;
  INSERT INTO demoTable VALUES( fileContent );
  COMMIT;
END;
/

-- View the file contents.

SELECT content FROM demoTable;