store Image file in oracle

I like to put some image file's (*.jpg or *.gif) into a Oracle Table. I can I do that (with sql or other tool) and what kind of field type should I use. i am using oracle 9.2 on win 2003

stuartwilAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pratikroyCommented:
Use CLOB or BLOB types
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
adixitCommented:
you can use  BLOB or CLOB.

or

Just create a Column in table with LONG RAW.

YOu asked any tool. Yes you can use Developer

read_image_file() command is there
and
write_image_file() command wrtie image file from db to disk.

Regards,
0
schwertnerCommented:
This is a solution 9Oracle environment):

SQL> create directory MYDIR as 'C:\MYIMAGE';

Directory created.

SQL> create table special(a number, b varchar2(20), c blob);

Table created.

SQL>
SQL> insert into special values(1,'abcd',empty_blob());

1 row created.

SQL>
SQL> commit;

Commit complete.


create or replace procedure load(id number)
is
dest_lob blob;
src_lob bfile:=bfilename('MYDIR','img.bmp');
amt integer:=sys.dbms_lob.getlength(src_lob);
begin
select c into dest_lob from special
where a=id for update;
dbms_lob.fileopen(src_lob);
dbms_lob.loadfromfile(dest_lob,src_lob,amt);
dbms_lob.fileclose(Src_lob);
commit;
end;
/

SQL> @ blob_insert

Procedure created.

SQL> exec load(1);

PL/SQL procedure successfully completed.

SQL> select a from special;

A




The LONG datatypes are accessible via subprograms from the DBMS_LOB package.
The DBMS_LOB package provides subprograms to operate on BLOBs, CLOBs,NCLOBs, BFILEs, and temporary LOBs.



You can use DBMS_LOB to access and manipulation specific parts of a LOB or complete LOBs.

For example, consider a READ operation past the end of the BFILE value, which generates a NO_DATA_FOUND



exception:

DECLARE
fil BFILE;
pos INTEGER;
amt BINARY_INTEGER;
buf RAW(40);
BEGIN
SELECT f_lob INTO fil FROM lob_table WHERE key_value = 21;
dbms_lob.open(fil, dbms_lob.lob_readonly);
amt := 40; pos := 1 + dbms_lob.getlength(fil); buf := '';
dbms_lob.read(fil, amt, pos, buf);
dbms_output.put_line('Read F1 past EOF: '||
utl_raw.cast_to_varchar2(buf));
dbms_lob.close(fil);
exception
WHEN no_data_found
THEN
BEGIN
dbms_output.put_line('End of File reached. Closing file');
dbms_lob.fileclose(fil);
-- or dbms_lob.filecloseall if appropriate
END;
END;
/
Statement processed.
End of File reached. Closing file



PURPOSE:
========
How to create LOB objects and differences compared with LONG RAW behavior.
Working with LOB objects in Forms 6.0.


SCOPE & APPLICATION:
====================
This article explains the basic use and advantages of the Oracle 8 LOB
column type recognized in Developer 6.0 and above.


RELATED DOCUMENTS:
==================
Note:66046.1, Note:1038856.6, Bug:804091, BUG:831105


Oracle Developer 6 To Oracle 8 NEW DATA TYPES
=============================================
The new table column TYPE's (constructs):

BLOB - binary data
CLOB - single byte character data, fixed width (8.1 fixed or variable width).
NCLOB - multi-byte character, fixed width (8.1 fixed or variable width)
BFILE - binary file stored to O/S.

Advantages of new column TYPE

1) Differences (Note:66046.1 Hints:  Oracle8 - New Constructs)
2) Multiple LOB's and BFILE columns (one LONG RAW column per table).
3) LOB's support object types.
4) Replication can be used with LOB's.
5) LOB's stored in a way that optimizes space and efficient access and participate in transaction model

of the server
6) BFILE's (external LOB) stored in O.S. files outside of database, so do not participate in transactions
7) LOB maximum size is 4GB, a LONG RAW maximum is 2GB.
 
Example:  
Create the table.
SQL*Plus simple create statement:
 
   Create table mylobdata  
  (refno number,
   myblob1 BLOB,
   myclob1 CLOB,
  mynclob1 NCLOB,
  mybfile1 BFILE);

Manipulate LOB's in SQLPLUS - NO !
1) It is not possible to query back LOB data.
Lets say you need to test/manipulate BLOB without using forms. PL/SQL provides package
called DBMS_LOB for this (see Note:61737.1)

2) Cannot alter type LONG to LOB and visa versa (Note:1038856.6), so do this :-
ALTER TABLE table_name ADD (new_column_name new_datatype);
UPDATE table_name SET new_column_name = old_column_name;

BLOB - IMAGES
=============
BLOB properties :
- Item Type -> Image
- Image format -> GIF, TIF , BMP...etc. Determines NOT incoming image type but storage format in database

