Privilege errors when using execute immediate?

[ssmith]

Trying to create a materialsed view using execute immediate and comes up with the following error:
QL> exec xxma_build_pace_views_pkg.build_pace_views;
BEGIN xxma_build_pace_views_pkg.build_pace_views; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "PACE.XXMA_BUILD_PACE_VIEWS_PKG", line 30
ORA-06512: at line 1

I can create this matarialised view without using execute immediate, so I have all the privileges.
I have create any table, create any view, drop any table, drop any view. I have even tried DBA privilege.
The following is the procedure:

PROCEDURE build_pace_views IS
--
v_sql VARCHAR2(10000);
v_view_exist NUMBER;
--
BEGIN
--
--call procedure to drop all pace disco views
drop_module_views('XXMA_DISCO_PACE%');
drop_materialized_views('XXMA_DISCO_PACE%');
--
--create all pace disco views
--
--xxma_disco_pace_sctr_bgt_mv
v_sql :=
'
CREATE MATERIALIZED VIEW xxma_disco_pace_sctr_bgt_mv
REFRESH WITH ROWID
AS
SELECT TRUNC(pf.paceflgt_scheduled_date_time) scheduled_date,
SUM(pf.paceflgt_no_of_passengers) pax_no
FROM pace.paceflgt pf
GROUP BY TRUNC(pf.paceflgt_scheduled_date_time)

';
--
EXECUTE IMMEDIATE v_sql;
Thanks for any help.

[PAVB]

... and you question is?

[ssmith]

My question is :

Trying to create a materialsed view using execute immediate and comes up with the following error:
QL> exec xxma_build_pace_views_pkg.build_pace_views;
BEGIN xxma_build_pace_views_pkg.build_pace_views; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "PACE.XXMA_BUILD_PACE_VIEWS_PKG", line 30
ORA-06512: at line 1

What privileges do I need to create materialised view using 'execute immediate'?

[PAVB]

you have to grant the privs to the user that owns the package

[Bore]

The stored procedures are not able to use privileges granted via roles, so if that's the case, grant privileges directly to the user or define the procedure with invoker rights

[ssmith]

I have granted privileges directly to the owner of the package, who is also the owner of the table, that I think it needs, but it still doesn't work. What privilege am I missing?
the list of privileges are as follows:
'create any procedure', 'create any sequence','create any table','create any view','create table','drop any table','drop any view','execute any procedure','select any sequence','select any table'.
Thanks.

[PAVB]

...and you are trying to create a Materialized View, aren't you?

Where is the CREATE MATERIALIZED VIEW priv? can't see it in your list.

[ssmith]

I have fixed it. I finally resorted to using invoker rights.
Thanks for all your help. Rgds. Sheryl