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


/