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

21 comments:

  1. I read about it in documentation(i early answer on this question in RuOUG):

    In releases prior to Oracle Database 10g Release 2, the database settings for date and timestamp formats were used for XML, instead of the XML Schema standard formats. You can reproduce this previous behavior by setting the database event 19119, level 0x8, as follows:

    ALTER SESSION SET EVENTS '19119 TRACE NAME CONTEXT FOREVER, LEVEL 0x8';

    ReplyDelete
  2. @xtender: Thanks for your reply but it doesn't apply as this is related to the XML% functions like XMLELEMENT, XMLFOREST etc. For many reasons I'm using DBMS_XMLGEN and must continue to do so as I don't want to place the burden of converting the query to XML on the caller -- that's my code's task.

    ReplyDelete
  3. @Finn Ellebaek Nielsen,
    Please try this code. Is normal output?
    If yes, then may be just wrap your queries into outer select?

    ReplyDelete
  4. Steven, could you make available tags "code" or "pre" in comments? Also would be great to add sintax highlighter into plsqlchallenge and this blog. Such as on http://source.virtser.net http://pastebin.com or http://paste.org.ru

    ReplyDelete
  5. Hi Iudith

    Thank you very much for your comment -- I've broken them up into smaller parts and will post them below.

    Cheers

    Finn

    ReplyDelete
  6. On behalf of Iudith Mentzel:

    Hello Steven, Finn,

    I performed the following small test in the SCOTT/TIGER schema, in Oracle11gR1,
    using SQL*PLUS:

    VAR c VARCHAR2(4000);

    1.
    The first test shows that indeed the DBMS_XMLGEN seems NOT to obey the date format set AFTER opening
    a REF CURSOR

    exec dbms_session.set_nls('NLS_DATE_FORMAT','''DD/MM/YYYY HH24:MI:SS''');

    rem - here the XML shows the date format set BEFORE opening the ref cursor

    declare
    cv sys_refcursor;

    Ctx DBMS_XMLGEN.ctxHandle;

    l_xmltype XMLTYPE;
    l_clob CLOB;

    begin
    open cv for select empno, ename, hiredate from emp where ROWNUM < 5;

    dbms_session.set_nls('NLS_DATE_FORMAT','''DD "of" MONTH, YYYY''');

    Ctx := DBMS_XMLGEN.newContext(cv);

    DBMS_XMLGEN.setNullHandling(Ctx, DBMS_XMLGEN.EMPTY_TAG);

    l_xmltype := DBMS_XMLGEN.getXMLTYPE(Ctx) ;

    l_clob := l_xmltype.getCLOBVal() ;

    :c := l_clob;
    end;
    /

    print c

    C
    -----------------------------------------------------------------


    7369
    SMITH
    17/12/1980 00:00:00


    7499
    ALLEN
    20/02/1981 00:00:00


    7521
    WARD
    22/02/1981 00:00:00


    7566
    JONES
    02/04/1981 00:00:00

    ReplyDelete
  7. On behalf of Iudith Mentzel:

    2. Now I tried using the DBMS_XMLGEN.RestartQuery, trying the same date format as above,
    after first resetting:

    exec dbms_session.set_nls('NLS_DATE_FORMAT','''DD/MM/YYYY HH24:MI:SS''');

    And now I receive an error, which did not happen on the first test !

    declare
    cv sys_refcursor;

    Ctx DBMS_XMLGEN.ctxHandle;

    l_xmltype XMLTYPE;
    l_clob CLOB;

    begin
    open cv for select empno, ename, hiredate from emp where ROWNUM < 5;

    dbms_session.set_nls('NLS_DATE_FORMAT','''DD "of" MONTH, YYYY''');

    Ctx := DBMS_XMLGEN.newContext(cv);

    DBMS_XMLGEN.restartQuery(Ctx);

    DBMS_XMLGEN.setNullHandling(Ctx, DBMS_XMLGEN.EMPTY_TAG);

    l_xmltype := DBMS_XMLGEN.getXMLTYPE(Ctx) ;

    l_clob := l_xmltype.getCLOBVal() ;

    :c := l_clob;

    end;
    /
    declare
    *
    ERROR at line 1:
    ORA-19202: Error occurred in XML processing
    ORA-01801: date format is too long for internal buffer
    ORA-06512: at "SYS.DBMS_XMLGEN", line 237
    ORA-06512: at "SYS.DBMS_XMLGEN", line 271
    ORA-06512: at line 20

    This just showed me that something "has moved" in the XML processing when adding that procedure call.

    ReplyDelete
  8. On behalf of Iudith Mentzel:

    3. The third test tries to use a date format without any blanks inside, also after an initial reset:

    exec dbms_session.set_nls('NLS_DATE_FORMAT','''DD/MM/YYYY HH24:MI:SS''');

    declare
    cv sys_refcursor;

    Ctx DBMS_XMLGEN.ctxHandle;

    l_xmltype XMLTYPE;
    l_clob CLOB;

    begin
    open cv for select empno, ename, hiredate from emp where ROWNUM < 5;

    dbms_session.set_nls('NLS_DATE_FORMAT','''DD"of"MONTH,YYYY''');

    Ctx := DBMS_XMLGEN.newContext(cv);

    DBMS_XMLGEN.restartQuery(Ctx);

    DBMS_XMLGEN.setNullHandling(Ctx, DBMS_XMLGEN.EMPTY_TAG);

    l_xmltype := DBMS_XMLGEN.getXMLTYPE(Ctx) ;

    l_clob := l_xmltype.getCLOBVal() ;

    :c := l_clob;

    end;
    /

    PL/SQL procedure successfully completed.

    And the result is:

    print c

    C
    ---------------------------------------------------------


    7369
    SMITH
    17ofDECEMBER ,1980


    7499
    ALLEN
    20ofFEBRUARY ,1981


    7521
    WARD
    22ofFEBRUARY ,1981


    7566
    JONES
    02ofAPRIL ,1981

    ReplyDelete
  9. On behalf of Iudith Mentzel:

    So, this seems to be working :) :) :) !

    I am not an XML specialist, I just simply found this in the 10gR2 documentation.

    I wonder what exactly are the implications of performing a "RestartQuery" on an XML context
    that is already bound to an open ref cursor.

    As far as I understand, this means reexecuting the query (that is, locating the result set)
    an additional time, after already performed once by the OPEN statement on the Ref Cursor.

    From this small test it is hard to see the full implications.

    While this seems to be a possible technical solution to your problem, I don't know whether it is satisfactory.

    If we compare the behavior of the XML to that of a ref cursor itself, it looks like the XML
    has indeed a bug related to the date format.
    I mean that if you simply open a ref cursor and
    AFTER this you set the NLS_DATE_FORMAT, then
    the rows retrieved from the ref cursor will obey the new format, which does not happen with the XML.

    If thinking it over, I cannot say definitely whether this is intended behavior or really a bug.

    Usually, when you want a specific date format,
    you set it BEFORE opening any cursor,
    that is, you create your database cursor that the ref cursor will point to already in a given NLS
    environment that does not change.

    I am not completely sure whether the intended behavior is that exhibited by the ref cursor itself
    or that of the XML, but for sure an inconsistency between the two does exist.

    Best Regards,
    Iudith Mentzel

    ReplyDelete
  10. Dear Iudith Mentzel

    Thank you very much for your solution. Pure genius, why didn't I think of calling DBMS_XMLGEN.RESTARTQUERY :-)?

    However, there is a side effect with the solution and it changes the values of D_TC, T1_TC and T2_TC from

    01.02.2011 14:25:30
    01.02.2011 14:25:30.123456789
    01.02.2011 14:25:30.123456789 +02:00

    (TO_CHAR without the date/timestamp format using the NLS settings in place when opening the cursor) to

    2011-02-01T14:25:30
    2011-02-01T14:25:30.123456789
    2011-02-01T14:25:30.123456789+02:00

    in fact the XML Schema compliant versions. But it's a little more tricky than that. Because if your NLS_DATE_FORMAT in place when opening the query is shorter than 19 characters (the length of values conforming to 'yyyy-mm-dd"T"hh24:mi:ss') the value is truncated, eg:

    nls_date_format = 'dd.mm.yy'

    D_TC will have a value of '2011-0'

    This makes me a little uneasy with the solution: Oracle uses the new NLS_DATE_FORMAT but truncated to the length of the previous NLS_DATE_FORMAT.

    First of all I'm a little worried 1. that this smells of C pointers going off, making the Oracle kernel code unstable but also 2. that the semantics of TO_CHAR calls suddenly being changed. I didn't consider 2. before but I now realize that it's also a problem with my XML_GEN package used with dynamic SQL.

    You could argue that it's bad practice to use TO_CHAR without a specific date/timestamp/number etc format but developers do it anyway.

    I'll think some more about this. If no better solution comes up you've definitely won the challenge :-).

    Best regards

    Finn

    ReplyDelete
  11. Update: Turns out that depending on how this is called it can lead to ORA-01801 if the NLS_DATE_FORMAT is shorter than the XML Schema compliant one, but only on 11.1. Works OK on 11.2. Seems to me that it's very unstable.

    ReplyDelete
  12. Hello Finn,
    I would have taken it for granted that you will make a deep research around this :) :)

    Yes, you are right, when using TO_CHAR with a DATE column WITHOUT specifying a format,
    this means that the intention is to have Oracle convert the database DATE value to a CHAR value
    using the NLS_DATE_FORMAT currently in effect.

    I guess that here by "current NLS format"
    the database means the format that is in effect
    when parsing/executing the query.

    Maybe it could be interesting to check this
    by using DBMS_SQL for separating the PARSE phase from the EXECUTE phase, and performing
    a DBMS_UTILITY.SET_ENV between the parse and execute calls to change the NLS_DATE_FORMAT.

    Anyway, since Oracle "knows" that it should return CHARACTER data from the database here, it should make "all the preparations" to return this data correctly, so it is the database that performs the (implicit) conversion.
    Think for example of the case when the SAME
    TO_CHAR(date_column) should display different strings in different languages.

    On the other hand, when you retrieve a DATE value, then this value is in fact composed of several numbers (integers), so the (implicit) conversion is somehow "left to the hands of the client/caller", that receives the output.

    I agree with you that using TO_CHAR on a date value without specifying a format is NOT a good practice, except if the author knows how to control exactly the format that he intends to receive, best by explicitly setting the NLS_DATE_FORMAT, usually BEFORE opening the cursor, be it even for the simple reason to make the code independent of any default setting
    inherited from somewhere else which might change over time.

    So, I think that instead of giving up the whole idea, you could maybe better adopt the "garbage in garbage out" paradigm for those that use such TO_CHAR without an explicit format ...

    In fact, your code only intends to control DATE-s
    and TIMESTAMP formats, NOT character string formats, so, in my opinion, TO_CHAR should "by definition" be considered as controlled by the caller and not by the procedure code itself.

    I was just wondering what are the implications of the RestartQuery call from a performance point of view, if the query is indeed supposed to return very large result sets.

    Anyway, it is a nice idea trying to encapsulate the XML stuff and make the callers' lives easier ...

    Thanks a lot & Best Regards,
    Iudith Mentzel

    ReplyDelete
  13. Hi all,

    Whenever I doubt if the result of a query or pl/sql execution differs from what I would expect, I read the documentation. Sometimes I even read the documentation first.
    Getting to the point: when reading the documentation even convinces me more that I'm running into a bug, I consult Oracle Support ( formerly metalink).
    Even that might leave me with empty hands, which leaves another choice: creating a service request on Oracle Support.
    both Finn's original example and iudith's analysis demonstrate the problem quite clearly and are perfectly usable to upload to Oracle Support.
    I'm curious what would be the answer. Or do 'we' already have such an answer from Oracle Support ?

    Best Regards,
    Rob van den Berg.

    ReplyDelete
  14. @xtender: Not sure I follow your suggestion. The issue I'm facing is with REF CURSORS, not dynamic SELECT statements (DBMS_XMLGEN behaves correctly here). So how would I wrap up my REF CURSOR in another REF CURSOR? It's a series of calls to various DBMS_XMLGEN procedures/functions in order to get the options right and work on a REF CURSOR, so you won't be able to do this in SQL. Please advise.

    ReplyDelete
  15. @Rob. You're absolutely right. I'm trying to work around an Oracle issue, my workaround has severe side effects and only works on Oracle 10.1 and 11.2, not 10.2 and not 11.1 (throws ORA-01801).

    But even though Oracle could be convinced to fix this for 10.1, 10.2, 11.1 and 11.2:

    1. It would not be fixed for 10.2 XE.
    2. It would take months before the patches were available.
    3. It would take even longer before the patches would be compiled into the next patchset (many clients I've worked with are not happy installing one-off patches).
    4. The solution would still have severe side effects (it changes the semantics of TO_CHAR/TO_DATE/TO_TIMESTAMP etc without specifying a format model).

    So at this time I'm leaning towards another solution entirely, one of the following alternatives:

    1. Write my own replacement for DBMS_XMLGEN. This is actually quite easy in a Java stored procedure.

    2. a. Fetch all the rows into an ANYDATASET. b. Change the NLS settings. c. Open a new REF CURSOR against the ANYDATASET. d. Call DBMS_XMLGEN on the REF CURSOR.

    1. Seems like a redundant effort seen that DBMS_XMLGEN is there (with its issues) and 1. would rule out 10.2 XE.

    2. Is doable for 11.1 and newer but very difficult for 10.1 and 10.2.

    Other solutions are still very welcome :-).

    Cheers

    ReplyDelete
  16. Thank you very much for this feature. This would definitely provide a best opportunity to learn.

    Wondering if there could be occasional quizzes without multiple choice options and asking players to provide the answer. Just an idea, may be something close to this could be implemented.

    One more suggestion, I just got a couple of single answer quizzes wrong and my score dropped significantly :( Reason, not careful enough. Is it possible to publish a quiz having 10 or may be greater or less questions every month that can be kept open for few days? And those that answer all of them correct get a significant score boost. This could be an opportunity to those that got wrong mainly due to carelessness and also those that missed many quizzes.

    Sorry, some of the points may not be relevant to this thread but thought of posting them all at one place.

    Best regards,
    Ramesh

    ReplyDelete
  17. Hello Finn,

    Your number 2 seems interesting, I just started to think of something similar around the lines
    of using the possibility to convert a REF CURSOR
    to a DBMS_SQL (integer) cursor using
    DBMS_SQL.TO_CURSOR_NUMBER and then using
    DBMS_SQL.DESCRIBE_COLUMNS to learn each column's datatype, a.s.o.

    But I think it gets too complicated, or, anyway,
    far too complicated as a means to just work-around a bug of DBMS_XMLGEN ... supposing that it is a bug indeed ...

    This whole issue raises at least a few questions:

    1. If XML does indeed have strict rules about which DATE formats it allows, then why at all
    is one supposed to use his session's NLS settings
    ( setting them accordingly ), instead of having
    DBMS_XMLGEN automatically apply its own "internal" formats ?

    On the other hand, if the XML does not have any restrictions regarding the DATE formats,
    then probably the bug should be corrected by Oracle so that the behavior to be consistent with that of a ref cursor.

    2. Another limitation that just arises from your idea is that DBMS_XMLGEN is missing an additional
    overload for the NEWCONTEXT function,
    that would accept a PL/SQL array of data (ideally of ANY datatype, including object types, a.s.o. ), besides the existing overloads
    that accept a query or a ref cursor.

    Not few of the difficulty with implementing your idea is that once you have the data stored in some (pl/sql) datastructure, you should "remake it" into some cursor for being able to call the
    DBMS_XMLGEN subprograms.

    Just a few more thoughts ... not another idea in the meantime ...

    Best Regards,
    Iudith

    ReplyDelete
  18. @Iudith: Your comments on converting the REF CURSOR to a DBMS_SQL cursor and then constructing an ANYDATASET is the easy part but it's only supported on 11.1 and newer. Adrian Billington has a brilliant example here.

    XML Schema has strict rules on datetime formatting. If you don't need to treat datetime values as such, you can use any format and the values will be treated as text, which in my view is not optimal. Once and for all there is an established standard format and Oracle disregards this by allowing any format to be used. At least we should be allowed to choose the standard formats without any side effects. I guess Oracle understood this by changing the XML% functions in 10.2 to work with the standard formats (refer to the first comment from xtender).

    My package XML_GEN found on my own blot (see link in my blot post) has overloads for any object type and collection instance. It doesn't work for ANYTYPE, ANYDATA and ANYDATASET as DBMS_XMLGEN and the XML% functions don't support these data types but "normal" object types will work with my wrapper code.

    Cheers

    ReplyDelete
  19. Sorry, I'm contradicting myself -- ANYDATA works, but ANYTYPE and ANYDATASET don't. I guess I should change the wrapper in XML_GEN for ANYDATASET to try to open a REF CURSOR on TABLE(myanydataset). I'll have a look at that tomorrow.

    ReplyDelete
  20. The challenge is over. Unfortunately my spec wasn't clear enough and my code turned out to have a severe side effect, so even though Iudith Mentzel came up with what seemed a useful solution, it turned out that it doesn't work on 10.2 and 11.1 if the current setting of NLS_DATE_FORMAT is shorter than what we need. And because of the side effect of changing the semantics of TO_CHAR/TO_DATE/TO_TIMESTAMP function calls without a format mask, no solution was found.

    However, I will still reward Iudith Mentzel for her contribution -- she is the winner of this challenge :-)!

    ReplyDelete