inserting into a BLOB in PRO*C

[Ed Shnekendorf]

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.

[Thomas Kyte]

A copy of this was sent to Ed Shnekendorf <eshn...@netscape.com>
(if that email address didn't require changing)

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
tk...@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.