ora-990 in dynamic sql
I'm trying to use dynamic sql to grant or revoke roles to a user account in an Oracle 12c database. I tried various things, eventually working up to the following code to try three methods of dynamic SQL. The three methods are DBMS_SQL, bind variables and concatenated statement methods.
SET SERVEROUTPUT ON
REVOKE cppm_admin FROM wbmarsh1;
DECLARE
cursor_name INTEGER;
rows_processed INTEGER;
v_stmt CLOB := NULL;
v_role VARCHAR2(20) := 'CPPM_ADMIN';
v_user VARCHAR2(20) := 'wbmarsh1';
BEGIN
BEGIN
cursor_name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_name,'GRANT :role TO :user',DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(cursor_name,':role','CPPM_ADMIN');