1 PACKAGE dbms_sqltune_util0 AS
2
3 -----------------------------------------------------------------------------
4 -- section for constants and global variables --
5 -----------------------------------------------------------------------------
6 INVALID_SQL EXCEPTION;
7 PRAGMA EXCEPTION_INIT(INVALID_SQL, -900);
8
9 -----------------------------------------------------------------------------
10 -- procedure/function specifications --
11 -----------------------------------------------------------------------------
12
13
14 ------------------------------ sqltext_to_signature -------------------------
15 --
16 -- NAME:
17 -- sqltext_to_signature - sql text to its signature
18 --
19 -- DESCRIPTION:
20 -- This function returns a sql text's signature.
21 -- The signature can be used to identify sql text in dba_sql_profiles.
22 --
23 -- PARAMETERS:
24 -- sql_text (IN) - (REQUIRED) sql text whose signature is required
25 -- force_match (IN) - If TRUE this function returns the FORCE maching
26 -- signature. Otherwise, it return the EXACT signature
27 --
28 -- RETURNS:
29 -- the signature of the specified sql text
30 -----------------------------------------------------------------------------
31 FUNCTION sqltext_to_signature(
32 sql_text IN CLOB,
33 force_match IN BINARY_INTEGER := 0)
34 RETURN NUMBER;
35
36 ------------------------------ sqltext_to_sqlid -----------------------------
37 --
38 -- NAME:
39 -- sqltext_to_signature - sql text to its signature
40 --
41 -- DESCRIPTION:
42 -- This function returns a sql text's id.
43 -- The signature can, for example, be used to identify sql text in
44 -- v$sqlXXX views.
45 --
46 -- PARAMETERS:
47 -- sql_text (IN) - (REQUIRED) sql text whose signature is required
48 --
49 -- RETURNS:
50 -- sqlid of the specified sql text
51 -----------------------------------------------------------------------------
52 FUNCTION sqltext_to_sqlid(sql_text IN CLOB)
53 RETURN VARCHAR2;
54
55 -------------------------------- validate_sqlid -----------------------------
56 --
57 -- NAME:
58 -- validate_sqlid - VALIDATE syntax of a SQL ID
59 --
60 -- DESCRIPTION:
61 -- This function checks to make sure that a sql id provided by a client
62 -- is valid by converting it to a ub8 and back and checking to make sure
63 -- there is no change.
64 --
65 -- PARAMETERS:
66 -- sql_id (IN) - (REQUIRED) sql id to validate
67 --
68 -- RETURNS:
69 -- 1 if valid, 0 otherwise.
70 -----------------------------------------------------------------------------
71 FUNCTION validate_sqlid(sql_id IN VARCHAR2)
72 RETURN BINARY_INTEGER;
73
74 --------------------------------- extract_bind ------------------------------
75 -- NAME:
76 -- extract_bind
77 --
78 -- DESCRIPTION:
79 -- Given the value of a bind_data column captured in v$sql and a
80 -- bind position, this function returns the value of the bind
81 -- variable at that position in the SQL statement. Bind position
82 -- start at 1. This function returns value and type information for
83 -- the bind (see object type SQL_BIND).
84 --
85 -- PARAMETERS:
86 -- bind_data (IN) - value of bind_data column from v$sql
87 -- position (IN) - bind position in the statement (starts from 1)
88 --
89 -- RETURN:
90 -- This function will return NULL if one of the condition below is
91 -- true:
92 -- - the specified bind variable was not captured (only interesting
93 -- bind values used by the optimizer are captured)
94 -- - bind position is invalid or out-of-bound
95 -- - the specified bind_data is NULL.
96 --
97 -- NOTE:
98 -- name of the bind in SQL_BIND object is not populated by this function
99 ----------------------------------------------------------------------------
100 FUNCTION extract_bind(
101 bind_data IN RAW,
102 bind_pos IN PLS_INTEGER)
103 RETURN SQL_BIND;
104
105 --------------------------------- extract_binds -----------------------------
106 -- NAME:
107 -- extract_binds
108 --
109 -- DESCRIPTION:
110 -- Given the value of a bind_data column captured in v$sql
111 -- this function returns the collection (list) of bind values
112 -- associated to the corresponding SQL statement.
113 --
114 -- PARAMETERS:
115 -- bind_data (IN) - value of bind_data column from v$sql
116 --
117 -- RETURN:
118 -- This function returns collection (list) of bind values of
119 -- type sql_bind.
120 --
121 -- NOTE:
122 -- For the content of a bind value, refert to function extract_bind
123 -----------------------------------------------------------------------------
124 FUNCTION extract_binds(
125 bind_data IN RAW)
126 RETURN SQL_BIND_SET PIPELINED;
127
128 -------------------------------- is_bind_masked -----------------------------
129 -- NAME:
130 -- is_bind_masked
131 --
132 -- DESCRIPTION:
133 -- This function examines a flag to determine if a bind at a given pos
134 -- is masked
135 --
136 -- PARAMETERS:
137 -- bind_pos (IN) - bind position in the stmt (starts from 1)
138 -- masked_binds_flag (IN) - flag to indicate which binds are masked
139 --
140 -- RETURN:
141 -- 1 if bind at specified posn is masked, 0 otherwise
142 --
143 ----------------------------------------------------------------------------
144 FUNCTION is_bind_masked(
145 bind_pos IN PLS_INTEGER,
146 masked_binds_flag IN RAW DEFAULT NULL)
147 RETURN NUMBER;
148
149 ------------------------------- get_binds_count -----------------------------
150 -- NAME:
151 -- get_binds_count
152 --
153 -- DESCRIPTION:
154 -- Given the value of a bind_data column in raw type this function
155 -- returns the number of bind values contained in the column.
156 --
157 -- PARAMETERS:
158 -- bind_data (IN) - value of bind_data column from v$sql
159 --
160 -- RETURN:
161 -- Number of bind values in the bind data
162 --
163 -- EXCEPTIONS:
164 -- None
165 -----------------------------------------------------------------------------
166 FUNCTION get_binds_count(bind_data IN RAW) RETURN PLS_INTEGER;
167
168 ----------------------------- cdbcon_dbid_to_name ---------------------------
169 --
170 -- NAME:
171 -- cdbcon_dbid_to_name - CDB CONtainer DBID TO NAME
172 --
173 -- DESCRIPTION:
174 -- This function returns a container name given a container dbid.
175 --
176 -- PARAMETERS:
177 -- con_dbid (IN) - (REQUIRED) CDB container dbid
178 --
179 -- RETURNS:
180 -- sqlid of the specified sql text
181 -----------------------------------------------------------------------------
182 FUNCTION cdbcon_dbid_to_name(con_dbid IN NUMBER)
183 RETURN VARCHAR2;
184
185 ----------------------------- cdbcon_id_to_dbid -----------------------------
186 --
187 -- NAME:
188 -- cdbcon_id_to_dbid - CDB CONtainer ID TO DBID
189 --
190 -- DESCRIPTION:
191 -- This procedure returns a container dbid given a container id.
192 --
193 -- PARAMETERS:
194 -- con_id (IN) - (REQUIRED) CDB container id
195 -- con_dbid (OUT) - CDB container dbid
196 --
197 -- RETURNS:
198 -- NONE
199 -----------------------------------------------------------------------------
200 PROCEDURE cdbcon_id_to_dbid(con_id IN PLS_INTEGER, con_dbid OUT NUMBER);
201 FUNCTION cdbcon_id_to_dbid(con_id IN PLS_INTEGER) RETURN NUMBER;
202
203
204 ----------------------------- cdbcon_name2ids-- -----------------------------
205 --
206 -- NAME:
207 -- cdbcon_name2ids - CDB CONtainer NAME TO DBID,CON_ID
208 --
209 -- DESCRIPTION:
210 -- This procedure returns a container dbid and container id given a container name.
211 --
212 -- PARAMETERS:
213 -- con_name (IN) - (REQUIRED) CDB container name
214 -- con_id (OUT) - CDB container id
215 -- con_dbid (OUT) - CDB container dbid
216 --
217 -- RETURNS:
218 -- NONE
219 -----------------------------------------------------------------------------
220 PROCEDURE cdbcon_name2ids(
221 con_name IN VARCHAR2,
222 con_id OUT PLS_INTEGER,
223 con_dbid OUT NUMBER);
224
225 ----------------------------- cdb_is_root -----------------------------------
226 --
227 -- NAME:
228 -- cdb_is_root - CDB is root
229 --
230 -- DESCRIPTION:
231 -- This procedure returns TRUE if this is the root container of a CDB
232 -- FALSE is returned for PDBs and for non-CDB
233 --
234 -- PARAMETERS:
235 -- con_name (OUT) - CDB container name
236 -- con_id (OUT) - CDB container id
237 --
238 --
239 -- RETURNS:
240 -- NONE
241 -----------------------------------------------------------------------------
242 FUNCTION cdb_is_root(
243 con_name OUT VARCHAR2,
244 con_id OUT NUMBER)
245 RETURN BOOLEAN;
246
247 ----------------------------- cdb_is_pdb ------------------------------------
248 --
249 -- NAME:
250 -- cdb_is_pdb - CDB is pdb
251 --
252 -- DESCRIPTION:
253 -- This procedure returns TRUE if this is a PDB in a CDB
254 -- FALSE is returned for root and for non-CDB
255 --
256 -- PARAMETERS:
257 -- con_name (OUT) - CDB container name
258 -- con_id (OUT) - CDB container id
259 --
260 --
261 -- RETURNS:
262 -- NONE
263 -----------------------------------------------------------------------------
264 FUNCTION cdb_is_pdb(
265 con_name OUT VARCHAR2,
266 con_id OUT NUMBER)
267 RETURN BOOLEAN;
268
269
270 END dbms_sqltune_util0;
[Home] [Help]