Home » SQL & PL/SQL » SQL & PL/SQL » Changing the schema within procedure
Changing the schema within procedure [message #219410] Wed, 14 February 2007 04:56 Go to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
Is it possible to change the schema from within a procedure ?

consider apps is executing proc1 and proc2 is executed within proc1.
Can we change the schema from within proc1 so that proc2 is executed by another user

--in apps schema--
create or replace procedure proc1 is
begin
--stmts which should be executed in apps schema
--proc2 should be executed by different user so can any stmt
--to alter schema from within procedure proc1 be added here?
proc2;
end;
Re: Changing the schema within procedure [message #227804 is a reply to message #219410] Thu, 29 March 2007 07:58 Go to previous message
gkodakalla
Messages: 49
Registered: March 2005
Member
I have two schemas, A and B. A has a table T with 10 rows and B has a table T with 100 rows. I granted select on B.T to A User.


1 declare
2 a_id_count number := 0;
3 b_id_count number := 0;
4 begin
5 select count(*) into a_id_count from t;
6 dbms_output.put_line('a_id_count is '||a_Id_count);
7 execute immediate 'ALTER SESSION SET CURRENT_SCHEMA=B';
8 select count(*) into b_id_count from t;
9 dbms_output.put_line('b_id_count is '||b_Id_count);
10* end;

a_id_count is 10
b_id_count is 100

PL/SQL procedure successfully completed.

Will this solve your purpose?

Thanks
Giridhar
Previous Topic: Help on subquery
Next Topic: Identifying tablespaces
Goto Forum:
  


Current Time: Thu Dec 08 22:28:39 CST 2016

Total time taken to generate the page: 0.08225 seconds