Why V$DATABASE is NOT playing Nice?

Mr JG recently graduated from his internship program and the company hired him as Oracle Developer. He is excited and working hard on his first stored procedures. As soon as he started working he came across two issues and wants someone to provide solution and explain the root cause.  He would like to learn the cause and know of  any work around if available.

Here are his two questions.

1. Database A: I cannot access v$database to get the database Name. 
2. Database B: I can access v$database in SQL Developer and In SQL*Plus but not Inside A Stored Procedure. It throws PL/SQL: ORA-00942: table or view does not exist.

His questions are valid and asked for more detail on the error message  for Database A as I wanted tackle his question in order. He did a good job by providing Oracle error number and message on second question.

Problem 1:

Database A: I cannot access v$database to get the database Name. See the error number and message below.

   
SQL> select name from v$database;
     select name from v$database
                 *
     ERROR at line 1:
     ORA-00942: table or view does not exist

Root Cause:
Mr Junior is using an account this is missing  "SELECT ANY DICTIONARY"  privilege or SELECT_CATALOG_ROLE role.

Solution:
He can open a ticket to DBA to add one those roles or privilege to his account. Some corporation will let you have those privilege and some may reject your request for security reason without a question.
SYSDBA are not confident with Mr Junior coding ability, therefore they are not going to grant
SELECT ANY DICTIONARY privilege. Junior needs it to create a procedure where he checks the database name before doing an insert/delete/update. How do you manage his without actually granting new priv/roles? There a two solutions:

Solution 1: SELECT UPPER(sys_context('USERENV','DB_NAME')) AS DB_NAME FROM DUAL; Solution 2: SELECT global_name FROM global_name;

These queries were run as it is on SQL Developer and within a Stored Procedure and worked on both.
These are great solutions to his problem #1 without having to bother DBAs.

Problem 2:
Database B: I can access v$database in SQL Developer and in SQL*Plus but not from a procedure.
     It throws PL/SQL: ORA-00942: table or view does not exist.

Root Cause:
This sounds weird and strange compared to problem #1 which we solved it. He is able to select from v$database but unable to use the query inside a procedure. Why are you seeing this strange behavior? Before finding a solution, let's try to re-create an issue. To re-create this issue, we will create an user test and grant some privileges/roles to access to database:

CREATE USER test identify by test123;
GRANT CREATE SESSION, CONNECT to test;
GRANT SELECT_CATALOG_ROLE to test;

Now, run the query below as test user:

select name from v$database;
It works

CREATE OR REPLACE PROCEDURE db_name_check_sp
AS
db_name VARCHAR2(25);
BEGIN
SELECT name INTO db_name FROM v$databse;
END;
/
show error;

Errors for PROCEDURE SOLO.DB_NAME_CHECK_SP_AS:

LINE/COL ERROR
-------- -----------------------------------------------
5/3      PL/SQL: SQL Statement ignored
5/33     PL/SQL: ORA-00942: table or view does not exist

Now, we are going to add grant SELECT ANY DICTIONARY priv to test.

GRANT SELECT ANY DICTIONARY TO test;

CREATE OR REPLACE PROCEDURE db_name_check_sp
AS
db_name VARCHAR2(25);
BEGIN
SELECT name INTO db_name FROM v$databse;
END;
/
show error;

Procedure DB_NAME_CHECK_SP compiled
No errors.

We solved problem #2 by granting some additional privilege. A DBA or developer should always know what each priv/role does before assigning to user therefore, we need to understand the difference between SELECT ANY DICTIONARY vs SELECT_CATALOG_ROLE and assign is appropriately. SELECT ANY DICTIONARY is a SYSTEM privilege that lets you select from dictionary views. You can select from any dictionary and used it within a procedure. A direct grant is required for any user to be able to use within a procedure.

SELECT_CATALOG_ROLE is a ROLE that lets you select from a dictionary view which cannot be used within a PL/SQL procedures/functions/packages.You can select from v$database but can't be used to create a procedure. Roles are collections of prive which aren't granted directly. In order to include in a procedure, you will need a direct grant of the database objects to a user.

Hey, If someone else has some background on this and would like to chime in, leave a comment below. If you’ve got a question for me, you can email me at pbaniya04@gmail.com. If you liked this article and want to read more tips, articles, and tutorial related database, please subscribe to my blog and share this with your buddies.