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
|
|
|
Re: Change Current schema to other schema [message #653750 is a reply to message #653749] |
Mon, 18 July 2016 03:48 |
John Watson
Messages: 8938 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>
|
|
|
|