We help IT Professionals succeed at work.

Alter Sesson set Current_schema = <schema_name> not working

ameliam
ameliam asked
on
4,525 Views
Last Modified: 2007-12-19
I have a logon trigger that seems to be executing when I logon to my database.  I'm trying to set the current_schema at that time and when I run:

    select sys_context('userenv', 'current_schema') from dual;

I see that my current_schema is the users schema (e.g. USER2) and not the one I'm setting it to (e.g. USER1).  This is my trigger:

 CREATE OR REPLACE TRIGGER db_logon
 AFTER logon ON DATABASE WHEN (USER IN ('USER2'))
 BEGIN
 
  execute immediate 'ALTER SESSION SET CURRENT_SCHEMA = USER1';

end;

The user has alter session privileges.  Anyone have any helpful hints??
Comment
Watch Question

Commented:
Please read this:

Within the procedure you can give:
EXECUTE IMMEDIATE 'alter session set current_schema = user_name';
This changes the current schema of the session to the specified schema. Subsequent unqualified references to schema objects during the session will resolve to objects in the specified schema. The setting persists for the duration of the session or until you issue another ALTER SESSION SET CURRENT_SCHEMA statement.
This setting offers a convenient way to perform operations on objects in a schema other than that of the current user without having to qualify the objects with the schema name. This setting changes the current schema, but it does not change the session user or the current user, nor does it give you any additional system or object privileges for the session.
Hence you can make use of this for your requirement. But changing the current user within a procedure is not possible.

it looks it does not change the current_schema variable.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Ah - I'm using 8.1.5 so that could be the problem.  Not the answer I wanted, but thanks Danielzt.  Back to the drawing board... I can't upgrade our database b/c one of our apps will no longer function (little thing like the ERP system... )

Thanks.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.