This discussion is archived
10 Replies Latest reply: Mar 10, 2013 9:11 AM by rp0428 RSS

Reg : sys_context -

ranit B Expert
Currently Being Moderated
Hi Experts,

I was going through <b>SYS_CONTEXT</b> but couldn't get the exact purpose of this function.

Ex -
SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') 
   FROM DUAL;

-- "same can be achieved by"
SELECT user FROM DUAL;

-- or by querying the dynamic view "V$SESSION"
Am i getting this wrongly?
Any pointers guys?

Thanks.
  • 1. Re: Reg : sys_context -
    Justin Cave Oracle ACE
    Currently Being Moderated
    Yes, you can get the username from many different places.

    You can, however, get a lot of things from the USERENV context in addition to the username. You can get the client IP address, the current schema, the current edition, etc. You can get most of that information from other data dictionary tables as well but it's rather useful (and rather more efficient) to have a single convenient place to go. That's particularly true if you're doing things like defining VPD policies where you wouldn't want to be constantly joining to data dictionary tables.

    If you create your own context, you would also use SYS_CONTEXT to access values stored in that context.

    Justin
  • 2. Re: Reg : sys_context -
    ranit B Expert
    Currently Being Moderated
    Thanks Justin. That was a nice explanation.

    But could you please explain what a 'context' exactly is? Is it something that can store some data andcan be defined inside a package/proc?
  • 3. Re: Reg : sys_context -
    Justin Cave Oracle ACE
    Currently Being Moderated
    The Database Security Guide has a nice discussion on application contexts which are the contexts that you would create in addition to the Oracle-provided USERENV context.

    Justin
  • 4. Re: Reg : sys_context -
    ranit B Expert
    Currently Being Moderated
    Thanks again Justin!
    I'll go through your inputs and workout to get a clear picture.
  • 5. Re: Reg : sys_context -
    Hoek Guru
    Currently Being Moderated
    As a practical example: you can use application context and SYS_CONTEXT as an alternative to bind variables.
    See the last example of this article from Tom: http://tkyte.blogspot.nl/search?q=varying
  • 6. Re: Reg : sys_context -
    rp0428 Guru
    Currently Being Moderated
    >
    But could you please explain what a 'context' exactly is? Is it something that can store some data andcan be defined inside a package/proc?
    >
    What OS are you using?

    Do you know what environment variables are and how to set them in your OS?

    CONTEXT variables serve a similar purpose. They are essentially 'environment' variables (name=value pairs) for your session. You can set them and used them.

    As previously shown Oracle provides a default context and some 'environment' variables (e.g. USER) automatically. You can also create your own CONTEXTs and store your own NAME=VALUE pairs in those contexts. By default those values are ONLY available for your session; that is, each session would have their own set.

    But you can create a GLOBAL set of values that can be accessed by any session.

    The powerful part is that you can use the SYS_CONTEXT function in your queries to access those environment variables just as if they were BIND variables or part of the query.

    There is a useful tutorial in the Database Security Guide that has sample code that creates a GLOBAL context and shows how to use it.
    http://docs.oracle.com/cd/B28359_01/network.111/b28531/app_context.htm#DBSEG79745
    >
    Tutorial: Creating a Global Application Context That Uses a Client Session ID
    This section contains:

    About This Tutorial

    Step 1: Create User Accounts

    Step 2: Create the Global Application Context

    Step 3: Create a Package for the Global Application Context

    Step 4: Test the Global Application Context

    Step 5: Remove the Components for This Tutorial

    About This Tutorial
    This tutorial shows how to create a global application context that uses a client session ID for a lightweight user application. It demonstrates how to control nondatabase user access by using a connection pool.
  • 7. Re: Reg : sys_context -
    ranit B Expert
    Currently Being Moderated
    Nice demo Hoek!! Thanks!
  • 8. Re: Reg : sys_context -
    ranit B Expert
    Currently Being Moderated
    Do you know what environment variables are and how to set them in your OS?

    CONTEXT variables serve a similar purpose. They are essentially 'environment' variables (name=value pairs) for your session. You can set them and used them.
    Environment variables are stored in our OS. So does that mean the context variables are also stored and are again used even after the restart of the database?
    But you can create a GLOBAL set of values that can be accessed by any session.
    With GLOBAL it is clear that it can be accessed across sessions, but is the variable/value present even after the database is restarted once?
  • 9. Re: Reg : sys_context -
    Hoek Guru
    Currently Being Moderated
    With GLOBAL it is clear that it can be accessed across sessions, but is the variable/value present even after the database is restarted once?
    No.
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:871067287878
  • 10. Re: Reg : sys_context -
    rp0428 Guru
    Currently Being Moderated
    >
    Environment variables are stored in our OS. So does that mean the context variables are also stored and are again used even after the restart of the database?
    >
    No - they are stored in memory so will disappear when the database is shutdown.

    You can, of course, create a startup trigger that will recreate the GLOBAL context variables when the database is restarted. That will provide the same effect.

    One way to do that is to create a simple table of 'name=value' pairs and then have your startup trigger read the table and use those values to create the GLOBAL context variables. If those settings might be changed you can add a SHUTDOWN trigger to save them back to the table. Keep in mind though that any abnormal termination could result in the save being unsuccessful.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points