DBMS_XMLGEN.GETXML ​​- How to set up a date format

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

We use dbms_xmlgen.getxml utility to use sql query generation xml, these queries get data from about 10-15 related tables.

By default, the date format is generated in dd-MMM-yy format. Is there any way we can do it in dbms_xmlgen.getxml Set dateformat in the utility.

Notes –

>From the orac that called this process It is not feasible for le users to use alter session nls_date_format.
>In addition, we want to avoid using the to_date function for each field, because the data is obtained from about 10-15 related tables, and it will reduce performance to use to_date to handle nearly 50 date fields.
> dbms_xmlgen.getxml is the first choice because it has higher performance than other similar software packages.

Thank you.

The limitation you listed is difficult to solve because dbms_xmlgen does not provide a way to set the date format. It uses nls_date_format. dbms_xmlquery allows you to specify the date format, but it is not high-performance.

You can try to add a column to the table to store the formatted date. Update the display column for inserts, updates, etc.

You can also try to wrap the inner selection in another in a smaller collection

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