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

Home » Articles » 8i » Here

Import CLOB

Related articles.

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 (
  id        NUMBER,
  clob_data CLOB
);

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', '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', '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;
/

For more information see:

Hope this helps. Regards Tim...

Back to the Top.