Good afternoon/morning all,
I am trying to write a procedure to be run on our central DB to allow us to change the user password on all databases deemed active/in use across the entire estate.
To do so I have broken it down into 2 sections.
1. a procedure which sits in the SYSTEM schema on the target database.
CREATE OR REPLACE procedure SYSTEM.ChangePassword(
DBUserName varchar2,
DBPassWord varchar2) is
begin
execute immediate 'alter user '||DBUserName||' identified by '||DBPassWord;
end if;
end;
/
This is called by running
execute passwd_changer@$DBNAME('fred', 'Password01');
and works well.
However I want to do this for all active DBs rather than 1 at a time so I have the following piece of PL/SQL
CREATE OR REPLACE PROCEDURE DBAINFO.PASSWD_CHANGER2
(UserName varchar2, UserPWD varchar2)
IS
cursor c1 is select distinct(db_link) from dba_db_links $where_links_are_active;
l_statement varchar2(4000);
BEGIN
dbms_output.enable(4000);
for db_link in c1
LOOP
l_statement:='changepassword@db_link('||UserName||','||UserPWD||')';
dbms_output.put_line(l_statement);
l_statement;
END LOOP;
END;
/
I have tried this with or without the l_statement method and get the following errors with and without when trying to compile;
WITH :- PLS-00221: 'L_STATEMENT' is not a procedure or is undefined
WITH EXECUTE IMMEDIATE in front of l_statement before end loop;
ORA-00900: invalid SQL statement
ORA-06512: at "DBAINFO.PASSWD_CHANGER2", line 12
ORA-06512: at line 9
WITHOUT USE of L_STATEMENT, dbms_output etc
PLS-00103: Encountered the symbol "changepassword@db_link("
PLS-00103: Encountered the symbol ";"
Can anyone tell me what I am missing/doing wrong?