8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Import CLOB

A simple method for importing the contents of a file into a CLOB datatype.

Setup
DML Approach
DBMS_LOB Approach

Setup

A directory object is created to point to the relevant filesystem directory.

create or replace directory documents_dir AS 'C:\';

We create a table to hold the CLOB.

create table tab1 (
  id        number,
  clob_data clob
);

DML Approach

We can import the CLOB with a single DML statement. This would be the preferred approach.

insert into tab1 (id, clob_data)
values (1, to_clob(bfilename('DOCUMENTS_DIR', 'Sample.txt')));

To update an existing CLOB we would do the following.

update tab1
set    blob_data = bfilename('DOCUMENTS_DIR', 'Sample.txt')
where  id = 1;

This DML approach was not available when this article was first written.

DBMS_LOB Approach

We import the file into a CLOB datatype and insert it into the table.

declare
  l_bfile  bfile;
  l_clob   clob;

  l_dest_offset   integer := 1;
  l_src_offset    integer := 1;
  l_bfile_csid    number  := 0;
  l_lang_context  integer := 0;
  l_warning       integer := 0;
begin
  insert into tab1 (id, clob_data)
  values (1, empty_clob())
  return clob_data into l_clob;

  l_bfile := bfilename('DOCUMENTS_DIR', 'Sample.txt');
  dbms_lob.fileopen(l_bfile, dbms_lob.file_readonly);
  -- loadfromfile deprecated.
  -- dbms_lob.loadfromfile(l_clob, l_bfile, dbms_lob.lobmaxsize);
  dbms_lob.loadclobfromfile (
    dest_lob      => l_clob,
    src_bfile     => l_bfile,
    amount        => dbms_lob.lobmaxsize,
    dest_offset   => l_dest_offset,
    src_offset    => l_src_offset,
    bfile_csid    => l_bfile_csid ,
    lang_context  => l_lang_context,
    warning       => l_warning);
  dbms_lob.fileclose(l_bfile);

  commit;
end;
/

To update an existing CLOB do the following.

declare
  l_bfile  bfile;
  l_clob   clob;

  l_dest_offset   integer := 1;
  l_src_offset    integer := 1;
  l_bfile_csid    number  := 0;
  l_lang_context  integer := 0;
  l_warning       integer := 0;
begin
  select clob_data
  into   l_clob
  from   tab1
  where  id = 1
  for update;

  l_bfile := bfilename('DOCUMENTS_DIR', 'Sample.txt');
  dbms_lob.fileopen(l_bfile, dbms_lob.file_readonly);
  dbms_lob.trim(l_clob, 0);
  -- loadfromfile deprecated.
  -- dbms_lob.loadfromfile(l_clob, l_bfile, dbms_lob.getlength(l_bfile));
  dbms_lob.loadclobfromfile (
    dest_lob      => l_clob,
    src_bfile     => l_bfile,
    amount        => dbms_lob.lobmaxsize,
    dest_offset   => l_dest_offset,
    src_offset    => l_src_offset,
    bfile_csid    => l_bfile_csid ,
    lang_context  => l_lang_context,
    warning       => l_warning);
  dbms_lob.fileclose(l_bfile);
end;
/

In reality you would probably put this code into a stored procedure, or packaged procedure. This example is available here.

create or replace procedure file_to_clob (p_clob      in out nocopy clob,
                                          p_dir       in  varchar2,
                                          p_filename  in  varchar2)
as
  l_bfile  bfile;

  l_dest_offset   integer := 1;
  l_src_offset    integer := 1;
  l_bfile_csid    number  := 0;
  l_lang_context  integer := 0;
  l_warning       integer := 0;
begin
  l_bfile := bfilename(p_dir, p_filename);
  dbms_lob.fileopen(l_bfile, dbms_lob.file_readonly);
  dbms_lob.trim(p_clob, 0);
  dbms_lob.loadclobfromfile (
    dest_lob      => p_clob,
    src_bfile     => l_bfile,
    amount        => dbms_lob.lobmaxsize,
    dest_offset   => l_dest_offset,
    src_offset    => l_src_offset,
    bfile_csid    => l_bfile_csid ,
    lang_context  => l_lang_context,
    warning       => l_warning);
  dbms_lob.fileclose(l_bfile);
end file_to_clob;
/

You might call this like the following.

declare
  l_clob   clob;
begin
  select clob_data
  into   l_clob
  from   tab1
  where  id = 1
  for update;

  file_to_clob (p_clob     => l_clob,
                p_dir      => 'DOCUMENTS_DIR',
                p_filename => 'Sample.txt');
end;
/

For more information see:

Hope this helps. Regards Tim...

Back to the Top.