OraFAQ Forum: SQL & PL/SQL » Change Current schema to other schema

Home » SQL & PL/SQL » SQL & PL/SQL » Change Current schema to other schema (Oracle 11g, windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Change Current schema to other schema [message #653749]

Mon, 18 July 2016 03:31

Vijay55
Messages: 16
Registered: October 2015

Junior Member

Hi All,

I would need your help one of my requirement. i'm trying to connect to APPS schema from my current schema(MySChema) to access few objects in it.(I don't want to use grant privileage here).

The below code that i have written is not helping me to achieve my requirement.

set serveroutput on;
declare
l_query clob;
l_count number;
l_user varchar2(200);
begin

select user into l_user from dual;
dbms_output.put_line(l_user);
execute immediate 'ALTER SESSION SET CURRENT_SCHEMA =APPS';

select user into l_user from dual;
dbms_output.put_line(l_user);
--l_query:='select count(*) from CDR_W520_2897CC0E.T681036830';
--execute immediate l_query into l_count;
dbms_output.put_line('count is='||l_count);
EXCEPTION 
  when others 
    then dbms_output.put_line('EXCEPTION is='||l_count);
end;
/
show errors



Output:
--------
anonymous block completed

MySchema
MySchema
count is=

No Errors.

After performing

execute immediate 'ALTER SESSION SET CURRENT_SCHEMA =APPS';

my current session is still in MySchema only. How to change this into APPS schema.

Thanks,
Vijay

Report message to a moderator

Re: Change Current schema to other schema [message #653750 is a reply to message #653749]

Mon, 18 July 2016 03:48

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

I think you are checking the wrong variable:

rclz>
orclz> conn scott/tiger
Connected.
orclz> select sys_context('userenv','current_schema') from dual;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------------------------------------
SCOTT

orclz> alter session set current_schema=system;

Session altered.

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

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------------------------------------
SYSTEM

orclz> select sys_context('userenv','current_user') from dual;

SYS_CONTEXT('USERENV','CURRENT_USER')
--------------------------------------------------------------------------------------------------------------
SCOTT

orclz>

Report message to a moderator

Re: Change Current schema to other schema [message #653751 is a reply to message #653749]

Mon, 18 July 2016 03:54

flyboy
Messages: 1903
Registered: November 2006

Senior Member

You have two options: CONNECT as a required user (not possible in a procedure) or GRANT the necessary privileges to MYSCHEMA.

CURRENT_SCHEMA session parameter only allows to use objects from the specified schema without the necessity of qualifying its name.
It does not change the current privileges, as documented for 11gR2 here: http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2013.htm#SQLRF53047

Your attempt seems like the try of breaking of security principles - accessing the objects to which you are not privileged.

Or, if the procedure is called from APPS schema, have a look at the setting of Invoker's/Definer's rights (AUTHID Property).
More details are also in the documentation: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/subprograms.htm#i18574

By the way, what is CDR_W520_2897CC0E.T681036830? Does it have any relation to APPS schema?

Report message to a moderator

Previous Topic:	Table joining
Next Topic:	transposing of data in oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 13:21:59 CDT 2024