Re: inserting into a BLOB in PRO*C
Date: 1998/05/27
Message-ID: <356b5800.3070184_at_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.
- write the results to a temporary file on disk and use DBMS_LOB.LOADFROMFILE()
- 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 CEST