Re: inserting into a BLOB in PRO*C

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/05/27
Message-ID: <356b5800.3070184@192.86.155.100>#1/1

A copy of this was sent to Ed Shnekendorf <eshneken_at_netscape.com> (if that email address didn't require changing) On Tue, 26 May 1998 17:16:53 -0400, you wrote:

>Hello,
>
>I'm looking for a way to insert binary data into a BLOB from PRO*C.
>
>I start out with base-64 encoded data which I run through a decoder and
>store in a char*. I want to use embedded PL/SQL's DBMS_LOB.WRITE()
>procedure to write data into the BLOB, but it requires input in RAW
>format. Whenever I try to insert my binary data into a RAW field, I get
>an 'ORA-1465: invalid hex number' error.
>
>Any suggestions on how to get this binary data directly into a BLOB or
>into a RAW so that I can use DBMS_LOB?
>
>thanks,
>Ed
>
>PS -- I'm using PRO*C 8.0.3 with Oracle 8 on NT.

1. write the results to a temporary file on disk and use DBMS_LOB.LOADFROMFILE()
2. use pro*c in a way similar to the following. Note this example expects the table:

create table test_o8blob ( the_blob blob );

and the package:

create package test_o8blob_pkg
as

   g_blob blob;
end;

I use the package to maintain my Lob Locator instead of trying to muck around with it in pro*c.... Anyway the code might look like:

....
typedef struct { short len; char arr[32000]; } my_blob;

EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL TYPE my_blob is VARRAW(32000);

my_blob blob_piece;
char * data;

int     data_size;
int     amt_to_write;
int     amt_written;

EXEC SQL END DECLARE SECTION;     EXEC SQL EXECUTE
    begin

        insert into test_o8blob values ( empty_blob() )
        return the_blob into test_o8blob_pkg.g_blob;

    END;
    END-EXEC;     data_size = 100000;
    data = (char *)malloc( data_size );
    memset( data, 0, data_size );

    for( amt_to_write = min(data_size,32000), amt_written = 0;
         amt_written < data_size;
         amt_written += amt_to_write,
                amt_to_write = min(32000,data_size-amt_written) )
    {
        memmove( blob_piece.arr, data+amt_written, amt_to_write );
        blob_piece.len = (short)amt_to_write;

        printf( "Writing blob from offset %d, for %d bytes\n",
                 amt_written+1, amt_to_write );

        EXEC SQL EXECUTE
        begin
            dbms_lob.write( lob_loc => test_o8blob_pkg.g_blob,
                            amount => :amt_to_write,
                            offset => :amt_written+1,
                            buffer => :blob_piece );
        end;
        END-EXEC;

    }
....

We take the data you want to write (char * data in my example) and put it in the lob 32,000 bytes at a time. We can use the VARRAW external data type to do this so no conversion (character set conversions) will take place and pl/sql won't try to do a hex to raw conversion on the string....  

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 Wed May 27 1998 - 00:00:00 CDT