Create or replace a role?

Question

How to create or replace a role (which may or may 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?

Answer 1

Decision

The combination of response data and 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 role creation report is not executed as expected:

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

Answer 2

The best practice is to try to create a role and then handle the corresponding exception gracefully if that happens; this means that you do not need to run potentially expensive data dictionary dictionaries:

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;

, PL/SQL, - . .

Answer 3

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 4

" " . Oracle, , . http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6012.htm

, , .

, - , , , DBA_ROLES.