PL/SQL (MOSC)

MOSC Banner

ora-990 in dynamic sql

edited Nov 19, 2017 4:05AM in PL/SQL (MOSC) 3 commentsAnswered

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');

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center