Posted by Just a quick note to give the supporting collateral to the latest episode of AskTOM TV.
The question I tackled is this one:
https://asktom.oracle.com/pls/apex/asktom.search?tag=want-to-retrive-numbers-in-words
which was a fun one to answer because it showcases several useful SQL techniques:
- Using CONNECT to synthesize rows,
- Using regular expressions to parse text ,
- Using MULTICAST in Oracle 10g to emulate the native LISTAGG functions from 11g onwards ,
- Using the hooks into the OCI aggregation facilities to build custom aggregations ,
- The JSP format mask as a mean to generate numeric words
And here is the entire script from the episode if you want to run it yourself.
drop type string_agg_type;
col column_value format a60
col digit format a60
col concat_str format a60
drop table t purge;
select to_char(to_date('7','J'),'JSP') from dual;
select to_char(to_date('0','J'),'JSP') from dual;
select
case x
when '0' then 'zero'
when '1' then 'one'
when '2' then 'two'
when '3' then 'three'
when '4' then 'four'
when '5' then 'five'
when '6' then 'six'
when '7' then 'seven'
when '8' then 'eight'
when '9' then 'nine'
end
from ( select '3' x from dual )
/
select
case x
when '0' then 'zero'
when '1' then 'one'
when '2' then 'two'
when '3' then 'three'
when '4' then 'four'
when '5' then 'five'
when '6' then 'six'
when '7' then 'seven'
when '8' then 'eight'
when '9' then 'nine'
end
from (
select substr('123',rownum,1) x
from dual
connect by level <= 3
)
/
create or replace type string_list is table of varchar2(1000);
/
create table t ( x int );
insert into t values (101);
insert into t values (456);
insert into t values (789);
select *
from t,
table(cast(multiset(
select substr(to_char(t.x),rownum,1)
from dual
connect by level <= length(to_char(t.x))) as string_list)
)
/
select
x,
digit
from (
select x, column_value digit
from t,
table(cast(multiset(
select
case substr(to_char(t.x),rownum,1)
when '0' then 'zero'
when '1' then 'one'
when '2' then 'two'
when '3' then 'three'
when '4' then 'four'
when '5' then 'five'
when '6' then 'six'
when '7' then 'seven'
when '8' then 'eight'
when '9' then 'nine'
end str
from dual
connect by level <= length(to_char(t.x))) as string_list)
)
)
/
create or replace type string_agg_type as object
(
data string_list,
static function
ODCIAggregateInitialize(sctx IN OUT string_agg_type )
return number,
member function
ODCIAggregateIterate(self IN OUT string_agg_type ,
value IN varchar2 )
return number,
member function
ODCIAggregateTerminate(self IN string_agg_type,
returnValue OUT varchar2,
flags IN number)
return number,
member function
ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type)
return number
);
/
create or replace type body string_agg_type
is
static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
return number
is
begin
sctx := string_agg_type( string_list() );
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT string_agg_type,
value IN varchar2 )
return number
is
begin
data.extend;
data(data.count) := value;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN string_agg_type,
returnValue OUT varchar2,
flags IN number)
return number
is
l_data varchar2(4000);
begin
for x in ( select column_value from TABLE(data) order by 1 )
loop
l_data := l_data || ',' || x.column_value;
end loop;
returnValue := ltrim(l_data,',');
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type)
return number
is
begin
for i in 1 .. ctx2.data.count
loop
data.extend;
data(data.count) := ctx2.data(i);
end loop;
return ODCIConst.Success;
end;
end;
/
CREATE or replace
FUNCTION stragg(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING string_agg_type;
/
with source_data as
(
select
x,
digit
from (
select x, column_value digit
from t,
table(cast(multiset(
select
case substr(to_char(t.x),rownum,1)
when '0' then 'zero'
when '1' then 'one'
when '2' then 'two'
when '3' then 'three'
when '4' then 'four'
when '5' then 'five'
when '6' then 'six'
when '7' then 'seven'
when '8' then 'eight'
when '9' then 'nine'
end str
from dual
connect by level <= length(to_char(t.x))) as string_list)
)
)
)
select x, stragg(digit) concat_str
from source_data
group by x
order by 1
/
with source_data as
(
select
x,
digit
from (
select x, column_value digit
from t,
table(cast(multiset(
select '@'||lpad(level,10,'0')||'~'||
case substr(to_char(t.x),rownum,1)
when '0' then 'zero'
when '1' then 'one'
when '2' then 'two'
when '3' then 'three'
when '4' then 'four'
when '5' then 'five'
when '6' then 'six'
when '7' then 'seven'
when '8' then 'eight'
when '9' then 'nine'
end str
from dual
connect by level <= length(to_char(t.x))) as string_list)
)
)
)
select x, stragg(digit)concat_str
from source_data
group by x
order by 1
/
with source_data as
(
select
x,
digit
from (
select x, column_value digit
from t,
table(cast(multiset(
select '@'||lpad(level,10,'0')||'~'||
case substr(to_char(t.x),rownum,1)
when '0' then 'zero'
when '1' then 'one'
when '2' then 'two'
when '3' then 'three'
when '4' then 'four'
when '5' then 'five'
when '6' then 'six'
when '7' then 'seven'
when '8' then 'eight'
when '9' then 'nine'
end str
from dual
connect by level <= length(to_char(t.x))) as string_list)
)
)
)
select x, regexp_replace(stragg(digit),'\@[0-9]*\~') concat_str
from source_data
group by x
order by 1
/
Learning is not a spectator sport 
One comment