Thanks for the question, Jacek.

Asked: April 10, 2017 - 11:25 pm UTC

Last updated: April 12, 2017 - 12:58 pm UTC

Version: 11g Release 2 XE, 12c SE Release 1, 12c SE Release 2

Viewed 1000+ times

You Asked

https://livesql.oracle.com/apex/livesql/file/content_ET2CD8U5GO17VY2H0CJ8REEWL.html

11g Release 2 XE, 12c SE Release 1, 12c SE Release 2

Date+Time in XML when converting from cursor

Hi Chris, Connor, Maria,

I'm working on utPLSQL v3 project with few of colleagues and I'm facing a big challenge.
I want to leverage XML methods of SQL or PL/SQL to convert any cursor into XML but there is a catch.

Our functionality needs to be able to convert any cursor into XMLTYPE. The cursor will be already open, when entering utPLSQL code.

I've realized, that Oracle is converting DATE datatype to XML element using NLS session settings that were defined at the time the cursor was opened. The trouble is that the session NLS settings can be set into anything (not under control of the framework) and usually the settings are set to loose TIME and only include the DATE information. Incomplete information about DATE/TIME is making cursor-to-XML loose information (sometimes really important).

Similar difficulties were already described by Finn on Oracle 10g.
https://ellebaek.wordpress.com/2011/01/27/converting-between-oracle-data-and-xml/

I have tested it on 11g XE, 12.1 and 12.2 - and can't see way around it.

Is there a way to influence XML conversion of DATE datatype when using:
- XMLSERIALIZE(cursor)
- XMLTYPE.createXML(cursor)
- DBMS_XMLGEN

Also, when converting to XML using XMLSERIALIZE or XMLTYPE.createXML, is there an option to include NULL columns as empty elements?

I'd really like to be able to have full control over how my XML is formatted/generated.

Thanks,
Jacek

with LiveSQL Test Case:

https://livesql.oracle.com/apex/livesql/file/content_ET2CD8U5GO17VY2H0CJ8REEWL.html

and Chris said...

You can use dbms_xmlgen.restartQuery to reprocess the query. This will pick up your new NLS settings after you run it:

declare
  c sys_refcursor;
  ctx  number;
  xdoc xmltype;
begin
  execute immediate q'[alter session set nls_date_format = 'yyyy-mm-dd']';
  open c for select sysdate from dual;
  execute immediate q'[alter session set nls_date_format = 'yyyy-mm-dd"T"hh24:mi:ss']';
  ctx := dbms_xmlgen.newContext(c);
  dbms_xmlgen.restartQuery(ctx);
  xdoc := dbms_xmlgen.getxmltype(ctx);  
  dbms_output.put_line(xdoc.getclobval());
  
  execute immediate q'[alter session set nls_date_format = 'dd-MON-yyyy']';
  dbms_xmlgen.restartQuery(ctx);
  xdoc := dbms_xmlgen.getxmltype(ctx);  
  dbms_output.put_line(xdoc.getclobval());
end;
/

<ROWSET>
 <ROW>
  <SYSDATE>2017-04-11T02:51:45</SYSDATE>
 </ROW>
</ROWSET>

<ROWSET>
 <ROW>
  <SYSDATE>11-APR-2017</SYSDATE>
 </ROW>
</ROWSET>

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_xmlgen.htm#ARPLS69862

I'm no XML expert, but it looks like the "show|hide defaults" option of XMLserailize may help you with displaying null elements (if you have a stylesheet):

http://docs.oracle.com/database/122/SQLRF/XMLSERIALIZE.htm#SQLRF06231

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

restartQuery os not stable to use with all cursors

Jacek Gebal, April 11, 2017 - 10:06 pm UTC

Hi,
I've already tried to use dbms_xmlgen.restartQuery and it's a no-go as it fails with many/most of scalar PLSQL data-types in bind variables.

Can you point me to any other way to address my original query?

The below example illustrates that dbms_xmlgen fails to retrieve cursor after restartQuery with bind variables on 11g r2, 12.1 and 12.2

declare
  gc_blob      blob := to_blob('123');
  gc_clob      clob := to_clob('abc');
  gc_date      date := sysdate;
  gc_ds_int    interval day to second := numtodsinterval(1.12345678912, 'day');
  gc_num       number := 123456789.1234567890123456789;
  gc_ts        timestamp := to_timestamp_tz('2017-03-30 00:21:12.123456789 cet','yyyy-mm-dd hh24:mi:ss.ff9 tzr');
  gc_ts_tz     timestamp with time zone := to_timestamp_tz('2017-03-30 00:21:12.123456789 cet','yyyy-mm-dd hh24:mi:ss.ff9 tzr');
  gc_ts_ltz    timestamp with local time zone := to_timestamp_tz('2017-03-30 00:21:12.123456789 cet','yyyy-mm-dd hh24:mi:ss.ff9 tzr');
  gc_varchar   varchar2(4000) := 'a varchar2';
  gc_ym_int    interval year to month := numtoyminterval(1.1, 'year');
  l_ctx  integer;
  l_xml  xmltype;
  l_crsr sys_refcursor;
begin
    open l_crsr for
      select
--               gc_blob some_blob,  -- Using BLOB PLSQL variable with restartQuery disconnects the session
--               gc_clob some_clob,  -- Using CLOB PLSQL variable with restartQuery disconnects the session
             gc_date some_date,
