Alter in PL SQL Loop

oracleoracle-11gplsql

I just started learning PL/SQL an hour ago… I'm trying to set profile settings dynamically… I select distinct profile names from dba_users and I'm trying to run an alter profile command for each profile name. How can I get the alter command to work in a loop like this?

SQL> SET SERVEROUTPUT ON
SQL> declare
  2  begin
  3     for rec in (select distinct Profile from dba_users) loop
  4       EXECUTE IMMEDIATE 'alter profile :1 LIMIT PASSWORD_LIFE_TIME UNLIMITED' using rec.PROFILE;
  5       EXECUTE IMMEDIATE 'alter profile :1 LIMIT PASSWORD_REUSE_MAX UNLIMITED' using rec.PROFILE;
  6     end loop;
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-00931: missing identifier
ORA-06512: at line 4

Best Answer

Figured it out. I saw documentation about the using keyword which apparently wasn't what I needed. Instead I needed to use string concatenation ||.

Here's the working version:

SET SERVEROUTPUT ON
declare

begin
    for rec in (select distinct Profile from dba_users) loop
     EXECUTE IMMEDIATE 'alter profile ' || rec.PROFILE || ' LIMIT PASSWORD_LIFE_TIME UNLIMITED';
     EXECUTE IMMEDIATE 'alter profile ' || rec.PROFILE || ' LIMIT PASSWORD_REUSE_MAX UNLIMITED';
    end loop;
end;
/
Related Question