[SOLVED] Creating an Oracle User if it doesn't already exist

Creating an Oracle User if it doesn't already exist

I am trying to create a script that will create users if they do not already exist.

CREATE USER "Kyle" PROFILE "DEFAULT" IDENTIFIED BY "password" ACCOUNT UNLOCK
WHERE NOT IN  //Also tried 'WHERE NOT EXISTS'
(
    SELECT username FROM all_users WHERE username = 'Kyle'
)

The following error is given:

SQL Error: ORA-00922: missing or invalid option

I was able to do this in SQL Server 2008 by using:

IF NOT EXISTS
(SELECT name FROM master.sys.server_principals
WHERE name = 'Kyle')
BEGIN
    CREATE LOGIN Kyle WITH PASSWORD = 'temppassword' MUST_CHANGE, CHECK_EXPIRATION=ON, CHECK_POLICY=ON
END

Is there a similar way in Oracle to check if a user already exists before attempting to create a new user?

Solution

Before Oracle 23c, the IF NOT EXISTS syntax available in SQL Server is not available in Oracle.

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 what all the standard Oracle deployment scripts do.

However, if you really want to check for existence, and only execute if object doesn't exist, thereby avoiding the error, you can code a PL/SQL block. Write a SQL that checks for user existence, and if it doesn't exist, use EXECUTE IMMEDIATE to do CREATE USER from the PL/SQL block.

An example of such a PL/SQL block might be:

declare
userexist integer;
begin
  select count(*) into userexist from dba_users where username='SMITH';
  if (userexist = 0) then
    execute immediate 'create user smith identified by smith';
  end if;
end;
/

Since Oracle 23c, a user can be created like this:

create user if not exists smith identified by smith;