Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4402
  • Last Modified:

Insert/Upload a file into an Oracle CLOB column

Hello,
I'd like to upload numerous files into a CLOB in Oracle 11g.  I have the table created with the column, I just need to be able to upload the files, hopefully using a stored procedure or Sql ldr.  I've searched for examples, but didn't quite find exactly what I need to complete the task.  Thank you so much for any help.
0
newtoperlpgm
Asked:
newtoperlpgm
  • 13
  • 13
1 Solution
 
slightwv (䄆 Netminder) Commented:
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?
0
 
newtoperlpgmAuthor Commented:
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
0
 
slightwv (䄆 Netminder) Commented:
>>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?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
newtoperlpgmAuthor Commented:
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.
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
newtoperlpgmAuthor Commented:
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.
0
 
slightwv (䄆 Netminder) Commented:
>> 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?  

You create a control file with an infile parameter.  The infile will have the samy layout as in the example.  One of the columns will be the full path and filename.

Just create a small test case and try it.

Create a table called bob: create table bob ( col1 clob);

Create a file: C:\bob.txt
add some test to bob.txt:  Hello world

Create a 'file list file': c:\files_to_load.txt
Contents: c:\bob.txt

This is untested but the control file should look something like:

infile 'C:\files_to_load.txt'
replace
into table bob
fields terminated by ','
(
  MY_LOB LOBFILE(col1) TERMINATED BY EOF
 )
0
 
newtoperlpgmAuthor Commented:
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.
0
 
slightwv (䄆 Netminder) Commented:
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?
0
 
slightwv (䄆 Netminder) Commented:
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

Open 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
)

Open in new window


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

Open in new window


You can create whatever test files you want that match the path/name used in bob.txt.
0
 
newtoperlpgmAuthor Commented:
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.
0
 
slightwv (䄆 Netminder) Commented:
>>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.
0
 
newtoperlpgmAuthor Commented:
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
0
 
slightwv (䄆 Netminder) Commented:
>>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.
0
 
newtoperlpgmAuthor Commented:
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.
0
 
slightwv (䄆 Netminder) Commented:
>>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.
0
 
newtoperlpgmAuthor Commented:
I still get a syntax error, expecting "," or ")", found FILLER.

Hmmm.
0
 
slightwv (䄆 Netminder) Commented:
I tested what I posted using Oracle 10.2.0.3.

Are you running the exact test case I posted?
0
 
newtoperlpgmAuthor Commented:
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.
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
newtoperlpgmAuthor Commented:
Oh yes, sorry about that.  I meant CLOB, not BLOB, oops.
0
 
newtoperlpgmAuthor Commented:
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.
0
 
newtoperlpgmAuthor Commented:
I've requested that this question be closed as follows:

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

for the following reason:

worked.
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
newtoperlpgmAuthor Commented:
Oops.  I did that inadvertently.  I meant to use ID: 38343529 as the solution.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 13
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now