• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2494
  • Last Modified:

dbms_session.clear_context

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?
0
jking1234
Asked:
jking1234
1 Solution
 
mohammadzahidCommented:
7.3.3.6 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:

DBMS_SESSION.SET_CONTEXT (
   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 : http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/app_context.htm#CIHIDBDD

0
 
Mark GeerlingsDatabase AdministratorCommented:
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;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now