1

i want to ensure that every time i logon the date format is changed from '24-Apr-2014' to '24/04/2014'

For that I created a pl/sql trigger as follows

    CREATE OR REPLACE TRIGGER CHANGE_DATE_FORMAT
    AFTER LOGON ON DATABASE
    BEGIN
    ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY';
    END;
    /

However i am not logged on as sysdba so perhaps thats why i get the error 

    SQL> @C:/pl/dateTrigger.sql
    AFTER LOGON ON DATABASE
           *
    ERROR at line 2:
    ORA-01031: insufficient privileges

What can i do to ensure that date format is changed permanently?

J. Chomel
  • 8,193
  • 15
  • 41
  • 69
Fazil Nadeem Hussain
  • 71
  • 1
  • 3
  • 1
    Hi Fazil, can you elaborate this little bit. how you login? e.g. using SQL Plus , etc.. because, simply you can use connect sys/pwd as sysdba command – Anura Adhikari Apr 24 '14 at 18:20
  • 8
    `nls_date_format` is generally something that is set on the client. Is there a reason that you can't/ don't want to do this as a client-side configuration? Are you saying that you do not and can not get privileges to create a logon trigger in the database? And, taking a step back, what problem are you trying to solve? When people are overly concerned about the `nls_date_format`, that often implies that application(s) are incorrectly relying on implicit string to date conversion. – Justin Cave Apr 24 '14 at 18:35
  • 2
    Also, you could consider setting this in a `login.sql` user profile if it is just for you, or `glogin.sql` site profile if it's for all local command-line users. Bear in mind a logon trigger will apply to all clients, not just SQL*Plus - including JDBC, for example, which might cause confusion. And all users unless you selectively apply it within the trigger itself. You'd also have to use `execute immediate` to run an `alter session` command, it won't be recognised within PL/SQL as a simple statement. – Alex Poole Apr 24 '14 at 22:40
  • You're definitely getting this error because you're missing the `CREATE TRIGGER` privilege. – J. Chomel Aug 03 '16 at 09:36

1 Answers1

1

Your error means you are not allowed to create such a trigger. But your on the wrong track. You must set the default at database level.

Setting Default Datetime Format Template

From Oracle documentation

The default datetime format template is specified either explicitly with the initialization parameter NLS_DATE_FORMAT or implicitly with the initialization parameter NLS_TERRITORY. You can change the default datetime formats for your session with the ALTER SESSION statement.

Then for your case this format must be set in your database initialization parameters.

To check you have the right value in current session:

select value from v$parameter where upper(name)='NLS_DATE_FORMAT';
J. Chomel
  • 8,193
  • 15
  • 41
  • 69