问题描述
我们使用dbms_xmlgen.getxml实用程序通过使用sql查询生成xml,这些查询从大约10-15个相关表中获取数据。
We are using dbms_xmlgen.getxml utility to generate xml using sql queries which fetches data from almost 10-15 related tables.
默认情况下,生成日期格式在 dd-MMM-yy
格式。有任何方法我们可以在dbms_xmlgen.getxml实用程序中设置dateformat。
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.
注 -
- 从调用此过程的oracle用户使用alter session nls_date_format是不可行的。
- 此外,我们希望避免为每个字段使用to_date函数,因为数据正在获取从几乎10-15个相关表格,并且它可以降低性能以使用to_date近50个日期字段。
- 优选dbms_xmlgen.getxml,因为它比其他类似软件包具有很高的性能。 >
- It is not feasible to use alter session nls_date_format from oracle user who calls this procedure.
- 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.
- dbms_xmlgen.getxml was preferred as it is highly perfomant than other comparable packages.
感谢。
推荐答案
已列出很难解决,因为dbms_xmlgen不提供设置日期格式的方法。它使用nls_date_format。 dbms_xmlquery 允许您指定日期格式,但不是一样高效。
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.
您还可以尝试将内部选择包装在另一个选择中,并在较小的集合上运行to_date函数。
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
)');
这篇关于dbms_xmlgen.getxml - 如何设置日期格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!