I need to create a view from a procedure. I could do it from an anonymous block whereas I am unable to do that from a Stored Procedure. Should I need a specific privilege for performing this operation? If so what would be the reason behind it? Will that privilege be given in production databases?
CREATE TABLE t11 AS SELECT * FROM DUAL;
CREATE OR REPLACE PROCEDURE p_etl_test_view
AS
BEGIN
EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW v_t11 AS SELECT * FROM t11';
END;
/
PROCEDURE P_ETL_TEST_VIEW compiled
Elapsed: 00:00:00.131
BEGIN
EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW v_t11 AS SELECT * FROM t11';
END;
/
anonymous block completed
Elapsed: 00:00:00.069
BEGIN
p_etl_test_view;
END;
/
Error starting at line 13 in command:
BEGIN
p_etl_test_view;
END;
Error report:
ORA-01031: insufficient privileges
ORA-06512: at "OCCSS_ENTMT_HK_DEV_01.P_ETL_TEST_VIEW", line 4
ORA-06512: at line 2
01031. 00000 - "insufficient privileges"
*Cause: An attempt was made to change the current username or password
without the appropriate privilege. This error also occurs if
attempting to install a database without the necessary operating
system privileges.
When Trusted Oracle is configure in DBMS MAC, this error may occur
if the user was granted the necessary privilege at a higher label
than the current login.
*Action: Ask the database administrator to perform the operation or grant
the required privileges.
For Trusted Oracle users getting this error although granted the
the appropriate privilege at a higher label, ask the database
administrator to regrant the privilege at the appropriate label.
Elapsed: 00:00:00.100
SELECT *
FROM dba_sys_privs
WHERE grantee = 'OCCSS_ENTMT_HK_DEV_01'
OR grantee IN (SELECT granted_role
FROM dba_role_privs
where grantee = 'OCCSS_ENTMT_HK_DEV_01')
ORDER BY 1;