--              gc_ds_int some_ds_interval, --Using INT_DS causes: ORA-01866: the datetime class is invalid
             gc_num some_nummber,
--              gc_ts some_timestamp,         --Using TS causes: ORA-01866: the datetime class is invalid
--              gc_ts_tz some_timestamp_tz,   --Using TS_TZ causes: ORA-01866: the datetime class is invalid
--              gc_ts_ltz some_timestamp_ltz, --Using TS_LTZ causes: ORA-01866: the datetime class is invalid
             gc_varchar some_varchar2,
--              gc_ym_int some_ym_interval, --Using INT_YM causes: ORA-01866: the datetime class is invalid
             systimestamp,
             localtimestamp,
             interval '1 23:56:55.123' day to second int_ds,
             interval '1-4' year to month int_ym,
             1 a_num
        from dual;

  l_ctx := dbms_xmlgen.newContext(l_crsr);
  --The cursor is processed well with all bind variables, if the query is not restarted.
  dbms_xmlgen.restartQuery(l_ctx);
  l_xml := dbms_xmlgen.getxmltype(l_ctx);
  dbms_xmlgen.closecontext(l_ctx);
  close l_crsr;

  dbms_output.put_line(l_xml.getclobval());

--   execute immediate q'[alter session set nls_date_format = 'dd-MON-yy']';
end;

April 12, 2017 - 12:58 pm UTC

When you open a cursor the result set is fixed at that point in time. This ensures you get a read consistent view of your data.

So if restartQuery isn't working, I don't think there's a way around this...

Magic with cursors and DBMS_XMLGEN

Jacek Gebal, April 12, 2017 - 11:06 am UTC

There is some magic going on in DBMS_XMLGEN.
In my previous example, the XMLGEN was failing on bind variables when using restartQurery.

It is however working perfectly fine, when we take such cursor and pass it into another cursor.

Magic!?

declare
  gc_blob      blob := to_blob('123');
  gc_clob      clob := to_clob('abc');
  gc_date      date := sysdate;
  gc_ds_int    interval day to second := numtodsinterval(1.12345678912, 'day');
  gc_num       number := 123456789.1234567890123456789;
  gc_ts        timestamp := to_timestamp_tz('2017-03-30 00:21:12.123456789 cet','yyyy-mm-dd hh24:mi:ss.ff9 tzr');
  gc_ts_tz     timestamp with time zone := to_timestamp_tz('2017-03-30 00:21:12.123456789 cet','yyyy-mm-dd hh24:mi:ss.ff9 tzr');
  gc_ts_ltz    timestamp with local time zone := to_timestamp_tz('2017-03-30 00:21:12.123456789 cet','yyyy-mm-dd hh24:mi:ss.ff9 tzr');
  gc_varchar   varchar2(4000) := 'a varchar2';
  gc_ym_int    interval year to month := numtoyminterval(1.1, 'year');
  l_ctx  integer;
  l_xml  xmltype;
  l_crsr sys_refcursor;
  l_crsr_new sys_refcursor;
begin
  open l_crsr for
    select
      gc_blob some_blob,  -- Using BLOB PLSQL variable with restartQuery disconnects the session
      gc_clob some_clob,  -- Using CLOB PLSQL variable with restartQuery disconnects the session
      gc_date some_date,
      gc_ds_int some_ds_interval, --Using INT_DS causes: ORA-01866: the datetime class is invalid
      gc_num some_nummber,
      gc_ts some_timestamp,         --Using TS causes: ORA-01866: the datetime class is invalid
      gc_ts_tz some_timestamp_tz,   --Using TS_TZ causes: ORA-01866: the datetime class is invalid
      gc_ts_ltz some_timestamp_ltz, --Using TS_LTZ causes: ORA-01866: the datetime class is invalid
      gc_varchar some_varchar2,
      gc_ym_int some_ym_interval, --Using INT_YM causes: ORA-01866: the datetime class is invalid
      systimestamp,
      localtimestamp,
      interval '1 23:56:55.123' day to second int_ds,
      interval '1-4' year to month int_ym
    from dual
    connect by level <= 2;

  --Here is a bit of magic.
  --If I pass the cursor to another cursor, then the restartQuery works perfectly fine with all bind variables.  
  open l_crsr_new for select l_crsr data from dual;
  l_ctx := dbms_xmlgen.newContext(l_crsr_new);

  execute immediate q'[alter session set nls_date_format = 'yyyy-mm-dd"T"hh24:mi:ss']';
  dbms_xmlgen.restartQuery(l_ctx);
  l_xml := dbms_xmlgen.getxmltype(l_ctx);
  dbms_xmlgen.closecontext(l_ctx);
  close l_crsr;
  close l_crsr_new;

  dbms_output.put_line(l_xml.getclobval());
  execute immediate q'[alter session set nls_date_format = 'dd-MON-yy']';
end;
/

April 12, 2017 - 12:58 pm UTC

Magic indeed! I'm not sure what's causing this. You may be best taking this up with support.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

restartQuery os not stable to use with all cursors

Magic with cursors and DBMS_XMLGEN

More to Explore

CLOB/BLOB/etc

Cursors to XML, Date/Time formatting and null columns

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

restartQuery os not stable to use with all cursors

Magic with cursors and DBMS_XMLGEN

More to Explore

CLOB/BLOB/etc