A Different Kind of Challenge for Your Consideration

Over the next year, I plan to expand the kind of quizzes you can take and how you can participate in and learn from the PL/SQL Challenge. One idea I have long entertained is to publish real-world problems faced by developers and solicit solutions from players. This would be an excellent way to leverage the depth and experience of PL/SQL developers from around the world - and we could all learn in the process.

I don't think I will have this ready as a built-in feature of the website for at least 6 months. In the meantime, however, my friend and co-founder of the PL/SQL Challenge, Finn Ellebaek Nielsen, has encountered an obstacle in one of his development efforts and I agreed to post it here and solicit solutions.

Please address a description of all possible solutions in comments to this blog post, which Finn fill monitor.

Finn will choose the optimal solution and I will then announce a winner through the blog. I will also post the solution (perhaps multiple solutions) so that everyone can benefit from the effort. Finn is offering an Amazon gift card of USD 100 for the person who first submitted the optimal solution.

Regards, Steven

Problem: DBMX_XMLGEN Doesn't Respect current NLS_DATE_FORMAT for DATE Values of REF CURSORs

Here's the issue: DBMS_XMLGEN does not generate XML with correct format for DATE column values when called with a REF CURSOR, even though the NLS_DATE_FORMAT has been changed prior to calling DBMS_XMLGEN, but after the REF CURSOR has been opened. If NLS_DATE_FORMAT is changed prior to opening the REF CURSOR it works OK. I realize that Oracle allocates some resources when opening the cursor but oddly, it works correct for TIMESTAMP and TIMESTAMP WITH [LOCAL] TIME ZONE. It also works correctly when using DBMS_XMLGEN with dynamic SQL. I need this working for Oracle Database 10g Release 1 and newer, supporting Express Edition as well (so no Java stored procedures). Preferably, the solution is in PL/SQL, not using a callout in C/C++ as that would require access to C/C++ compilers on all sorts of platforms. Also, the solution cannot alter the REF CURSOR query, so suggesting converting the DATE columns using TO_CHAR is not an acceptable solution.

This challenge closes at Monday 14 February 2011 23:59:59 UTC.

The Desired Solution

Make iterations 1 and 3 in the SQL*Plus script below produce the XML for the D column in the correct format (the correct value '2011-02-01T14:25:30' instead of the incorrect value '01.02.2011 14:25:30'). It must be a generic solution, so using DBMS_DESCRIBE to describe the REF CURSOR columns and dynamically reformatting any DATE values is not good enough, also because we cannot be certain that the NLS_DATE_FORMAT in place when opening the REF CURSOR incorporates time values.

set serveroutput on format truncated
set long 100000
set pagesize 50000
set linesize 1000

alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
alter session set nls_timestamp_format = 'dd.mm.yyyy hh24:mi:ss.ff';
alter session set nls_timestamp_tz_format = 'dd.mm.yyyy hh24:mi:ss.ff tzh:tzm';

prompt Checks NLS formats

select sysdate,
       localtimestamp,
       systimestamp
from   dual;

declare
  --- REF CURSOR.
  rc sys_refcursor;
  --- DBMX_XMLGEN context.
  context dbms_xmlgen.ctxtype;

  /**
   * XML Schema compliant datetime format, no fraction, no time zone
   * (Oracle DATE).
   */
  xml_nls_date_format constant varchar2(23) :=
      'yyyy-mm-dd"T"hh24:mi:ss';
  /**
   * XML Schema compliant datetime format, with fraction, no time zone (Oracle
   * TIMESTAMP).
   */
  xml_nls_timestamp_format constant varchar2(27) :=
      'yyyy-mm-dd"T"hh24:mi:ss.ff9';
  /**
   * XML Schema compliant datetime format, with fraction, with time zone (Oracle
   * TIMESTAMP WITH [LOCAL] TIME ZONE).
   */
  xml_nls_timestamp_tz_format constant varchar2(34) :=
      'yyyy-mm-dd"T"hh24:mi:ss.ff9tzh:tzm';

  type nls_parameters_c is table of
  v$nls_parameters.value%type
  index by v$nls_parameters.parameter%type;

  --- Session NLS formats.
  session_nls_parameters nls_parameters_c;
  --- NLS Formats required for XML compliance.
  xml_nls_parameters nls_parameters_c;

  --- Query.
  query varchar2(1000);

  --- Generated XML.
  xml xmltype;
  xmlc clob;

