Insert/Upload a file into an Oracle CLOB column

Based on your username, I assume perl?

Where is the file?

If it is on the database server, you can use dbms_lob.loadclob from file:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_lob.htm#i998978

using sql loader:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:627223659651

Where are the examples falling short?

The files are on in a location that can be accessed by the stored procedure.  I wanted to use sql ldr, but the example shows the .dat file with the filenames stored in it.  I want to use a ctl file to point to my files and upload them.  That is where I am not understanding what to do.
Thanks.
from example...load data
infile *
replace
into table test_lob
fields terminated by ','
(
 lob_id char(1),
 lob_file FILLER char,
  MY_LOB LOBFILE(lob_file) TERMINATED BY EOF
 )
begindata
1,h:\roy\doc\acct.txt
2,h:\roy\doc\acct.txt

>>the example shows the .dat file with the filenames stored in it.

That is how sql loader does it.  It takes the path/filename of the file to load.

What part of the example are you not understanding?

I want to upload the files and store the actual files in the blob column.  Does that mean in my control file I have to list the name of every file in each row of the file?  I have thousands.  Normally I am used to the INFILE containing the names of the files that sql ldr uses to upload data.  Thank you.

infile will work.  begindata is just a shortcut normally used for quick examples.

As long as the file has the path to the files you want to load, it will work.

So if I am understanding this correctly, in order to use sql ldr to upload a file into a blob column in my Oracle table, I just need to create the ctl file with a reference to the location of the file and as long as it can access the  file it will upload it?  Because I can easily create a file with the 20+ thousand filenames.  
Thank you.

I created my ctl file and when I run sql ldr I get a syntax error....
Syntax error at line 7.
Expecting "," or ")", found "FILLER"
FNAME   FILLER CHAR,

Below is my ctl file....I don't see anything wrong syntactically but that doesn't mean anything.....

LOAD DATA
INFILE 'myfilename'
APPEND INTO TABLE MYTABLENAME
FIELDS TERMINATED BY ','
(
ID,
FNAME  FILLER CHAR,
CLOB_CONTENT LOBFILE(FILENAME) TERMINATED BY EOF,
MIME_TYPE   CHAR,
LAST_UPDATED   SYSDATE,
CHARACTER_SET  CHAR
)

Thank you.

Try setting the data type for ID:
ID CHAR,

or whatever it is.

If that doesn't work, I'll create a working test case on my side.


What does MYTABLENAME look like?

Can you post the contents of myfilename?

Just noticed this today:  You have a typo in your control file.  The FNAME filler needs to match the name used in LOBFILE on the next line.

Here is a working test case based on your controlfile.

bob.txt

1,c:\q1.txt,a,b
2,c:\q2.txt,c,d
3,c:\q3.txt,e,f

Select allOpen in new window

bob.ctl

LOAD DATA
INFILE 'bob.txt'
APPEND INTO TABLE bob
FIELDS TERMINATED BY ','
(
ID,
FNAME  FILLER CHAR,
CLOB_CONTENT LOBFILE(FNAME) TERMINATED BY EOF,
MIME_TYPE   CHAR,
LAST_UPDATED   SYSDATE,
CHARACTER_SET  CHAR
)

Select allOpen in new window

table:

create table bob ( id number, clob_content clob, mime_type char(1), last_updated date, character_set char(1));

Select allOpen in new window

You can create whatever test files you want that match the path/name used in bob.txt.

Is the Oracle database column storing the actual file or just a reference to the location of the file?  I will try your suggestion and let you know.  Thank you.

>>Is the Oracle database column storing the actual file or just a reference to the location of the file?

It is storing the contents of the file.

I am still running into a problem, I think....

I was able to get it to run and load contents into the table, however, below is how I got it to run

LOAD DATE
INFILE 'rpt_fnames'
APPEND INTO TABLE "X_FILES"
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
ID SEQUENCE(MAX,1)
FNAME,
CLOB_CONTENT,
MIME_TYPE CHAR,
LOAD_DATE SYSDATE,
CHARACTER_SET CHAR
)

I had to remove the FILLER CHAR for FNAME and the LOBFILE(FNAME) for CLOB_CONTENT in order for the syntax errors to go away.
My table is populated, however, my CLOB_CONTENT column has the following value
\\abcdir\dir1\dir2\2983745995.txt
rather than the contents of the CLOB file.

My rpt_fnames.dat file looks like the following:
2983756995.txt, \\abcdir\dir1\dir2\2983745995.txt
4137472340.txt, \\abcdir\dir1\dir2\4137472340.txt

>>the FILLER CHAR for FNAME and the LOBFILE(FNAME) for CLOB_CONTENT

ummm... that's what makes it work.  Otherwise you are loading the string itself not the actual contents.

I'm also not sure if you can use UNC syntax.

Please get my test case using a local drive working.  Then post your error and we'll move on from there.

What is UNC syntax?

In your example you have
LOAD DATA
INFILE 'bob.txt'
APPEND INTO TABLE bob
FIELDS TERMINATED BY ','
(
ID,
FNAME  FILLER CHAR,
CLOB_CONTENT LOBFILE(FNAME) TERMINATED BY EOF,
MIME_TYPE   CHAR,
LAST_UPDATED   SYSDATE,
CHARACTER_SET  CHAR
)

but in your create statement for table named bob you don't have FNAME as a column, is that correct?
Thanks.

>>What is UNC syntax?

This:  \\abcdir\dir1\dir2\2983745995.txt

Universal Naming Convention.
http://en.wikipedia.org/wiki/Path_%28computing%29#Uniform_Naming_Convention

>>you don't have FNAME as a column, is that correct?

Correct.  I'm not storing the file name, just the contents.

I still get a syntax error, expecting "," or ")", found FILLER.

Hmmm.

I tested what I posted using Oracle 10.2.0.3.

Are you running the exact test case I posted?

Yes, I am running the exact test case.  I am using Toad 9.5.2, but I am about to try using sql ldr from the command line.  I can use Toad to upload data from a file into an Oracle table, so I expected to be able to use it to upload a blob without problems.
Thanks.

I'm not a toad user so I'm not sure what it can or can not do.

Try sqlldr from from a command prompt.

I also want to clarify something:  The original question mentioned CLOB.  A couple times now you have mentioned BLOB.  These are two very different data types.

Oh yes, sorry about that.  I meant CLOB, not BLOB, oops.

Hello, i just learned that it is not supported by Toad....that is why it isnt' working.  I'll get it working via command line.  Thanks for all your help.

I've requested that this question be closed as follows:

Accepted answer: 0 points for newtoperlpgm's comment #a38343392

for the following reason:

worked.

I hope this was a mis-click and you didn't mean to close this this way:
--------------------
newtoperlpgm requested that this question be closed by accepting newtoperlpgm's comment #a38343392 (0 points) as the solution for the following reason:

Just in case it wasn't, I'll have to object.

Oops.  I did that inadvertently.  I meant to use ID: 38343529 as the solution.