Dynamic Sql, problem with binding

Question

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?

Tony Andrews · Accepted Answer · 2010-11-16 17:04:14Z

2

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;

answered Nov 16, 2010 at 17:04

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic Sql, problem with binding

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related