-
Notifications
You must be signed in to change notification settings - Fork 14
Expand file tree
/
Copy path11g_plscope.sql
More file actions
174 lines (138 loc) · 4.74 KB
/
Copy path11g_plscope.sql
File metadata and controls
174 lines (138 loc) · 4.74 KB
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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL'
/
CREATE OR REPLACE PACKAGE plscope_pkg
IS
FUNCTION plscope_func (plscope_fp1 NUMBER)
RETURN NUMBER;
PROCEDURE plscope_proc (plscope_pp1 VARCHAR2);
END plscope_pkg;
/
CREATE OR REPLACE PROCEDURE plscope_proc1
IS
plscope_var1 NUMBER := 0;
BEGIN
plscope_pkg.plscope_proc (TO_CHAR (plscope_var1));
DBMS_OUTPUT.put_line (SYSDATE);
plscope_var1 := 1;
END plscope_proc1;
/
/* Verify PL/Scope setting. */
SELECT name, plscope_settings
FROM user_plsql_object_settings
WHERE name LIKE 'PLSCOPE%'
/
/* Output...
PLSCOPE_SETTINGS
-------------------
IDENTIFIERS:ALL
*/
/* Display all declarations */
SELECT name, signature, TYPE
FROM user_identifiers
WHERE name LIKE 'PLSCOPE%' AND usage = 'DECLARATION'
ORDER BY object_type, usage_id
/
/* Output...
NAME SIGNATURE TYPE
PLSCOPE_PKG 7DFBE4474A77569165B7DCB606761B81 PACKAGE
PLSCOPE_FUNC 78168BCBE1511996C92DEA6FD93E0484 FUNCTION
PLSCOPE_FP1 864F31A5B51B94097568688379D5959C FORMAL IN
PLSCOPE_PROC F51FC44CA81F59C6B428AB27C6415B2E PROCEDURE
PLSCOPE_PP1 9124512252B0AB1320818EADAAD87162 FORMAL IN
PLSCOPE_PROC1 4A24FD31BEA28212C696235F192E6CEE PROCEDURE
PLSCOPE_VAR1 401F008A81C7DCF48AD7B2552BF4E684 VARIABLE
*/
/* Find local variable declarations */
SELECT a.name variable_name, b.name context_name, a.signature
FROM user_identifiers a, user_identifiers b
WHERE a.usage_context_id = b.usage_id
AND a.TYPE = 'VARIABLE'
AND a.usage = 'DECLARATION'
AND a.object_name = 'PLSCOPE_PROC1'
AND a.object_name = b.object_name
ORDER BY a.object_type, a.usage_id
/
/* Output ...
VARIABLE_NAME CONTEXT_NAME SIGNATURE
--------------- ------------ --------------------------------
A F1 0F0D30D4BC6F7CC34D6BF73469DEF737
PR1 P1 AE40893577009279A269896AAD7B40F6
*/
/* Find all usages of local variable "plscope_var1" */
SELECT usage, usage_id, object_name, object_type
FROM user_identifiers sig
, (SELECT a.signature
FROM user_identifiers a, user_identifiers b
WHERE a.usage_context_id = b.usage_id
AND a.TYPE = 'VARIABLE'
AND a.usage = 'DECLARATION'
AND a.object_name = 'PLSCOPE_PROC1'
AND a.object_name = b.object_name) variables
WHERE sig.signature = variables.signature
ORDER BY object_type, usage_id
/
/* Output....
USAGE USAGE_ID OBJECT_NAME OBJECT_TYPE
----------- ---------- ------------------------------ -------------
DECLARATION 3 PLSCOPE_PROC1 PROCEDURE
ASSIGNMENT 4 PLSCOPE_PROC1 PROCEDURE
REFERENCE 7 PLSCOPE_PROC1 PROCEDURE
ASSIGNMENT 9 PLSCOPE_PROC1 PROCEDURE
*/
/* From the declaration of "a", determine its type.
NOTE: only useful if the STANDARD package has been
compiled with IDENTIFIERS:ALL.
*/
SELECT a.name, a.TYPE
FROM user_identifiers a
, user_identifiers b
, (SELECT a.signature
FROM user_identifiers a, user_identifiers b
WHERE a.usage_context_id = b.usage_id
AND a.TYPE = 'VARIABLE'
AND a.usage = 'DECLARATION'
AND a.object_name = 'PLSCOPE_PROC1'
AND a.object_name = b.object_name) variables
WHERE a.usage = 'REFERENCE'
AND a.usage_context_id = b.usage_id
AND b.usage = 'DECLARATION'
AND a.object_type = b.object_type
AND a.object_name = b.object_name
AND b.signature = variables.signature
/
ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL'
/
CREATE OR REPLACE PACKAGE my_package
IS
FUNCTION func (arg NUMBER)
RETURN NUMBER;
PROCEDURE proc (arg VARCHAR2);
END my_package;
/
CREATE OR REPLACE PROCEDURE use_proc
IS
BEGIN
my_package.proc ('a');
END use_proc;
/
CREATE OR REPLACE PROCEDURE use_func
IS
BEGIN
DBMS_OUTPUT.put_line (my_package.func (1));
END use_func;
/
SELECT called.object_name
FROM user_identifiers called, user_identifiers declared
WHERE declared.usage = 'DECLARATION'
AND declared.name = 'PROC'
AND declared.object_name = 'MY_PACKAGE'
AND called.usage = 'CALL'
AND called.signature = declared.signature
AND called.object_name <> 'MY_PACKAGE'
/
/*======================================================================
| Supplement to the fifth edition of Oracle PL/SQL Programming by Steven
| Feuerstein with Bill Pribyl, Copyright (c) 1997-2009 O'Reilly Media, Inc.
| To submit corrections or find more code samples visit
| http://oreilly.com/catalog/9780596514464/
*/