Oracle PL/SQL Tutorial/Large Objects/BFILE

BFILE column and directory

SQL> CREATE TABLE myBFile
  2  (id NUMBER PRIMARY KEY,
  3   bfile_data BFILE);
Table created.
SQL>
SQL> CREATE DIRECTORY bfile_dir AS "c:\proj";

SQL>
SQL> DECLARE
  2    bfileValue BFILE;
  3  BEGIN
  4    INSERT INTO myBFile VALUES (1,BFILENAME("BFILE_DIR","test.bmp"));
  5    SELECT bfile_data
  6    INTO bfileValue
  7    FROM myBFile
  8    WHERE id = 1;
  9
 10  END;
 11  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table myBFile;
Table dropped.
SQL>

BFILE type column

SQL> CREATE TABLE myBFile
  2  (id NUMBER PRIMARY KEY,
  3   bfile_data BFILE);
Table created.
SQL>
SQL> INSERT INTO myBFile VALUES (1,null);
1 row created.
SQL>
SQL>
SQL> drop table myBFile;
Table dropped.
SQL>

BFILE value and BFILENAME function

SQL> CREATE TABLE myBFile
  2  (id NUMBER PRIMARY KEY,
  3   bfile_data BFILE);
Table created.
SQL>
SQL> INSERT INTO myBFile VALUES (1,BFILENAME("BFILE_DIR","test.bmp"));
1 row created.
SQL>
SQL>
SQL> drop table myBFile;
Table dropped.
SQL>

Create a BFILE locator

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     web_page BFILE;
  3  BEGIN
  4     DELETE FROM facebook WHERE name="Tannery Falls";
  5
  6     web_page := BFILENAME("BFILE_DATA","Tannery Falls.htm");
  7
  8     INSERT INTO facebook (name, web_page) VALUES ("Tannery Falls",web_page);
  9  END;
 10  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table facebook;
Table dropped.
SQL> drop directory bfile_data;
Directory dropped.
SQL>

Creating Tables Containing BFILE Objects

SQL>
SQL> CREATE TABLE myTable (
  2    id           INTEGER PRIMARY KEY,
  3    bfile_column BFILE NOT NULL
  4  );
Table created.
SQL>
SQL> desc myTable;
 Name            Null?    Type
 ---------------
 ID              NOT NULL NUMBER(38)
 BFILE_COLUMN    NOT NULL BINARY FILE LOB
SQL>
SQL> drop table myTable;
Table dropped.

Populating a BFILE Column with a Pointer to a File

You use the Oracle database"s BFILENAME() function to populate the BFILE column with a pointer to your external file.

The BFILENAME() function accepts two parameters:

the database directory object"s name (which was created earlier) and the name of the file.

34. 2. BFILE 34. 2. 1. <A href="/Tutorial/Oracle/0660__Large-Objects/CreatingTablesContainingBFILEObjects.htm">Creating Tables Containing BFILE Objects</a> 34. 2. 2. Populating a BFILE Column with a Pointer to a File 34. 2. 3. <A href="/Tutorial/Oracle/0660__Large-Objects/PopulatingBFILE.htm">Populating BFILE</a> 34. 2. 4. <A href="/Tutorial/Oracle/0660__Large-Objects/BFILEtypecolumn.htm">BFILE type column</a> 34. 2. 5. <A href="/Tutorial/Oracle/0660__Large-Objects/BFILEvalueandBFILENAMEfunction.htm">BFILE value and BFILENAME function</a> 34. 2. 6. <A href="/Tutorial/Oracle/0660__Large-Objects/BFILEcolumnanddirectory.htm">BFILE column and directory</a> 34. 2. 7. <A href="/Tutorial/Oracle/0660__Large-Objects/CreateaBFILElocator.htm">Create a BFILE locator</a> 34. 2. 8. <A href="/Tutorial/Oracle/0660__Large-Objects/RetrievetheLOBlocater.htm">Retrieve the LOB locater</a> 34. 2. 9. <A href="/Tutorial/Oracle/0660__Large-Objects/UseaBFILEtoloadaLOBcolumn.htm">Use a BFILE to load a LOB column</a>

Populating BFILE

Oracle accesses files on the server by using a directory.

A directory is just a pointer to an operating system folder.

Assuming that a folder C:\IO exists on your server, and you want to call that folder IO within Oracle:

create directory IO as "C:\IO";

grant read, write on directory IO to public;

Now, when you refer to IO in any commands, you"re referring to the C:\IO folder in the file system.

To create a pointer to the file on the server and place that pointer in the table on an existing record

SQL> --1: Creating a Pointer
SQL>
SQL> create table catalog
  2  (id number,
  3   name VARCHAR2(2000),
  4   manual_cl CLOB,
  5   firstpage_bl BLOB,
  6   mastertxt_bf BFILE
  7   );
Table created.
SQL>
SQL> create directory IO as "C:\IO";
Directory created.
SQL> --grant read, write on directory IO to public;
SQL>
SQL>
SQL> declare
  2      v_bf BFILE;
  3  begin
  4      v_bf:=BFILENAME ("IO", "text.htm");
  5      insert into catalog(id, name, mastertxt_bf) values (1, "TEXT.HTM", v_bf);
  6  end;
  7  /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table catalog;
Table dropped.

Retrieve the LOB locater

SQL>
SQL> CREATE TABLE waterfalls (
  2     falls_name VARCHAR2(80),
  3     falls_photo BLOB,
  4     falls_directions CLOB,
  5     falls_description NCLOB,
  6     falls_web_page BFILE);
Table created.
SQL>
SQL> CREATE DIRECTORY bfile_data AS "c:\xxx";
Directory created.
SQL>
SQL> DECLARE
  2     web_page BFILE;
  3     html RAW(60);
  4     amount BINARY_INTEGER := 60;
  5     offset INTEGER := 1;
  6  BEGIN
  7
  8     SELECT falls_web_page
  9       INTO web_page
 10       FROM waterfalls
 11      WHERE falls_name="Tannery Falls";
 12
 13     DBMS_LOB.OPEN(web_page);
 14     DBMS_LOB.READ(web_page, amount, offset, html);
 15     DBMS_LOB.CLOSE(web_page);
 16
 17     DBMS_OUTPUT.PUT_LINE(RAWTOHEX(html));
 18
 19     DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(html));
 20  END;
 21  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 8

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> drop table waterfalls;
Table dropped.
SQL> drop directory bfile_data;
Directory dropped.
SQL>

Use a BFILE to load a LOB column

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>