To change database link definition in Oracle database we have to connect as a owner of this database link. In most cases it's possible by using output from dba_db_links and connect from sqlplus as a owner.
But if we use undocumented function dbms_sys_sql.parse_as_user, then it's possible to change definition from pl/sql:
-- if we use specific password schema
define pass_prefix = 'pre'
define pass_postfix = 'post'
DECLARE
sqltext VARCHAR2(1000);
l_result NUMBER;
l_cursor INTEGER;
l_cursors dbms_sql.Number_Table;
BEGIN
-- modify db_links
FOR cur IN
(SELECT u.user_id,
d.OWNER,
d.DB_LINK,
d.USERNAME,
d.HOST
FROM dba_db_links d,
dba_users u
WHERE d.owner=u.username
)
LOOP
--parse the cursor only if we haven't already
sqltext:= 'alter database link "'||cur.DB_LINK||'" connect to '||cur.USERNAME||' identified by &&pass_prefix'||cur.USERNAME||'&&pass_postfix';
IF ( NOT l_cursors.exists(cur.user_id) ) THEN
l_cursors(cur.user_id):=dbms_sys_sql.open_cursor;
--parsing anonymous PL/SQL block as a job owner
dbms_sys_sql.parse_as_user( c => l_cursors(cur.user_id), STATEMENT => sqltext, language_flag => dbms_sql.native, userid => cur.user_id );
END IF;
--bind the job number
--remove the job by executing
l_result:=sys.dbms_sys_sql.execute(l_cursors(cur.user_id));
END LOOP;
END;
/
But if we use undocumented function dbms_sys_sql.parse_as_user, then it's possible to change definition from pl/sql:
-- if we use specific password schema
define pass_prefix = 'pre'
define pass_postfix = 'post'
DECLARE
sqltext VARCHAR2(1000);
l_result NUMBER;
l_cursor INTEGER;
l_cursors dbms_sql.Number_Table;
BEGIN
-- modify db_links
FOR cur IN
(SELECT u.user_id,
d.OWNER,
d.DB_LINK,
d.USERNAME,
d.HOST
FROM dba_db_links d,
dba_users u
WHERE d.owner=u.username
)
LOOP
--parse the cursor only if we haven't already
sqltext:= 'alter database link "'||cur.DB_LINK||'" connect to '||cur.USERNAME||' identified by &&pass_prefix'||cur.USERNAME||'&&pass_postfix';
IF ( NOT l_cursors.exists(cur.user_id) ) THEN
l_cursors(cur.user_id):=dbms_sys_sql.open_cursor;
--parsing anonymous PL/SQL block as a job owner
dbms_sys_sql.parse_as_user( c => l_cursors(cur.user_id), STATEMENT => sqltext, language_flag => dbms_sql.native, userid => cur.user_id );
END IF;
--bind the job number
--remove the job by executing
l_result:=sys.dbms_sys_sql.execute(l_cursors(cur.user_id));
END LOOP;
END;
/