Dbms_xmlgen.getxml - How to set the date format

Question

We use the dbms_xmlgen.getxml utility to generate xml using SQL queries that extract data from almost 10-15 related tables.

By default, the date format is generated in the dd-MMM-yy format. Is there a way to set dateformat in the dbms_xmlgen.getxml utility.

Notes -

• Cannot use alter session nls_date_format from oracle user who calls this procedure.
• In addition, we want to avoid using the to_date function for each field, since the data is extracted from almost 10-15 related tables and can degrade performance in order to use to_date for almost 50 date fields.
• dbms_xmlgen.getxml was preferable because it was very permanent than other comparable packages.

Thanks.

Answer 1

The restrictions you specified are hard to get around because dbms_xmlgen does not provide a way to set the date format. It uses nls_date_format. dbms_xmlquery does allows you to specify the date format, but not as shown.

You can try adding columns to tables to store formatted dates. Refresh the display column when inserting, updating, etc.

You can also try wrapping your inner selection inside another selection 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 )');