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:

Remains of the Day: An old but overall great post on using and customizing Oracle SqlDeveloper

The best trick learned is to changed default syntax highlighting to more readable one. Read an article here peoplesofttipster.com/sql-developer.

Syntax reminder: Get number of DML operations from GoldenGate replicat report file

This little piece of a bash code lets you obtain number of DML operations from Oracle GoldenGate report file:

#!/bin/sh
dml_cnt=`perl -p -e 's/\s{2,}/\t/g' /opt/oracle/ggate/dirrpt/R1.rpt | awk -F "\t" '($3 ~/(inserts|updates|deletes):/){dml_count += $4} END {print dml_count}'`

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