Database Reference
In-Depth Information
Now, if we inspect the contents of the
LOB_DEMO
table after running SQLLDR, we will discover the following:
EODA@ORA12CR1> select owner, time_stamp, filename, dbms_lob.getlength(data)
2 from lob_demo
3 /
OWNER TIME_STAM FILENAME DBMS_LOB.GETLENGTH(DATA)
---------- --------- -------------------- ------------------------
oracle 22-JUL-14 demo1.log_xt 14889
oracle 22-JUL-14 demo2.ctl 123
oracle 23-JUL-14 demo.bad 712
oracle 09-MAR-14 demo.control_files 8136
oracle 23-JUL-14 demo.ctl 825
oracle 23-JUL-14 demo.log 0
oracle 23-JUL-14 dl.sql 118
oracle 23-JUL-14 lob_demo.sql 127
oracle 10-MAR-14 p.bsh 171
oracle 10-MAR-14 prime.bsh 327
oracle 06-MAR-14 run_df.sh 24
11 rows selected.
■
You might ask, “Why is the size of
demo.log
apparently 0?” During the running of SQLLDr it would open the
demo.log
file for writing, which would zero out the length of that file and reset that file. So while loading the demo.log
file, it was empty.
Note
This works with
CLOB
s as well as
BLOB
s. Loading a directory of text files using SQLLDR in this fashion is easy.
Loading LOB Data into Object Columns
Now that we know how to load into a simple table we have created ourselves, we might also find the need to load
into a table that has a complex object type with a LOB in it. This happens most frequently when using the image
capabilities. The image capabilities are implemented using a complex object type,
ORDSYS.ORDIMAGE
. We need to be
able to tell SQLLDR how to load into this.
To load a LOB into an
ORDIMAGE
type column, we must understand a little more about the structure of the
ORDIMAGE
type. Using a table we want to load into, and a
DESCRIBE
on that table in SQL*Plus, we discover that we have
a column called
IMAGE
of type
ORDSYS.ORDIMAGE
, which we want to ultimately load into
IMAGE.SOURCE.LOCALDATA
. The
following examples will work only if you have Oracle Text installed and configured; otherwise, the datatype
ORDSYS.
ORDIMAGE
will be an unknown type:
EODA@ORA12CR1> create table image_load(
2 id number,
3 name varchar2(255),
4 image ordsys.ordimage
5 )
6 /
Table created.
