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>




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. Smile

 

 

OakTable area, UKOUG 2002

image

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. Smile

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 ? Smile

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 ! Smile

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