This content has been marked as final. Show 2 replies
Ora_83 wrote:EXPIRED is different from LOCKED!
I want to check the account status of three users.
If it is locked, I want to unlock it in plsql.
Your help is appreciated.
select account_status from dba_users where username in ('SDC','NZU','WEB');
If account_status = 'EXPIRED'
execute immediate 'alter user username account unlock';
1* select account_status , count(*) from dba_users group by account_status order by 1 SQL> / ACCOUNT_STATUS COUNT(*) -------------------------------- ---------- EXPIRED 1 EXPIRED & LOCKED 25 LOCKED 4 OPEN 21
Status Level: Newbie
Registered: Nov 8, 2009
Total Posts: 513
Total Questions: 196 (125 unresolved)
why do you waste time here when you rarely get answers to your questions?
Here's one way:
You'll notice that the EXECUTE IMMEDIATE statement is commented out. When developing dynamic SQL, always do this first: display the dynamic SQL statement, but do not execute it. When the rest of the code is working correctly, and the text shown looks like what you really want, then you can un-comment the EXECUTE IMMEDIATE statement (and comment out the call to put_line right before it.)
DECLARE sql_txt VARCHAR2 (100); BEGIN FOR u IN ( SELECT username , account_status FROM dba_users WHERE username IN ('SDC', 'NZU', 'WEB') ) LOOP IF u.account_status = 'LOCKED' THEN sql_txt := 'ALTER USER ' || u.username || ' ACCOUNT UNLOCK'; dbms_output.put_line ( sql_txt || ' = UNLOCK command' ); -- EXECUTE IMMEDIATE sql_txt; dbms_output.put_line ( 'Unlocked user ' || u.username ); ELSE dbms_output.put_line ( 'User ' || u.username || ' is not locked' ); END IF; END LOOP; END; /