/
utplsql_style_check.sql
68 lines (58 loc) · 2.38 KB
/
utplsql_style_check.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
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