1 package dbms_utility is
2
3 $if utl_ident.is_oracle_server <> TRUE and
4 utl_ident.is_timesten <> TRUE $then
5 $error 'dbms_utility is not supported in this environment' $end
6 $end
7
8 ------------
9 -- OVERVIEW
10 --
11 -- This package provides various utility routines.
12
13 ----------------------------
14 -- PL/SQL TABLES
15 --
16 type uncl_array IS table of VARCHAR2(227) index by BINARY_INTEGER; -- dbms_id
17 -- Lists of "USER"."NAME"."COLUMN"@LINK should be stored here
18
19 type name_array IS table of dbms_id index by BINARY_INTEGER;
20 -- Lists of NAME should be stored here
21
22 type quoted_name_array IS table of VARCHAR2(32) index by BINARY_INTEGER;
23 -- Lists of NAME that might be quoted should be stored here
24
25 type lname_array IS table of VARCHAR2(4000) index by BINARY_INTEGER;
26 -- Lists of Long NAME should be stored here, it includes fully
27 -- qualified attribute names.
28
29 type maxname_array IS table of VARCHAR2(32767) index by BINARY_INTEGER;
30 -- Lists of large VARCHAR2s should be stored here
31
32 type dblink_array IS table of VARCHAR2(128) index by BINARY_INTEGER;
33 -- Lists of database links should be stored here
34
35 TYPE index_table_type IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER;
36 -- order in which objects should be generated is returned here
37
38 TYPE number_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
39 -- order in which objects should be generated is returned here for users.
40
41 TYPE instance_record IS RECORD (
42 inst_number NUMBER,
43 inst_name VARCHAR2(60));
44
45 TYPE instance_table IS TABLE OF instance_record INDEX BY BINARY_INTEGER;
46 -- list of active instance number and instance name
47 -- the starting index of instance_table is 1
48 -- instance_table is dense
49
50 $if utl_ident.is_oracle_server $then
51 TYPE anydata_array IS TABLE OF AnyData INDEX BY BINARY_INTEGER;
52 -- array of anydata
53 $else
54 /* AnyData type is not supported */
55 $end
56
57 SUBTYPE maxraw IS RAW(32767);
58
59
60 ----------------------------
61 -- PROCEDURES AND FUNCTIONS
62
63 --
64 --
65 procedure compile_schema(schema varchar2, compile_all boolean default TRUE,
66 reuse_settings boolean default FALSE);
67 -- Compile all procedures, functions, packages and triggers in the specified
68 -- schema. After calling this procedure you should select from view
69 -- ALL_OBJECTS for items with status of 'INVALID' to see if all objects
70 -- were successfully compiled. You may use the command "SHOW ERRORS
71 -- <type> <schema>.<name>" to see the errors assocated with 'INVALID'
72 -- objects.
73 -- Input arguments:
74 -- schema
75 -- Name of the schema.
76 -- compile_all
77 -- This is a boolean flag that indicates whether we should compile all
78 -- schema objects or not, regardless of whether the object is currently
79 -- flagged as valid or not. The default is to support the previous
80 -- compile_schema() behaviour and compile ALL objects.
81 -- reuse_settings
82 -- This is a boolean flag that indicates whether the session settings in
83 -- the objects should be reused, or whether the current session settings
84 -- should be picked up instead.
85 -- Exceptions:
86 -- ORA-20000: Insufficient privileges for some object in this schema.
87 -- ORA-20001: Cannot recompile SYS objects.
88
89 /*
90 * NAME:
91 * validate
92 *
93 * PARAMETERS:
94 * object_id (IN) - ID number of object to be validated. This is
95 * the same as the value of the OBJECT_ID column
96 * from ALL_OBJECTS.
97 *
98 * DESCRIPTION:
99 * This procedure validates a database object if it is in status
100 * 4, 5, or 6 using the same mechanism that is used for automatic
101 * re-validation.
102 *
103 * EXCEPTIONS:
104 * None. No errors are raised if the object doesn't exist or is
105 * already valid or is an object that cannot be validated.
106 */
107 procedure validate(object_id number);
108
109 /*
110 * NAME:
111 * validate
112 *
113 * PARAMETERS:
114 * owner (IN) - name of the user who owns the object. Same as
115 * the OWNER field in ALL_OBJECTS
116 * objname (IN) - name of the object to be validated. Same as the
117 * OBJECT_NAME field in ALL_OBJECTS
118 * namespace (IN) - namespace of the object. Same as the namespace
119 * field in obj$
120 * edition (IN) - Edition that object resides in. Same as edition_name
121 * field in ALL_OBJECTS.
122 *
123 * DESCRIPTION:
124 * This procedure validates a database object using the same
125 * mechanism that is used for automatic re-validation.
126 *
127 * EXCEPTIONS:
128 * None. No errors are raised if the object doesn't exist or is
129 * already valid or is an object that cannot be validated.
130 */
131 procedure validate(owner varchar2, objname varchar2, namespace number,
132 edition varchar2 :=
133 $if utl_ident.is_oracle_server $then
134 sys_context('USERENV', 'CURRENT_EDITION_NAME'));
135 $elsif utl_ident.is_timesten $then
136 -- no edition support for TimesTen, default it to null
137 null);
138 $end
139
140 inv_not_exist_or_no_priv exception;
141 pragma exception_init(inv_not_exist_or_no_priv, -24237);
142
143 inv_malformed_settings exception;
144 pragma exception_init(inv_malformed_settings, -24238);
145
146 inv_restricted_object exception;
147 pragma exception_init(inv_restricted_object, -24239);
148
149 /*
150 * Option flags supported by invalidate.
151 * inv_error_on_restrictions - The invalidate routine imposes various
152 * restrictions on the objects that can be invalidated. For example,
153 * the object specified by p_object_id cannot be a table. By default,
154 * invalidate quietly returns on these conditions (and does not raise
155 * an exception). If the caller sets this flag, the exception
156 * inv_restricted_object is raised.
157 */
158 inv_error_on_restrictions constant pls_integer := 1;
159
160 /*
161 * NAME:
162 * invalidate
163 *
164 * PARAMETERS:
165 * p_object_id (IN)
166 * ID number of object to be invalidated. This is the same as the
167 * value of the OBJECT_ID column from ALL_OBJECTS.
168 *
169 * If the object_id argument is null or invalid then the exception
170 * inv_not_exist_or_no_priv is raised.
171 *
172 * The caller of this procedure must have create privileges on the
173 * object being invalidated else inv_not_exist_or_no_priv exception
174 * is raised.
175 *
176 * p_plsql_object_settings (IN)
177 * This optional parameter is ignored if the object specified by
178 * p_object_id is not a PL/SQL object.
179 *
180 * If no value is specified for this parameter then the PL/SQL
181 * compiler settings are left unchanged, that is, equivalent to
182 * REUSE SETTINGS.
183 *
184 * If a value is provided, it must specify the values of the PL/SQL
185 * compiler settings separated by one or more spaces. Each setting
186 * can be specified only once else inv_malformed_settings exception
187 * will be raised. The setting values are changed only for the object
188 * specified by p_object_id and do not affect dependent objects that
189 * may be invalidated.
190 *
191 * The setting names and values are case insensitive.
192 *
193 * If a setting is omitted and REUSE SETTINGS is specified, then if a
194 * value was specified for the compiler setting in an earlier
195 * compilation of this library unit, Oracle Database uses that earlier
196 * value. If a setting is omitted and REUSE SETTINGS was not specified
197 * then the database will obtain the value for that setting from the
198 * session environment.
199 *
200 * Note that an empty, non-null, string can be passed in. This will
201 * cause all compiler settings to be read from the session environment.
202 *
203 * p_option_flags (IN)
204 * Option flags supported (see note above). This parameter is optional
205 * and defaults to zero (no flags).
206 *
207 * DESCRIPTION:
208 * This procedure invalidates a database object and (optionally) modifies
209 * its PL/SQL compiler parameter settings. It also invalidates any
210 * objects that (directly or indirectly) depend on the object being
211 * invalidated.
212 *
213 * The object type (object_type column from ALL_OBJECTS) of the object
214 * specified by p_object_id must be a PROCEDURE, FUNCTION, PACKAGE,
215 * PACKAGE BODY, TRIGGER, TYPE, TYPE BODY, LIBRARY, VIEW, OPERATOR,
216 * SYNONYM, or JAVA CLASS. If the object is not one of these types
217 * and the flag inv_error_on_restrictions is specified in p_option_flags
218 * then the exception inv_restricted_object is raised, else no action
219 * is taken.
220 *
221 * If the object specified by p_object_id is the package specification
222 * of STANDARD, DBMS_STANDARD, or specification or body of DBMS_UTILITY
223 * and the flag inv_error_on_restrictions is specified in p_option_flags
224 * then the exception inv_restricted_object is raised, else no action
225 * is taken.
226 *
227 * If the object specified by p_object_id is an object type specification
228 * and the type is a system generated type, an earlier type version or
229 * there exist tables which depend on the type and the flag
230 * inv_error_on_restrictions is specified in p_option_flags then the
231 * exception inv_restricted_object is raised, else no action is taken.
232 *
233 * EXAMPLES:
234 *
235 * dbms_utility.invalidate(1232,
236 * 'PLSQL_OPTIMIZE_LEVEL = 2 REUSE SETTINGS');
237 *
238 * Assume that the object_id 1232 refers to the procedure remove_emp
239 * in the hr schema. Then the above call will mark the remove_emp
240 * procedure invalid and change it's PLSQL_OPTIMIZE_LEVEL compiler
241 * setting to 2. The values of other compiler settings will remain
242 * unchanged since REUSE SETTINGS is specified.
243 *
244 * Objects that depend on hr.remove_emp will also get marked invalid.
245 * Their compiler parameters will not be changed.
246 *
247 * dbms_utility.invalidate(40775,
248 * 'plsql_code_type = native');
249 *
250 * Assume that the object_id 40775 refers to the type body
251 * leaf_category_typ in the oe schema. Then the above call will mark
252 * the type body invalid and change it's PLSQL_CODE_TYPE compiler
253 * setting to NATIVE. The values of other compiler settings will be
254 * picked up from the current session environment since REUSE SETTINGS
255 * has not been specified.
256 *
257 * Since no objects can depend on bodies, there are no cascaded
258 * invalidations.
259 *
260 * dbms_utility.invalidate(40796);
261 *
262 * Assume that the object_id 40796 refers to the view oc_orders in
263 * the oe schema. Then the above call will mark the oc_orders view
264 * invalid.
265 *
266 * Objects that depend on oe.oc_orders will also get marked invalid.
267 *
268 * EXCEPTIONS:
269 * This procedure can raise various exceptions. See detailed description
270 * above.
271 */
272 procedure invalidate(p_object_id number,
273 p_plsql_object_settings varchar2 default NULL,
274 p_option_flags pls_integer default 0);
275
276 $if utl_ident.is_oracle_server $then
277 procedure analyze_schema(schema varchar2, method varchar2,
278 estimate_rows number default null,
279 estimate_percent number default null, method_opt varchar2 default null);
280 $else
281 /* analyze_schema is not supported */
282 $end
283 -- Analyze all the tables, clusters and indexes in a schema.
284 -- Input arguments:
285 -- schema
286 -- Name of the schema.
287 -- method, estimate_rows, estimate_percent, method_opt
288 -- See the descriptions above in sql_ddl.analyze.object.
289 -- Exceptions:
290 -- ORA-20000: Insufficient privileges for some object in this schema.
291
292 $if utl_ident.is_oracle_server $then
293 procedure analyze_database(method varchar2,
294 estimate_rows number default null,
295 estimate_percent number default null, method_opt varchar2 default null);
296 $else
297 /* analyze_database is not supported */
298 $end
299 -- Analyze all the tables, clusters and indexes in a database.
300 -- Input arguments:
301 -- method, estimate_rows, estimate_percent, method_opt
302 -- See the descriptions above in sql_ddl.analyze.object.
303 -- Exceptions:
304 -- ORA-20000: Insufficient privileges for some object in this database.
305
306 function format_error_stack return varchar2;
307 pragma interface (C, format_error_stack); -- 1 (see psdicd.c)
308 -- Format the current error stack. This can be used in exception
309 -- handlers to look at the full error stack.
310 -- Output arguments:
311 -- format_error_stack
312 -- Returns the error stack. May be up to 2000 bytes.
313 function format_call_stack return varchar2;
314 pragma restrict_references(format_call_stack,WNDS);
315 pragma interface (C, format_call_stack); -- 2 (see psdicd.c)
316 -- Format the current call stack. This can be used an any stored
317 -- procedure or trigger to access the call stack. This can be
318 -- useful for debugging.
319 -- Output arguments:
320 -- format_call_stack
321 -- Returns the call stack. May be up to 2000 bytes.
322
323 $if utl_ident.is_oracle_server $then
324 function is_cluster_database return boolean;
325 $else
326 /* is_cluster_database is not supported */
327 $end
328 -- Find out if this database is running in cluster database mode.
329 -- Output arguments:
330 -- is_cluster_database
331 -- TRUE if this instance was started in cluster database mode,
332 -- FALSE otherwise.
333
334 function get_time return number;
335 -- Find out the current elapsed time in 100th's of a second.
336 -- Output:
337 -- The returned elapsed time is the number of 100th's
338 -- of a second from some arbitrary epoch.
339 -- Related Function(s): "get_cpu_time" [See below].
340
341 $if utl_ident.is_oracle_server $then
342 function get_parameter_value(parnam in varchar2,
343 intval in out binary_integer,
344 strval in out varchar2,
345 listno in binary_integer default 1)
346 return binary_integer;
347 $else
348 /* get_parameter_value is not supported */
349 $end
350 -- Gets value of specified init.ora parameter.
351 -- Input arguments:
352 -- parnam
353 -- Parameter name
354 -- listno
355 -- List item number. If we are retrieving the parameter values for
356 -- a parameter that can be specified multiple times to accumulate
357 -- values (Eg rollback_segments) then this can be used to get each
358 -- individual parameter. Eg, if we have the following :
359 --
360 -- rollback_segments = rbs1
361 -- rollback_segments = rbs2
362 --
363 -- then use a value of 1 to get "rbs1" and a value of 2 to get "rbs2".
364 --
365 -- Output arguments:
366 -- intval
367 -- Value of an integer parameter or value length of a string parameter
368 -- strval
369 -- Value of a string parameter
370 -- Returns:
371 -- partyp
372 -- Parameter type
373 -- 0 if parameter is an integer/boolean parameter
374 -- 1 if parameter is a string/file parameter
375 -- Notes
376 -- 1. Certain parameters can store values much larger than can be
377 -- returned by this function. When this function is requested to
378 -- retrieve the setting for such parameters and "unsupported parameter"
379 -- exception will be raised. The "shared_pool_size" parameter is one
380 -- such parameter.
381 -- Example usage:
382 -- DECLARE
383 -- parnam VARCHAR2(256);
384 -- intval BINARY_INTEGER;
385 -- strval VARCHAR2(256);
386 -- partyp BINARY_INTEGER;
387 -- BEGIN
388 -- partyp := dbms_utility.get_parameter_value('max_dump_file_size',
389 -- intval, strval);
390 -- dbms_output.put('parameter value is: ');
391 -- IF partyp = 1 THEN
392 -- dbms_output.put_line(strval);
393 -- ELSE
394 -- dbms_output.put_line(intval);
395 -- END IF;
396 -- IF partyp = 1 THEN
397 -- dbms_output.put('parameter value length is: ');
398 -- dbms_output.put_line(intval);
399 -- END IF;
400 -- dbms_output.put('parameter type is: ');
401 -- IF partyp = 1 THEN
402 -- dbms_output.put_line('string');
403 -- ELSE
404 -- dbms_output.put_line('integer');
405 -- END IF;
406 -- END;
407 procedure name_resolve(name in varchar2, context in number,
408 schema out varchar2, part1 out varchar2, part2 out varchar2,
409 dblink out varchar2, part1_type out number, object_number out number);
410 -- Resolve the given name. Do synonym translation if necessary. Do
411 -- authorization checking.
412 -- Input arguments:
413 -- name
414 -- The name of the object. This can be of the form [[a.]b.]c[@d]
415 -- where a,b,c are SQL identifier and d is a dblink. No syntax
416 -- checking is performed on the dblink. If a dblink is specified,
417 -- of the name resolves to something with a dblink, then object
418 -- is not resolved, but the schema, part1, part2 and dblink out
419 -- arguments are filled in. a,b and c may be delimted identifiers,
420 -- and may contain NLS characters (single and multi-byte).
421 -- context
422 -- Must be an integer between 0 and 9.
423 -- 0 -- table or view, error if extra name parts present
424 -- 1 -- pl/sql (for 2 part names)
425 -- 2 -- sequence, or table/view with extra trailing name parts allowed
426 -- 3 -- trigger
427 -- 4 -- Java Source
428 -- 5 -- Java resource
429 -- 6 -- Java class
430 -- 7 -- type
431 -- 8 -- Java shared data
432 -- 9 -- index
433 -- Output arguments:
434 -- schema
435 -- The schema of the object. If no schema is specified in 'name'
436 -- then the schema is determined by resolving the name.
437 -- part1
438 -- The first part of the name. The type of this name is specified
439 -- part1_type (synonym, procedure or package).
440 -- part2
441 -- If this is non-null, then this is a procedure name within the
442 -- package indicated by part1.
443 -- dblink
444 -- If this is non-null then a database link was either specified
445 -- as part of 'name' or 'name' was a synonym which resolved to
446 -- something with a database link. In this later case, part1_type
447 -- will indicate a synonym.
448 -- part1_type
449 -- The type of part1 is
450 -- 5 - synonym
451 -- 7 - procedure (top level)
452 -- 8 - function (top level)
453 -- 9 - package
454 -- If a synonym, it means that 'name' is a synonym that translats
455 -- to something with a database link. In this case, if further
456 -- name translation is desired, then you must call the
457 -- dbms_utility.name_resolve procedure on this remote node.
458 -- object_number
459 -- If non-null then 'name' was successfully resolved and this is the
460 -- object number which it resolved to.
461 -- Exceptions:
462 -- All errors are handled by raising exceptions. A wide variety of
463 -- exceptions are possible, based on the various syntax error that
464 -- are possible when specifying object names.
465 procedure name_tokenize( name in varchar2,
466 a out varchar2,
467 b out varchar2,
468 c out varchar2,
469 dblink out varchar2,
470 nextpos out binary_integer);
471 -- Call the parser to parse the given name as "a [. b [. c ]][@ dblink ]".
472 -- Strip doublequotes, or convert to uppercase if there are no quotes.
473 -- Ignore comments of all sorts. Do no semantic analysis. Leave any
474 -- missing values as null.
475 -- For each of a,b,c,dblink, tell where the following token starts
476 -- in anext,bnext,cnext,dnext respectively.
477
478 PROCEDURE comma_to_table( list IN VARCHAR2,
479 tablen OUT BINARY_INTEGER,
480 tab OUT uncl_array);
481 -- Convert a comma-separated list of names into a PL/SQL table of names
482 -- This uses name_tokenize to figure out what are names and what are commas
483 -- See name_tokenize for the expected syntax of the names.
484
485 PROCEDURE comma_to_table( list IN VARCHAR2,
486 tablen OUT BINARY_INTEGER,
487 tab OUT lname_array);
488 -- Convert a comma-separated list of names into a PL/SQL table of names
489 -- This is an overloaded version for supporting fully-qualified attribute
490 -- names of the form "a [. b ]*".
491
492 PROCEDURE table_to_comma( tab IN uncl_array,
493 tablen OUT BINARY_INTEGER,
494 list OUT VARCHAR2);
495 -- Convert a PL/SQL table of names into a comma-separated list of names
496
497 PROCEDURE table_to_comma( tab IN lname_array,
498 tablen OUT BINARY_INTEGER,
499 list OUT VARCHAR2);
500 -- Convert a PL/SQL table of names into a comma-separated list of names
501 -- This is an overloaded version for supporting fully-qualified attribute
502 -- names.
503
504 $if utl_ident.is_oracle_server $then
505 FUNCTION port_string RETURN VARCHAR2;
506 PRAGMA RESTRICT_REFERENCES(port_string, WNDS, RNDS, WNPS, RNPS);
507 $else
508 /* port_string is not supported */
509 $end
510 -- Return a string that uniquely identifies the port (operating system)
511 -- and the two task protocol version of Oracle. EG "VAX/VMX-7.1.0.0"
512 -- "SVR4-be-8.1.0" (SVR4-b(ig)e(ndian)-8.1.0)
513 -- maximum length is port specific.
514
515 PROCEDURE db_version(version OUT VARCHAR2,
516 compatibility OUT VARCHAR2);
517 -- Return version information for the database:
518 -- version -> A string which represents the internal software version
519 -- of the database (e.g., 7.1.0.0.0). The length of this string
520 -- is variable and is determined by the database version.
521 -- compatibility -> The compatibility setting of the database determined by
522 -- the "compatible" init.ora parameter. If the parameter
523 -- is not specified in the init.ora file, NULL is returned.
524
525 $if utl_ident.is_oracle_server $then
526 function make_data_block_address(file number, block number) return number;
527 PRAGMA RESTRICT_REFERENCES(make_data_block_address, WNDS, RNDS, WNPS, RNPS);
528 $else
529 /* make_data_block_address is not supported */
530 $end
531 -- Creates a data block address given a file# and a block#. A data block
532 -- address is the internal structure used to identify a block in the
533 -- database. This is function useful when accessing certain fixed tables
534 -- that contain data block addresses.
535 -- Input arguments:
536 -- file - the file that contains the block
537 -- block - the offset of the block within the file in terms of block
538 -- increments
539 -- Output arguments:
540 -- dba - the data block address
541
542 $if utl_ident.is_oracle_server $then
543 function data_block_address_file(dba number) return number;
544 PRAGMA RESTRICT_REFERENCES(data_block_address_file, WNDS, RNDS, WNPS, RNPS);
545 $else
546 /* data_block_address_file is not supported */
547 $end
548 -- Get the file number part of a data block address
549 -- Input arguments:
550 -- dba - a data block address
551 -- Output Arguments:
552 -- file - the file that contains the block
553
554 $if utl_ident.is_oracle_server $then
555 function data_block_address_block(dba number) return number;
556 PRAGMA RESTRICT_REFERENCES(data_block_address_block, WNDS, RNDS, WNPS, RNPS);
557 $else
558 /* data_block_address_block is not supported */
559 $end
560 -- Get the block number part of a data block address
561 -- Input arguments:
562 -- dba - a data block address
563 -- Output Arguments:
564 -- block - the block offset of the block
565 function get_hash_value(name varchar2, base number, hash_size number)
566 return number;
567 PRAGMA RESTRICT_REFERENCES(get_hash_value, WNDS, RNDS, WNPS, RNPS);
568 -- Compute a hash value for the given string
569 -- Input arguments:
570 -- name - The string to be hashed.
571 -- base - A base value for the returned hash value to start at.
572 -- hash_size - The desired size of the hash table.
573 -- Returns:
574 -- A hash value based on the input string.
575 -- For example, to get a hash value on a string where the hash value
576 -- should be between 1000 and 3047, use 1000 as the base value and
577 -- 2048 as the hash_size value. Using a power of 2 for the hash_size
578 -- parameter works best.
579 -- Exceptions:
580 -- ORA-29261 will be raised if hash_size is 0 or if hash_size or
581 -- base are null
582
583 function get_sql_hash(name IN varchar2, hash OUT raw,
584 pre10ihash OUT number)
585 return number;
586 PRAGMA RESTRICT_REFERENCES(get_sql_hash, WNDS, RNDS, WNPS, RNPS);
587 -- Compute a hash value for the given string using md5 algo
588 -- Input arguments:
589 -- name - The string to be hashed.
590 -- hash - An optional field to store all 16 bytes of returned
591 -- hash value.
592 -- pre10ihash - An optional field to store the pre 10i database
593 -- version hash value.
594 -- Returns:
595 -- A hash value (last 4 bytes) based on the input string.
596 -- The md5 hash algorithm computes a 16 byte hash value, but
597 -- we only return the last 4 bytes so that we can return an
598 -- actual number. One could use an optional RAW parameter to
599 -- get all 16 bytes and to store the pre 10i hash value of 4
600 -- 4 bytes in the pre10ihash optional parameter.
601
602 $if utl_ident.is_oracle_server $then
603 function sqlid_to_sqlhash(sql_id varchar2)
604 return number;
605 PRAGMA RESTRICT_REFERENCES(sqlid_to_sqlhash, WNDS, RNDS, WNPS, RNPS);
606 $else
607 /* sqlid_to_sqlhash is not supported */
608 $end
609 -- This routine will convert a sql_id into a hash value
610 -- Input arguments:
611 -- sql_id - SQL ID of a sql statement. Must be VARCHAR2(13).
612 -- Returns:
613 -- A hash value converted from the sql_id.
614
615 $if utl_ident.is_oracle_server $then
616 procedure analyze_part_object
617 (schema in varchar2 default null,
618 object_name in varchar2 default null,
619 object_type in char default 'T',
620 command_type in char default 'E',
621 command_opt in varchar2 default null,
622 sample_clause in varchar2 default 'sample 5 percent');
623 $else
624 /* analyze_part_object is not supported */
625 $end
626 -- Equivalent to SQL "ANALYZE TABLE|INDEX [<schema>.]<object_name>
627 -- PARTITION <pname> [<command_type>] [<command_opt>] [<sample_clause>]
628 -- for each partition of the object, run in parallel using job queues.
629 -- The package will submit a job for each partition
630 -- It is the users responsibilty to control the number of concurrent
631 -- jobs by setting the INIT parameter JOB_QUEUE_PROCESSES correctly
632 -- There is minimal error checking for correct syntax. Any error will be
633 -- reported in SNP trace files.
634 -- Input arguments:
635 -- schema
636 -- schema of the object_name
637 -- object_name
638 -- name of object to be analyzed, must be partitioned
639 -- object_type
640 -- type of object, must be T(able) or I(ndex)
641 -- command_type
642 -- must be one of the following
643 -- - C(omput statistics)
644 -- - E(stimate statistics)
645 -- - D(elete statistics)
646 -- - V(alidate structure)
647 -- command_opt
648 -- Other options for the command type.
649 -- For C, E it can be FOR table, FOR all LOCAL indexes, FOR all columns or
650 -- combination of some of the 'for' options of analyze statistics (table)
651 -- For V, it can be 'CASCADE' when object_type is T
652 -- sample_clause
653 -- Specifies the sample clause to use when command_type is 'E'
654
655 $if utl_ident.is_oracle_server $then
656 procedure exec_ddl_statement(parse_string in varchar2);
657 -- Will execute the DDL statement in parse_string
658 -- parse_string
659 -- DDL statement to be executed
660 $else
661 /* exec_ddl_statement is not supported */
662 $end
663
664 $if utl_ident.is_oracle_server $then
665 function current_instance return number;
666 $else
667 /* current_instance is not supported */
668 $end
669 -- Return the current connected instance number
670 -- Return NULL when connected instance is down
671
672 $if utl_ident.is_oracle_server $then
673 procedure active_instances(instance_table OUT instance_table,
674 instance_count OUT number);
675 $else
676 /* active_instances is not supported */
677 $end
678 -- instance_table contains a list of the active instance numbers and names
679 -- When no instance is up ( or non-OPS setting), the list is empty
680 -- instance_count is the number of active instances, 0 under non-ops setting
681
682 procedure get_dependency (type IN VARCHAR2,
683 schema IN VARCHAR2,
684 name IN VARCHAR2);
685 -- This procedure will show all the dependencies on the object passed in.
686 -- The inputs are
687 -- type: The type of the object, for example if the object is a table
688 -- give the type as 'TABLE'.
689 -- schema: The schema name of the object.
690 -- name: The name of the object.
691
692 $if utl_ident.is_oracle_server $then
693 procedure create_alter_type_error_table ( schema_name IN VARCHAR2,
694 table_name IN VARCHAR2);
695 $else
696 /* create_alter_type_error_table is not supported */
697 $end
698 -- This procedure will create an error table to be used in the EXCEPTION
699 -- clause of ALTER TYPE statement. An error will be returned if the table
700 -- already exists.
701 -- The inputs are:
702 -- schema: The schema name
703 -- table: The name of the table to be created.
704
705 procedure canonicalize(name IN VARCHAR2,
706 canon_name OUT VARCHAR2,
707 canon_len IN BINARY_INTEGER);
708 -- canonicalize the given string
709 -- if name is NULL, canon_name becomes NULL
710 -- if name is not a dotted name,
711 -- if name begins and ends with a double quote, remove both
712 -- otherwise, convert to upper case with NLS_UPPER
713 -- Note that this case does not include a name with special
714 -- characters, e.g., space, but is not doubly quoted.
715 -- if name is a dotted name, e.g., a."b".c,
716 -- for each component in the dotted name,
717 -- if the component begins and ends with a double quote,
718 -- no transformation will be done in this component
719 -- else
720 -- this component will be capitalized with NLS_UPPER and
721 -- a begin and end double quotes will be applied to the
722 -- capitalized form of this component.
723 -- each canonicalized component will be concatenated together in
724 -- the input position, separated by ".".
725 -- return the first canon_len bytes in canon_name
726 --
727 -- Any other character after a[.b]* will be ignored.
728 --
729 -- NOTES:
730 -- 1. It does not handle cases like 'A B';
731 -- 2. It handles a single reserved/key word, e.g., 'table';
732 -- 3. It strips off white spaces for a single identifier, e.g., ' table '
733 -- becomes TABLE.
734 --
735 -- Examples:
736 -- a becomes A
737 -- "a" becomes a
738 -- "a".b becomes "a"."B"
739 -- "a".b,c.f becomes "a"."B", ",c.f" is ignored.
740 --
741
742 FUNCTION is_bit_set(r IN RAW,
743 n IN NUMBER)
744 RETURN NUMBER;
745 PRAGMA RESTRICT_REFERENCES(is_bit_set, RNPS, WNPS, RNDS, WNDS);
746 -- Return 1 if bit n in raw r is set. Bits are numbered high to low
747 -- with the lowest bit being bit number 1. This is a utility to assist
748 -- the view DBA_PENDING_TRANSACTION.
749
750 $if utl_ident.is_oracle_server $then
751 procedure get_tz_transitions(regionid number,transitions OUT maxraw);
752 $else
753 /* get_tz_transitions is not supported */
754 $end
755
756
757 -- Get timezeone transitions from the timezone.dat file
758 -- Input arguments:
759 -- regionid
760 -- Number corresponding to the region
761 -- Output arguments:
762 -- transitions
763 -- The raw bytes from the timezone.dat file
764
765 --
766 -- The following two functions provide the pre-825066-fix behavior of
767 -- SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') and
768 -- SYS_CONTEXT('USERENV', 'CURRENT_USER')
769 -- These functions are temporarily provided for
770 -- *** Warning ***: backward compatibility and will be removed in
771 -- near future.
772 --
773 $if utl_ident.is_oracle_server $then
774 function old_current_schema return varchar2;
775 $else
776 /* old_current_schema is not supported */
777 $end
778
779 $if utl_ident.is_oracle_server $then
780 function old_current_user return varchar2;
781 $else
782 /* old_current_user is not supported */
783 $end
784
785 function get_endianness return number;
786 pragma interface (C, get_endianness); -- 3 (see psdicd.c)
787
788 function format_error_backtrace return varchar2;
789 pragma interface (C, format_error_backtrace); -- 4 (see psdicd.c)
790 -- Format the backtrace from the point of the current error
791 -- to the exception handler where the error has been caught.
792 -- NULL string is returned if no error is currently being
793 -- handled.
794
795 function get_cpu_time return number;
796 -- Find out the current CPU time in 100th's of a second.
797 --
798 -- Output:
799 -- The returned CPU time is the number of 100th's
800 -- of a second from some arbitrary epoch.
801 --
802 -- Related Function(s):
803 -- "get_time" [See above].
804 --
805 -- Usage Example:
806 -- ..
807 -- start_cpu_time NUMBER;
808 -- end_cpu_time NUMBER;
809 -- ..
810 -- BEGIN
811 --
812 -- start_cpu_time := dbms_utility.GET_CPU_TIME;
813 --
814 -- ... -- some work that needs to be timed
815 --
816 -- end_cpu_time := dbms_utility.GET_CPU_TIME;
817 --
818 -- dbms_output.put_line('CPU Time (in seconds)= '
819 -- || ((end_cpu_time - start_cpu_time)/100));
820 --
821
822 $if utl_ident.is_oracle_server $then
823 function wait_on_pending_dml(tables in varchar2,
824 timeout in binary_integer,
825 scn in out number)
826 return boolean;
827 $else
828 /* wait_on_pending_dml is not supported */
829 $end
830 --
831 -- Waits until all transactions (other than the caller's own) that have
832 -- locks on the listed tables and began prior to the specified SCN
833 -- have either committed or been rolled back.
834 --
835 -- Input arguments:
836 -- tables: a comma separated list of one or more table names. The
837 -- list must be valid to comma_to_table(), and each item valid
838 -- to name_resolve(). Neither column specifiers nor dblink
839 -- specifiers are allowed in the names, and each name must
840 -- resolve to an existing table in the local database.
841 --
842 -- timeout: max number of seconds to wait, total across all tables/txns.
843 -- A null or negative value will cause a very long wait.
844 --
845 -- In/Out arguments:
846 -- scn: The SCN prior to which transactions must have begun to be
847 -- considered relevant to this request. If the value is null or
848 -- not recognized as a meaningful scn on input, the most current
849 -- SCN across all instances will be used and will be set into
850 -- the passed argument as an output. If a meaningful value is
851 -- passed in, its value will be preserved in the output.
852 --
853 -- Output: TRUE if all relevant transactions have committed or been
854 -- rolled back. FALSE if the timeout occurred prior to all relevant
855 -- transactions committing or being rolled back.
856
857 $if utl_ident.is_oracle_server $then
858
859 /*-----------------------------------------------------------------------------
860 Procedure: expand_sql_text
861
862 Recursively replaces any view references in the input SQL query with the
863 corresponding view subquery.
864
865 Parameters:
866
867 input_sql_text - Input sql query text
868 output_sql_text - View expanded query text
869
870 Exceptions:
871 - ORA-24256 will be raised if the current user does not have SELECT
872 privileges on all the views and tables recursively referenced in the
873 input sql text. It will also be raised if the user does not have
874 EXECUTE privileges on all functions and types referenced from within
875 views that are expanded as well as any other reason a valid query could
876 not be expanded. The ORA-24256's error message text contains information
877 regarding the particular restriction that wasn't satisfied.
878 - ORA-24251 will be raised if the input_sql text is not a select statement.
879 - ORA-00900 will be raised if the input is not valid.
880 - ORA-29477 will be raised if the input lob size exceeds the maximum size
881 of 4GB -1.
882
883 Notes:
884 The expanded and merged SQL statement text is copied to output_sql_text on
885 successful completion. The resulting query text only
886
887 contains references to underlying tables and is semantically equivalent
888 with some caveats:
889 - If there are invoker rights functions called from any of the views then
890 they may get called as a different user in the resulting query text if
891 the view owner is different from the user who will eventually compile/run
892 the expanded sql text.
893 - If there are VPD policies on any of the views that produce different
894 expansions depending on who the user is.
895 - Also if there are references to remote objects then it may not work.
896
897 */
898
899 procedure expand_sql_text(input_sql_text in clob,
900 output_sql_text out nocopy clob);
901 $else
902 /* expand_sql_text is not supported */
903 $end
904 end;
[Home] [Help]