Example 5-1: Inserting an external XML file into a CLOB

CREATE OR REPLACE PROCEDURE insertXmlFile( dir VARCHAR2,
                                          file VARCHAR2,
                                          name VARCHAR2 := NULL) IS
  theBFile   BFILE;
  theCLob    CLOB;
  theDocName VARCHAR2(200) := NVL(name,file);
BEGIN
  -- (1) Insert a new row into xml_documents with an empty CLOB, and
  -- (2) Retrieve the empty CLOB into a variable with RETURNING..INTO

  INSERT INTO xml_documents(docname,xmldoc) VALUES(theDocName,empty_clob())
  RETURNING xmldoc INTO theCLob;

  -- (3) Get a BFile handle to the external file
  theBFile := BFileName(dir,file);

  -- (4) Open the file
  dbms_lob.fileOpen(theBFile);

  -- (5) Copy the contents of the BFile into the empty CLOB
  dbms_lob.loadFromFile(dest_lob => theCLob,
                         src_lob => theBFile,
                         amount  => dbms_lob.getLength(theBFile));

  -- (6) Close the file and commit
  dbms_lob.fileClose(theBFile);
  COMMIT;
END;