Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    44

    Smile Unanswered: How to use BLOB and CLOB datatype?

    Can anyone tell me how to use of BLOB and CLOB datatype

    Thanks in Advace
    Mahesh

  2. #2
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    depends what you want to do really, but assuming 9i take a look at http://download-west.oracle.com/docs...a96540/toc.htm

    we use blobs to store images and documents and a web server to insert and extract them. You can create an Oracle Directory that references a directory on the file system and then you can load them into the database. Have a look at this oracle note:
    --------------------------------------------------------------------------
    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.
    ------------------------------------------------------------------------

    To get the files back out into the file system you need to use a dll (assuming windows not Linux/Unix etc)

    hth
    Robert

Posting Permissions

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