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.
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: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.