数字和日期格式:更改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;

报告显示会话已更改:

然后我用:

1. 以上标题之前"过程.
2. 显示NLS参数值的报告.
3. 显示我的一个表中的日期和数字的报告.
4. 源类型为"SQL查询"的文本字段项目可获取日期.
5. 数字"字段项，其源类型为"SQL查询"以获取数字.
6. 与JQuery绑定的按钮，该按钮调用一个应用程序，该应用程序调用一个PL/SQL过程，该过程从表中htp.prn()一个日期，然后填充我的文本字段项.
7. 与JQuery绑定的按钮，用于在表中的两个字段之间进行插入(使用应用程序等).

1. The above Before Header process.
2. A report to show NLS parameters values.
3. A report that show date and numbers from one of my tables.
4. A textfield items with a Source Type as "SQL Query" to get a date.
5. A Number field item with a Source Type as "SQL Query" to get a number.
6. 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.
7. 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屋！