Oracle SYS_REFCURSOR to XMLTYPE

create or replace function CUR2XML(pCur in out sys_refcursor, pCloseCursor in boolean default true) return xmltype
as
/*

    #1 Performance
    **************
    with setNLS/restoreNLS: 3,4
    without setNLS/restoreNLS: 2,75

    Script: 

    declare
      x xmltype;
      dSeconds number;
      dSeconds2 number := 0;
      testc sys_refcursor;
    begin
      for i in 1..1000
      loop
        open testc for
          select dbms_random.value num, dbms_random.string('p', 16) str, sysdate + dbms_random.value dat from dual connect by level < 10;
        dSeconds := dbms_utility.get_time/100;
        x := CUR2XML(testc);
        dSeconds := dbms_utility.get_time/100 - dSeconds;
        dSeconds2 := dSeconds2 + dSeconds;
      end loop;
      dbms_output.put_line(dSeconds2);
    end;
    /

    #2 Oracle bug fixes (magic)
    ***************************
    https://asktom.oracle.com/pls/asktom/f?p=100:11:::NO:RP:P11_QUESTION_ID:9534431900346311764

*/
  cXML_NLS_DATE_FORMAT         constant varchar2(23) := 'yyyy-mm-dd"T"hh24:mi:ss';
  cXML_NLS_TIMESTAMP_FORMAT    constant varchar2(27) := 'yyyy-mm-dd"T"hh24:mi:ss.ff9';
  cXML_NLS_TIMESTAMP_TZ_FORMAT constant varchar2(34) := 'yyyy-mm-dd"T"hh24:mi:ss.ff9tzh:tzm';
  
  type tNLS_PARAMETERS is table of v$nls_parameters.value%type index by v$nls_parameters.parameter%type;
  vSTORED_NLS_PARAMETERS tNLS_PARAMETERS;
  
  vRESULT xmltype;
  vHANDLE dbms_xmlgen.ctxHandle;
  
  procedure setNLS as
    PRAGMA AUTONOMOUS_TRANSACTION;
    
    vNEEDCOMMIT boolean := false;
  begin
    for rec in (
      select /*+ cursor_sharing_exact */ parameter,value,
        decode(parameter, 
          'NLS_DATE_FORMAT', cXML_NLS_DATE_FORMAT,
          'NLS_TIMESTAMP_FORMAT', cXML_NLS_TIMESTAMP_FORMAT,
          'NLS_TIMESTAMP_TZ_FORMAT', cXML_NLS_TIMESTAMP_TZ_FORMAT,
          null) newvalue
      from v$nls_parameters
      where parameter in ('NLS_DATE_FORMAT', 'NLS_TIMESTAMP_FORMAT', 'NLS_TIMESTAMP_TZ_FORMAT'))
    loop
      if rec.value <> rec.newvalue then
        vSTORED_NLS_PARAMETERS(rec.parameter) := rec.value;
        dbms_session.set_nls(rec.parameter, '''' || rec.newvalue || '''');
        vNEEDCOMMIT := true;
      end if;
    end loop;
    
    if vNEEDCOMMIT then
      commit;
    end if;
  end;
  
  procedure restoreNLS as
    PRAGMA AUTONOMOUS_TRANSACTION;
    
    vNEEDCOMMIT boolean := false;
    vPARAMETER  v$nls_parameters.parameter%type;
  begin
    vPARAMETER := vSTORED_NLS_PARAMETERS.first;
    
    while vPARAMETER is not null
    loop
      dbms_session.set_nls(vPARAMETER, '''' || vSTORED_NLS_PARAMETERS(vPARAMETER) || '''');
      vNEEDCOMMIT := true;
      vPARAMETER := vSTORED_NLS_PARAMETERS.next(vPARAMETER);
    end loop;
 
    if vNEEDCOMMIT then
      commit;
    end if;
  exception
    when NO_DATA_FOUND then
      null;  
  end;
  
begin
  setNLS;
  
  vHANDLE := dbms_xmlgen.newContext(pCur);
  begin
    dbms_xmlgen.restartQuery(vHANDLE);
    vRESULT := dbms_xmlgen.getXMLType(vHANDLE);
    dbms_xmlgen.closeContext(vHANDLE);
  exception
    when OTHERS then
      dbms_xmlgen.closeContext(vHANDLE);
      raise;
  end;
  
  restoreNLS;
  
  if pCloseCursor then
    close pCur;
  end if;
  
  return vRESULT;
exception
  when others then
    restoreNLS;
    raise;
end;