Script to know the procedure of a schema owner

ACCEPT user_namen CHAR PROMPT 'Schema Owner: '

set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
spool show_procedures.lst
ttitle off
column datum new_value datum noprint
column owner format A20 heading 'Procedure|Owner'
column name format A20 heading 'Procedure|Name'
column type format A20 heading 'Procedure|Type'

ttitle left 'Defined Procedures, Functions, Packages'skip 2

select  distinct to_char(sysdate, 'MM/DD/YY') datum,
    substr(owner,1,12) owner,
    substr(name,1,27) name,
    substr(type,1,27) type
from dba_source
where owner LIKE UPPER('&user_namen')
order by 1,2,3,4;
spool off;
  • January 20, 2019 | 17 views
  • Comments