Home » SQL & PL/SQL » SQL & PL/SQL » Login as a different schema in the middle of a procedure
Login as a different schema in the middle of a procedure [message #225345] Mon, 19 March 2007 13:46 Go to next message
boi-alien
Messages: 10
Registered: March 2007
Junior Member
Hi,

I have a procedure written up but since changes at my company I can't run the whole procedure under 1 schema. Is it possible to switch to a different schema in a different environment halfway through a procedure then switch back to the original schema and original environment to finish running the procedure?

Thanks.
Re: Login as a different schema in the middle of a procedure [message #225349 is a reply to message #225345] Mon, 19 March 2007 13:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe:

alter session set current_schema=...;

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_2012.htm#sthref4525

Regards
Michel
Re: Login as a different schema in the middle of a procedure [message #225351 is a reply to message #225345] Mon, 19 March 2007 14:00 Go to previous messageGo to next message
boi-alien
Messages: 10
Registered: March 2007
Junior Member
Hi Michel,

I'm not sure this would work because the schema I need to log into is into a different environment. Correct me if I'm wrong, the alter session set current_schema command only works if I'm going into the same environment.
Re: Login as a different schema in the middle of a procedure [message #225353 is a reply to message #225345] Mon, 19 March 2007 14:02 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
The best bet would be to create a db link to the second schema and create a public synonym. If the DBA's will go for it.

I believe that should work.
Re: Login as a different schema in the middle of a procedure [message #225357 is a reply to message #225345] Mon, 19 March 2007 14:06 Go to previous messageGo to next message
boi-alien
Messages: 10
Registered: March 2007
Junior Member
Unfortunately therein lies my problem, the DBAs aren't willing to allow me to create DBlinks =(
Re: Login as a different schema in the middle of a procedure [message #225360 is a reply to message #225351] Mon, 19 March 2007 14:11 Go to previous message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I didn't understand different environement meant different database.
There is no way to switch the current schema to another database one.

Regards
Michel
Previous Topic: Substitute for the IIF function used in Access
Next Topic: intersection
Goto Forum:
  


Current Time: Mon Dec 05 11:15:08 CST 2016

Total time taken to generate the page: 0.08227 seconds