!
- Load in from file usinge read_image_file() used as normal to process the image on the file system.

GOTCHAS !
=========
Create all table BLOB columns as outset otherwise you may hit bug:804091. This bug meant
that if you create table with one BLOB column then add another BLOB column at a later date, forms
will save images only to first column.

BLOB - SOUND
============
BLOB properties :
- Item type -> Sound
- sound format -> eg. WAVE
- Load (wav) file from file
- read_sound_file() as usual to process the wav file.

BLOB - OLE
==========
BLOB properties :
- Item Type -> OLE container
- Insert object ...etc. Just the same as with LONG RAW columns.

CLOB - text
===========
CLOB Properties :
- Item Type -> Text Item
Problems :
- Query on CLOB item criteria does not work in form

NCLOB - text
============
NCLOB properties :
Item Type -> Text Item
Problems :
- Cannot insert or query data in form, BUG:831105.
NB: To insert from SQLPLUS :-
INSERT INTO <CLOBTABLE> VALUES
(N'mydata');

BFILE
=====
- The files are stored on the server as BFILE  (binary file) column type.
- The directory alias and file name is held in the BFILE column.
Forms 6 can display the image (say) directly, using the directory alias and filename held in  the BFILE

column.
With Forms 5 you would use dbms_lob() to identify the file and READ_IMAGE_FILE() to display it (Note:66312.1).
Problems:
- You can't load BFILE data directly using Forms 6. So, in SQL Plus do the following :
GRANT CREATE ANY DIRECTORY TO SCOTT;
CREATE OR REPLACE DIRECTORY MYDIR AS 'D:\TEMP2';
CREATE TABLE MY_BFILETABLE
   (REFNO NUMBER, PHOTO BFILE);
INSERT INTO MY_BFILETABLE VALUES (1,BFILENAME('MYDIR','A_PHOTO.BMP'));






How to read image (in rcle environment):

CREATE OR REPLACE PACKAGE BODY myblob AS

FUNCTION get_blob_len
       RETURN NUMBER IS
BEGIN
      RETURN dbms_lob.getlength(my_blob);
END;

PROCEDURE read_blob
              (
              amount          IN OUT NUMBER,
               offset          IN NUMBER,
              buf             IN OUT RAW
              ) IS
BEGIN
      dbms_lob.read(my_blob,amount,offset,buf);
END;

FUNCTION load
              (
              filename IN VARCHAR2
              )
               RETURN INTEGER AS
lobd       BLOB;
fils       BFILE := BFILENAME('MYBLOBS', filename);
amt        INTEGER;
id         INTEGER;
BEGIN
      select myblobs_s.nextval
           into id
           from dual;
      dbms_lob.fileopen(fils, dbms_lob.file_readonly);
      amt:=dbms_lob.getlength(fils);
      insert into myblobs values (id, empty_blob())
               returning theblob into lobd;
      dbms_lob.loadfromfile(lobd, fils, amt);
      commit;
      dbms_lob.fileclose(fils);
     
       return(id);

END;


FUNCTION find
              (
              iFind   IN INTEGER
              )
              RETURN INTEGER is
BEGIN
      select theblob
         into my_blob
         from myblobs
       where id = iFind;
      return (1);
EXCEPTION
      when no_data_found then
              return (-1);
END;

END;
/


Run this as a user who has privelige to create a directory, and access to dbms_lob.
You will be prompted to provide a value for 'blob_dir'.  This is the physical
directory on the server that you will load your sample image files from.

Generally speaking you have to use the DBMS_LOB package:
This package provides general purpose routines for operations
   on Oracle Large OBject (LOBs) datatypes - BLOB, CLOB (read-write)
   and BFILEs (read-only).
   
   Oracle 8.0 SQL supports the definition, creation, deletion, and
   complete updates of LOBs. The main bulk of the LOB operations
   are provided by this package.

