How to write binary data to BLOB ?

[Matthias Spaeth]

Hi,

how can I write binary data given by pointer 'ptr' and length 'size' to a
BLOB column using a PL/SQL-Block in Pro*C/C++? The oracle documentation was
not very helpful.

function: int oraWrite(void* ptr, long size)

thanks in advance,
Matthias

[Thomas Kyte]

A copy of this was sent to "Matthias Spaeth" <matthia...@optimum-gmbh.de>
(if that email address didn't require changing)

In 8.0, the choices are to use OCI in your pro*c to manipulate the lobs or to
let the lob locator stay in the database and use the plsql packages to write the
lob. Here is an example showing #2 (using plsql). I think its a little easier
and when you upgrade to 8.1, pro*c will have native "exec sql lob" commands you
can use instead (easier migration).

In the database, you will create a package to hold a lob locator, it might look
like:

create or replace package my_pkg
as
g_blob blob;
g_clob clob;
end;
/

I used a demo table as follows:

drop table demo;
create table demo ( x int primary key, y blob );

and then the proc code would look like:

static void process( void * ptr, long size )
{
typedef struct TAGmy_raw
{
long len;
unsigned char arr[32000];
}
my_raw;

EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL TYPE my_raw IS LONG VARRAW(32000);
my_raw buffer;
int amt;
int offset = 1;
EXEC SQL END DECLARE SECTION;

exec sql whenever SQLERROR do sqlerror_hard();
exec sql delete from demo;

exec sql execute
begin
insert into demo values ( 1, empty_blob() )
returning y into my_pkg.g_blob;
end;
end-exec;

for( ; size > 0; size -= 32000, ptr += 32000, offset += 32000 )
{
amt = size>32000?32000:size;
memmove( buffer.arr, ptr, amt );
buffer.len = amt;

printf( "Writing %d bytes at offset %d\n", amt, offset );
exec sql execute
begin
dbms_lob.write( my_pkg.g_blob, :amt, :offset, :buffer );
end;
end-exec;
}

exec sql commit work;
}

Thomas Kyte
tk...@us.oracle.com
Oracle Service Industries
Reston, VA USA

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

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