Re: Loading BLOB / CLOB

A simple method could be to use a web server with a file upload capability. There are many JavaScript widgets on the net for doing this nicely, and JavaScript has direct methods for uploading built in. You could have the user upload the file and then click a "load" button that called a CGI or other script to make the appropriate stored procedure call to use the method Daniel Morgan described.

Perl has a very nice DBI/DBD Oracle interface that could be run in fast CGI mode using MOD_PERL on Apache for taking care of the load functionality..

If you don't want to code a CGI or other middleware, you could have a cron job that called a sql script to load the uploaded file and then moved the file somewhere else. This would require minimal coding other than setting up the web server and the cron script.

If you cannot use a web server, then you will need a client that can interface with stored procedures, which can then write the stream in chunks. The DBMS_LOB package has a WRITEAPPEND methods for writing chunks of data at the end of existing LOBs. So the steps for doing this should probably be something like the following:

1. Create the bob and load the first chunk in the table.
2. Read the next chunk use writeappend to add the chunk to the end of the new LOB.
3. Repeat 3 until done.

Couple of things:

1. You probably will not be able to pass the LOB locator back to the client - LOB support tends to be poor. You can create a global that will persist on the database side within a package with the LOB locator after writing the first chunk.
2. You will need a fairly robust client. If you are in the middle of streaming the lob, and the connection goes down, you could end up with an unusable binary in the database - a broken image, etc.
3. Whatever client software you use will need to be able to handle raw data and bind this data to a stored procedure parameter. Many Oracle interfaces don't handle extended data well, so you should do your homework before you commit too far down a given path.

Perl with TK can handle the work and provide a reasonable interface using ActiveState Perl on Windows, but so could a Java based client, C/C++, etc. I assume VB could as well, but it has been quite some time since I looked at the Oracle interface with VB. If you are loading

A local client will have to be able to read from a file. The web server with a separate cron process for loading the file would be the most robust since it would be asynchronous. If the file upload died, no harm no foul. If the write script failed, the file is still preserved for later use.

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Chris Weiss
mailto:chris_at_hpdbe.com
www.hpdbe.com
High Performance Database Engineering
Available for long and short term contracts


"Thomas Ruschival" <t.ruschival_at_vivid-md.de> wrote in message
news:20020507005356.29a51afd.t.ruschival_at_vivid-md.de...


> Hi,

>

> I don't know, didn't find the right information about "how to load LOB

> (BLOB/CLOB) into the Database".

> OK, I have binary Data and Character in Files > 500 MB. How can I load

this


> stream into a BLOB Field in the Database?

> - BTW I can't use SQLJ or any Java classes because there is no JServer

> option on this Oracle 8.1.7 Instance.

>

> Thanks alot

>

> Thomas