I know, in 11g, I can use LISTAGG for varchar2(), but does there exist a way to aggregate BLOB data?
(also how do I convert 255 into a single byte== 0xFFh for the BLOB?)

I have an SQL statement that aggregates raw data and returns values that can be used for producing an image. (ie a heat map of the data)
I am currently stuck trying to aggregate the values into a single BLOB value that can then be used by ORDImage as a "foreign image type". (This way, I can then convert it into a standard format like GIF.)

I could use a "for loop dbms_lob.append()", but I figured that would be the wrong way to do it.

many thanks ahead of time.

Michael Kutz

/* simulate results */
create table t_image_raw as
with x_generator as (select level X from dual connect by level <= 100)
,y_generator as (select level Y from dual connect by level <= 30)
,id_generator as (select level img_id from dual connect by level <= 10)
select img_id, X, Y, floor( 255/100 * X ) RED, floor( 255/30 * Y ) GREEN, floor(255/10 * img_id) BLUE
from x_generator, y_generator, id_generator;


create type image_table_t as object (img_id int, img ORDImage, height int, width int);
/

create type image_table_tab as table of image_table_t;
/

create or replace procedure makeHeatMap
as
image_table image_table_tab;
begin
/* magic SQL Here*/
select image_table_t(img_id
,null -- ???
,max(x),max(Y) )
bulk collect into image_table
from (select img_id, x,y, red,green,blue
from T_image_raw order by img_id,x,y)
group by img_id;
-- i'm still working on the section below
for process_me in (select * from table(image_table))
loop
null;
/* process image here similar to this...
process_me.img.setProperties('height=' || process_me.height
|| ' width=' || process_me.width );
process_me.img.process('fileFormat=GIF');
*/
end loop;
/*
merge into report_image_table RT
using (select * from table(image_table)) X
blah ...
*/
end;
/