Monthly Archives: November 2013

Great design: Go to parrent topic link.

SyBooks got some great design insight for hierarchical documentation. All greatness comes from “Go to parent topic” link placed at the end of the document. It’s just easy to click that link when you’ve read or skimmed the whole topic. “Current location” navigation element placed at the top of the page looses that easiness cause you have to read more and to think even more. With “Go to parent topic” link one has no alternatives and she don’t even need to know the name of the parent topic. It always leads one level up.

Check it out:

awesome design insight from sybooks

Custom listagg function using cursor to bypass ORA-01489

When using build-in listagg function one should always consider that resulting string must have a length less or equal to 4000 characters. If does exceed then an error ‘SQL Error: ORA-01489: result of string concatenation is too long’ is thrown.
Here’s a package implementing a custom aggregate function that bypasses this restriction.


create or replace PACKAGE "PK_UTILS" AS

type ref_cur is ref cursor;

function LISTAGG_CLOB( a_cur in pk_utils.ref_cur, a_delimiter in varchar2 ) return clob;

END PK_UTILS;
/
create or replace PACKAGE BODY "PK_UTILS" AS

function listagg_clob( a_cur in pk_utils.ref_cur, a_delimiter in varchar2 ) return clob
as
v_single_value clob;
v_result clob;
begin
fetch a_cur into v_single_value;
if ( a_cur%NOTFOUND ) then
goto FIN;
end if;
v_result := v_single_value;
loop
fetch a_cur into v_single_value;
exit when a_cur%NOTFOUND;
v_result := v_result || a_delimiter || v_single_value;
end loop;
<<FIN>>
return v_result;
end;

END PK_UTILS;
/

And here is a small user test emulating ‘within group (order by …)’ functionality.


with prepared as (
select 1 a, 1 b from dual
union all
select 2 a, 2 b from dual
union all
select 3 a, 1 b from dual
union all
select 4 a, 2 b from dual
)
select q.b
, pk_utils.listagg_clob( cursor( select a from prepared where b = q.b order by a desc ) , ',' )
from (
select b
from prepared
group by b
) q
;/

B LISTAGG_
---------- ------------------
1 3,1
2 4,2