Ok, I show you how to do the RAW but I'll also make you see the right datatype to use as well (BLOB directly to OCI). Offhand, I do not see your ORA-1460 but I cooked up this example:


drop table t;

create table t ( x blob );

declare
l_raw long raw;
l_blob blob;
l_blob_size number := 5000;
begin
for i in 1 .. l_blob_size loop
l_raw := l_raw || hextoraw(to_char(mod(i,256), 'fmXX'));
end loop;
insert into t values ( empty_blob() )
returning x into l_blob;
dbms_lob.writeappend( l_blob, l_blob_size, l_raw );
commit;
end;
/


So, that created a 5,000 byte blob. It has every character we can have from chr(0) to chr(255). We know the SUM of the bytes in this raw is:

629,476

that is -- if we

tkyte@TKYTE816> declare
2 sum_of_bytes double precision := 0;
3 begin
4 for i in 1 .. 5000 loop
5 sum_of_bytes := mod(i,256)+sum_of_bytes;
6 end loop;
7 dbms_output.put_line( sum_of_bytes );
8 end;
9 /
629476

PL/SQL procedure successfully completed.


we get that sum... Thats what our C code will do as an example (sum the bytes)....

So, here is the package I want to implement:


create or replace package demo_passing_pkg
as
function pass_lob( p_in in BLOB ) return double precision;

function pass_raw( p_in in RAW ) return double precision;

end demo_passing_pkg;
/


I'm going to have one for a RAW and another for a blob. My body for that is:


create or replace package body demo_passing_pkg
as
function pass_lob( p_in in blob ) return double precision
as
language C
name "pass_lob"
library demoPassing
with context
parameters ( CONTEXT,
p_in OCILOBLOCATOR,
p_in INDICATOR short,

return INDICATOR short );

-- void pass_clob
-- (
-- OCIExtProcContext *, /* 1 : With-Context */
-- OCILobLocator *, /* 2 : P_IN */
-- short , /* 3 : P_IN (Indicator) */
-- short *, /* 4 : RETURN (Indicator) */
-- );

function pass_raw( p_in in raw ) return double precision
as
language C
name "pass_raw"
library demoPassing
with context
parameters ( CONTEXT,
p_in RAW,
p_in INDICATOR short,
p_in LENGTH int,
RETURN INDICATOR );

-- void pass_long_raw
-- (
-- OCIExtProcContext *, /* 1 : With-Context */
-- unsigned char *, /* 2 : P_IN */
-- short , /* 3 : P_IN (Indicator) */
-- int , /* 4 : P_IN (Length) */
-- short *, /* 5 : RETURN (Indicator) */
-- );


end demo_passing_pkg;
/


I like to be verbose. I want to be able to return NULL from this function (if for example, my inputs are NULL). Therefore, I need the return indicator. We don't need to pass the raw by reference (just makes it harder to read the length and all) since raws are already sent to us as a pointer -- so I won't...

My C code for these two functions is then:


#include <oci.h>

#ifdef WIN_NT
_declspec (dllexport)
#endif
double pass_raw
( OCIExtProcContext * ctx /* CONTEXT */,

unsigned char * p_iraw /* RAW */,
short p_iraw_i /* INDICATOR short */,
int p_iraw_l /* LENGHT INT */,
short * return_i /* return indicator */
)
{
double sum_of_bytes = 0;
unsigned char* cp;
int i;

if ( p_iraw_i == OCI_IND_NOTNULL )
{
for( cp = p_iraw, i = 0; i < p_iraw_l; i++, cp++ )
{
sum_of_bytes += (double) *cp;
}
*return_i = OCI_IND_NOTNULL;
}
else
{
*return_i = OCI_IND_NULL;
}
return sum_of_bytes;
}


#ifdef WIN_NT
_declspec (dllexport)
#endif
double pass_lob
( OCIExtProcContext * ctx /* CONTEXT */,

OCILobLocator * p_in /* OCILOBLOCATOR */,
short p_in_i /* INDICATOR short */,
short * return_i /* return indicator */
)
{
ub4 lob_length;
unsigned char * cp;
double sum_of_bytes;

OCIEnv * envhp; /* OCI environment handle */
OCISvcCtx * svchp; /* OCI Service handle */
OCIError * errhp; /* OCI Error handle */

if ( OCIExtProcGetEnv( ctx, &envhp,
&svchp, &errhp ) != OCI_SUCCESS )
{
OCIExtProcRaiseExcpWithMsg(ctx,20000,
"failed to get OCI Connection",0);
return 0;
}

if ( p_in_i == OCI_IND_NOTNULL )
{
if ( OCILobGetLength ( svchp, errhp, p_in,
&lob_length ) != OCI_SUCCESS)
{
OCIExtProcRaiseExcpWithMsg(ctx,20000,
"failed to get lob length",0);
return 0;
}

if ( (cp = OCIExtProcAllocCallMemory( ctx,
lob_length )) == NULL )
{
OCIExtProcRaiseExcpWithMsg ( ctx, 20001,
"No memory", 0 );
}
else
{
if ( OCILobRead( svchp, errhp, p_in, &lob_length,
1, (dvoid *) cp,
(ub4) lob_length, (dvoid *)0,
NULL, (ub2) 0,
(ub1) SQLCS_IMPLICIT) != OCI_SUCCESS )
{
OCIExtProcRaiseExcpWithMsg(ctx,20000,
"failed to read lob",0);
}
else
{
for( ; lob_length; lob_length-- )
sum_of_bytes += (double) *(cp+lob_length-1);
*return_i = OCI_IND_NOTNULL;
}
}
}
else
{
*return_i = OCI_IND_NULL;
}
return sum_of_bytes;
}


Now, the blob code looks more complex however, if you look at the plsql code for interfacing with each one:


tkyte@TKYTE816> declare
2 sum_of_bytes double precision;
3 l_blob blob;
4 l_raw raw(32000);
5 l_offset number default 1;
6 l_amt number;
7 begin
8 select x into l_blob from t;
9 l_amt := dbms_lob.getlength( l_blob );
10
11 dbms_lob.read( l_blob, l_amt, l_offset, l_raw );
12
13 sum_of_bytes := demo_passing_pkg.pass_raw( l_raw );
14
15 dbms_output.put_line( sum_of_bytes );
16 end;
17 /
629476

PL/SQL procedure successfully completed.

tkyte@TKYTE816>
tkyte@TKYTE816> declare
2 sum_of_bytes double precision;
3 l_blob blob;
4 begin
5 select x into l_blob from t;
6 sum_of_bytes := demo_passing_pkg.pass_lob( l_blob );
7
8 dbms_output.put_line( sum_of_bytes );
9 end;
10 /
629476

PL/SQL procedure successfully completed.

You can see we just moved complexity from PLSQL into C or vice versa (someone has to do the work). The added benefit of the LOB implementation will come in for blobs >32k. It will take repeated calls to the extproc to process the entire blob, if we pass the blob, we can use OCILobRead to piecewise read it in C instead of PLSQL (as plsql will limit you to a 32k raw)....