CODEWITHSUNDEEP
sqloracle-database
Jared Tritsch
Jared Tritsch

Reputation: 229

Use Query result in ALTER statement (Oracle)

I am trying to create a script to update passwords for a large number of users listed in a given table.

alter user FOO identified by FOOWORD;

I can call the usernames via the following statement:

select owner from usertable_verson where rownum = 1

Is there a way to combine these two statements, so that the alter user command works for each result of the select command?

The eventual goal is to create a loop for each username in the selected column, and apply the password change to each.

Upvotes: 0

Views: 138

Answers (1)

are
are

Reputation: 2615

you can do this via dynamic SQL smth like this:

begin
  for rc in (select owner from usertable_verson) loop
    execute immediate 'alter user '||rc.owner||' identified by FOOWORD'; 
  end loop;
end;

Upvotes: 2

Related Questions