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

Popular posts from this blog

PHP-SOAP web service with out a WSDL

Boomi Mapping - Removing special chars from an input

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