Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    1

    Question Unanswered: help me!!!how to insert *.jpg into oracle?

    how to insert *.jpg into oracle?

  2. #2
    Join Date
    Apr 2002
    Posts
    56
    That would be done using BLOB's. (Binary Large Object).
    The package to do things with them is DBMS_LOB.

    I don't have much experience with them myself, but it's been done many times, so you should be able to find helpful documentation with Google.

  3. #3
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    yes, you can create a directory in oracle and use dbms_lob to upload files, see the file attached

    Code:
    Doc ID:  Note:73825.1 
    Subject:  How to load images into BLOB columns using DBMS_LOB and displaying them via OAS 
    Type:  BULLETIN 
    Status:  PUBLISHED 
    Creation Date:  24-AUG-1999 
    Last Revision Date:  26-JUN-2001 
     
    
    PURPOSE
     
    This bulletin is a simple step-by-step introduction to loading images into
    the Oracle RDBMS from a local file system using DBMS_LOB and then creating a 
    procedure to access them via OAS.
     
    
    SCOPE & APPLICATION
     
    DBA's and Web Developer's who would like a step-by-step guide on how to store
    images in the Oracle RDBMS and then display them via OAS.  It covers the use
    of the DBMS_LOB package and the bfilename function.
     
    
    RELATED DOCUMENTS
     
    Note:68016.1
    
    
    Loading an image into a BLOB column and displaying it via OAS
    -------------------------------------------------------------
    
     The steps are as follows:
    
    
     Step 1.
     -------
    
     Create a table to store the blobs:
    
     create table blobs
     ( id    varchar2(255),
       blob_col blob
     );
    
    
     Step 2.
     -------
    
     Create a logical directory in the database to the physical file system:
    
     create or replace directory MY_FILES as 'c:\images';
    
    
     Step 3.
     -------
    
     Create a procedure to load the blobs from the file system using the logical 
     directory.  The gif "aria.gif" must exist in c:\images.
    
     create or replace procedure insert_img as
         f_lob   bfile;
         b_lob   blob;
     begin
         insert into blobs values ( 'MyGif', empty_blob() )
         return blob_col into b_lob;
    
         f_lob := bfilename( 'MY_FILES', 'aria.gif' );
         dbms_lob.fileopen(f_lob, dbms_lob.file_readonly);
         dbms_lob.loadfromfile( b_lob, f_lob, dbms_lob.getlength(f_lob) );
         dbms_lob.fileclose(f_lob);
         commit;
     end;
    /
    
    
     Step 4.
     -------
    
     Create a procedure that is called via Oracle Application Server to display the 
     image.
    
    create or replace procedure get_img as
    	vblob blob;
    	buffer raw(32000);
    	buffer_size integer := 32000;
    	offset integer := 1;
    	length number;
    begin
    	owa_util.mime_header('image/gif');
    	select blob_col into vblob from blobs where id = 'MyGif';
    	length := dbms_lob.getlength(vblob);
    	while offset < length loop
    		dbms_lob.read(vblob, buffer_size, offset, buffer);
    		htp.prn(utl_raw.cast_to_varchar2(buffer));
    		offset := offset + buffer_size;
    	end loop;
    exception
    	when others then
    		htp.p(sqlerrm);
    end; 
    /
    
    
     Step 5.
     -------
    
     Use the PL/SQL cartridge to call the get_img procedure
    
     OR
    
     Create that procedure as a function and invoke it within your PL/SQL code to 
     place the images appropriately on your HTML page via the PL/SQL toolkit.
    
    
    
    from a html form
    
    1. Create an HTML form where the image field will be <input type="file">. You also 
    need the file MIME type . 
    2. Create a procedure receiving the form parameters. The file field will be a Varchar2 
    parameter, because you receive the image path not the image itself. 
    3. Insert the image file into table using "Create directory NAME as IMAGE_PATH" and 
    then use "Insert into TABLE (consecutive, BLOB_OBJECT, MIME_OBJECT) values (sequence.nextval, 
    EMPTY_BLOB(), 'GIF' or 'JPEG') returning BLOB_OBJECT, consecutive into variable_blob, 
    variable_consecutive. 
    4. Load the file into table using: 
    dbms_lob.loadfromfile(variable_blob, variable_file_name, dbms_lob.getlength(variable_file_name)); 
    dbms_lob.fileclose(variable_file_name); 
    commit.
    There are 10 types of people in the world, those that know Binary and those that don't.

  4. #4
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    You can also use BFILE datatype, depending on your Oracle vesion.

    Once you have table (imagestore) with bfile to store the images, create a directory and use it to store the image files

    create or replace directory imagedir AS 'c:\images\';


    insert into imagestore (id, imagename, imagefile) values (1010, 'john picture', bfilename ('IMAGEDIR', 'john.jpeg'));


    HTH,

    clio_usa - OCP 8/8i/9i

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •