Database Reference
In-Depth Information
LOBFILES
are relatively simple data files aimed at facilitating LOB loading. The attribute that distinguishes
LOBFILE
s from the main data files is that in
LOBFILE
s, there is no concept of a record, hence
newlines never get in the
way
. In
LOBFILE
s, the data is in any of the following formats:
LOBFILE
)
•
Fixed-length fields (e.g., load bytes 100 through 1000 from the
•
Delimited fields (terminated by something or enclosed by something)
•
Length/value pairs, a variable-length field
The most common of these types is the delimited fields—ones that are terminated by an end-of-file (EOF), in
fact. Typically, you have a directory full of files you would like to load into LOB columns, and each file in its entirety
will go into a
BLOB
. The
LOBFILE
statement with
TERMINATED BY EOF
is what you will use.
So, let's say we have a directory full of files we would like to load into the database. We would like to load the
OWNER
of the file, the
TIME_STAMP
of the file, the
NAME
of the file, and the file itself. The table we would load into would
be created as follows:
EODA@ORA12CR1> create table lob_demo
2 ( owner varchar2(255),
3 time_stamp date,
4 filename varchar2(255),
5 data blob
6 )
7 /
Table created.
Using a simple
ls -l
on UNIX/Linux, and
dir /q /n
on Windows, and capturing that output, we can generate
our input file and load it using a control file such as this on UNIX/Linux:
LOAD DATA
INFILE *
REPLACE
INTO TABLE LOB_DEMO
( owner position(14:19),
time_stamp position(31:42) date "Mon DD HH24:MI",
filename position(44:100),
data LOBFILE(filename) TERMINATED BY EOF
)
BEGINDATA
-rwxr-xr-x 1 oracle dba 14889 Jul 22 22:01 demo1.log_xt
-rwxr-xr-x 1 oracle dba 123 Jul 22 20:07 demo2.ctl
-rwxr-xr-x 1 oracle dba 712 Jul 23 12:11 demo.bad
-rwxr-xr-x 1 oracle dba 8136 Mar 9 12:36 demo.control_files
-rwxr-xr-x 1 oracle dba 825 Jul 23 12:26 demo.ctl
-rwxr-xr-x 1 oracle dba 1681 Jul 23 12:26 demo.log
-rw-r----- 1 oracle dba 118 Jul 23 12:52 dl.sql
-rwxr-xr-x 1 oracle dba 127 Jul 23 12:05 lob_demo.sql
-rwxr-xr-x 1 oracle dba 171 Mar 10 13:53 p.bsh
-rwxr-xr-x 1 oracle dba 327 Mar 10 11:10 prime.bsh
-rwxr-xr-x 1 oracle dba 24 Mar 6 12:09 run_df.sh
