Hi,
I am trying to create a similar trigger to change the NLS_LENGTH_SEMANTICS parameter after logon to a schema. The trigger condition is applied as the record gets inserted into the table, however the alter session part doesn't work out:
select * from NLS_SESSION_PARAMETERS where parameter = 'NLS_LENGTH_SEMANTICS'; still shows BYTE after a logon to the EDW_USER schema.
Executing the alter session statement manually works and changes the parameter.
My trigger looks like this:
CREATE OR REPLACE TRIGGER NLS_LENGTH_SEMANTICS_LOGONTRG
AFTER LOGON ON EDW_USER.schema
BEGIN
execute immediate 'alter session set NLS_LENGTH_SEMANTICS = char';
insert into EDW_USER.deletemetest ( KEY_FIELD, TEXT_FIELD) values (1, 'text5');
END;
The trigger is created in my sandbox environment using the SYS user.
Any advice is highly appreciated. Thank you!
Gerd