CODEWITHSUNDEEP
plsqldynamic-sql
maks
maks

Reputation: 6006

Dynamic Sql, problem with binding

I have a block:

DECLARE
  stmnt VARCHAR2(100);
  rol VARCHAR2(10); --role name
  tab_name VARCHAR2(10); --table name
BEGIN
  rol := '&Role_name';
  stmnt := 'create role ' || rol;
  EXECUTE IMMEDIATE stmnt;
  stmnt := 'grant :p on ' || '&tab_name' || ' to ' || rol;
  EXECUTE IMMEDIATE stmnt using '&Privilege';
END;

when I execute this block, after enetering the privilege SELECT, Oracle gives me an error that missing or invalid privilege ORA-00990: missing or invalid privilege

Why it doesn't bind variable?

Upvotes: 1

Views: 223

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132750

You cannot bind Oracle names, only data values. Do this instead:

DECLARE
  stmnt VARCHAR2(100);
  rol VARCHAR2(10); --role name
  tab_name VARCHAR2(10); --table name
BEGIN
  rol := '&Role_name';
  stmnt := 'create role ' || rol;
  EXECUTE IMMEDIATE stmnt;
  stmnt := 'grant &Privilege. on &tab_name. to ' || rol;
  EXECUTE IMMEDIATE stmnt;
END;

Upvotes: 2

Related Questions