Here's what I came up with. Is there better efficient and elegant code out there?
Thanks
----------------------------------------------------------
TYPE testCur IS REF CURSOR;
cur testCur;
stmt_str VARCHAR2(200);
testrecord table%ROWTYPE;
v_record varchar2(2000);
prev_index varchar2(25);
BEGIN
--sql query to execute
stmt_str := 'select * from table order by index';
OPEN cur FOR stmt_str;
LOOP
FETCH cur INTO testrecord;
IF prev_index<>testrecord.index THEN
--process the record and reset v_record for the next index
htp.p(prev_index);
htp.p(substr(v_record,2,length(v_record)));htp.p('<br>');
v_record:='';
ELSE
v_record:=v_record||'|'||testrecord.column1||'|'||testrecord.column2||'|'||testrecord.column3||'|';
END IF;
prev_index:=testrecord.index;
EXIT WHEN cur%NOTFOUND;
END LOOP;
--process the last record
htp.p(prev_index);
htp.p(substr(v_record,2,length(v_record)));htp.p('<br>');
CLOSE cur;
EXCEPTION
when others then
htp.p('message');
END;