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.

Do you still want to apply the grants if it already exists? What about (re-)setting the password, and unlocking? Do you want any notification? And how are the user names provided - are you sure you won't have a clash with a built-in account name? It doesn't really seem like an error you'd want to ignore. — Alex Poole, Jun 10 '15 at 19:39
possible duplicate of [Creating an Oracle User if it doesn't exist already](http://stackoverflow.com/questions/30710990/creating-an-oracle-user-if-it-doesnt-exist-already) — Mark J. Bobak, Jun 10 '15 at 21:47
@MarkJ.Bobak That question was about checking for a user and determining if it exists. If so, the user would not be created. This question is asking something different. This question is about ignoring errors when a script is ran in Oracle. One of the answers on your posted question recommended that instead of complicated code, just ignore the error. — Kyle Williamson, Jun 11 '15 at 13:12
@AlexPoole Hello. I was told that it is an error you wish to ignore. On the question Mark posted, the highest voted answer said: "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." — Kyle Williamson, Jun 11 '15 at 13:14
Right, but are you trying to ignore the error like the standard deployment scripts do, or stop the error being raised in the first place? Your question says both 'ignore' and 'don't want to return' errors, so it isn't clear what you want to see when the script runs. Are you running from SQL\*Plus and just need to change the `whenever sqlerror` behaviour, perhaps? — Alex Poole, Jun 11 '15 at 13:19
I wish to ignore the error like the standard deployment scripts do. I have edited my question to better explain. This script will be ran on different computers through SQL*Plus. @AlexPoole. The grants will be applied even if the user already exists. — Kyle Williamson, Jun 11 '15 at 13:22
Unrelated, using quoted identifiers is in general a bad idea - especially for user names. That will give you too much trouble in the long run. — , Jun 11 '15 at 13:23
If you run this script through `sqlplus` you can use `whenever sqlerror continue` at the beginning of the script to ignore errors. — , Jun 11 '15 at 13:23
@a_horse_with_no_name Would you recommend I replace the quotations with brackets? ( [ ] ) — Kyle Williamson, Jun 11 '15 at 13:23
`[ ]` are invalid in SQL identifiers. Quotes make identifiers case-sensitive, `"John"` is a different username than `"JOHN"` or `"john"` - just use `john`. It's best to use no quotes at all `create user john ...` — , Jun 11 '15 at 13:25

score 1 · Answer 1 · answered Jun 10 '15 at 19:46

1

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;

answered Jun 10 '15 at 19:46

kevinskio

4,431
1
22
36

The user could be created in another session after the SELECT and before the CREATE. – David Balažic Oct 06 '17 at 13:09

score 1 · Answer 2 · answered Jun 10 '15 at 23:48

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;
/

score 1 · Accepted Answer · answered Jun 11 '15 at 13:24

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.

Ignoring User Exists Error in Oracle

3 Answers3