Use a BFILE to load a LOB column : BFILE « Large Objects « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Large Objects
3. BFILE

SQL>
SQL> CREATE TABLE facebook (
  2     name VARCHAR2(80),
  3     photo BLOB,
  4     directions CLOB,
  5     description NCLOB,
  6     web_page BFILE);

Table created.

SQL>
SQL> CREATE DIRECTORY bfile_data AS 'c:\xxx';

Directory created.

SQL>
SQL>
SQL> DECLARE
  2     myBFile BFILE := BFILENAME('BFILE_DATA','TanneryFalls.directions');
  3     directions CLOB;
  4     destination_offset INTEGER := 1;
  5     source_offset INTEGER := 1;
  6     language_context INTEGER := DBMS_LOB.default_lang_ctx;
  7     warning_message INTEGER;
  8  BEGIN
  9
 10     DELETE FROM facebook WHERE name='Falls';
 11
 12     INSERT INTO facebook (name,directions)VALUES ('Falls',EMPTY_CLOB());
 13
 14     SELECT directions INTO directions FROM facebook WHERE name='Falls';
 15
 16     DBMS_LOB.OPEN(directions, DBMS_LOB.LOB_READWRITE);
 17     DBMS_LOB.OPEN(myBFile);
 18
 19     DBMS_LOB.LOADCLOBFROMFILE(directions, myBFile,
 20                               DBMS_LOB.LOBMAXSIZE,
 21                               destination_offset, source_offset,
 22                               NLS_CHARSET_ID('US7ASCII'),
 23                               language_context, warning_message);
 24
 25     IF warning_message = DBMS_LOB.WARN_INCONVERTIBLE_CHAR THEN
 26          dbms_output.put_line('Warning! Some characters couldn''t be converted.');
 27     END IF;
 28
 29     DBMS_LOB.CLOSE(directions);
 30     DBMS_LOB.CLOSE(myBFile);
 31  END;
 32  /
DECLARE
*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
The system cannot find the path specified.
ORA-06512: at "SYS.DBMS_LOB", line 716
ORA-06512: at line 17


SQL>
SQL>
SQL>
SQL> drop table facebook;

Table dropped.

SQL> drop directory bfile_data;

Directory dropped.

SQL>