Re: BLOB's and Webserver

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 29 May 1998 01:23:44 GMT
Message-ID: <35700d9b.4127715@192.86.155.100>

A copy of this was sent to lofftjm_at_itec.suny.edu (if that email address didn't require changing) On Thu, 28 May 1998 20:22:49 GMT, you wrote:

>I have a BLOB stored in a table. The BLOB is actually a self-extracting
>zip file. (filename.exe) What I want to be able to do is have a PL/SQL
>procedure that is executed by clicking a hyberlink that will then take
>this BLOB and write it to "standard out" of the users browser which would
>then cause the browser to open up the "save as" dialog box.
>
>I would like to use something like the following example, but the
>htp.print function only allows alpha, numeric and date variable types
>as parameters. Does anyone has any ideas on how to get the data from
>the TEMP_BLOB variable back to the users browser?
>
>Thanks for any help,
>
>lofftjm_at_itec.suny.edu
>

It could look something like this (this one returns arbitrarily sized GIF files):

create or replace procedure get_blob( id in varchar2 ) is

    l_lob   blob;
    l_amt   number default 4096;
    l_off   number default 1;
    l_raw   raw(4096);

begin

    begin

        select b_lob into get_blob.l_lob
          from blobs
         where id = get_blob.id;
    exception
        when no_data_found then
            owa_util.status_line( 404, 'Not Found' );

    end;

    begin

        owa_util.mime_header( 'image/gif' );
        loop
            dbms_lob.read( l_lob, l_amt, l_off, l_raw );
            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;

So, assuming ID was the primary key of the blobs table and you wanted image 55, a URL like:

   http://hostname/yourdcd/owa/get_blob?id=55

would retrieve it and show it to you. If you wanted to save it, you would just right mouse click on it and the file/save as dialog would appear...

(utl_raw is shipped with the database but not installed unless you have replication or the procedural gateway installed, to install it, do an ls *raw* in the $ORACLE_HOME/rdbms/admin directory. You'll find the spec and body for utl_raw. Install both using svrmgrl when connected as internal)

>---------------------------------------------------------------------
>
>procedure TEST is
> TEMP_BLOB blob;
>begin
> select BINARYDATA
> into TEMP_BLOB
> from BLOB_TABLE
> where FILENAME = 'filename.exe';
> htp.print(TEMP_BLOB);
>end TEST;
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu May 28 1998 - 20:23:44 CDT