Home » SQL & PL/SQL » SQL & PL/SQL » procedure to change db link (merged)
procedure to change db link (merged) [message #386671] Mon, 16 February 2009 09:45 Go to next message
mlei@txi.com
Messages: 3
Registered: December 2008
Junior Member
I want to create a procedure to alter database link (drop and recreate db link) when quarterly changing schema password.

The tricky thing is we are not able to hardcode password inside the procedure. i.e. The procedure need to be open and allow any new schema being added to the database within this quarter.

Has any one had this experience before?

Thanks.
mike
Re: procedure to change db link [message #386673 is a reply to message #386671] Mon, 16 February 2009 09:56 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

>The tricky thing is we are not able to hardcode password inside the procedure. i.e.
HUH? Why do you think any password is needed?

>The procedure need to be open and allow any new schema being added to the database within this quarter.
HUH? What does this mean "The procedure need to be open"?

In order to invoke any PL/SQL procedure, you are already logged into the database!

Just have the schema which owns the DB link own the procedure that changes the DB Link & problem is solved.

Re: procedure to change db link [message #386674 is a reply to message #386671] Mon, 16 February 2009 09:56 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Do NOT post same problem more than once!
Re: procedure to change db link [message #386678 is a reply to message #386673] Mon, 16 February 2009 10:11 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
I want to create a procedure to alter database link (drop and recreate db link) when quarterly changing schema password.


@BlackSwan

Generally, in Oracle, when createing a database link, you need to specify a password, in the IDENTIFIED BY part of the SQL. Your 'HUHs' are a little unnecessary.

@OP

If by 'Creating a procedure' you actually mean a proper pl/sql stored procedure then the simple option would be to accept the new password as a parameter, something like this:
CREATE OR REPLACE PROCEDURE rebuild_db_link (p_link  in  varchar2
                                            ,p_pwd   in  varchar2) as
BEGIN
  execute immediate 'drop database link '||p_link;

  execute immediate 'create database link '||p_link||' connect to quarterly_schema_name identified by '''||p_pwd||'''';
end;
/


You'd want to rewrite to code to be less vulnerable to SQL injection attacks, but that would be a viable approach.

If that's not what you mean, please provide more details and an example, about what you're trying to achieve,
Previous Topic: Revoke DML on user's objects
Next Topic: PLS-00405: subquery not allowed in this context
Goto Forum:
  


Current Time: Thu Dec 08 12:33:23 CST 2016

Total time taken to generate the page: 0.10569 seconds