I have the below select statement which works in SQLPLUS in my schema
select count(1) from gv$lock where id1 ='202';
But the same select statement in my function in same schema FN_GET_LOCK throws error "table or view does not exist" in PLSQL
select count(1) into l_lockcnt from gv$lock where id1 = l_lock_hold;
Any idea how to make the SQL statement work in PLSQL
This question is not duplicate since I am not getting "insufficient privileges error"
It appears that you've got the SELECT privilege on gv$lock via a role. If that's so, well - it won't work in named PL/SQL procedures. You'll have to grant that privilege directly to the user.
[EDIT: About roles]
Generally speaking - not only about roles - a good resource for further investigation is the dictionary. Here's a sample query you might use to find that info:
SQL> select * from dictionary where lower(table_name) like '%role%';
TABLE_NAME COMMENTS
------------------------------ --------------------------------------------------
DBA_CONNECT_ROLE_GRANTEES Information regarding which users are granted CONN
ECT
DBA_ROLES All Roles which exist in the database
DBA_ROLE_PRIVS Roles granted to users and roles
DBA_SCHEDULER_JOB_ROLES All scheduler jobs in the database by database rol
e
USER_ROLE_PRIVS Roles granted to current user
ROLE_ROLE_PRIVS Roles which are granted to roles
ROLE_SYS_PRIVS System privileges granted to roles
ROLE_TAB_PRIVS Table privileges granted to roles
SESSION_ROLES Roles which the user currently has enabled.
9 rows selected.
SQL>
Let's create a role and grant it some privileges:
SQL> create role rl_scott_sel;
Role created.
SQL> grant select on emp to rl_scott_sel;
Grant succeeded.
SQL> grant select on bonus to rl_scott_Sel;
Grant succeeded.
SQL>
OK, so - what does the ROLE_TAB_PRIVS contain? It looks like a good starting point, reading the comments above.
SQL> select role, owner, table_name, privilege
2 from role_Tab_privs;
ROLE OWNER TABLE_NAME PRIVILEGE
--------------- ---------- ---------- ----------
RL_SCOTT_SEL SCOTT BONUS SELECT
RL_SCOTT_SEL SCOTT EMP SELECT
SQL>
