Pl/Sql触发器，用于在Oracle中更改默认日期格式 [英] Pl/Sql Trigger for changing default date format in oracle

问题描述

我想确保每次登录时日期格式从'24-Apr-2014'更改为'24/04/2014'

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

为此，我如下创建了一个pl/sql触发器

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;
    /

但是我没有以sysdba身份登录，所以也许这就是为什么我得到错误

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?

推荐答案

您的错误表示不允许您创建此类触发器.但是你走错了路.您必须在数据库级别设置默认值.

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.

来自 Oracle文档

默认日期时间格式模板是使用初始化参数NLS_DATE_FORMAT明确指定，或使用初始化参数NLS_TERRITORY明确隐式指定的.您可以使用ALTER SESSION语句更改会话的默认日期时间格式.

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';

这篇关于Pl/Sql触发器，用于在Oracle中更改默认日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！