Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

1 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;
/
share|improve this answer
    
using is normally want you want for dynamic SQL, in combination with bind variables, to avoid SQL injection. As you've discovered though, you can't use bind variables with DDL statements, hence you have to fall back to concatenation in this case. –  Alex Poole Feb 12 '12 at 23:15
    
Thanks for the helpful explanation @AlexPoole :-) –  Andy Arismendi Feb 12 '12 at 23:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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