----------------------
RULES AND LIMITATIONS
----------------------
   The following rules apply in the specification of functions and
    procedures in this package.

   LENGTH and OFFSET parameters for routines operating on BLOBs and
   BFILEs are to be specified in terms of bytes.
   LENGTH and OFFSET parameters for routines operating on CLOBs
   are to be specified in terms of characters.
   
   A function/procedure will raise an INVALID_ARGVAL exception if the
   the following restrictions are not followed in specifying values
   for parameters (unless otherwise specified):

   1. Only positive, absolute OFFSETs from the beginning of LOB data
      are allowed. Negative offsets from the tail of the LOB are not
      allowed.
   2. Only positive, non-zero values are allowed for the parameters
      that represent size and positional quantities such as AMOUNT,
      OFFSET, NEWLEN, NTH etc.
   3. The value of OFFSET, AMOUNT, NEWLEN, NTH must not exceed the
      value lobmaxsize (which is (4GB-1) in Oracle 8.0) in any DBMS_LOB
      procedure or function.
   4. For CLOBs consisting of fixed-width multi-byte characters, the
      maximum value for these parameters must not exceed
            (lobmaxsize/character_width_in_bytes) characters
      For example, if the CLOB consists of 2-byte characters such as
      JA16SJISFIXED, then the maximum amount value should not exceed
            4294967295/2 = 2147483647 characters

   PL/SQL language specifications stipulate an upper limit of 32767
   bytes (not characters) for RAW and VARCHAR2 parameters used in
   DBMS_LOB routines.
   
   If the value of AMOUNT+OFFSET exceeds 4GB (i.e. lobmaxsize+1) for
   BLOBs and BFILEs, and (lobmaxsize/character_width_in_bytes)+1 for
   CLOBs in calls to update routines - i.e. APPEND, COPY, TRIM, and
   WRITE routines, access exceptions will be raised. Under these input
   conditions, read routines such as READ, COMPARE, INSTR, SUBSTR, will
   read till End of Lob/File is reached.
   For example, for a READ operation on a BLOB or BFILE, if the user
   specifies offset value of 3GB, and an amount value of 2 GB, READ
   will read only ((4GB-1) - 3GB) bytes.

   Functions with NULL or invalid input values for parameters will
   return a NULL. Procedures with NULL values for destination LOB
    parameters will raise exceptions.  

   Operations involving patterns as parameters, such as COMPARE, INSTR,
   and SUBSTR do not support regular expressions or special matching
    characters (such as % in the LIKE operator in SQL) in the PATTERN
    parameter or substrings.

   The End Of LOB condition is indicated by the READ procedure using
   a NO_DATA_FOUND exception. This exception is raised only upon an
   attempt by the user to read beyond the end of the LOB/FILE. The
   READ buffer for the last read will contain 0 bytes.
     
   For consistent LOB updates, the user is responsible for locking
   the row containing the destination LOB before making a call to
   any of the procedures (mutators) that modify LOB data.
   
   For BFILEs, the routines COMPARE, INSTR, READ, SUBSTR, will raise
   exceptions if the file is not already opened using FILEOPEN.

---------
SECURITY
---------

   Privileges are associated with the the caller of the procedures/
   functions in this package as follows:
   If the caller is an anonymous PL/SQL block, the procedures/functions
   are run with the privilege of the current user.
    If the caller is a stored procedure, the procedures/functions are run
   using the privileges of the owner of the stored procedure.

-------------------------
PROCEDURES AND FUNCTIONS
-------------------------
   Procedures and functions are listed here but do not have separate
   notes as there is plenty of existing documentation on the use of
   the PL/SQL DBMS_LOB routines.

PROCEDURE append(dest_lob IN OUT NOCOPY BLOB, src_lob  IN BLOB);
PROCEDURE close(lob_loc IN OUT NOCOPY BLOB);
FUNCTION  compare(lob_1    IN CLOB CHARACTER SET ANY_CS, ... );
PROCEDURE copy(dest_lob    IN OUT NOCOPY BLOB, ... );
PROCEDURE createtemporary(lob_loc IN OUT NOCOPY  BLOB, ... );
PROCEDURE erase(lob_loc IN OUT NOCOPY  BLOB, ... );
PROCEDURE fileclose(file_loc IN OUT NOCOPY  BFILE);
PROCEDURE filecloseall;
FUNCTION  fileexists(file_loc IN BFILE)
PROCEDURE filegetname(file_loc  IN  BFILE, ... );
FUNCTION  fileisopen(file_loc IN BFILE)
PROCEDURE fileopen(file_loc  IN OUT NOCOPY  BFILE, ... );
PROCEDURE freetemporary(lob_loc IN OUT NOCOPY  BLOB);
FUNCTION  getchunksize(lob_loc IN BLOB) /* Oracle8i only */
FUNCTION  getlength(lob_loc IN BLOB)
FUNCTION  istemporary(lob_loc IN BLOB)
FUNCTION  isopen(lob_loc in blob)
PROCEDURE loadfromfile(dest_lob    IN OUT NOCOPY  BLOB, ... );
PROCEDURE open(lob_loc   IN OUT NOCOPY BLOB, ... );
FUNCTION  instr(lob_loc IN BLOB, ... );
PROCEDURE read(lob_loc IN            BLOB, ... );
FUNCTION  substr(lob_loc IN BLOB, ... );
PROCEDURE trim(lob_loc IN OUT NOCOPY  BLOB, ... );
PROCEDURE write(lob_loc IN OUT NOCOPY  BLOB, ... );
PROCEDURE writeappend(lob_loc IN OUT NOCOPY  BLOB, ... );
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.