04 February 2011

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

03 February 2011

January 2011 Winners

We've got an especial;y big set of prizes for January, a way of welcoming everyone back to the PL/SQL Challenge. Many thanks to Enciva, ODTUG and MaltMaps for sponsoring several of the prizes.

Top Ranking Winners
_Nikotin wins Amazon.com US$100 Gift Card
(_Nikotin was the only player who achieved 100% correctness in January)

High Ranking Winners
Rosemary wins US$50 Amazon.com gift card, provided by Enciva.com
Thierry Poels wins US$50 Amazon.com gift card, provided by Enciva.com
(picked randomly from all players scoring at least 95% correctness in January)

Participation Raffle Winners
Sl. Mav wins One Year Membership in ODTUG, courtesy of ODTUG
GilRoy wins One Year Membership in ODTUG, courtesy of ODTUG
macpac wins One Year Membership in ODTUG, courtesy of ODTUG
Alwyn D'Souza wins O'Reilly Media Oracle eBook
Patrick Barel wins O'Reilly Media Oracle eBook
AVB wins O'Reilly Media Oracle eBook
Tamil wins O'Reilly Media Oracle eBook
Dmitry Pushkashu wins O'Reilly Media Oracle eBook
An Verherstraeten wins O'Reilly Media Oracle eBook
William (Yi) Zhu wins O'Reilly Media Oracle eBook
igstef wins O'Reilly Media Oracle eBook
Mensi wins O'Reilly Media Oracle eBook
Mojibul Hoque wins O'Reilly Media Oracle eBook
John Vaughan wins Malt Whiskey Yearbook, sponsored by MaltMaps
Zoran Krekic wins Nameplate Signed by Steven Feuerstein
Yaroslav wins Nameplate Signed by Steven Feuerstein
Wim van Valenberg wins Nameplate Signed by Steven Feuerstein
rpworld wins Nameplate Signed by Steven Feuerstein
Youseedat wins Nameplate Signed by Steven Feuerstein
buttersa wins Nameplate Signed by Steven Feuerstein
Wendy T wins Nameplate Signed by Steven Feuerstein
Shakeel Ahmad wins Nameplate Signed by Steven Feuerstein
Alpana kumari wins Nameplate Signed by Steven Feuerstein
Jerry Bull wins Nameplate Signed by Steven Feuerstein

02 February 2011

Explanation of scoring for 1 February (only one choice correct quiz)

 Several players wrote with a complaint about their scoring for the 1 February quiz. Here's a typical comment: "Hi, I got a 0 score for February 1st, is this supposed to happen? I answered that none was correct - though I have not found that there was a correct answer I successfully saw the other 3 as incorrect. "

With 1.9 of PL/SQL Challenge, that is as of 31 January 2011, some quizzes are "one choice only can be correct". This happens when the choices offered as answers are all clearly mutually exclusive. In this case, you can only check one box when you submit your answer. That one box might be "No choices are correct."

If you try to submit an answer with more than one box checked, you will see an error message.

Scoring for such quizzes is done on an "all or nothing" basis. If you checked the right box, you get 100%. If not, you get 0%. That is, there are only two different scores you can get, but your weighted score will vary based on the time it takes you to submit an answer.

30 January 2011

Even our assumptions help develpers!

I thought I would share this email received from a player last week:

Steven,

I took an extra moment today to look as the updated assumptions on the PL/SQL Quiz and found the DBMS_SESSION notes really useful to resolve a curious problem we’ve been having ...

The problem – for various reasons – came down to the simple reproducible SQL statement:-
select * from dual
where regexp_like('aa', '[ -~]');
If you run the following SQL:-
exec DBMS_SESSION.SET_NLS ('NLS_LANGUAGE'  ,'DUTCH');
then the regexp_like command fails with:
ORA-12728: invalid range in regular expression
Our problem was that, for the DBMS_JOB that uses this regular expression, the database server is running on a Dutch system so that task does not run automatically and has to be done manually. We can now use DBMS_SESSION to set it to American to enable it to run automatically.

Now it would be rather evil to pose a PL/SQL Quiz on this topic ...

I’ve no doubt there’s an explanation for the problem buried somewhere in how Oracle handles ‘~’ as a combining character in a UTF-8 database when the language used for the session is one that has such combined characters ...

Anyway – thought it would provide some amusement and I wanted to say “thanks”.
Tim