Import CLOB Contents

The following article presents a simple methods for importing a file into a CLOB datatype. First a directory object is created to point to the relevant filesystem directory:

CREATE OR REPLACE DIRECTORY documents AS 'C:\';

Next we create a table to hold the CLOB:

CREATE TABLE tab1 (col1 CLOB);

Finally we import the file into a CLOB datatype and insert it into the table:

DECLARE
  l_bfile  BFILE;
  l_clob   CLOB;
BEGIN
  INSERT INTO tab1 (col1)
  VALUES (empty_clob())
  RETURN col1 INTO l_clob;

  l_bfile := BFILENAME('DOCUMENTS', 'Sample.txt');
  DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
  DBMS_LOB.loadfromfile(l_clob, l_bfile, DBMS_LOB.getlength(l_bfile));
  DBMS_LOB.fileclose(l_bfile);

  COMMIT;
END;
/

Hope this helps. Regards Tim...

Back to the Top.