/**
 * Wrapper for DBMS_OUTPUT.PUT_LINE, in order to be able to print text larger
 * than 255 characters for Oracle 10.1 and earlier.
 * @param   c       CLOB to be put on DBMS_OUTPUT.
 */

  procedure dbms_output_put_line(c in clob) as

    i integer := 1;
    j integer;
    l integer := nvl(length(c), 0);
    o integer;

  begin
    while i <= l loop
      -- Try to find next LF.
      j := instr(c, chr(10), i);
      if j = 0 or j - i >= 255 then
        -- Too long or no LF found, need to cut off.
        j := i + 255;
        o := 0;
      else
        o := 1;
      end if;
      dbms_output.put_line(substr(c, i, j - i));
      i := j + o;
    end loop;
    if l = 0 then
      dbms_output.put_line('');
    end if;
  end dbms_output_put_line;

/**
 * Sets specific NLS date/timestamp formats required for XML compliance.
 */

  procedure set_xml_nls_formats is
  pragma autonomous_transaction;

    needs_commit boolean := false;

  begin
    for nls in (
          select parameter,
                 value
          from   v$nls_parameters
          where  parameter like 'NLS_%FORMAT' and
                 parameter not like 'NLS\_TIME\_%' escape '\'
        ) loop
      session_nls_parameters(nls.parameter) := nls.value;
      if nls.value != xml_nls_parameters(nls.parameter) then
        -- Change NLS format for XML-compliance.
        dbms_session.set_nls(
          nls.parameter,
          '''' || xml_nls_parameters(nls.parameter) || ''''
        );

        needs_commit := true;
      end if;
    end loop;

    if needs_commit then
      commit;
    end if;
  end set_xml_nls_formats;

/**
 * Restores NLS date/timestamp formats as it was before setting up for XML
 * compliance.
 */

  procedure set_session_nls_formats is
  pragma autonomous_transaction;

    needs_commit boolean := false;
    nls_parameter v$nls_parameters.parameter%type;

  begin
    nls_parameter := xml_nls_parameters.first;
    while nls_parameter is not null loop
      if xml_nls_parameters(nls_parameter) !=
          session_nls_parameters(nls_parameter) then
        -- Restore original NLS format.
        dbms_session.set_nls(
          nls_parameter,
          '''' || session_nls_parameters(nls_parameter) || ''''
        );

        needs_commit := true;
      end if;

      nls_parameter := xml_nls_parameters.next(nls_parameter);
    end loop;

    if needs_commit then
      commit;
    end if;
  end set_session_nls_formats;

begin
  xml_nls_parameters('NLS_DATE_FORMAT') := xml_nls_date_format;
  xml_nls_parameters('NLS_TIMESTAMP_FORMAT') := xml_nls_timestamp_format;
  xml_nls_parameters('NLS_TIMESTAMP_TZ_FORMAT') := xml_nls_timestamp_tz_format;

  query :=
    q'[select 1 id,
           123.456 n,
           'abcDEF' vc,
           cast(timestamp'2011-02-01 14:25:30' as date) d,
           timestamp'2011-02-01 14:25:30.123456789' t1,
           timestamp'2011-02-01 14:25:30.123456789 +02:00' t2,
           to_char(cast(timestamp'2011-02-01 14:25:30' as date)) d_tc,
           to_char(timestamp'2011-02-01 14:25:30.123456789') t1_tc,
           to_char(timestamp'2011-02-01 14:25:30.123456789 +02:00') t2_tc
    from   dual]';

  for i in 1 .. 6 loop
    -- Iteration 1: Sets NLS after REF CURSOR has been opened. XMLTYPE returned.
    -- Iteration 2: Sets NLS before REF CURSOR has been opened. XMLTYPE returned.
    -- Iteration 3: Sets NLS after REF CURSOR has been opened. CLOB returned.
    -- Iteration 4: Sets NLS before REF CURSOR has been opened. CLOB returned.
    -- Iteration 5: Dynamic SQL. XMLTYPE returned.
    -- Iteration 6: Dynamic SQL. CLOB returned.

    if i in (2, 4) then
      set_xml_nls_formats;
    end if;

    if i in (1, 2, 3, 4) then
      open rc for query;
    end if;

    if i in (1, 3, 5, 6) then
      set_xml_nls_formats;
    end if;
    if i in (1, 2, 3, 4) then
      context := dbms_xmlgen.newcontext(rc);
    else
      context := dbms_xmlgen.newcontext(query);
    end if;
    dbms_xmlgen.setnullhandling(context, dbms_xmlgen.empty_tag);
    dbms_output.put_line('Iteration ' || i);
    if i in (1, 2, 5) then
      xml := dbms_xmlgen.getxmltype(context);
      dbms_output_put_line(xml.getclobval);
    else
      xmlc := dbms_xmlgen.getxml(context);
      dbms_output_put_line(xmlc);
    end if;
    dbms_xmlgen.closecontext(context);

    set_session_nls_formats;
  end loop;
end;
/

prompt Checks NLS formats

