Change NLS_DATE_FORMAT

[Robson Costa]

Hello,

How do I make the parameter NLS_DATE_FORMAT to be with the value 'DD/MM/YYYY' I have already used the alter system ... scope=spfile and tb I already changed the init.ora To change the spfile.ora but when I will consult the parameters it is as 'dD-MON-RR'

[diegolenhardt]

Lowered the bank and started again?

after alter system scope=both

[Robson Costa]

1) Yes. I took the seat and put it again. The problem continues

2) The alter system for nls_date_format scope=both gives error or-02096. With scope=spfile gives a message of success. I made Shutdown and Startup and did not work.

Oracle version: 11g

[stcoutinho]

Hello Robson,

In the past I had problems with other NLS variables, which no matter how much I changed in the instance, the same appeared not to have been changed by the commands.

As I could read at the time on the subject, there are 3 types of NLS parameters.

a) NLS of the instance;
) b) Bank NLS;
c) NLS of the session;

There is a hierarchy between the three.

Session NLS prevails over the Bank, which prevails on the instance.

In other words: NLS-session parameters replaced those of NSL-bank, which in turn suses those of NLS-instance.

In this case, you have changed the instance NLS to DD / MM / YYYY.

But if you open a SQL session through an Oracle Client in English, it is likely that the date format in your SQL * Plus session is different from DD / mm / yyyy.

That is, it did not help you change the format of the instance date as it was overwritten by the NLS of the session.

A form of you diblar This restriction would be to create a Bank level trigger (Database Level).

This type of trigger needs to be created under the SYS user schema.

You can create this trigger for all users or for a specific bank user.

Apply the example below on a test base, before installing it in the definitive environment.

Select all

 
REM. -- 
REM. ---- Exemplo de trigger para todos os usuarios da instancia 
REM. -- 
 
CREATE OR REPLACE TRIGGER sys.tgr_seta_nls_date  
AFTER LOGON ON DATABASE 
BEGIN 
  EXECUTE IMMEDIATE 'alter session set NLS_DATE_FORMAT=''DD/MM/YYYY'''; 
END tgr_seta_nls_date; 
/ 

Select all

 
REM. -- 
REM. ---- Exemplo de trigger para um usuario especifico 
REM. -- 
 
CREATE OR REPLACE TRIGGER sys.tgr_seta_nls_date_zebedeu  
AFTER LOGON ON zebedeu.SCHEMA 
BEGIN 
  EXECUTE IMMEDIATE 'alter session set NLS_DATE_FORMAT=''DD/MM/YYYY'''; 
END tgr_seta_nls_date_zebedeu; 
/ 

Once created without error, try to connect to the base again and make a SELECT SYSDATE FROM DUAL to confirm the date format;

I hope it works,

Hugs,

Sergio Coutinho