Re: Help!!! IT'S THE BLOB...

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 28 May 1998 17:19:56 GMT
Message-ID: <356d9afa.17682435@192.86.155.100>

A copy of this was sent to CW120463_at_my-dejanews.com (if that email address didn't require changing) On Thu, 28 May 1998 14:03:18 GMT, you wrote:

>'Sorry, I mean an Oracle8 BLOB column type. We're not having any trouble
>getting data _INTO_ the column, but we haven't been able to retrieve it back
>out to "re-create" its native file (using VB-- or anything else for that
>matter).
>
>Thanks again for any suggestions,
>
>Chuck Wessel

Here are 2.

1.) using proc the code would look like the following (actually ANY language that can get a RAW type out of the database and call PL/SQL can use this method. I don't do VB myself so I don't know if VB can do 'RAWs' or not, I would assume that it can tho. This will work with V7 as well as V8 clients since we keep the lob locator 'hidden' in the database and only ever return RAWs to the client 32k at a time...)

static void process()
{
typedef struct { short len; char arr[32000]; } my_blob; EXEC SQL TYPE my_blob is VARRAW(32000);

my_blob blob_piece;

int        amt_read = 0;
int        amt_actually_read;

    /*  counts on the table test_o8blob ( the_blob blob )
        existing AND the package:
        create package test_o8blob_pkg
        as
            g_blob    blob;
        end;
        existing...                                      */

EXEC SQL WHENEVER SQLERROR DO sqlerror_hard(); EXEC SQL EXECUTE
begin

        select the_blob into test_o8blob_pkg.g_blob
          from test_o8blob
         where rownum = 1;

    END;
    END-EXEC;     for( ;; )
    {

        amt_actually_read = 32000;
        EXEC SQL WHENEVER NOTFOUND do break;
        EXEC SQL EXECUTE
        begin
            dbms_lob.read( lob_loc => test_o8blob_pkg.g_blob,
                            amount => :amt_actually_read,
                            offset => :amt_read+1,
                            buffer => :blob_piece );
        end;
        END-EXEC;

        printf("%d Fetched %d bytes from offset %d\n",
                sqlca.sqlcode, amt_actually_read, amt_read);

        amt_read += amt_actually_read;

}
EXEC SQL commit work;
}

2.) use the pl/sql cartridge and a web browser to download and save the file. that would look something like:

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

        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 document 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)

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 - 12:19:56 CDT