数字和日期格式:更改NLS_SESSION_PARAMETER无效吗? [英] Number and date format : altering NLS_SESSION_PARAMETER does not work?
问题描述
Oracle 11.2.0.3.0,APEX 4.1.1.00.23.
Oracle 11.2.0.3.0, APEX 4.1.1.00.23.
我们需要在应用程序中以FM999999999990.000
格式显示数字,以英文格式DD-MON-YYYY
显示日期.
We need to display numbers in our application with the format FM999999999990.000
and dates with the English format DD-MON-YYYY
.
即使应用程序语言要更改(法语,西班牙等),我们也总是需要这种格式的数字(组分隔符没有空格或逗号,而十进制分隔符是,即点.- 1254.010 )和日期(英语月份名称即2012年2月12日的3个首字母).
Even if the application language is going to change (french, spain, etc.), we always need this format for numbers (no space or comma for group separator, and a point for decimal separator, ie. -1254.010) and date (3 first letters from the English month name ie. 12-FEB-2012).
以下是我们正在使用的全球化属性(应用程序生成器->应用程序->编辑全球化属性):
Here are globalization attributes we are using (Application Builder -> Application -> Edit Globalization Attributes) :
- 应用主要语言:法语(法国)(法语)
- 源自以下应用程序的语言:会话
- 申请日期格式: DD-MON-YYYY
- Application Primary Language: French (France) (fr)
- Application Language Derived From: Session
- Application Date Format: DD-MON-YYYY
我无法使其按预期工作...
我仍然得到像-1254,01
这样的数字和类似12-FÉVR.-2012
这样的日期,而不是-1254.010
和12-FEB-2012
.
看来APEX会忽略任何更改会话的呼叫...
I cannot manage to make it work as expected...
I still get numbers like -1254,01
and dates like 12-FÉVR.-2012
instead of -1254.010
and 12-FEB-2012
.
It seems APEX ignore any call to alter session...
我尝试在"初始化PL/SQL代码"属性( Application Builder-> Application-> Edit Security Attributes )中输入以下代码,但没有任何输入成功:
I have tried to enter the following code in the "Initialization PL/SQL Code" attribute (Application Builder -> Application -> Edit Security Attributes) but without any success :
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS= ''.,'' ';
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_LANGUAGE = ''AMERICAN'' ';
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE = ''AMERICAN'' ';
END;
我有一个包含以下查询的报告,以查看参数是否正在更改:
I have a report with the following query to see if parameters are changing :
SELECT
a1.parameter as "Parameter",
a1.value as "Database value",
a2.value as "Instance value",
a3.value as "Session value"
FROM
nls_database_parameters a1
LEFT JOIN nls_instance_parameters a2 ON a1.parameter = a2.parameter
LEFT JOIN nls_session_parameters a3 ON a1.parameter = a3.parameter
ORDER BY
a1.parameter asc;
结果:
如您所见ALTER SESSION
通话没有任何改变...
As you see ALTER SESSION
calls don't change anything...
当我尝试在" Header 之前"应用程序过程中调用ALTER SESSION
时,会话似乎已更改(报告显示已修改的值),但报告中的日期和数字格式仍然错误和项目...
我已经尝试了"数字字段"项的"格式掩码"属性,但似乎也没有改变...
When I try ALTER SESSION
calls in a "Before Header" application process, session seems to be altered (report show modified values), but I still get wrong date and number format in my reports and items...
I have tried the "Format Mask" attribute of the "Number Field" items but it seems it does not change anything too...
使它起作用的唯一方法是在我从APEX调用的每个PL/SQL函数中调用ALTER SESSION
.对于报告,我可以使用数字/日期格式"列属性.
The only way I can get it to work is to call ALTER SESSION
in each PL/SQL function I call from APEX. And for reports I can use the "Number / Date Format" column attribute.
问题:我有什么办法可以更改整个应用程序会话的数字和日期参数?
QUESTION: Is there any way I can alter number and date parameters for the session for the whole application ?
当我在每个页面上运行以下 Header之前的过程:
When I run the following Before Header process on each page :
BEGIN
APEX_UTIL.SET_SESSION_LANG('fr');
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS= ''.,'' ';
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_LANGUAGE = ''AMERICAN'' ';
END;
报告显示会话已更改:
然后我用:
- 以上标题之前"过程.
- 显示NLS参数值的报告.
- 显示我的一个表中的日期和数字的报告.
- 源类型为"SQL查询"的文本字段项目可获取日期.
- 数字"字段项,其源类型为"SQL查询"以获取数字.
- 与JQuery绑定的按钮,该按钮调用一个应用程序,该应用程序调用一个PL/SQL过程,该过程从表中
htp.prn()
一个日期,然后填充我的文本字段项. - 与JQuery绑定的按钮,用于在表中的两个字段之间进行插入(使用应用程序等).
- The above Before Header process.
- A report to show NLS parameters values.
- A report that show date and numbers from one of my tables.
- A textfield items with a Source Type as "SQL Query" to get a date.
- A Number field item with a Source Type as "SQL Query" to get a number.
- A button bound with JQuery that calls an application process that calls a PL/SQL procedure that
htp.prn()
a date from a table, then fill my textfield item. - A button bound with JQuery that do an insert (using application process, etc.) of the two fields in a table.
页面加载后,报告显示会话已更改,并且项目和报告中的数字和日期格式正确. 插入"按钮可以毫无问题地进行插入.
Once the page is loaded, report shows that the session has been altered coorectly, and I got right number and date formats, in the items and in the report. The "insert" button do the insertion without any problem.
当我单击"getDate"按钮以使用ajax调用从数据库中获取日期时,将以法语格式获取日期!然后插入"按钮失败(invalid date
).
When I click the "getDate" button to get a date from the database using an ajax call, I get the date in the French format! And then the "insert" button fails (invalid date
).
您是否知道为什么要从JavaScript中获取值(对要在程序包中调用PL/SQL函数的按需应用程序进程进行ajax调用)导致此问题?
Do you have any idea on why getting values from JavaScript (making an ajax call to an On Demand Application Process that calls a PL/SQL function in a package) causes the problem ?
仍然和我现有的其他页面一样,即使我使用相同的Before Header流程,报表中的格式仍然错误.我们必须与同事一起对此进行更多研究,也许我们在某处有一个隐藏"的代码可以破坏一切.
And it is still strange as in my other existing pages, I get wrong format in the report even if I use the same Before Header process. We have to look more into this with my collegue, maybe we have a "hidden" piece of code somewhere which breaks all.
我查看了页面的调试消息数据,但对我来说并不奇怪.
I have looked at the debug message data for the page but nothing strange for me.
推荐答案
从apex客户端到服务器的每个调用都是单独的oracle会话. 它回答了为什么您通过js回调获取错误的格式.
Each call from apex-client to server is the separate oracle session. It answers why you are getting wrong format by js callback.
您尝试过吗?
可以通过 APEX_UTIL.SET_SESSION_LANG 过程或通过URL中F过程的 P_LANG 参数来设置Application Express会话语言.
The Application Express session language can be set via either the APEX_UTIL.SET_SESSION_LANG procedure or via the P_LANG parameter of the F procedure in the URL.
也可以尝试将初始化代码放在此处:安全属性->数据库会话->初始化PL/SQL代码.但是我不确定回调.
Also you can try put initialization code here: Security Attributes -> Database Session -> Initialization PL/SQL Code. But I'm not sure about callbacks.
这篇关于数字和日期格式:更改NLS_SESSION_PARAMETER无效吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!