| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Concatenating BLOB data in PL/SQL, returning it to Pro*C
A copy of this was sent to charlie_root_at_my-dejanews.com
(if that email address didn't require changing)
On Thu, 21 Jan 1999 19:28:19 GMT, you wrote:
>I may be going about this in entirely the wrong way, but ...
>
>I have a database containing blob data. I wish to write
>a PL/SQL stored function which can be called from Pro*C.
>
>I would like this stored procedure to collect the data
>from blobs stored in several rows and return it in some
>parseable form to my C program.
>
you can do this the way you are trying IF the total lenght of the concatenate blob is 32k or LESS.
>Several hours with enormous manuals later, and I'm thinking
>this is not an easy problem. Some sample code follows; in
>particular, what I want is for something like the CONCAT
>to work, looping over each row collecting blob data into
>some form which is returned to the caller.
>
your code works (once I fixed the typos and supplied the missing declares for some variables anyway).
I ran in my database the following:
drop table widgits;
create table widgits
( widgit_id number ,
widgit_data blob
)
/
begin
for i in 1 .. 10 loop
insert into widgits values ( 1, utl_raw.cast_to_raw( 'Hello World ' ));
end loop;
end;
/
That created a table with 10 rows, each row has 'Hello World' stuffed into the raw field.
To test this in sqlplus simply I compiled:
CREATE OR REPLACE FUNCTION goober RETURN RAW IS
blob_locator BLOB;
offset NUMBER := 1;
buffer RAW(1000);
rval RAW(10000);
length NUMBER := 0;
b_ptr NUMBER := 0;
continue NUMBER := 0;
id number;
CURSOR csr_get_id IS SELECT widgit_id from widgits;
CURSOR csr_get_locator(in_id NUMBER) IS SELECT widgit_data from
widgits where widgit_id = in_id;
BEGIN
OPEN csr_get_id;
LOOP
FETCH csr_get_id INTO id;
EXIT WHEN csr_get_id%NOTFOUND;
OPEN csr_get_locator(id);
FETCH csr_get_locator INTO blob_locator;
IF csr_get_locator%FOUND THEN
length := DBMS_LOB.GETLENGTH(blob_locator);
IF length > 0 THEN
DBMS_LOB.READ(blob_locator, length, offset, buffer);
-- ***
-- It would be sooooo cool if this next line worked.
-- ***
rval := concat( rval, buffer);
END IF;
CLOSE csr_get_locator;
ELSE
CLOSE csr_get_locator;
END IF;
END LOOP;
CLOSE csr_get_id;
RETURN rval;
(this one compiles -- fixed tablenames and variable declares and such) and then ran:
SQL> set serveroutput on
SQL> exec dbms_output.put_line( utl_raw.cast_to_varchar2( goober ) )
Hello World Hello World Hello World Hello World Hello World Hello World Hello World Hello World Hello World Hello World
PL/SQL procedure successfully completed.
So, that shows the function works anyway -- it concated the 10 rows together.
to call from Pro*c i code:
typedef struct TAGmy_raw
{
long len;
unsigned char arr[32760];
}
my_raw;
static void process( char * proc_name, char * proc_body ) {
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL TYPE my_raw IS LONG VARRAW(32760);
my_raw data;
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
data.len = sizeof( data.arr );
EXEC SQL EXECUTE
begin
:data := goober;
end;
END-EXEC;
printf( "data = '%.*s'\n", data.len, data.arr );
}
and it works as well....
>Any thoughts?
>
>Thanks,
>--chuck
>
[snip]
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
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 Fri Jan 22 1999 - 07:22:20 CST
 |
 |