alter session set plscope_settings= 'identifiers:all';

set linesize 300

set pagesize 10000

--install or comple all code here

exec dbms_utility.compile_schema(USER,compile_all => TRUE,reuse_settings => FALSE);

set echo off

set feedback off

var errcnt number

column errcnt_a noprint new_value errcnt_a

column errcnt_l noprint new_value errcnt_l

column errcnt_c noprint new_value errcnt_c

column NAME FORMAT A30

column TYPE FORMAT A18

column OBJECT_NAME FORMAT A30

column OBJECT_TYPE FORMAT A18

column USAGE FORMAT A16

column LINE FORMAT 99999

column COL FORMAT 9999

PROMPT parameters that are not prefixed with "a_"

select name, type, object_name, object_type, usage, line, col, count(*) over() errcnt_a

from user_identifiers

where type like 'FORMAL%' and usage = 'DECLARATION'

and name != 'SELF'

and name not like 'A#_%' escape '#'

order by object_name, object_type, line, col

;

PROMPT

PROMPT

PROMPT variables that are not prefixed with "l_"

select i.name, i.type, i.object_name, i.object_type, i.usage, i.line, i.col, count(*) over() errcnt_l

from user_identifiers i

join user_identifiers p

on p.object_name = i.object_name and p.object_type = i.object_type

and i.usage_context_id = p.usage_id

where i.type like 'VARIABLE' and i.usage = 'DECLARATION'

and i.object_type not in ('TYPE')

and (i.name not like 'L#_%' escape '#' and p.type in ('PROCEDURE','FUNCTION','ITERATOR','TRIGGER')

or i.name not like 'G#_%' escape '#' and p.type not in ('PROCEDURE','FUNCTION','ITERATOR','TRIGGER'))

and p.type != 'RECORD'

order by object_name, object_type, line, col

;

PROMPT

PROMPT

PROMPT constants that are not prefixed with with "c_"

PROMPT global constants that are not prefixed with "gc_"

select i.name, i.type, i.object_name, i.object_type, i.usage, i.line, i.col, count(*) over() errcnt_c

from user_identifiers i

join user_identifiers p

on p.object_name = i.object_name and p.object_type = i.object_type

and i.usage_context_id = p.usage_id

where i.type like 'CONSTANT' and i.usage = 'DECLARATION'

and (i.name not like 'C#_%' escape '#' and p.type in ('PROCEDURE','FUNCTION','ITERATOR')

or i.name not like 'GC#_%' escape '#' and p.type not in ('PROCEDURE','FUNCTION','ITERATOR'))

order by object_name, object_type, line, col

;

exec :errcnt := nvl('&errcnt_a',0) + nvl('&errcnt_l',0) + nvl('&errcnt_c',0);

--quit :errcnt

exit :errcnt