I am trying to run the below script. My script works fine without ' WITH ADMIN OPTION' in EXECUTE IMMEDIATE. But when using ' WITH ADMIN OPTION' i get below error.
"Error report - ORA-00900: invalid SQL statement ORA-06512: at line 17 00900. 00000 - "invalid SQL statement"".
SET SERVEROUTPUT ON;
DECLARE
v_Model_UserName VARCHAR2(30) := UPPER('&Model_UserName');
v_Cloned_UserName VARCHAR2(30) := UPPER('&Cloned_UserName');
v_dba_role_privs VARCHAR2(3000); -- for dba_role_privs
--- selecting the roles from model user (from dba_role_privs table)
CURSOR c_role_privs (var01 Varchar2 )is
SELECT granted_role from dba_role_privs where grantee = var01;
BEGIN
--- granting the roles from model user to cloned user (from dba_role_privs table)
OPEN c_role_privs (v_Model_UserName);
LOOP
FETCH c_role_privs INTO v_dba_role_privs;
EXIT WHEN c_role_privs%NOTFOUND;
EXECUTE IMMEDIATE 'grant'||v_dba_role_privs||' to '||v_Cloned_UserName||' WITH ADMIN OPTION';
END LOOP;
CLOSE c_role_privs;
END;
/
Try to add a space after grant
keyword:
EXECUTE IMMEDIATE 'grant '||v_dba_role_privs||' to '||v_Cloned_UserName||' WITH ADMIN OPTION';