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
video
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
OTN Yathra 2017
The amazing 6 city set of events is just around the corner, with all of the dates and details available at http://otnyathra.in/
Here are my thoughts on why you should be attending
12c Release 2 – Transparent Data Encryption online !
It doesn’t take a rocket scientist to know that even if you have incredibly stringent controls on user authentication, user authorisation etc, that wont save you if your data on disk is not encrypted. All you need is an errant tape, a missing disk, a misplaced flash stick…and kersplat, someone has a copy of your datafiles from your Oracle database.
Data at rest should be encrypted, but that often meant taking applications offline to do so.
I’ve put my Speed Racer hat on 
and here’s a video on a new 12c Release 2 feature covered in 60 seconds !
