Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ratna.

Asked: January 09, 2017 - 7:16 pm UTC

Last updated: January 12, 2017 - 2:01 am UTC

Version: Oracle 12c

Viewed 1000+ times

You Asked

Is there a way to specify the lob file path?
I can not change my .dat files to include the path so I am looking for a way when I load to specify the lob file direcotry.

Lob file name was dynamically generated so I can't use CONSTANT either.


Example:

.dat file

name, MESSAGE.001.lob, addr
name2, MESSAGE.002.lob, addr

LOB files

/lobdata
MESAAGE.001.lob
MESSAGE.002.lob

.ctl file
load data
infile 'ENROLLMENT_MESSAGE.dat'
into table PQMINST1.AUTO_ENROLLMENT_MESSAGE
trailing nullcols
(
ID,
MESSAGE_CONTENT_ref FILLER ,
MESSAGE_CONTENT LOBFILE(MESSAGE_CONTENT_ref) TERMINATED BY EOF,
)

I need specify that the lob file are in the /lobdata dir?
Please let me know how to solve this problem.

and Connor said...

The challenge here is that you want to both *skip* some data (ie, use FILLER) as well as *use* that information in an expression.

Probably the quickest way would be parse the incoming file first, eg

<code>
bash$ cat demo.txt
name,MESSAGE.001.lob,addr
name2,MESSAGE.002.lob,addr

bash$ awk -F, '{print $1",/lobdata/"$2","$3}' demo.txt
name,/lobdata/MESSAGE.001.lob,addr
name2,/lobdata/MESSAGE.002.lob,addr
<code>

and then use that file instead.

Rating

  (3 ratings)

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

Comments

Current directory?

Christian COMMARMOND, January 10, 2017 - 1:39 pm UTC

Hi,

I thought that the current directory is where Oracle will try to read the files?

So
cd /lobdata

then run sqlldr from there.

And tell us if it solves the problem...

Regards,

Christian



Ratna Vemuri, January 10, 2017 - 3:21 pm UTC

Right, if I change the .dat with the path it works. But the problem is with volume. I have 150-200k rows in that table and I have around 25 tables where I need to do this.
Isn't there any way I can just change the control file?

Christian
Keeping everything in one dir works but that makes it hard for us to process. We have thousands of lob files and multiple tables we are targeting to load from DB2 to Oracle. So keeping everything in one dir makes it hard for us.


Connor McDonald
January 11, 2017 - 6:09 am UTC

You could approach it the other way - since you have 1000's of files I'm assuming you are *generating* the control file, ie the list of files to be loaded ?

If that is the case, then why not generate the control file with the fully qualified path ?

Ratna Vemuri, January 11, 2017 - 3:28 pm UTC

Ok but how?

I can specify the path for the .dat file but how do I specify the path for the lob file?

.ctl file
load data
infile 'ENROLLMENT_MESSAGE.dat'
into table PQMINST1.AUTO_ENROLLMENT_MESSAGE
trailing nullcols
(
ID,
MESSAGE_CONTENT_ref FILLER ,
MESSAGE_CONTENT LOBFILE(MESSAGE_CONTENT_ref) TERMINATED BY EOF,
)

Connor McDonald
January 12, 2017 - 2:01 am UTC

So I see two possible scenarios here

1) all your files are in 1 directory

In that case, just run sqlldr from that directory

2) all of your files are spread across many directories

In that case, you must have some means of identifying which file is in which directory no ?

That means you have a *listing* of them in some form that *includes* the directory, or if not, it would trivial to create one, eg "find . -ls" or "dir /s /b"

That list becomes the input into your control file, and since the directory name is included for each file, the LOBFILE clause works without issue
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 latest video and Chris's latest video 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