Re: Using SQL to load images into Oracle

From: Bastiaan Schaap <bschaap_at_desyde.nl>
Date: Mon, 23 Apr 2001 08:31:02 +0200
Message-ID: <_UPE6.2$dF4.417@psinet-eu-nl>

Hi Sean,

There are a couple of ways to insert an image into a table. If you have to insert it once as base information, you could do this with a third party PLSQL editor. We are used to working with PLSQL Developer from allround automations, but there are probably other tools that can do it too (toad, sql navigator?).

Another option is to create an upload servlet (java), which presents a user with a simple HTML form, and uploads any binary file format into a BLOB column. These are very simple servlets to write.

You can also do this by using the dbms_lob package (as mentioned by Sybrand). The restriction (if you want to call it that) is that file must be available on the database box itself. This means that if you want to insert it from a client machine, you would first have to copy it to the database machine, or create a share that doesn't require a password (this is *very* important, since oracle cannot provide passwords when accessing a shared drive) on the client machine. A simple piece of code would then suffice to read the data into the column.

First create a DIRECTORY object that points to the dir you want to get the file out of (in this example called document_dir). Then create a procedure something like:

create or replace procedure read_file
is

   src_file   BFILE := bfilename('DOCUMENT_DIR','image.gif');
   dst_file   BLOB;
   lgh_file   integer;

begin

src_file := bfilename('DOCUMENT_DIR','image.gif');

lock record select bin_data into dst_file from db_image for update;
open the file dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
determine length lgh_file := dbms_lob.getlength(src_file);
read file dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
now update the blob field update db_image set bin_data = dst_file;
save changes commit;
close file dbms_lob.fileclose(src_file);

EXCEPTION
WHEN ACCESS_ERROR THEN
dbms_output.put_line('Access Error');

WHEN INVALID_ARGVAL THEN
dbms_output.put_line('Invalid Argval');

WHEN INVALID_DIRECTORY THEN
dbms_output.put_line('Invalid Directory');

WHEN NO_DATA_FOUND THEN
dbms_output.put_line('No Data Found');

WHEN NOEXIST_DIRECORY THEN
dbms_output.put_line('Noexist Directory');

WHEN NOPRIV_DIRECORY THEN
dbms_output.put_line('Nopriv directory');

WHEN OPEN_TOOMANY THEN
dbms_output.put_line('Open Toomany');

WHEN OPERATION_FAILED THEN
dbms_output.put_line('Operation Failed');

WHEN UNOPENED_FILE THEN
dbms_output.put_line('Unopened File');

WHEN OTHERS THEN
do other things.....

END; Programming in Oracle is too easy, isn't it?

HTH, Bastiaan Schaap

Never underestimate the power of stupid people in large groups. Received on Mon Apr 23 2001 - 01:31:02 CDT