Database Reference
In-Depth Information
So a control file to load this might look like this:
LOAD DATA
INFILE *
INTO TABLE image_load
REPLACE
FIELDS TERMINATED BY ','
( ID,
NAME,
file_name FILLER,
IMAGE
column object
(
SOURCE
column object
(
LOCALDATA LOBFILE (file_name) TERMINATED BY EOF
NULLIF file_name = 'NONE'
)
)
)
BEGINDATA
1,icons,icons.gif
I have introduced two new constructs here:
COLUMN OBJECT
: This tells SQLLDR that this is not a column name; rather, it is part of a column
name. It is not mapped to a field in the input file, but is used to build the correct object column
reference to be used during the load. In the preceding file, we have two column object tags,
one nested inside the other. Therefore, the column name that will be used is
IMAGE.SOURCE.
LOCALDATA
, as we need it to be. Note that we are not loading any of the other attributes of these
two object types (e.g.,
IMAGE.HEIGHT
,
IMAGE.CONTENTLENGTH
, and
IMAGE.SOURCE.SRCTYPE
).
We'll shortly see how to get those populated.
•
NULLIF FILE_NAME = 'NONE'
: This tells SQLLDR to load a
NULL
into the object column in the
event that the field
FILE_NAME
contains the word
NONE
in it.
•
Once you have loaded an Oracle Text type, you will typically need to postprocess the loaded data using PL/SQL to
have Oracle Text operate on it. For example, with the preceding data, you would probably want to run the following to
have the properties for the image set up correctly:
begin
for c in ( select * from image_load ) loop
c.image.setproperties;
end loop;
end;
/
SETPROPERTIES
is an object method provided by the
ORDSYS.ORDIMAGE
type, which processes the image itself and
updates the remaining attributes of the object with appropriate values.
