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?
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With