Quite often it is necessary to do some repetetive operations / few line queries against set of objects. Let's say you have a list of accounts to unlock via

alter user %username% account unlock

and instead of typing the same line multiple times for each user I believe some cycle construction can be used but don't know how to write it in PL/SQL. Can somebody advice on this?


You can generate this sort of statements using dynamic SQL, e.g.

select 'alter user ' || username || ' account unlock' as cmd
  from dba_users
 where account_status = 'LOCKED';

Following up on your latest comment, if you want to manually enumerate the usernames you can use a PL/SQL collection such as a nested table, e.g.

SQL> set serveroutput on
SQL> declare
  2     type users_t is table of dba_users.username%type;
  3     my_users users_t := users_t('USER1', 'USER2', 'USER3');
  4  begin
  5     for i in my_users.first .. my_users.last loop
  6        dbms_output.put_line('alter user ' || my_users(i) || ' account unlock');
  7     end loop;
  8  end;
  9  /
alter user USER1 account unlock
alter user USER2 account unlock
alter user USER3 account unlock

PL/SQL procedure successfully completed.

To actually run the commands instead of just printing them, substitute dbms_output.put_line with execute immediate.

| improve this answer | |
  • Thanks - that looks good, though I'm actually looking for something more generic with use of array/variable (to avoid getting users etc. from database and use some sort of explicitly specified list). So that I can use it in other scenarious - not only for account unlock – Mikhail Nov 7 '13 at 10:40
  • To be more specific how can I avoid reading users from dba_users table and just somehow enumerate accounts I want to check? Something like USR={jsmith; jdow; emunch}... – Mikhail Nov 7 '13 at 15:32
  • 1
    @Mikhail: I have expanded my answer – Marco Baldelli Nov 7 '13 at 16:05
set serveroutput on size unlimited
for i in (select 'alter user '||username||' account unlock' cmd
            from dba_users
           where account_status = 'LOCKED'
   dbms_output.put_line (i.cmd);
 --  execute immediate i.cmd;
end loop;

This works. Be careful, I have seen ppl doing similar things without testing, effectively destroying databases, especially when using patterns and manipulating database objects with the results. If the output is OK, activate the execute immediate.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.