Loading a CLOB from a file

The Oracle user account to be used for loading the file contents into a CLOB needs read access on the Oracle directory containing the file. Using an account with the ability to create a directory, commands such as the following need to be executed:

CREATE DIRECTORY public_html AS '/home/rik/public_html';
GRANT read ON DIRECTORY public_html TO rik;

The user session will need to (re)connect after the above GRANT has been applied, then code like the below can be used to load the file contents into a CLOB.

  l_clob   CLOB;
  l_bfile  BFILE;
  l_amt    NUMBER;
  l_pg_num NUMBER;
  l_bfile := BFILENAME('PUBLIC_HTML', 'meters.php');
  l_amt := DBMS_LOB.getlength(file_loc => l_bfile);
  SELECT MAX(c.page_number) + 1
  INTO   l_pg_num
  FROM   table_with_clob_field c;
  INTO   table_with_clob_field (page_number, html)
  VALUES (l_pg_num, empty_clob())
  RETURNING html INTO l_clob;
  DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
  DBMS_LOB.loadfromfile(l_clob, l_bfile, l_amt);