saving gif pdf etc in oracle db
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: saving gif pdf etc in oracle db

Threaded View

  1. #1
    Join Date
    Feb 2007
    Posts
    212

    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';

    declare
    l_blob blob;
    l_bfile bfile;
    begin
    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 );
    end;
    /

    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
    as
    procedure gif( p_id in demo.id%type );
    end;
    /

    create or replace package body image_get
    as

    procedure gif( p_id in demo.id%type )
    is
    l_lob blob;
    l_amt number default 30;
    l_off number default 1;
    l_raw raw(4096);
    begin
    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' );

    begin
    loop
    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;
    exception
    when no_data_found then
    NULL;
    end;
    end;

    end;
    /

    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