Home » SQL & PL/SQL » SQL & PL/SQL » Trying to delete bunch of users using loop but failing (Oracle 12.2)
Trying to delete bunch of users using loop but failing [message #678675] Fri, 27 December 2019 03:36 Go to next message
a100
Messages: 34
Registered: March 2019
Member
Hello all,
I am trying to delete bunch of users with their data from Oracle 12 using the following command:
BEGIN
    FOR i IN (SELECT t.username
              FROM   dba_users t
              WHERE  t.username LIKE 'abc%'
                     AND t.username NOT LIKE 'abc_this_one_save') LOOP
        EXECUTE IMMEDIATE 'DROP USER '|| i.username|| ' CASCADE';
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
      dbms_output.Put_line(SQLERRM);
END; 
but it looks like it doesn't work (I use DBeaver) - I test it with the following command:
SELECT t.username
    FROM DBA_USERS t
    WHERE t.username LIKE 'abc%'
    order BY username;
and it still shows me all those users.

Can anyone tell me please what is wrong with it ?

[Updated on: Fri, 27 December 2019 03:53]

Report message to a moderator

Re: Trying to delete bunch of users using loop but failing [message #678677 is a reply to message #678675] Fri, 27 December 2019 04:15 Go to previous messageGo to next message
John Watson
Messages: 8318
Registered: January 2010
Location: Global Village
Senior Member
You need to show what happens when you run the code. "it doesn't work" is not a documented Oracle error message.

Incidentally, are your schemas really abc%, and not ABC% ?
Re: Trying to delete bunch of users using loop but failing [message #678678 is a reply to message #678675] Fri, 27 December 2019 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 67290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Can anyone tell me please what is wrong with it ?
Read WHEN OTHERS.

Remove this clause and re-execute your PL/SQL block with SQL*Plus and copy and paste your session.

Re: Trying to delete bunch of users using loop but failing [message #678679 is a reply to message #678678] Fri, 27 December 2019 04:26 Go to previous messageGo to next message
vippysharma
Messages: 61
Registered: May 2013
Location: www
Member
Quote:
WHERE t.username LIKE 'abc%'
this is wrong.

Put it as t.username LIKE 'ABC%' or lower(t.username) like 'abc%' and it will work...

John is right.
Re: Trying to delete bunch of users using loop but failing [message #678681 is a reply to message #678679] Fri, 27 December 2019 06:15 Go to previous messageGo to next message
a100
Messages: 34
Registered: March 2019
Member
I have removed
when others
and now getting this error:
SQL Error [1940] [42000]: ORA-01940: cannot drop a user that is currently connected
ORA-06512: at line 13
ORA-06512: at line 13
Let's say I have a lot of users starting with "abc" (over 100 - this is testing DB).
Does that mean that if one of them cannot be deleted all the others can't be neither ?
Is it atomic ?
Re: Trying to delete bunch of users using loop but failing [message #678684 is a reply to message #678681] Fri, 27 December 2019 06:19 Go to previous messageGo to next message
Michel Cadot
Messages: 67290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you have red the link I posted you you'd know the answer.

[Updated on: Fri, 27 December 2019 06:21]

Report message to a moderator

Re: Trying to delete bunch of users using loop but failing [message #678685 is a reply to message #678684] Fri, 27 December 2019 06:37 Go to previous message
a100
Messages: 34
Registered: March 2019
Member
Thanks @Michel.
Understood.

[Updated on: Fri, 27 December 2019 07:24]

Report message to a moderator

Previous Topic: Output Dummy Record When No Record Exists
Next Topic: Validation of PAN Number in sql query
Goto Forum:
  


Current Time: Fri Aug 07 23:04:27 CDT 2020