CODEWITHSUNDEEP
oracle-databaseprocedure
Stephane Maarek
Stephane Maarek

Reputation: 5352

Procedure doesn't compile on sys.dba_policies

It is probably an easy mistake but I still can't spot it. Using my sec_admin schema, I can run the following:

 SELECT count(*) FROM sys.dba_policies;

Which outputs 1

Now I want to create a procedure using:

create or replace PROCEDURE pr_add_policy_row

   IS
   num NUMBER := 0;
   BEGIN
      SELECT count(*)
        INTO num
        FROM sys.dba_policies;

   END pr_add_policy_row;

But it gives me this error:

Error(6,7): PL/SQL: SQL Statement ignored
Error(8,18): PL/SQL: ORA-00942: table or view does not exist

What am I doing wrong here?

Thanks!

Upvotes: 1

Views: 237

Answers (1)

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

To be able to select from a table or view within a procedure, you need the select privilege with grant option. Let the DBA grant you

GRANT SELECT ON sys.dba_policies TO user3019499 WITH GRANT OPTION;

Upvotes: 2

Related Questions