Skip to Main Content
  • Questions

  • SQL*Loader - how to load a LOB file using the file name (including its path) obtained using the sql script (select)

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rene.

Asked: July 25, 2016 - 12:42 pm UTC

Last updated: March 03, 2022 - 8:30 am UTC

Version: SQL*Loader 10.1.0.5.0

Viewed 10K+ times! This question is

You Asked

Please, advice, how to load (using the SQL Loader) a LOB file into a table column using the file name (including its path) that is obtained using the sql query script (select).
I tried to use the following construction of the .ctl file:
-----------------
LOAD DATA
INFILE *
APPEND
INTO TABLE GEUP_OM_RELEASES_LOAD
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
ID "GEUP_OM_XML_LOAD_SEQ.NEXTVAL",
CREATION_DATE SYSDATE,
file_name "(select fnd_profile.value('GEAC_APPS_MIGRATION_DIRECTORY') || /Releases.xml' from dual)",
XML_TEXT LOBFILE(file_name) TERMINATED BY EOF)
BEGINDATA
"0"
-----------------
The file name is correctly created and also loaded into the file_name table column. But the file itself is not loaded due to the following error:
SQL*Loader-462: error inserting LOB into column XML_TEXT, row 1, table "XXCUST"."GEUP_OM_RELEASES_LOAD"
SQL*Loader-646: lob set to EMPTY in column XML_TEXT, row 1, table "XXCUST"."GEUP_OM_RELEASES_LOAD"
no value set for dynamic file for column XML_TEXT in table "XXCUST"."GEUP_OM_RELEASES_LOAD" row 1
field for dynamic file name is FILE_NAME in table "XXCUST"."GEUP_OM_RELEASES_LOAD"

When using a constant instead of the sql query:
file_name CONSTANT 'arpogaa1/custom/msca/Releases.xml'
then the file is correctly loaded.

Is there any way how to create this file name dynamically?
Regards, Rene

and Chris said...

From the docs:

You can specify LOBFILEs either statically (the name of the file is specified in the control file) or dynamically (a FILLER field is used as the source of the filename).
http://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_loading.htm#i1006993

So to dynamically specify a lobfile, you need to use a filler field. Unfortunately:

SQL strings cannot be specified as part of a filler field specification, because no space is allocated for fillers in the bind array.

http://docs.oracle.com/database/121/SUTIL/GUID-EAB9BE96-6F29-4623-9BEF-323C2FE05CB7.htm#SUTIL1149

So you can't use a query to populate the values for the lob file name filler :(

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Capture file name

Narendra, March 01, 2022 - 6:10 pm UTC

Hello Chris/Connor,

I am sure I am missing something obvious but can't seem to figure it out.
How to capture and store file name, along with its contents when file name is dynamic?
Borrowing an example from MOS note 549856.1:

- XML documents to be loaded::

doc1.xml contains: <planet>venus</planet>
doc2.xml contains: <planet>mercury</planet>

- Table specifications:

create table planets
(pl_id number(4), pl_name varchar2(20), xml_doc SYS.XMLTYPE);

SQL*Loader control file called load_xml.ctl contains:

LOAD DATA
INFILE *
INTO TABLE planets
REPLACE
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(pl_id,
pl_name,
fname FILLER CHAR,
xml_doc LOBFILE(fname) TERMINATED BY EOF
)

BEGINDATA
1,Mercury,doc1.xml
2,Venus,doc2.xml

Run SQL*Loader using the controlfile:

sqlldr scott/tiger control=load_xml.ctl

Results seen in table planets:

PL_ID PL_NAME XML_DOC
---------- -------------------- ----------------------------------------
1 Mercury <planet>venus</planet>
2 Venus <planet>mercury</planet>


In the above example, how can I capture the file names (doc1.xml and doc2.xml) in order to populate them in a column of the table (say in pl_name in this example)

Thanks in advance
Connor McDonald
March 03, 2022 - 8:28 am UTC

I don't think we s have a native mechanism for that, but check out

https://asktom.oracle.com/pls/apex/asktom.search?tag=sqlloader-save-filename-into-table-column

https://asktom.oracle.com/pls/apex/asktom.search?tag=identifying-the-data-file


Re: Capture File Name

Narendra, March 02, 2022 - 9:17 am UTC

Thank you Connor for the pointers.
While I was playing with it, I (accidently) managed to achieve the objective using below control file

LOAD DATA
INFILE *
INTO TABLE planets
REPLACE
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(pl_id,
pl_name,
xml_doc LOBFILE(pl_name) TERMINATED BY EOF
)

BEGINDATA
1,doc1.xml
2,doc2.xml


The above appears to have populated data (in PLANETS table) with file name

PL_ID PL_NAME XML_DOC
1 doc1.xml (XMLTYPE)
2 doc2.xml (XMLTYPE)



Connor McDonald
March 03, 2022 - 8:30 am UTC

nice work.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here