Can't execute a Grant with Dynamic SQL?
jack mMay 9 2013 — edited May 10 2013Hi,
Any help would be most appreciated.
I am trying to create a stored procedure that will create all my privilege framework. It queries the dictionary and creates dynamic plsql to create grants.
The problem is that when I run this as a script in SQLPLUS it works, but when I run the same code from a the stored procedure it gives that that the table does not exist.
Thanks for your support and insights.
DECLARE
vTable_Name VARCHAR2(30);
vStatement_Command LONG;
BEGIN
vTable_Name := 'employee';
vStatement_Command := 'GRANT INSERT ON MyShema.' || vTable_Name || ' TO "MyUser"';
DBMS_OUTPUT.PUT_LINE (vStatement_Command);
EXECUTE IMMEDIATE vStatement_Command;
END;
/