It’s a big day here at AskTOM HQ !
asktom
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
Subtle changes in XML, 11g vs 12c
An AskTOM reader brought this to our attention. It is unlikely to cause you any issues, but perhaps is good to know when it comes times to upgrade from 11g to 12c.
If you are taking an user defined object type and transposing that to XML, you will see a slightly different handling of NULLs in the object attributes. Here’s a quick example which demonstrates the difference.
11g
Nulls attributes do not appear within the XML output
SQL> create or replace type test_dummy_object as object (
2 id number,
3 "name" varchar2(30),
4 "Value" varchar2(30)
5 )
6 /
Type created.
SQL>
SQL> select xmlserialize(content xmltype(anydata.convertObject( test_dummy_object(1, null, '0') )) indent SHOW DEFAULTS) from dual
2 union all
3 select xmlserialize(content xmltype(anydata.convertObject( test_dummy_object(1, null, '0') )) indent HIDE DEFAULTS) from dual
4 union all
5 select xmltype(anydata.convertObject( test_dummy_object(1, null, '0') )).getclobval() from dual
6 union all
7 select xmltype(test_dummy_object(1, null, '0')).getclobval() from dual
8 union all
9 select xmltype.createxml(test_dummy_object(1, null, '0')).getclobval() from dual;
XMLSERIALIZE(CONTENTXMLTYPE(ANYDATA.CONVERTOBJECT(TEST_DUMMY_OBJECT(1,NULL,'0'))
--------------------------------------------------------------------------------
<TEST_DUMMY_OBJECT>
<ID>1</ID>
<Value>0</Value>
</TEST_DUMMY_OBJECT>
<TEST_DUMMY_OBJECT>
<ID>1</ID>
<Value>0</Value>
</TEST_DUMMY_OBJECT>
<TEST_DUMMY_OBJECT><ID>1</ID><Value>0</Value></TEST_DUMMY_OBJECT>
<TEST_DUMMY_OBJECT><ID>1</ID><Value>0</Value></TEST_DUMMY_OBJECT>
<TEST_DUMMY_OBJECT><ID>1</ID><Value>0</Value></TEST_DUMMY_OBJECT>
12c
Nulls attributes do appear within the XML output
SQL> create or replace type test_dummy_object as object (
2 id number,
3 "name" varchar2(30),
4 "Value" varchar2(30)
5 )
6 /
Type created.
SQL>
SQL> select xmlserialize(content xmltype(anydata.convertObject( test_dummy_object(1, null, '0') )) indent SHOW DEFAULTS) from dual
2 union all
3 select xmlserialize(content xmltype(anydata.convertObject( test_dummy_object(1, null, '0') )) indent HIDE DEFAULTS) from dual
4 union all
5 select xmltype(anydata.convertObject( test_dummy_object(1, null, '0') )).getclobval() from dual
6 union all
7 select xmltype(test_dummy_object(1, null, '0')).getclobval() from dual
8 union all
9 select xmltype.createxml(test_dummy_object(1, null, '0')).getclobval() from dual;
XMLSERIALIZE(CONTENTXMLTYPE(ANYDATA.CONVERTOBJECT(TEST_DUMMY_OBJECT(1,NULL,'0')))INDENTSHOWDEFAULTS)
---------------------------------------------------------------------------------------------------------------------------------------
<TEST_DUMMY_OBJECT>
<ID>1</ID>
<name/>
<Value>0</Value>
</TEST_DUMMY_OBJECT>
<TEST_DUMMY_OBJECT>
<ID>1</ID>
<name/>
<Value>0</Value>
</TEST_DUMMY_OBJECT>
<TEST_DUMMY_OBJECT><ID>1</ID><name/>
<Value>0</Value></TEST_DUMMY_OBJECT>
<TEST_DUMMY_OBJECT><ID>1</ID><name/>
<Value>0</Value></TEST_DUMMY_OBJECT>
<TEST_DUMMY_OBJECT><ID>1</ID><name/>
<Value>0</Value></TEST_DUMMY_OBJECT>
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
UKOUG is coming
Yes it is just a few more weeks until the UKOUG conference swings by.
This has been one of my favourite conferences for years – dating back to my first one in 2002 !!. You can see from the picture at the tail of this post – whilst times have changed in those 15 years, the basic tenets of community, networking and technical content remain unchanged to this day.
The AskTOM team will all be there and there’s a fantastic agenda spread over 4 days. This user group really knows how to “bake” a good conference. Maybe they followed my recipe. 
OakTable area, UKOUG 2002
Why being wrong can be awesome
OK, Now that I’ve started the post with a nice click-bait heading, let’s get down to the business of being wrong. 
I did a lot of conference presentations last year, and the great thing about that for me was that I got to meet a lot of new people in the Oracle community in the Developer and DBA space. One of the questions that came up over and over again was about putting one’s knowledge “out there” in the community and how to deal with the repercussions of that. In particular, “What if you publish something that is proven wrong?”
Here’s the thing about being wrong …. there’s two likely outcomes:
- Someone tells you that you are wrong, or
- You never know that you’re wrong and you wallow about in flawed darkness for all eternity.
Which would you prefer ?
This is really all about perception from both the blogger and the reader, and the way they behave.
If you are the reader and you find something that is incorrect, you have choices:
- Absolutely go ballistic on the author via comments, insult their intelligence, tell them how much smarter than them you are, and that they should never have been born, or
- Initiate a reasoned discussion about where the errors might be, how the author may have come to that point, discuss boundary conditions and both leave the discussion more knowledgeable as a result.
Rest assured, if you take the former position, no amount of smarts is going outweigh the public reputation you’ve just acquired as being a schmuck.
And similarly, as an author of content, when someone points out an error you can:
- take it in good faith, and work toward improving your knowledge by investigating further and collaborating with the person who discovered it, or
- just deny it, and lose your mind at them and denigrate them so that you don’t lose your fine public standing.
If you do the latter….guess what you just lost? Yup, your fine public standing.
And here’s the thing. Even if the person pointing out the error is indeed lacking the basic skills of civility, and is demonstrating their “schmucky-ness” all over your blog, just put that aside and focus on improving the content. Other readers will pick up on this, and they’ll value your contribution to the community much more than Joe Schmuck. They’ll be “red flagged” in the minds of the community as “one of those members that just isn’t worth the time of day”.
And if you’re wondering what the motivation for this post is – just this morning on an AskTOM answer, one of the Product Managers inside Oracle reached out to me and said “Hey Connor – I don’t think that answer is completely correct” and gave some me some additional content about the cause, and how to improve the answer. The net result of that:
- I learn some new stuff!
- The community gets better content from AskTOM!
- I make a new contact within the Oracle organization!
So don’t ever let being wrong stop you from contributing to the community. It’s the best way of improving yourself and the community as well.
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
/
