how to resolve error while using grant admin option in execute immediate

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;
    /

Answer

Try to add a space after grant keyword:

EXECUTE IMMEDIATE 'grant '||v_dba_role_privs||' to '||v_Cloned_UserName||' WITH ADMIN OPTION';

Advertisement

Answer