PL/SQL - Execute immediate loop using parameter and if/then statement

Question

Within a shell script I am using a pl/sql block to drop certain users using $USER parameter. If another parameter ${DROP_AB} is Y then drop all users from the cursor. How do I drop all users except one specific user ${USER}AB if the parameter passed is N? Please see below and help with suggestions:

BEGIN
DECLARE
  CURSOR c1 IS
    SELECT username FROM dba_users WHERE username LIKE '${USER}%';
BEGIN
  FOR user_rec IN c1
    IF ${DROP_AB}='Y' THEN
      LOOP
        EXECUTE IMMEDIATE 'DROP USER ' ||user_rec.username|| ' CASCADE';
    ELSIF ${DROP_AB}='N' THEN
      LOOP
        --DROP ALL USERS EXCEPT FOR USER '${USER}AB'
   END LOOP;
END;
END;
/

you cant directly take all users from dba_users and drop them, even if the user enters 'Y' or 'N', pls run the select statement for select username from dba_users , you will find a list of sys related schemas as well. It would be a better idea to get the list of all the users to be dropped and then run the script — anudeepks
– anudeepks, Commented Apr 21, 2015 at 0:57

Shaun Peterson · Accepted Answer · 2015-04-21 01:03:58Z

1

Untested code but somthing along the lines of the below should work, but as suggested in the comment above this will then error on the system schemas / users.

BEGIN
DECLARE
  CURSOR c1 IS
    SELECT username FROM dba_users WHERE username LIKE '${USER}%';
BEGIN
  FOR user_rec IN c1 loop
    IF ${DROP_AB}='Y' THEN
        EXECUTE IMMEDIATE 'DROP USER ' ||user_rec.username|| ' CASCADE';
    ELSE
        IF user_rec.username != ${USER}AB THEN
          EXECUTE IMMEDIATE 'DROP USER ' ||user_rec.username|| ' CASCADE';
        END IF;
    END IF;  
   END LOOP;
END;
END;
/

answered Apr 21, 2015 at 1:03

Shaun Peterson

1,7901 gold badge16 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3224907 Over a year ago

For the else condition.. will it drop all users like ${USER}% except for ${USER}AB?

Shaun Peterson Over a year ago

The IF user_rec.username != ${USER}AB THEN around the drop statement should stop it dropping that user.

Collectives™ on Stack Overflow

PL/SQL - Execute immediate loop using parameter and if/then statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related