select sysdate,
       localtimestamp,
       systimestamp
from   dual;

The script will produce the following output for the anonymous PL/SQL block:

Iteration 1
<ROWSET>
 <ROW>
  <ID>1</ID>
  <N>123.456</N>
  <VC>abcDEF</VC>
  <D>01.02.2011 14:25:30</D>
  <T1>2011-02-01T14:25:30.123456789</T1>
  <T2>2011-02-01T14:25:30.123456789+02:00</T2>
  <D_TC>01.02.2011 14:25:30</D_TC>
  <T1_TC>01.02.2011 14:25:30.123456789</T1_TC>
  <T2_TC>01.02.2011 14:25:30.123456789 +02:00</T2_TC>
 </ROW>
</ROWSET>
Iteration 2
<ROWSET>
 <ROW>
  <ID>1</ID>
  <N>123.456</N>
  <VC>abcDEF</VC>
  <D>2011-02-01T14:25:30</D>
  <T1>2011-02-01T14:25:30.123456789</T1>
  <T2>2011-02-01T14:25:30.123456789+02:00</T2>
  <D_TC>2011-02-01T14:25:30</D_TC>
  <T1_TC>2011-02-01T14:25:30.123456789</T1_TC>
  <T2_TC>2011-02-01T14:25:30.123456789+02:00</T2_TC>
 </ROW>
</ROWSET>
Iteration 3
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <ID>1</ID>
  <N>123.456</N>
  <VC>abcDEF</VC>
  <D>01.02.2011 14:25:30</D>
  <T1>2011-02-01T14:25:30.123456789</T1>
  <T2>2011-02-01T14:25:30.123456789+02:00</T2>
  <D_TC>01.02.2011 14:25:30</D_TC>
  <T1_TC>01.02.2011 14:25:30.123456789</T1_TC>
  <T2_TC>01.02.2011 14:25:30.123456789 +02:00</T2_TC>
 </ROW>
</ROWSET>
Iteration 4
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <ID>1</ID>
  <N>123.456</N>
  <VC>abcDEF</VC>
  <D>2011-02-01T14:25:30</D>
  <T1>2011-02-01T14:25:30.123456789</T1>
  <T2>2011-02-01T14:25:30.123456789+02:00</T2>
  <D_TC>2011-02-01T14:25:30</D_TC>
  <T1_TC>2011-02-01T14:25:30.123456789</T1_TC>
  <T2_TC>2011-02-01T14:25:30.123456789+02:00</T2_TC>
 </ROW>
</ROWSET>
Iteration 5
<ROWSET>
 <ROW>
  <ID>1</ID>
  <N>123.456</N>
  <VC>abcDEF</VC>
  <D>2011-02-01T14:25:30</D>
  <T1>2011-02-01T14:25:30.123456789</T1>
  <T2>2011-02-01T14:25:30.123456789+02:00</T2>
  <D_TC>2011-02-01T14:25:30</D_TC>
  <T1_TC>2011-02-01T14:25:30.123456789</T1_TC>
  <T2_TC>2011-02-01T14:25:30.123456789+02:00</T2_TC>
 </ROW>
</ROWSET>
Iteration 6
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <ID>1</ID>
  <N>123.456</N>
  <VC>abcDEF</VC>
  <D>2011-02-01T14:25:30</D>
  <T1>2011-02-01T14:25:30.123456789</T1>
  <T2>2011-02-01T14:25:30.123456789+02:00</T2>
  <D_TC>2011-02-01T14:25:30</D_TC>
  <T1_TC>2011-02-01T14:25:30.123456789</T1_TC>
  <T2_TC>2011-02-01T14:25:30.123456789+02:00</T2_TC>
 </ROW>
</ROWSET>

So why do I bother? Because I've written a package XML_GEN that can be used to generated XML off dynamic queries, REF CURSORS, ANYDATA instances etc and I would like to avoid that callers need to call my package like this

declare
  rc sys_refcursor;
  xml xmltype;
begin
  xml_gen.set_xml_nls_formats;

  open rc for
  select sysdate
  from   dual;

  xml := xml_gen.from_ref_cursor(rc);
  xml_gen.set_session_nls_formats;
exception
  when others then
    xml_gen.set_session_nls_formats;
    raise;
end;
/

but instead this should suffice:

declare
  rc sys_refcursor;
  xml xmltype;
begin
  open rc for
  select sysdate
  from   dual;

  xml := xml_gen.from_ref_cursor(rc);
end;
/

Much cleaner and less error prone. I've written about this on my own blog: Converting Between Oracle Data and XML.

I hope the challenge is clear. If not, please ask.

Thanks in advance for your assistance and best of luck in winning the USD 100 gift card.

Best regards

Finn