Ignoring User Exists Error in Oracle

Question

I have created a script that creates Oracle users and grants them roles. I am unable to find a way to ignore the "user exists" error:

ORA-01920: user name '' conflicts with another user or role name.

I understand that when the script is ran, it is possible that the user already exists, but I want to ignore any returned errors. Is this possible?

My Oracle code:

CREATE USER "John" PROFILE "DEFAULT" IDENTIFIED BY "temppassword" ACCOUNT UNLOCK;

Edit: This question is not asking how to create a user if it doesn't exist. This question is asking how to ignore "the user exists" error. According to a previously asked question, the top answer stated

In general, Oracle scripts simply execute the CREATE statement, and if the object already exist, you'll get an error indicating that, which you can ignore. This is whaat all the standard Oracle deployment scripts do.

Answer 1

It isn't clear how you're running your script, but assuming its via SQL*Plus you can modify the behaviour when an error is encountered with the whenever sqlerror command.

If your script is setting that to exit at the moment, or you're picking that up from a startup script (login.sql, glogin.sql) you can change it back, or modify it temporarily:

...
-- do not stop on error
WHENEVER SQLERROR CONTINUE;
CREATE USER "John" PROFILE "DEFAULT" IDENTIFIED BY "temppassword" ACCOUNT UNLOCK;
-- to stop when later errors are encountered
WHENEVER SQLERROR EXIT FAILURE;
ALTER USER ...

You'll still see the ORA-01920 in the output but it will continue on to execute the next statement. This pattern is also useful for a protective drop of a schema object before attempting to create it.

Answer 2

Why can't you find if the user exists first?

SELECT COUNT(*) 
INTO V_count 
from ALL_USERS 
where username = 'YourUserName'

IF v_count = 0 THEN
  --create the user
  --execute the grants
ELSE
  ---log that the user already exists
END IF;

Answer 3

SET SERVEROUTPUT ON;
    DECLARE
  TYPE t_list IS TABLE OF VARCHAR2 (30);
  l_list t_list := t_list ('X0', 'X1', 'X2');
  e_user_already_exists EXCEPTION;
  PRAGMA EXCEPTION_INIT (e_user_already_exists, -1920);
BEGIN
  FOR l_iterator IN 1 .. l_list.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE ('Creating user ' || l_list (l_iterator));
    BEGIN
      EXECUTE IMMEDIATE 'CREATE USER "' || l_list (l_iterator) || '" PROFILE DEFAULT IDENTIFIED BY "WELCOME" ACCOUNT UNLOCK';
      EXECUTE IMMEDIATE 'GRANT SOME_APPLICATION_ROLE TO ' || l_list (l_iterator);
    EXCEPTION
      WHEN e_user_already_exists THEN
        DBMS_OUTPUT.PUT_LINE ('User exists, ignored');
      WHEN OTHERS THEN
        RAISE;
    END;
  END LOOP;
END;
/