dbms_xmlgen.getxml - How to set a date format

问题:

We are using dbms_xmlgen.getxml utility to generate xml using sql queries which fetches data from almost 10-15 related tables.

By default, date format is getting generated in dd-MMM-yy format. Is there any way we can set dateformat in dbms_xmlgen.getxml utility.

Notes -

1. It is not feasible to use alter session nls_date_format from oracle user who calls this procedure.
2. Also, We want to avoid using to_date function for each and every field since data is getting fetched from almost 10-15 related tables and it can degrade performance to use to_date for almost 50 date fields.
3. dbms_xmlgen.getxml was preferred as it is highly perfomant than other comparable packages.

Thanks.

回答1:

The restrictions you have listed are difficult to work around because dbms_xmlgen does not provide a way to set a date format. It uses nls_date_format. dbms_xmlquery does allow you to specify a date format, but is not as performant.

You could try adding columns to the tables to store the formatted dates. Update the display column on insert, update, etc.

You could also try wrapping your inner select inside another select that runs the to_date function on a smaller set.

    dbms_xmlgen.getxml('
     select to_date(date_column_1, 'your/date/format') from (
        your original query here
      )');