UPDATE a BFILE Using BFILENAME()

Figure 11-30 Use Case Diagram: UPDATE a BFILE Using BFILENAME()

See Also: "Use Case Model: External LOBs (BFILEs)" for all basic operations of External LOBs (BFILES).

Fe describes how to UPDATE a BFILE using BFILENAME().

Usage Notes

BFILENAME() Function

The BFILENAME() function can be called as part of SQL INSERT or UPDATE to initialize a BFILE column or attribute for a particular row by associating it with a physical file in the server's filesystem.

The DIRECTORY object represented by the directory_alias parameter to this function need not already be defined using SQL DDL before the BFILENAME() function is called in SQL DML or a PL/SQL program. However, the directory object and operating system file must exist by the time you actually use the BFILE locator (for example, as having been used as a parameter to an operation such as OCILobFileOpen(), DBMS_LOB.FILEOPEN(), OCILobOpen(), or DBMS_LOB.OPEN()).

Note that BFILENAME() does not validate privileges on this DIRECTORY object, or check if the physical directory that the DIRECTORY object represents actually exists. These checks are performed only during file access using the BFILE locator that was initialized by the BFILENAME() function.

You can use BFILENAME() as part of a SQL INSERT and UPDATE statement to initialize a BFILE column. You can also use it to initialize a BFILE locator variable in a PL/SQL program, and use that locator for file operations. However, if the corresponding directory alias and/or filename does not exist, then PL/SQL DBMS_LOB routines that use this variable will generate errors.

The directory_alias parameter in the BFILENAME() function must be specified taking case-sensitivity of the directory name into consideration.

Syntax

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

See Also: "DIRECTORY Name Specification" for information about the use of uppercase letters in the directory name, and OCILobFileSetName() in Oracle Call Interface Programmer's Guide for an equivalent OCI based routine.

Syntax

Use the following syntax references:

• SQL (Oracle8i SQL Reference):Chapter 7, "SQL Statements" -- UPDATE. Chapter 4, "Functions" -- BFILENAME()

Scenario

This example updates Multimedia_tab by means of the BFILENAME function.

Examples

The example is provided in SQL syntax and applies to all programmatic environments:

• SQL: Update a BFILE by means of BFILENAME()

SQL: Update a BFILE by means of BFILENAME()

UPDATE Multimedia_tab 
   SET Photo = BFILENAME('PHOTO_DIR', 'Nixon_photo') where Clip_ID = 3;