2 Replies Latest reply: Jan 10, 2013 3:31 PM by Frank Kulash RSS

    plsql

    Ora_83
      Hello

      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.



      i.e

      declare

      select account_status from dba_users where username in ('SDC','NZU','WEB');

      If account_status = 'EXPIRED'
      then
      execute immediate 'alter user username account unlock';
        • 1. Re: plsql
          sb92075
          Ora_83 wrote:
          Hello

          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.



          i.e

          declare

          select account_status from dba_users where username in ('SDC','NZU','WEB');

          If account_status = 'EXPIRED'
          then
          execute immediate 'alter user username account unlock';
          EXPIRED is different from LOCKED!
            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
          Handle:     Ora_83
          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?
          • 2. Re: plsql
            Frank Kulash
            Hi,

            Here's one way:
            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;
            /
            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.)