-- lob_example.sql

-- NOTE: hamlet.xml must be placed in /tmp or another directory readable by
-- the oracle install user, then use the same directory for TEMPFILE_DIR

DROP TABLE xmltable
/
CREATE TABLE xmltable (
    filename   VARCHAR2(20),
    xmlData    CLOB
)
/
CREATE OR REPLACE DIRECTORY TEMPFILE_DIR AS '/tmp'
/
CREATE OR REPLACE FUNCTION GetDoc( filename VARCHAR2 ) RETURN CLOB
IS
  file         BFILE;
  data         CLOB;
  src_offset   INTEGER := 1;
  dst_offset   INTEGER := 1;
  src_charset  NUMBER := NLS_CHARSET_ID('WE8MSWIN1252');
  lang_context NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
  fileLen      INTEGER;
  warnings     INTEGER;
BEGIN
  file := BFILENAME('TEMPFILE_DIR',filename);
  DBMS_LOB.OPEN(file);
  DBMS_LOB.CREATETEMPORARY(data, TRUE, DBMS_LOB.SESSION);
  fileLen := DBMS_LOB.GETLENGTH(file);
  DBMS_LOB.LOADCLOBFROMFILE(data, file, fileLen, src_offset, dst_offset,
                            src_charset, lang_context, warnings);
  DBMS_LOB.CLOSE(file);
  RETURN data;
END;
/
DECLARE
  clobData CLOB;
BEGIN
  clobData := GetDoc('hamlet.xml');
  INSERT INTO xmltable VALUES ('hamlet.xml',clobData);
END;
/
