It’s a big day here at AskTOM HQ !
video
Trip down memory lane
I did a little video for St Patricks day, but it also brought back memories of my first computer experiences. A Sinclair ZX80, a Commodore 64, and many other machines that I thought were so cool for their time.
Feel free to share your experiences in comments.
AskTOM Office Hours for DBA’s
We had the first AskTOM Office Hours Q&A for Database Administrators yesterday. Thanks to everyone that showed up, and thanks for the questions.
If you missed it, you can catch a replay here
Seasons greetings from the AskTOM team
This year we’ve answered over 2800 of your questions and followed up on 3500 reviews. It’s been a great and busy year! We’ll see you all in 2018
So good … it’s scary
Well…this isn’t a horror movie, it’s something awesome
AskTOM TV–episode 11
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
/
Partitioning Guide updated
Just a quick note to say I’ve updated our Getting Started With Partitioning Guide! 9 easy to follow demonstrations to get developers up to speed with handling large databases. Check it out here
