sqldlr LOBFILE specification

From: <AdamDonahue_at_maximus.com>
Date: Wed, 24 Dec 2003 10:09:25 -0800
Message-ID: <F001.005DB027.20031224100925@fatcity.com>

Folks,

Is there a straightforward way to dynamically create a LOBFILE specification via an Oracle expression?

For example, assume the following table (note there is /not/ a field for the file name itself):

create table clob_data (

        id number


,       data clob

);

And the following import file, clob_data.dat:

1,1.pdf
2,2.pdf

and so forth.

A typical control file might read:

LOAD DATA
INFILE clob_data.dat
TRUNCATE
INTO TABLE clob_data
...
(

        id


,       file_name filler

,       data lobfile( file_name ) terminated by eof

)

Let's assume, though, that we cannot execute the script from the blob file directory, and, also, that the filenames in the import file cannot be changed.

Thus, what we need is to dynamically form the full path to the file, and then use that as the input to the lobfile call in the control file.

Assuming a path, '/data/clobs' we'd want something like

(

        id


,       file_name filler

,       data lobfile( [[psuedo] '/data/clobs/' || file_name )

)

Unfortunately, sqlldr seems to support only imported filter columns (no constants or expressions), and lobfile only constants, not expressions. So there's no seeming way to do something like,

(

        id


,       file_name

,       data lobfile( expression "'/data/clobs/' || :file_name"  )

)

which of course doesn't work.

Any workarounds for this, involving /only/ modification of the control file, and not rewriting of the import datafile?

Thanks,

Adam

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: AdamDonahue_at_maximus.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Dec 24 2003 - 12:09:25 CST