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 theD 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 thisdeclare
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
