Re: Permissions required for create materialized view dynamically

In article <DE2yd.539954$Pl.370054_at_pd7tw1no>, Jack Addington says...
>
>As soon as I do a set role none; then I can't do anything. I get
>insufficient privs.
>

that means you are getting the privs to do what you need

FROM A ROLE :) it is all about the role -- you do NOT have the direct privs necessary to create materialized views.

do you meet the prereq's documented for creating them?

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_63a.htm#2063839

(cut and pastes of what you are attempting to do -- with the accompanying error messages are better than "this is what I'm running" -- it is like we can see your monitor)

(when others should be outlawed....)

>Here is a sample that shows what I am trying to do.
>
>drop table test cascade constraints;
>
>create table test (f1 number, f2 number);
>
>ALTER TABLE EPM.TEST
>
>ADD CONSTRAINT TEST_PK
>
>PRIMARY KEY (f1)
>
>ENABLE
>
>VALIDATE;
>
>
>insert into test values (1,2);
>
>insert into test values (2,3);
>
>
>commit;
>
>
>select * from test;
>
>create snapshot ss_test refresh FORCE on demand
>
>as
>
>select f1, f2 from test;
>
>
>
>
>select * from ss_test;
>
>
>drop snapshot ss_test;
>
>
>declare
>
>begin
>
>execute immediate 'create view ss_test as select f1, f2 from test';
>
>end;
>
>/
>
>
>select * from ss_test;
>
>
>drop snapshot ss_test;
>
>
>create or replace procedure proc_test_ss as
>
>lv_Sql varchar2(1000);
>
>
>begin
>
>
>lv_sql := 'create view ss_test as select f1, f2 from test';
>
>
>execute immediate lv_sql;
>
>
>exception when others then raise_application_error(-20001,'arg - ' ||
>sqlerrm);
>
>
>end proc_test_ss;
>
>/
>
>execute proc_test_ss;
>
>select * from ss_test;
>
>
>
>

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation