13.2.1 The BFILENAME function

The BFILENAME function initializes a BFILE column in a database table or a BFILE variable in PL/SQL by returning a locator to a physical file in the server's filesystem. The header for BFILENAME is:

FUNCTION BFILENAME
   (directory_alias IN VARCHAR2, filename IN VARCHAR2
RETURN BFILE;

where directory_alias is a DIRECTORY database object which has already been defined with a CREATE DIRECTORY statement (see the following examples), and filename is the name of the file containing the large binary object.

This function will return NULL if you try to get a BFILE locator with a directory alias which does not yet exist.

You can use BFILENAME in SQL INSERT and UPDATE statements. You can also use it to initialize a BFILE locator in a PL/SQL program. Here are examples of both usages:

UPDATE school_report
   SET photo_op =
       BFILENAME ('projects', 'grasshoppers.atwork')
 WHERE title = 'REPRODUCTIVE CYCLES OF BUGS';

DECLARE
   pricelist BFILE;
BEGIN
   /* Now this is a BIG file. */
   pricelist := BFILENAME ('OraclePrices', '1997.all');
   

The BFILENAME function does not validate that the user has READ privileges on the specified file or directory alias. It also does not perform a physical check to see if the directory or file actually exist. Instead, these checks are performed when access against the BFILE object is attempted.

There are several issues to keep in mind with directory aliases:

• An alias is a database object, an "internal" name for an external, filesystem-based directory location.

• You must create a directory alias before you can use it.

• To create a directory alias, you will need the CREATE DIRECTORY or CREATE ANY DIRECTORY privileges.

• To access a directory alias, you must be the owner or be granted the READ privilege on that alias.

• Directory aliases can be case-sensitive!

Let's step through the creation and use of several directory aliases to drive home these points. Suppose I want to let the SCOTT account create directory aliases. I will connect to my DBA account and grant the privilege:

SQL> GRANT CREATE DIRECTORY TO SCOTT;

Then, connecting as SCOTT, I will create two directory aliases:

SQL> CREATE DIRECTORY projects AS 'm:\school\projects';
SQL> CREATE DIRECTORY "OraclePrices" AS '/oracle/prices';

Notice the double quotes around OraclePrices. By taking this approach, I have requested that this directory alias be stored in the database with mixed case. The PROJECTS directory alias, on the other hand, has been defined in the database using the default, uppercase method.

I want to let anyone access the OraclePrices directory, but let only ELI access the projects directory:

SQL> GRANT READ ON DIRECTORY projects TO SCOTT;
Grant succeeded.
SQL> GRANT READ ON DIRECTORY OraclePrices TO PUBLIC;
GRANT READ ON DIRECTORY OraclePrices TO PUBLIC;
                        *
ERROR at line 1:
ORA-22930: directory does not exist

What went wrong? I did not put double quotes around OraclePrices, so Oracle converted the identifier to uppercase and then could not find a match inside the ALL_DIRECTORIES data dictionary view.

Once I have defined the directory in mixed case, I need to continue to use the double quotes in my DDL statements for those statements to succeed. Within SQL DML and PL/SQL, the situation is a little bit different. When you call BFILENAME, you pass in a directory alias, either as an identifier or as a literal. In the call to BFILENAME, the case used to set that directory alias must match the case originally used to define the directory alias.

The following INSERT statement correctly identifies the OraclePrices alias:

INSERT INTO sw_budget
   (item_desc, price, source_lob)
VALUES
   ('ORACLE8', a_bargain, BFILENAME ('OraclePrices', '1997.rdbms');

where a_bargain is a PL/SQL variable previously defined and set.

The following PL/SQL block incorrectly defines the projects directory alias:

DECLARE
   projects_dir VARCHAR2(30) := 'projects'; -- lower case!
   ants_scurrying BFILE;
BEGIN
   ants_scurrying := BFILENAME (projects_dir, 'ants_building.hill');

   /* FILEOPEN will fail because BFILE locator is NULL. */
   DBMS_LOB.FILEOPEN (ants_scurrying);
BEGIN

I have passed in a lowercase "projects", but the directory alias has been set to uppercase since I did not surround the directory alias name in double quotes in the CREATE DIRECTORY statement. This behavior differs from much of the rest of PL/SQL's interaction with the data dictionary. Usually, all names passed in for data dictionary access are by default uppercased. You will need to be careful how you define directory aliases and then reference them in your code.

13.2.2 The EMPTY_BLOB function

The EMPTY_BLOB function returns an empty locator of type BLOB (binary large object). The specification for the EMPTY_BLOB function is:

FUNCTION EMPTY_BLOB RETURN BLOB;

You can call this function without any parentheses or with an empty pair. Here are some examples:

INSERT INTO family_member (name, photo)
   VALUES ('Steven Feuerstein', EMPTY_BLOB());

DECLARE
   my_photo BLOB := EMPTY_BLOB;
BEGIN

Use EMPTY_BLOB to initialize a BLOB to "empty." Before you can work with a BLOB, either to reference it in SQL DML statements such as INSERTs or to assign it a value in PL/SQL, it must contain a locator. It cannot be NULL. The locator might point to an empty BLOB value, but it will be a valid BLOB locator.

13.2.3 The EMPTY_CLOB function

The EMPTY_CLOB function returns an empty locator of type CLOB. The specification for the EMPTY_CLOB function is:

FUNCTION EMPTY_CLOB RETURN CLOB;

You can call this function without any parentheses or with an empty pair. Here are some examples:

INSERT INTO diary (entry, text) VALUES (SYSDATE, EMPTY_CLOB());

DECLARE
   the_big_novel CLOB := EMPTY_CLOB;
BEGIN

Use EMPTY_CLOB to initialize a CLOB to "empty". Before you can work with a CLOB, either to reference it in SQL DML statements such as INSERTs or to assign it a value in PL/SQL, it must contain a locator. It cannot be NULL. The locator might point to an empty CLOB value, but it will be a valid CLOB locator.