| procedure to change db link (merged) [message #386671] |
Mon, 16 February 2009 09:45  |
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 #386678 is a reply to message #386673] |
Mon, 16 February 2009 10:11  |
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,
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
