Re: Permissions required for create materialized view dynamically

My system provides a data entry mechanism for research projects. The data entry module provides a mechanism to build a 'questionnaire'. The answers to these questions can be numbers, text, dates, memos, or even attachments. To allow for a dynamic set of questionnaires to be created the data is captured in 1 row for each question, allowing different result types. So instead of one table with each answer a column for each questionnaire, the system is dynamic.

However when the time comes to do data analysis it is impossible to analyse the data in rows, it must be transposed into columns (like excel). So, I have written some simple code that loops through the definition of the questionnaire and transposes the data into a useful excel type format. Because this is a commercial product and not a single inhouse project there is not someone around to create a snapshot everytime they modify a questionnaire.

The snapshot then provides a very simple source for them to use for data extracting.

You seem to have lots of good ideas... any other thoughts?

"DA Morgan" <damorgan_at_x.washington.edu> wrote in message news:41c8d40c$1_4_at_127.0.0.1...
> 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
> =-----
Received on Tue Dec 21 2004 - 23:08:57 CST