Bypassing ORA-00942: table or view does not exist

The infamous ORA-00942: table or view does not exist error is a constant source of frustration with Oracle.

Ususally, the cause for this error is that someone is granted access to a table via a role rather than directly. So, the user can select from that_table but as soon as he uses the statement in a compiled PL/SQL source, it won't work anymore, erroring out with this ORA-00942.

I can demonstrate this easily: First, a fresh user is created and given a few privileges:

connect / as sysdba create user ipc_user identified by ipc_user; grant create procedure, create session, select_catalog_role to ipc_user;

The user has been granted select_catalog_role so he can do

select count(*) from v$process;

and

select count(*) from v$session;

Now, the user wants to create a stored procedure that shows him the amount of memory used for his process:

create or replace function tq84_proc_memory return varchar2 as v_result varchar2(200); begin select 'Used: ' || round(pga_used_mem /1024/1024)||', '|| 'Alloc: ' || round(pga_alloc_mem /1024/1024)||', '|| 'Freeable: ' || round(pga_freeable_mem/1024/1024)||', '|| 'PGA Max: ' || round(pga_max_mem /1024/1024) into v_result from v$process where addr = (select paddr from v$session where sid = sys_context('USERENV','SID')); return v_result; end tq84_proc_memory; /

This procedure won't compile for the reasons outlined in the beginning of this post:

-- 5/5 PL/SQL: SQL Statement ignored -- 15/33 PL/SQL: ORA-00942: table or view does not exist

So, what to do? For such reasons, I have created the IPC PL/SQL package. The package's function exec_plsql_in_other_session uses dbms_job to create another session that can make full use of the granted privileges and also uses dbms_pipe to return a value to the caller.

Of course, I need to grant the required privileges also:

grant execute on dbms_job to ipc_user; grant execute on dbms_pipe to ipc_user; grant create job to ipc_user;

After installing the packages, I can rewrite my function like so:

create or replace function tq84_proc_memory return varchar2 as v_result varchar2(200); begin select 'Used: ' || round(pga_used_mem /1024/1024)||', '|| 'Alloc: ' || round(pga_alloc_mem /1024/1024)||', '|| 'Freeable: ' || round(pga_freeable_mem/1024/1024)||', '|| 'PGA Max: ' || round(pga_max_mem /1024/1024) into v_result from v$process where addr = (select paddr from v$session where sid = sys_context('USERENV','SID')); return v_result; end tq84_proc_memory; /

Now, I can use the function to report some memory figures

select tq84_proc_memory from dual;

Links

Github:

SQL demonstration script