Posted on 2007-10-10
Last Modified: 2013-12-19
an oracle stored procedure stops at the line
dbms_session.clear_context('CTX_RPT_BillingDocuments', NULL, 'p_Location_Code');

with the following error

ORA-01031: insufficient privileges

What do I have to do?
Question by:jking1234
    LVL 11

    Expert Comment

    by:mohammadzahid Using DBMS_SESSION.SET_CONTEXT to Set Session Information
    After you have used the SYS_CONTEXT function to retrieve the session data of a user, you are ready to set the application context values from the session of this user. To do so, use the DBMS_SESSION.SET_CONTEXT procedure. (Ensure that you have EXECUTE privileges for the DBMS_SESSION PL/SQL package.)

    Its syntax is as follows:

       namespace VARCHAR2,
       attribute VARCHAR2,
       value     VARCHAR2,
       username  VARCHAR2,
       client_id VARCHAR2);
    In this specification:

    namespace: The namespace of the application context to be set, limited to 30 bytes. For example, if you were using a namespace called custno_ctx, you would specify it as follows:

    namespace => 'empno_ctx',
    attribute: The attribute of the application context to be set, limited to 30 bytes. For example, to create the ctx_attrib attribute for the custno_ctx namespace:

    attribute => 'ctx_attrib',
    value: The value of the application context to be set, limited to 4000 bytes. Typically, this is the value retrieved by the SYS_CONTEXT function and stored in a variable. For example:

    value => ctx_value,
    username: Optional. The database user name attribute of the application context. The default is NULL, which permits any user to access the session. For database session-based application contexts, omit this setting so that it uses the NULL default.

    The username and client_id parameters are used for globally accessed application contexts. See "Setting the username and client_id DBMS_SESSION.SET_CONTEXT Parameters" for more information.

    client_id: Optional. The application-specific client_id attribute of the application context (64-byte maximum). The default is NULL, which means that no client ID is specified. For database session-based application contexts, omit this setting so that it uses the NULL default.

    I got this information from this url :

    LVL 34

    Accepted Solution

    Check the permissions on the package: dbms_session.  That is in the SYS schema, and it should have "public execute" rights, but if it doesn't you could get this error.  If this package does not have public execute permission, log in as SYS, then:
    grant execute on dbms_session to public;

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now