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

  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 11:47 PM.

  2. #2
    Join Date
    Feb 2007
    Posts
    212
    Hi Friends,

    Just a follow-up related to this post.

    We have an old application developed in VB and the database in MSSQL
    server. Our developers (not including me) developed our in house applications using VB and MSSQL (i dont know how to use this VB, coz
    I am only a diehard fan of Oracle). Our programmers were old in this
    company while I am only new. I was hired because they wanted to shift
    to oracle db.

    Now we migrated our MSSQL database to Oracle DB but the front end is
    still VB. The program codes are a lot similar accessing between MSSQL
    and Oracle DBs. So not so much revisions.

    My problem is with regard to the saving of images/jpeg/gif which was done
    before in VB/MSSQL. The programmers adopted the datatype longchar in
    MSSQL, and they used the counterpart in Oracle which is CLOB, because
    the programmers have so little knowledge about Oracle and its saving
    of images. But still they were able to save and retrieve the images using
    CLOB since their codes were based/patterned on what they used to do
    with MSSQL datatype.
    What I want to tell them is to use BLOB because I know it is the right
    datatype of storing images data. But their codes wont work on BLOBs,I dont
    know what command/code to give them to pass in VB to be able to
    save and retrieve images.

    If i follow or install the program demonstrated by TOM, can the VB program
    used the oracle commad like this :

    >>>>> saving image file <<<<<
    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;
    /

    How do I pass this in VB please?

    Thanks
    Last edited by jennifer2007; 04-26-2007 at 12:33 AM.

  3. #3
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Hi Jen,
    I used to program with C/C++ but not in VB. But you can probably use this codes from docs actually.

    The following example shows usage of OraBlob and OraBfile.

    Dim OraDyn as OraDynaset, OraSound1 as OraBLOB, OraSoundClone as OraBlob,
    OraMyBfile as OraBFile

    OraConnection.BeginTrans
    set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab order by clip_
    id", ORADYN_DEFAULT)
    set OraSound1 = OraDyn.Fields("Sound").value
    set OraSoundClone = OraSound1

    OraParameters.Add "id", 1,ORAPARAM_INPUT
    OraParameters.Add "mybfile", Empty,ORAPARAM_OUTPUT
    OraParameters("mybfile").ServerType = ORATYPE_BFILE

    OraDatabase.ExecuteSQL ("begin GetBFile(:id, :mybfile ") end")

    Set OraMyBFile = OraParameters("mybfile").value
    'Go to Next row
    OraDyn.MoveNext

    OraDyn.Edit
    'Lets update OraSound1 data with that from the BFILE
    OraSound1.CopyFromBFile OraMyBFile
    OraDyn.Update

    OraDyn.MoveNext
    'Go to Next row
    OraDyn.Edit
    'Lets update OraSound1 by appending with LOB data from 1st row represenetd by
    'OraSoundClone
    OraSound1.Append OraSoundClone
    OraDyn.Update

    OraConnection.CommitTrans


    In the preceding example:

    OraSound1 -- represents the locator for the current row in the dynaset OraSoundClone -- represents the locator for the 1st row.

    A change in the current row (say a OraDyn.MoveNext) means the following:

    OraSound1 -- will represent the locator for the 2nd row

    OraSoundClone -- will represent the locator in the 1st row. OraSoundClone only refers the locator for the 1st row irrespective of any OraDyn row navigation).

    OraMyBFile -- refers to the locator obtained from an PL/SQL "OUT" parameter as a result of executing a PL/SQL procedure, either by doing an OraDatabase.ExecuteSQL.
    ---------------

  4. #4
    Join Date
    Feb 2007
    Posts
    212
    Thanks dear ....do u mean I don't need to install something like the program
    demo of TOM? and use only the codes u provided?

  5. #5
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    if its a package then desc it like SQL>DESC DBMS_JOB;
    in your case DESC UTL_RAW.

    if it exist then you don't need to install.
    ---------------

  6. #6
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    I got it now :-)
    You are confused about UTL_RAW. Since it is located at /rdbms/admin that means it has nothing to do with TOM. This is standard database package and should be installed by catproc.sql
    Just in case if it is not, you get the recommendation to install it by hand, finding the scripts at /rdbms/admin and running them

    Support to Ray, try describe utl_raw before doing anything

    Regards

  7. #7
    Join Date
    Feb 2007
    Posts
    212
    thank you all

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