如何解决在executeimmediate中使用grant admin选项时的错误

hwazgwia  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(646)

我正在尝试运行下面的脚本。我的脚本在executeimmediate中没有“with admin option”就可以正常工作。但是当使用“with admin option”时,我得到下面的错误。
“错误报告-ora-00900:无效的sql语句ora-06512:位于第17 00900行。00000-“sql语句“”无效。

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

hpxqektj1#

尝试在后面添加空格 grant 关键字:

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

相关问题