Create or replace role?

Question

Initial Question

How do you create or replace a role (that might or might not exist) in Oracle? For example, the following does not work:

CREATE OR REPLACE ROLE role_name;
  GRANT SELECT ON SCM1_VIEW_OBJECT_VW TO role_name;

Any way to do this without PL/SQL?

Update #1

The following does not compile:

CREATE OR REPLACE FUNCTION create_role( role_name IN VARCHAR2 ) RETURN BOOLEAN IS
BEGIN
  CREATE ROLE role_name;

EXCEPTION
  WHEN OTHERS THEN
    -- ORA-01921: The role name already exists, so ignore the error.
    IF SQLCODE = -01921 THEN
      RETURN TRUE;
    ELSE
      RAISE;
    END IF;

  RETURN FALSE;
END create_role;

Error: PLS-00103: Encountered the symbol "CREATE" ... Line: 3 Text: CREATE ROLE role_name;

Answer 1

There is no syntax for "create or replace" for roles. Not sure of your version of Oracle but this hasn't changed much that I can recall. http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6012.htm

You can grant select to the role multiple times and it will accept the grant every time provided the role exists.

You could do an anonymous block and ignore the execption if the role already exists or something else where you see if the role exists by querying DBA_ROLES.

Answer 2

DECLARE
  v_dummy NUMBER;
BEGIN
  SELECT 1
  INTO v_dummy
  FROM dba_roles
  WHERE role = 'MY_ROLE_NAME';
EXCEPTION
  WHEN no_data_found THEN
    EXECUTE IMMEDIATE 'CREATE ROLE my_role_name';
END;
/

Answer 3

Best practice is to attempt the creation of the role, then handle the appropriate exception gracefully if it occurs; this means you don't need to run potentially expensive data dictionary queries:

begin
  execute immediate 'create role role_name';
exception
  when others then
    --"ORA-01921: role name 'x' conflicts with another user or role name"
    if sqlcode = -01921 then 
      null;
    else
      raise;
    end if;
end;

And yes, you need PL/SQL to do this - it's the best tool for this job, anyway.

Answer 4

Solution

A combination of the given answers and a pragma control accomplishes this task for Oracle 10g.

CREATE OR REPLACE PROCEDURE create_role( role_name IN VARCHAR2 ) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  EXECUTE IMMEDIATE 'CREATE ROLE '||role_name;
EXCEPTION
  WHEN OTHERS THEN
    -- ORA-01921: If The role name exists, ignore the error.
    IF SQLCODE <> -01921 THEN
      RAISE;
    END IF;
END create_role;

Test

This sequence works:

DROP ROLE role_name;
CREATE ROLE role_name;
CALL create_role( 'role_name' );
CALL create_role( 'role_name' );

The final create role statement fails, as expected:

DROP ROLE role_name;
CALL create_role( 'role_name' );
CREATE ROLE role_name;

Thanks for the help!