Alter DateFormat Settings for Session

romanFleischer · Post by **romanFleischer** » Wed 02 Jun 2010 16:41

Hello,

we have some problems putting values into a oracle database when triggers with another nls_language exists than used by our local environment.

For example - the database has nls_language=german; nls_date_format='dd.mm.yy'; my local development environment has both english. When i try to update a row, i get an invalid date format exception of of a database trigger.

It seems, that my local settings are used instead of the database settings. so we have to alter the session settings. But where in our code can we do this? We use the entity framework together with devart.

Where can we set OracleGlobalization.SetApplicationInfo

Thanks
roman

AndreyR · Post by **AndreyR** » Fri 04 Jun 2010 12:02

You can call OracleGlobalization.SetApplicationInfo() before or after the context was instantiated. It influences just after it is called till the next call or till the end of application's lifetime.
Please let me know if anything goes wrong.

romanFleischer · Post by **romanFleischer** » Mon 07 Jun 2010 10:35

We tried to solve the problem as suggested - but it does not work.
We inserted this code in our application - just before the creation of the object context.

Code: Select all

var locale = OracleGlobalization.GetApplicationInfo();
locale.DateFormat = "dd.mm.yyyy";
locale.DateLanguage = "GERMAN";
locale.Territory = "GERMANY";
OracleGlobalization.SetApplicationInfo(locale);

this is our Code:

Code: Select all

public partial class SibisEntities : global::System.Data.Objects.ObjectContext
{
/// 
/// Initialisiert ein neues SibisEntities-Objekt mithilfe der in Abschnitt 'SibisEntities' der 
///Anwendungskonfigurationsdatei gefundenen Verbindungszeichenfolge.
/// 
public SibisEntities() : 
             base("name=SibisEntities", "SibisEntities")
{
           *****here we placed the new locale settings*****
           this.OnContextCreated();
 }

...

But this does not work. We get the same error although the globalisation settings were displayed right.

It seems thas something happens after the creation of the context.
The second solution does also not work, because OracleConnection.SetSessionInfo does not exist.

Did we have to place the code anywhere else?

AndreyR · Post by **AndreyR** » Mon 07 Jun 2010 13:19

Could you please post here the call stack of the exception?

romanFleischer · Post by **romanFleischer** » Mon 07 Jun 2010 13:44

Yes sure we can post it here
this is our SQL statement:

Code: Select all

update AZUBI
set DB_PREIS_ID = 251, DB_PREIS_KZF = 0, VERR_ENDE = to_date('2012/08/31','YYYY/MM/DD')
where (DB_AZUBIID = 32511)

this is the exception we get:

Code: Select all

Error	58	ORA-01843: not a valid month
ORA-06512: at "TU_BA_AZUBI", line 55
ORA-04088: error during execution of trigger 'TU_BA_AZUBI'

and this ist the part of the trigger (line 55) which causes the exception:

Code: Select all

begin
-- indiv.Ausbildungsende
  if nvl(:old.db_abbruch,'01.01.2000')  nvl(:new.db_abbruch ,'01.01.2000') then
   update  azubi_bestellen
  set st_ba_pruefung=2 , st_abbruch=1, aend_id = :new.aend_id
  where  db_azubiid       =  :OLD.db_azubiid and st_ba_pruefung > 0;
  end if;
end;

this is the only part of the trigger which handels with dates.
When we insert following lines before the execution of the SQL statement above, we get no errors.

Code: Select all

alter session set nls_language=german;
alter session set nls_territory=germany;
alter session set nls_date_format='dd.mm.yy';

AndreyR · Post by **AndreyR** » Mon 07 Jun 2010 15:52

Thank you for the report, we are investigating the issue.
I will let you know about the results of our investigation.

AndreyR · Post by **AndreyR** » Tue 06 Jul 2010 08:10

This behaviour is designed. Date being set in the code depends on the NLS_LANG client setting.