Alternative To SELECT_CATALOG_ROLE In Oracle

It may be useful to allow developers to look at the plsql (views, procs, etc) for a schema without granting the SELECT_CATALOG_ROLE role. This view will provide access, with the possible drawback of adding an object to the SYS schema.

Run this as sys, like:

sqlplus "/ as sysdba" @script.sql

CREATE OR REPLACE VIEW SYS.plsql_ASCHEMAOWNER (OWNER,NAME,TYPE,LINE,TEXT) AS
select OWNER,NAME,TYPE,LINE,TEXT
from sys.dba_source
where owner='ASCHEMAOWNER';

create or replace public synonym plsql_ASCHEMAOWNER for sys.plsql_ASCHEMAOWNER;
grant select on sys.plsql_ASCHEMAOWNER to public;

desc plsql_aschemaowner
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)