 |
|
Oracle Tips by Burleson |
Oracle 10g BFILE Datatypes
The BFILE datatype is unique, in that it has two
pieces of information stored in it: the directory object for the
file and the name of the file within that directory object.
The method of unloading BFILE columns using
the oracle_datapump access driver consists of storing the directory
object name and the file name in two separate columns in the
external table. You use the procedure dbms_lob.filegetname to return
both parts of the name.
However, because dbms_lob.filegetname is a
procedure, it cannot be used directly in a SELECT statement.
Instead, you need to create two functions. The first returns the
name of the directory object and the second returns the name of the
file.
The steps in the following example demonstrate
the unloading of BFILE datatypes.
-
First you must create a function to extract
the directory object for a BFILE column. Note that when the
column is NULL, then NULL is returned.
SQL>
CREATE FUNCTION get_dir_name (bf BFILE) RETURN VARCHAR2 IS
2
DIR_ALIAS VARCHAR2(255);
3
FILE_NAME VARCHAR2(255);
4 BEGIN
5 IF bf is
NULL
6 THEN
7 RETURN
NULL;
8 ELSE
9
DBMS_LOB.FILEGETNAME (bf, dir_alias, file_name);
10 RETURN
dir_alias;
11 END IF;
12 END;
13 /
Function
created.
-
Next create a function to extract the
file name for the BFILE column.
SQL>
CREATE FUNCTION get_file_name (bf BFILE) RETURN VARCHAR2 is
2
dir_alias VARCHAR2(255);
3
file_name VARCHAR2(255);
4 BEGIN
5 IF bf is
NULL
6 THEN
7 RETURN
NULL;
Get the complete Oracle10g story:
To get the code instantly, click here:
Need an Oracle Mentor?
BEI is now offering personal mentors for Oracle DBAs where you can have an
Oracle expert right at your fingertips, anytime day or night. We work with
hundreds of Oracle databases every year, so we know exactly how to quickly
assist you with any Oracle question.
Why risk an unplanned outage? You can now get telephone access to Don
Burleson or any of his Oracle Certified DBAs with more than 20 years of
full-time IT experience. Click here for details:
http://www.dba-oracle.com/service_oracle_backup.htm
|
