APEX

Hi,

Oracle 11.2.0.3.0, APEX 4.1.1.00.23.

We need to display numbers in our application with the format 'FM999999999990.000' and dates with the English format 'DD-MON-YYYY'.
Even if the application language is going to change (french, spanish, 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) :

- Application Primary Language: French (France) (fr)
- Application Language Derived From: Session
- Application Date Format: DD-MON-YYYY

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...

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;

Result :

Parameter		Database Value			Instance Value		Session Value
NLS_CALENDAR		GREGORIAN		 				GREGORIAN
NLS_CHARACTERSET	WE8ISO8859P1		 	 
NLS_COMP		BINARY				BINARY			BINARY
NLS_CURRENCY		$		 					¿
NLS_DATE_FORMAT		DD-MON-FXYYYY			DD-MON-FXYYYY		DD-MON-YYYY
NLS_DATE_LANGUAGE	AMERICAN		 				FRENCH
NLS_DUAL_CURRENCY	$		 					¿
NLS_ISO_CURRENCY	AMERICA		 					FRANCE
NLS_LANGUAGE		AMERICAN			AMERICAN		FRENCH
NLS_LENGTH_SEMANTICS	BYTE				BYTE			BYTE
NLS_NCHAR_CHARACTERSET	AL16UTF16		 	 
NLS_NCHAR_CONV_EXCP	FALSE				FALSE			FALSE
NLS_NUMERIC_CHARACTERS	.,		 					,
NLS_RDBMS_VERSION	11.2.0.3.0		 	 
NLS_SORT		BINARY		 					FRENCH
NLS_TERRITORY		AMERICA				AMERICA			FRANCE
NLS_TIME_FORMAT		HH.MI.SSXFF AM		 				HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT	DD-MON-RR HH.MI.SSXFF AM		 		DD/MM/RR HH24:MI:SSXFF
NLS_TIMESTAMP_TZ_FORMAT	DD-MON-RR HH.MI.SSXFF AM TZR		 		DD/MM/RR HH24:MI:SSXFF TZR
NLS_TIME_TZ_FORMAT	HH.MI.SSXFF AM TZR		 			HH24:MI:SSXFF TZR

Nothing changes...

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.

Is there any way I can alter number and date parameters for the session for the whole application ?

Thank you.

Yann.

EDIT:

OK 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...

Edited by: Yann39 on 21 juin 2012 02:55