Dynamic SQL privilege

[NuWood]

Hi

I am having trouble with the EXECUTE IMMEDIATE statement. I am generating a CREATE VIEW command which works great as an annonymous block. When I put the same code into a package I get ORA-1031 insufficient privileges error which talks about trying to change the user and password. The view needs to be in them same schema as the package and the call is made from the same schema.

 

[NuWood]

Hi everyone

I have forgotten the requirement to explicitly GRANT privilege to the user when run from within a procedure etc....

i.e GRANT CREATE VIEW TO MYUSER;

is needed when you want to create a view from within a procedure using DBMS_SQL or EXECUTE IMMEDIATE