Re: HELP - PL/SQL using DBMS_SQL in Store Procedures doesn't work

Thanks for the idea.

I went back in and re-created the procedure as the schema owner of the table. So access to all tables should be a given, since the schema owner is the one who created the tables. But then, when I run it, it still bombs out with the same message. Regardless of whether or not I execute the procedure as the schema owner, or if I grant access to the procedure to my regular id and run it from there.

I am checking with our DBA's to make sure of how we are granted CREATE TABLE privs to make sure that is not the problem.

Pavel Luzanov wrote:
>
> Larry --
> Check your privileges.
> You must have SELECT privelege on w_wellwork_common, w_selected_wellwork
> tables granted directly:
> SQL> GRANT SELECT ON w_wellwork_common TO <user_name>;
> GRANT SELECT ON w_selected_wellwork TO <user_name>;
> or
> GRANT SELECT ON w_wellwork_common TO public;
> Also, You must have CREATE TABLE privilege granted directly :
> SQL> GRANT CREATE TABLE TO <user_name>;
> or
> SQL> GRANT CREATE TABLE TO public;
>
> You can not create table in PL/SQL with CREATE TABLE privilege granted via
> database role
> (in many cases, this is RESOURCE role).
> You must have direct access to all objects which called from PL/SQL.
> It is PL/SQL constraint, not SQL, therefore you may create tables
> directly in sqlplus.
> -----
> Pavel Luzanov
> Kuzbassprombank
>
 

-- 
**********************
Larry Jones
lljo_at_chevron.com