DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Threaded View

  1. #1
    Join Date
    Feb 2007

    saving gif pdf etc in oracle db

    Hi Friends,

    I got this excerpt from ASK TOM, about saving and retreiving objects
    to Oracle DB. Does this program be able to save movies too? or a
    mp3 music?
    Are there other codes/programs shorter or better than these
    I am using 10gXe DB, I believe his code is generated from lower
    Db versions like 8i coz he is mentioning svrmgrl
    and I think 10g has more advanced features

    Thanks a lot

    Saving and Retrieving .gif .pdf etc. etc

    Here is an example that loads a GIF into the datbase and displays it.
    You would of course change the mime type and the name of the procedure
    from .gif to .pdf. Then you will be able to use HTML like:

    Click Me

    to retrieve "document 55" for example.

    The following code just created a demo table and loads an image into it -- you already
    have the pdfs loaded so its not 100% relevant to your problem

    create table demo
    ( id int primary key,
    theBlob blob

    create or replace directory my_files as '/export/home/tkyte/public_html';

    l_blob blob;
    l_bfile bfile;
    insert into demo values ( 1, empty_blob() )
    returning theBlob into l_blob;

    l_bfile := bfilename( 'MY_FILES', 'aria.gif' );
    dbms_lob.fileopen( l_bfile );

    dbms_lob.loadfromfile( l_blob, l_bfile,
    dbms_lob.getlength( l_bfile ) );

    dbms_lob.fileclose( l_bfile );

    Now here is the package that can retrieve the pdf (or anything for that matter. Just
    keep adding procedures that are named after the file type like .doc, .pdf, .xls and so
    on. Some browsers really want the extension in the URL to be "correct")

    create or replace package image_get
    procedure gif( p_id in demo.id%type );

    create or replace package body image_get

    procedure gif( p_id in demo.id%type )
    l_lob blob;
    l_amt number default 30;
    l_off number default 1;
    l_raw raw(4096);
    select theBlob into l_lob
    from demo
    where id = p_id;

    -- make sure to change this for your type!
    owa_util.mime_header( 'image/gif' );

    dbms_lob.read( l_lob, l_amt, l_off, l_raw );

    -- it is vital to use htp.PRN to avoid
    -- spurious line feeds getting added to your
    -- document
    htp.prn( utl_raw.cast_to_varchar2( l_raw ) );
    l_off := l_off+l_amt;
    l_amt := 4096;
    end loop;
    when no_data_found then


    UTL_RAW might not be installed on your database. It is part of replication. If you
    do not have it installed, simply:

    o cd $ORACLE_HOME/rdbms/admin
    o find the two files with "raw" in their name (eg: ls *raw*)
    o using svrmgrl connect as INTERNAL OR SYS -- only these users, no one else can
    successfully install UTL_RAW
    o run the .sql and then the .plb file
    Last edited by jennifer2007; 04-24-2007 at 10:47 PM.

Posting Permissions

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

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.