Put your tips and tricks online - Share your knowledge! Login | Register
 
 
  Search     Advanced search
 

Home | Ask Question | Add tip | Questions | My tips | Recent tips & tricks | Suggest a category | FAQ | Forums

 
 
 
 Category : Home > Databases > Oracle     

Set NLS parameter for session in logon trigger


If you are trying to run something like ALTER SESSION SET in logon trigger you will get
ORA-04092 or if you are trying to do it with DBMS_SESSION you will get ORA-06512.

The solution is to create a procedure and then call procedure from the trigger i.e.:

--
-- procedure to set NLS_DATE_FORMAT using DBMS_SESSION package
--
CREATE OR REPLACE PROCEDURE SET_NLSDATEFORMAT AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
        DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT','YYYYMMDD');
        COMMIT;
END;
/

- OR -

--
-- procedure to set NLS_TIMESTAMP_TZ_FORMAT using dynamic sql
--
CREATE OR REPLACE PROCEDURE SET_NLSTIMESTAMPFORMAT AS
PRAGMA AUTONOMOUS_TRANSACTION;
	i NUMBER;
	x INTEGER;
	v_format VARCHAR2(50);
BEGIN
	i:=dbms_sql.open_cursor;
	v_format := 'YYYYMMDD HH24:MI:SS';
	dbms_sql.parse(i,'ALTER SESSION SET nls_date_format=''' || v_format || '''',
DBMS_SQL.NATIVE);
	x:=dbms_sql.execute(i);
	dbms_sql.parse(i,'ALTER SESSION SET nls_timestamp_tz_format=''' || v_format || '''',
DBMS_SQL.NATIVE);
	x:=dbms_sql.execute(i);
	dbms_sql.parse(i,'ALTER SESSION SET nls_timestamp_format=''' || v_format || '''',
DBMS_SQL.NATIVE);
	x:=dbms_sql.execute(i);
	dbms_sql.close_cursor(i);
	COMMIT;
EXCEPTION
	WHEN OTHERS THEN
	IF DBMS_SQL.IS_OPEN(i) THEN
		DBMS_SQL.CLOSE_CURSOR(i);
	END IF;
	RAISE;
END;
/

--
-- logon trigger to set session parameters when user connects to database
--
CREATE OR REPLACE TRIGGER SET_NLSDATEFORMAT_ONLOGIN AFTER LOGON ON DATABASE
DECLARE
    uname VARCHAR2(50);
BEGIN
    uname := SYS_CONTEXT('USERENV','SESSION_USER');
    IF uname = 'SCOTT' THEN
        --SET_NLSDATEFORMAT;
        SET_NLSTIMESTAMPFORMAT;
    END IF;
END;
/

You could use the above trigger and stored procedure to modify other session parameters for a
specific user.


  Options
 
   del.icio.us  |  newsvine  |  digg  |  furl  |  google  |  yahoo  |  Ma.gnolia  |  vigillar  |  reddit  |  technorati  |  icerocket  |  pubsub

     (Average: 5 / Votes: 9)   Rate this tip:    

Start discussion or add comment to this tip

  Details
Tip reference : #231
views : 6369
Added on : 03/31/09
Submited by : h8dk97
 
Send a message Send a message Printer friendly output Printer friendly output
Display this member's tips Display this member's tips (214)
 
 
<< Previous Next >>
 Most viewed tips 
  Databases > Oracle > Security : How to unlock Oracle user account  
  Databases > Oracle > Performance Tuning : How to enable trace in Oracle  
  Databases > Oracle > DR : Adding new datafiles to standby database  
  Operating Systems > Unix : How to kill Unix user session  
   
  All categories
Databases | Programming | Hardware | Operating Systems | Networking | Internet | ERP / CRM | Games & Multimedia | Graphics & Design | Miscellaneous | Mobile Phones / Tablets | Office Software
 
 

Home |  FAQ |  Terms of Use |  Privacy Policy

© 2005 tiplib.com