Re: Checking value set via "set transaction" or "alter session"

From: Jack <jreid_at_stirlingonline.com>
Date: Sat, 8 Mar 2008 11:38:31 -0800 (PST)
Message-ID: <7db162d6-ca59-45af-8ce9-ae49b2afc2d9@f47g2000hsd.googlegroups.com>


On Mar 8, 4:38 am, Thomas Kellerer <FJIFALSDG..._at_spammotel.com> wrote:
> Thomas Kellerer wrote on 08.03.2008 10:36:
>
>
>
> > Jack wrote on 08.03.2008 07:16:
> >> This seemed like it would be a simple question to answer, but I can't
> >> find anything on either.
>
> >> Can anyone shed some light on how to determine the value I have used
> >> for "set transaction" (say to "read only") and for "alter
> >> session" (say to change the NLS_DATE_FORMAT)?
>
> > For the NLS parameter see:
> > <http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statvi...>
>
> > For the transaction state I don't know.
> > Check out the Reference manual for this:
> >http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/toc.htm
>
> And the sys_context function might also be interesting for you:
>
> http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/fun...

Thanks! This is definitely the solution for the NLS_DATE_FORMAT (and the supported other parameters set thorugh the context).

Ex:

  • check_sys_context.sql
  • 03/08/08 - created

DECLARE v_session_parameter VARCHAR2(50);

BEGIN v_session_parameter := SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT');

DBMS_OUTPUT.PUT_LINE('The value of the parameter is: ' ||

   v_session_parameter);

END;
/

  • end of script

SQL> @check_sys_context
The value of the parameter is: YYYY-MM-DD:HH24:MI:SS

PL/SQL procedure successfully completed. Received on Sat Mar 08 2008 - 13:38:31 CST

Original text of this message