OraFAQ Forum: SQL & PL/SQL » Persistent remote connection in PL/SQL?

Home » SQL & PL/SQL » SQL & PL/SQL » Persistent remote connection in PL/SQL?

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

Persistent remote connection in PL/SQL? [message #8983]

Wed, 08 October 2003 19:39

William
Messages: 17
Registered: September 2000

Junior Member

Is it possible to establish a persistent remote connection in PL/SQL?

ie:

declare
user_v VARCHAR2(10) := 'scott';
password_v VARCHAR2(10) := 'tiger';
begin
execute immediate 'connect :user/:sys_pass@'||instance_v using user_v, password_v;
execute immediate 'alter system set audit_trail=db scope=spfile';
end;

I know this doesn't work, but is there some way to accomplish the same effect, since system control commands don't have a syntax for db_links?

Thanks

Report message to a moderator

Re: Persistent remote connection in PL/SQL? [message #8990 is a reply to message #8983]

Thu, 09 October 2003 07:26

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

[i]execute immediate 'connect :user/:sys_pass@'||instance_v using user_v, password_v;
[/i]
connect is an sqlplus command.
i beleive, you cannot use it inside pl/sql block.
the other (clumsy and dumb ) workaroudn would be
to create a new oracle session using sqlplus.
you can call os commands from pl/sql.
do somehting like this ( i have not tried this, and have no idea whther it will work seamlessly)
sqlplus scott/tiger@remotedb;
do_the_business;
exit;
[url=http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:952229840241]TomKyte<a/> has method to call the os commands from pl/sql

Report message to a moderator

Re: Persistent remote connection in PL/SQL? [message #8997 is a reply to message #8983]

Thu, 09 October 2003 12:43

Thiru
Messages: 1089
Registered: May 2002

Senior Member

William,
there are certain things that are meant for SQL/SQLPlus and certain other things meant for PL/SQL. Is'nt it much easier to write a sqlplus script with parameters to perform such core DBA activities ?

-Thiru

Report message to a moderator

Previous Topic:	Distinct values
Next Topic:	'&' in pl/sql

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 07:58:51 CDT 2024