Re: Permissions required for create materialized view dynamically

Jack Addington wrote:

> I am trying to write a stored procedure that dynamically drops/creates a
> snapshot. I got all the sql right (I think) but I am running into a
> insufficient privileges message when it fires. If I take the same code an
> run it directly it creates the MV just fine. I am running as the schema
> owner right now. I even tried altering the code to do a create view and I
> got the same error message.
>
> What permissions could I be missing? The snapshot is fairly straight
> forward and selects only from local tables and uses a few procedure calls.
>
> thx
>
> jack

Likely it has nothing to do with permissions but you must use dynamic SQL (native dynamic SQL or DBMS_SQL).

A bigger issue is that doing this in a stored procedure is a horrible thing to do to a database. Why are you wanting to do this at all?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)


-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------
   http://www.newsfeed.com       The #1 Newsgroup Service in the World!
-----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =-----