通過觸發器為會話設置 NLS_LENGTH_SEMANTICS

我的 Oracle 12c 數據庫存在以下問題。

我必須安裝需要 Oracle 數據庫實例的第三方應用程序。供應商要求將NLS_LENGTH_SEMANTICS參數設置為CHAR。但是，Oracle 強烈建議不要將其從全域更改BYTE為CHAR.

我的解決方案是創建一個觸發器，它會更改會話的參數。觸發器如下所示：

create or replace TRIGGER SYS.ALTER_NLS_FOR_SAPIENT
AFTER LOGON on DATABASE
BEGIN
   IF ( user like 'SAPIENT%' ) THEN
       execute immediate 'ALTER SESSION SET NLS_LENGTH_SEMANTICS="CHAR"';
   END IF;
END;

不幸的是，觸發器不會更改會話參數。我懷疑觸發器沒有啟動，所以我通過將文件寫入磁碟來確保它。肯定是觸發了。

如果我從客戶端更改例如 SQL 開發人員的會話參數，則設置該參數。

ALTER SESSION SET NLS_LENGTH_SEMANTICS="CHAR";
select * from NLS_SESSION_PARAMETERS where parameter='NLS_LENGTH_SEMANTICS';

有誰知道為什麼不能NLS_LENGTH_SEMANTICS從伺服器端設置參數？

系統資訊

有可能的。

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 29 08:29:35 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             343933032 bytes
Database Buffers          721420288 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
SQL> create user sapient identified by sapient;

User created.

SQL> grant create session to sapient;

Grant succeeded.

SQL> show parameter seman

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics                 string      BYTE

SQL> create or replace TRIGGER SYS.ALTER_NLS_FOR_SAPIENT
AFTER LOGON on DATABASE
BEGIN
   IF ( user like 'SAPIENT%' ) THEN
       execute immediate 'ALTER SESSION SET NLS_LENGTH_SEMANTICS="CHAR"';
   END IF;
END;  2    3    4    5    6    7
 8  /

Trigger created.

SQL> grant select_catalog_role to sapient;

Grant succeeded.
SQL> connect sapient/sapient
Connected.
SQL> show parameter seman

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics                 string      CHAR
SQL>

引用自：https://dba.stackexchange.com/questions/254417