Database Reference
In-Depth Information
Loading a LOB via PL/SQL
The
DBMS_LOB
package has entry points called
LoadFromFile
,
LoadBLOBFromFile
, and
LoadCLOBFromFile
. These
procedures allow us to use a
BFILE
(which can be used to read operating system files) to populate a
BLOB
or
CLOB
in the database. There is not a significant difference between the
LoadFromFile
and
LoadBLOBFromFile
routines,
other than the latter returns
OUT
parameters that indicate how far into the
BLOB
column we have loaded data.
The
LoadCLOBFromFile
routine, however, provides a significant feature: character set conversion. If you recall, in
Chapter 12 we discussed some of the National Language Support (NLS) features of the Oracle database and the
importance of character sets.
LoadCLOBFromFile
allows us to tell the database that the file it is about to load is in
a character set different from the one the database is using, and that it should perform the required character set
conversion. For example, you may have a
UTF8
-compatible database, but the files received to be loaded are encoded
in the
WE8ISO8859P1
character set, or vice versa. This function allows you to successfully load these files.
■
For complete details on the procedures available in the
DBMS_LOB
package and their full set of inputs and
outputs, please refer to the
Oracle PL/SQL Packages and Types Reference
.
Note
To use these procedures, we will need to create a
DIRECTORY
object in the database. This object will allow us to
create
BFILES
(and open them) that point to a file existing on the file system that the database server has access to.
This last phrase, “that the database server has access to,” is a key point when using PL/SQL to load LOBs. The
DBMS_LOB
package executes entirely in the server. It can see only the file systems the server can see. It cannot, in
particular, see your local file system if you are accessing Oracle over the network.
So we need to begin by creating a
DIRECTORY
object in the database. This is a straightforward process. We will
create two directories for this example (note that these examples are executed in a UNIX/Linux environment; you will
use the syntax for referring to directories that is appropriate for your operating system):
EODA@ORA12CR1> create or replace directory dir1 as '/tmp/';
Directory created.
EODA@ORA12CR1> create or replace directory "dir2" as '/tmp/';
Directory created.
■
oracle
DIRECTORY
objects are logical directories, meaning they are pointers to existing, physical directories in
your operating system. the
CREATE DIRECTORY
command
does not
actually create a directory in the file system—you
must perform that operation separately.
Note
The user who performs this operation needs to have the
CREATE ANY DIRECTORY
privilege. The reason we create
two directories is to demonstrate a common case-related (“case” as in uppercase versus lowercase characters) issue
with regard to
DIRECTORY
objects. When Oracle created the first directory
DIR1
, it stored the object name
DIR1
in
uppercase
as it is the default. In the second example with
dir2
, it will have created the
DIRECTORY
object preserving the
case we used in the name. The importance of this will be demonstrated shortly when we use the
BFILE
object.
