Oracle PL/SQL script to drop a user if not exists

This script will run a query to count the number of users with a specified user name and if the count!=0 then drop user query is executed.


DECLARE
    u_count number;
    user_name VARCHAR2 (50);

    BEGIN

        u_count :=0;
        user_name :='datauser';
           
        SELECT COUNT (1) INTO u_count FROM dba_users WHERE username = UPPER (user_name);

             IF u_count != 0
             THEN
                 EXECUTE IMMEDIATE ('DROP USER '||user_name||' CASCADE');
              END IF;

              u_count := 0;
       
        EXCEPTION
           WHEN OTHERS
              THEN
                     DBMS_OUTPUT.put_line (SQLERRM);
                     DBMS_OUTPUT.put_line ('   ');

    END;

/



Include the above script in a file .sql (Ex: DropOracleUser.sql). Make sure to replace the username with correct name which is 'datauser' in above script. You can run the above oracle PL/SQL script in sqlPlus as follows.

sql> START DropOracleUser.sql

Comments

  1. wow great Article, the details you have provided are much clear, easy to understand, if you post some more Article, it will be very much useful for me.
    PL/SQL Training in Chennai

    ReplyDelete

Post a Comment

Popular posts from this blog

How to add Maven dependency from a relative path referencing a local jar

PHP-SOAP web service with out a WSDL

Manually install a maven dependency