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
asktom
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
/
The AskTOM team at OpenWorld 2017
The AskTOM team will be out and about at OpenWorld in October, so if you are at the conference as a full attendee, or just with a Discovery pass, please come up and say Hi! As well as our session talks, we’ll be doing impromptu discussions and mini-sessions during the week either in the Developer Lounge area or the Exhibition Hall, so keep your eyes open on the OpenWorld twitter feeds during the week. You can see our session times below.
Obviously OpenWorld is a big event, and since sports TV stations go totally over the top with promotion when big events are coming up, I thought to myself: “Why should they have all the fun ?”. So crank up the volume to maximum, and get down with our pumping AskTOM promo video for OpenWorld ! 
Our Sessions
Connor McDonald
War of the Worlds: DBAs Versus Developers
Wednesday, Oct 04, 1:00 p.m. – 1:45 p.m. | Moscone West – Room 3014
Don’t let the name deceive you – this is a technical session covering a myriad of 12c Release 2 features for both DBAs and Developers. For decades, developers and DBAs have battled over who controls the world. As cloud databases now free DBAs from the mundane the battle flares again, as developers need greater flexibility, less structure, and faster turnaround to deploy. DBAs use a more methodical approach, worried about the wreckage that may result from playing so fast and loose. But does that just make the DBA an inhibitor to your productivity? Who is right? Perhaps both? Can developers and DBAs peacefully coexist? Maybe they just need the right technology. In this session see how to integrate the demands of the modern-day developer with the Oracle Database (and the modern-day administrators).
Click here to register.
Leaner, Faster Code with Advanced SQL Techniques
Tuesday, Oct 03, 1:15 p.m. – 2:00 p.m. | Moscone West – Room 2002
Most SQL is so simple to write that we can hand off the job to an ORM tool or a similar mechanism. But by delving a little deeper into more-advanced SQL facilities, we can get performance benefits and write a lot less middle-tier code. This session highlights some SQL techniques for solving problems that would otherwise require a lot of complex coding. Learn how to become a more productive developer by expanding your knowledge of the SQL language.
Click here to register
Using Advanced SQL Techniques for Faster Applications
Tuesday, Oct 03, 5:45 p.m. – 6:30 p.m. | Moscone West – Room 3014
Most SQL is so simple to write that you can hand off the job to an object-relational mapping tool or similar mechanism. But by delving a little deeper into more advanced SQL techniques, we can get performance benefits and write a lot less middle-tier code. This session highlights some SQL techniques to solve problems that would otherwise require a lot of complex coding. Learn how to become a more productive developer by expanding your knowledge of the SQL language.
Click here to register
Also, I’ll be doing some mini-lessons in The Exchange
Ask TOM’s Favorite Multitenant/In-Memory Features in Oracle Database 12c R2
Monday, Oct 02, 11:30 a.m. – 11:50 a.m. | The Exchange @ Moscone West – Showcase Theater 2
Click here to register
Ask TOM Hard Core SQL: Common Table Expressions and Hierarchy Processing
Monday, Oct 02, 3:30 p.m. – 3:50 p.m. | The Exchange @ Moscone West – Showcase Theater 2
Click here to register
Chris Saxon
12 Things Developers Will Love About Oracle Database 12c Release 2
Tuesday, Oct 03, 3:45 p.m. – 4:30 p.m. | Moscone West – Room 3014
Oracle Database 12c Release 2 is here. The headline features are sharding, multitenant, and application containers. But it’s the small changes to SQL that make a real difference in your day-to-day life work with Oracle Database. In this session get an overview of the changes to SQL and PL/SQL that will help you build faster, more robust database applications. If you’re a developer or DBA who regularly writes SQL or PL/SQL and wants to keep up to date this session is for you.
How to Hack into Your Oracle Database via Node.js, Using SQL Injection
Wednesday, Oct 04, 8:30 a.m. – 9:15 a.m. | Moscone West – Room 2004
Hackers are constantly searching for personal data they can use to exploit people. And they’re often successful. Each week brings new stories of large-scale data breaches. A common attack vector is SQL injection. If your application is vulnerable to this, hackers can get whatever they want from your database. This session shows you how easy it is to access private data with SQL injection and how to change your code to stop it. It ends with a discussion of further recommendations for writing secure code. This is a must-attend session for all developers who write database access code.
(co-speaker Dan Mcghan)
Maria Colgan
Oracle Database Features Every Developer Should Know About
Wednesday, Oct 04, 12:00 p.m. – 12:45 p.m. | Moscone West – Room 3020
Over the years, a lot of new features have been introduced in Oracle Database to save developers time and to spare them from having to reinvent the wheel. Many of these features are actively highlighted via conference sessions or blogs when a new release is launched, but over time are forgotten before they can be put to good use. In this session learn about the critical features already existing in Oracle Database that every developer should not only know about but also utilize. Using easy-to-follow examples, see when and where you should take advantage of these features to make your life easier and help you get the most out of Oracle Database, regardless of whether you are a DBA or a developer.
(co-speaker Gerald Venzl)
Five Things You Might Not Know About Oracle Database
Wednesday, Oct 04, 2:00 p.m. – 2:45 p.m. | Moscone West – Room 3020
In this session take a look at five things you might not have known about Oracle Database—or that you might have known about but never realized how it could benefit you. Get a detailed explanation of each feature’s functionality and see the benefits through real-world examples. The topics covered are for Oracle Database 11g and Oracle Database, Standard Edition 12c, with only a couple of minor exceptions. Come away with a better understanding of these features and how they can benefit you and your organization.
(co-speaker Dominic Giles)
Oracle Database and the Internet of Things
Tuesday, Oct 03, 11:30 a.m. – 12:15 p.m. | Moscone West – Room 3011
In recent years there has been a rapid surge in the adoption of smart devices. Everything, from phones and tablets to smart meters and fitness devices, connect to the Internet and share data. With all of these smart devices comes a huge increase in the frequency and volume of data being ingested into and processed by databases. But, what most folks forget is that industries like telecoms and manufacturing have been dealing with these challenges for years with the help of their Oracle Database. This session provides step-by-step instructions for deploying a high-ingest, mission critical IoT workload on Oracle Database.
(co-speaker Dominic Giles)
Sergiusz Wolicki
New Paradigm for Case-Sensitivity and Collation on Oracle Database 12c Release 2
Monday, Oct 02, 11:00 a.m. – 11:45 a.m. | Moscone West – Room 3008
Historically, the way an Oracle Database compares and sorts character data is controlled in a pretty coarse way by the parameters NLS_SORT and NLS_COMP. This session presents a new feature of Oracle Database 12c Release 2 (12.2) that introduces a declarative method of associating collation with particular data. Using the new mechanism, data-bound collation (DBC), the user can precisely control how any given character data is compared depending just on the source of the data. For example, content of given columns may be declared as case-insensitive. The new functionality is based on the ISO/IEC SQL standard, which simplifies application porting to Oracle Database from other RDBMS systems that support similar functionality
