Privilege errors when using execute immediate?
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.