1 PACKAGE dbms_metadata_int AUTHID DEFINER AS
2 ------------------------------------------------------------
3 -- Overview
4 -- This pkg implements the privileged functions of the mdAPI.
5 ---------------------------------------------------------------------
6 -- SECURITY
7 -- This package is owned by SYS. It runs with definers, not invokers rights
8 -- because it needs to access dictionary tables.
9
10 -------------
11 -- EXCEPTIONS
12 --
13 invalid_argval EXCEPTION;
14 PRAGMA EXCEPTION_INIT(invalid_argval, -31600);
15 invalid_argval_num NUMBER := -31600;
16 -- "Invalid input value %s for parameter %s in function %s"
17 -- *Cause: A NULL or invalid value was supplied for the parameter.
18 -- *Action: Correct the input value and try the call again.
19
20 invalid_operation EXCEPTION;
21 PRAGMA EXCEPTION_INIT(invalid_operation, -31601);
22 invalid_operation_num NUMBER := -31601;
23 -- "Function %s cannot be called now that fetch has begun"
24 -- *Cause: The function was called after the first call to FETCH_xxx.
25 -- *Action: Correct the program.
26
27 inconsistent_args EXCEPTION;
28 PRAGMA EXCEPTION_INIT(inconsistent_args, -31602);
29 inconsistent_args_num NUMBER := -31602;
30 -- "parameter %s value \"%s\" in function %s inconsistent with %s"
31 -- "Value \"%s\" for parameter %s in function %s is inconsistent with %s"
32 -- *Cause: The parameter value is inconsistent with another value specified
33 -- by the program. It may be not valid for the the object type
34 -- associated with the OPEN context, or it may be of the wrong
35 -- datatype: a boolean rather than a text string or vice versa.
36 -- *Action: Correct the program.
37
38 object_not_found EXCEPTION;
39 PRAGMA EXCEPTION_INIT(object_not_found, -31603);
40 object_not_found_num NUMBER := -31603;
41 -- "object \"%s\" of type %s not found in schema \"%s\""
42 -- *Cause: The specified object was not found in the database.
43 -- *Action: Correct the object specification and try the call again.
44
45 invalid_object_param EXCEPTION;
46 PRAGMA EXCEPTION_INIT(invalid_object_param, -31604);
47 invalid_object_param_num NUMBER := -31604;
48 -- "invalid %s parameter \"%s\" for object type %s in function %s"
49 -- *Cause: The specified parameter value is not valid for this object type.
50 -- *Action: Correct the parameter and try the call again.
51
52 inconsistent_operation EXCEPTION;
53 PRAGMA EXCEPTION_INIT(inconsistent_operation, -31607);
54 inconsistent_operation_num NUMBER := -31607;
55 -- "Function %s is inconsistent with transform."
56 -- *Cause: Either (1) FETCH_XML was called when the "DDL" transform
57 -- was specified, or (2) FETCH_DDL was called when the
58 -- "DDL" transform was omitted.
59 -- *Action: Correct the program.
60
61 object_not_found2 EXCEPTION;
62 PRAGMA EXCEPTION_INIT(object_not_found2, -31608);
63 object_not_found2_num NUMBER := -31608;
64 -- "specified object of type %s not found"
65 -- (Used by GET_DEPENDENT_xxx and GET_GRANTED_xxx.)
66 -- *Cause: The specified object was not found in the database.
67 -- *Action: Correct the object specification and try the call again.
68
69 stylesheet_load_error EXCEPTION;
70 PRAGMA EXCEPTION_INIT(stylesheet_load_error, -31609);
71 stylesheet_load_error_num NUMBER := -31609;
72 -- "error loading file %s from file system directory \'%s\'"
73 -- *Cause: The installation script initmeta.sql failed to load
74 -- the named file from the file system directory into the database.
75 -- *Action: Examine the directory and see if the file is present
76 -- and can be read.
77
78
79 stylesheets_not_loaded EXCEPTION;
80 PRAGMA EXCEPTION_INIT (stylesheets_not_loaded, -39212);
81 stylesheets_not_loaded_num NUMBER := -39212;
82 -- "installation error: XSL stylesheets not loaded correctly"
83 -- *Cause: The XSL stylesheets used by the Data Pump Metadata API
84 -- were not loaded correctly into the Oracle dictionary table
85 -- "sys.metastylesheet". Either the stylesheets were not loaded
86 -- at all, or they were not converted to the database character
87 -- set.
88 -- *Action: Connect AS SYSDBA and execute dbms_metadata_util.load_stylesheets
89 -- to reload the stylesheets.
90
91 xdb_not_loaded EXCEPTION;
92 PRAGMA EXCEPTION_INIT (xdb_not_loaded, -38500);
93 xdb_not_loaded_num NUMBER := -38500;
94 -- "%s"
95 -- *Cause: The operation requires XDB functionality which is not present in
96 -- the database.
97 -- *Action: Install the missing functionality and retry.
98
99 ---------------------------
100 -- TYPES
101 --
102
103 -- type used in BULK COLLECT fetches and in SET_OBJECTS_FETCHED
104
105 TYPE t_num_coll IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
106
107 ---------------------------
108 -- PROCEDURES AND FUNCTIONS
109 --
110 -- OPEN: Specifies the type of object whose metadata is to be retrieved.
111 -- PARAMETERS:
112 -- object_type - Identifies the type of objects to be retrieved; i.e.,
113 -- TABLE, INDEX, etc. This determines which view is selected.
114 -- version - The version of the objects' metadata to be fetched.
115 -- To be used in downgrade scenarios: Objects in the DB that are
116 -- incompatible with an older specified version are not returned.
117 -- Values can be 'COMPATIBLE' (default), 'LATEST' or a specific
118 -- version number.
119 -- model - The view of the metadata, such as Oracle proprietary,
120 -- ANSI99, etc. Currently only 'ORACLE' is supported.
121 -- public_func - Name of the public function in DBMS_METADATA called
122 -- by the user; for error reporting.
123 -- current_user - Current user name.
124 --
125 -- RETURNS:
126 -- A handle to be used in subsequent calls to SET_FILTER,
127 -- ADD_TRANSFORM, GET_QUERY, SET_PARSE_ITEM and CLOSE.
128 -- EXCEPTIONS:
129 -- INVALID_ARGVAL - a NULL or invalid value was supplied for an input
130 -- parameter.
131
132 FUNCTION open (
133 object_type IN VARCHAR2,
134 version IN VARCHAR2,
135 model IN VARCHAR2,
136 public_func IN VARCHAR2,
137 current_user IN VARCHAR2)
138 RETURN NUMBER;
139
140
141 -- SET_FILTER: Specifies restrictions on the objects whose metadata
142 -- is to be retrieved.
143 -- This function is overloaded: the filter value can be a varchar2
144 -- or a boolean.
145 -- PARAMETERS:
146 -- handle - Context handle from previous OPEN call.
147 -- name - Name of the filter.
148 -- value - Value of the filter.
149 -- object_type - Object type to which the filter applies.
150
151 PROCEDURE set_filter (
152 handle IN NUMBER,
153 name IN VARCHAR2,
154 value IN VARCHAR2,
155 object_type IN VARCHAR2 DEFAULT NULL);
156
157 PROCEDURE set_filter (
158 handle IN NUMBER,
159 name IN VARCHAR2,
160 value IN BOOLEAN DEFAULT TRUE,
161 object_type IN VARCHAR2 DEFAULT NULL);
162
163 PROCEDURE set_filter (
164 handle IN NUMBER,
165 name IN VARCHAR2,
166 value IN NUMBER,
167 object_type IN VARCHAR2 DEFAULT NULL);
168
169
170 -- SET_COUNT: Specifies the number of objects to be returned in a single
171 -- FETCH_xxx call.
172 -- PARAMETERS:
173 -- handle - Context handle from previous OPEN call.
174 -- value - Number of objects to retrieve.
175 -- object_type - Object type to which the count applies.
176
177 PROCEDURE set_count (
178 handle IN NUMBER,
179 value IN NUMBER,
180 object_type IN VARCHAR2 DEFAULT NULL);
181
182
183 -- SET_XMLFORMAT: Specifies formatting attributes for XML output.
184 -- PARAMETERS:
185 -- handle - Context handle from previous OPEN call.
186 -- name - Attribute to set. (Only 'PRETTY' is supported.)
187 -- value - Value of the attribute.
188
189 PROCEDURE set_xmlformat (
190 handle IN NUMBER,
191 name IN VARCHAR2,
192 value IN BOOLEAN DEFAULT TRUE);
193
194 -- GET_QUERY: Return the text of the query (or queries) that will be
195 -- used by FETCH_xxx. This function is provided to aid
196 -- in debugging.
197 -- PARAMETERS: handle - Context handle from previous OPEN call.
198 -- RETURNS: Text of the query.
199
200 FUNCTION get_query (
201 handle IN NUMBER)
202 RETURN VARCHAR2;
203
204
205 -- SET_PARSE_ITEM: Enables output parsing and specifies an object attribute
206 -- to be parsed and returned
207 -- PARAMETERS:
208 -- handle - Context handle from previous OPEN call.
209 -- name - Attribute name.
210 -- object_type- Object type to which the transform applies.
211
212 PROCEDURE set_parse_item (
213 handle IN NUMBER,
214 name IN VARCHAR2,
215 object_type IN VARCHAR2 DEFAULT NULL);
216
217
218 -- ADD_TRANSFORM : Specify a transform to be applied to the XML representation
219 -- of objects returned by FETCH_xxx.
220 -- PARAMETERS: handle - Context handle from previous OPEN call.
221 -- name - The name of the transform: Internal name (like 'DDL')
222 -- or a URI to a stylesheet.
223 -- encoding- If name is a URI, encoding of the target stylesheet.
224 -- object_type - Object type to which the transform param applies.
225 -- RETURNS: An opaque handle to the transform to be used in subsequent
226 -- calls to SET_TRANSFORM_PARAM.
227
228 FUNCTION add_transform (
229 handle IN NUMBER,
230 name IN VARCHAR2,
231 encoding IN VARCHAR2 DEFAULT NULL,
232 object_type IN VARCHAR2 DEFAULT NULL)
233 RETURN NUMBER;
234
235
236 -- SET_TRANSFORM_PARAM: Specifies a value for a parameter to the XSL-T
237 -- stylesheet identified by handle.
238 -- This procedure is overloaded: the parameter value can be a varchar2,
239 -- number, or a boolean.
240 -- PARAMETERS:
241 -- transform_handle - Handle from previous ADD_TRANSFORM call.
242 -- name - Name of the parameter.
243 -- value - Value for the parameter.
244 -- object_type - Object type to which the transform param applies.
245
246 PROCEDURE set_transform_param (
247 transform_handle IN NUMBER,
248 name IN VARCHAR2,
249 value IN VARCHAR2,
250 object_type IN VARCHAR2 DEFAULT NULL);
251
252 PROCEDURE set_transform_param (
253 transform_handle IN NUMBER,
254 name IN VARCHAR2,
255 value IN NUMBER,
256 object_type IN VARCHAR2 DEFAULT NULL);
257
258 PROCEDURE set_transform_param (
259 transform_handle IN NUMBER,
260 name IN VARCHAR2,
261 value IN BOOLEAN DEFAULT TRUE,
262 object_type IN VARCHAR2 DEFAULT NULL);
263
264 -- SET_REMAP_PARAM: Specifies values for a remap parameter to the XSL-T
265 -- stylesheet identified by handle.
266 -- PARAMETERS:
267 -- transform_handle - Handle from previous ADD_TRANSFORM call.
268 -- name - Name of the parameter.
269 -- old_value - Old value for the remapping
270 -- new_value - New value for the remapping
271 -- object_type - Object type to which the transform param applies.
272
273 PROCEDURE set_remap_param (
274 transform_handle IN NUMBER,
275 name IN VARCHAR2,
276 old_value IN VARCHAR2,
277 new_value IN VARCHAR2,
278 object_type IN VARCHAR2 DEFAULT NULL);
279
280 -- GET_OBJECT_TYPE_INFO
281 -- PARAMETERS:
285 PROCEDURE get_object_type_info (
282 -- handle - Context handle from previous OPEN call.
283 -- heterogeneous - (OUT) TRUE if heterogeneous type
284
286 handle IN NUMBER,
287 heterogeneous OUT BOOLEAN );
288
289 -- GET_VIEW_FILTER_INPUTS: For some object types (TABLE, MView, etc.)
290 -- we define multiple views for fetching the objects (e.g., separate
291 -- views for partitioned and non-partitioned tables). We can improve
292 -- performance by avoiding querying views which don't match the
293 -- the user filters: cheap queries against ku$_tabprop_view, etc.
294 -- allow us to avoid expensive queries against the object views.
295 -- To avoid SQL injection, the cheap queries must be issued from
296 -- the invoker rights package rather than from this package.
297 -- This procedure returns filters for the caller to issue the query.
298 -- PARAMETERS:
299 -- handle - Context handle from previous OPEN call.
300 -- obj_handle - (OUT) handle of the current object
301 -- object_type - (OUT) current object type
302 -- schema_filter - (OUT) schema
303 -- name_filter - (OUT) name
304 -- schema_expr_filter - (OUT) schema expression
305 -- name_expr_filter - (OUT) name expression
306 -- primary_filter - (OUT) filter for primary objects
307 -- secondary_filter - (OUT) filter for secondary objects
308 -- objnum_count - (OUT) number of entries in object_numbers
309 -- object_numbers - (OUT) table of object numbers
310 -- objnum_filter_attrname - (OUT) attrname of the objnum filter
311 -- object_type_path - (OUT) full path name of object type
312 -- Output parameters are set to NULL if the corresponding filter was not
313 -- specified.
314
315 PROCEDURE get_view_filter_inputs (
316 handle IN NUMBER,
317 obj_handle OUT NUMBER,
318 object_type OUT VARCHAR2,
319 schema_filter OUT VARCHAR2,
320 name_filter OUT VARCHAR2,
321 schema_expr_filter OUT VARCHAR2,
322 name_expr_filter OUT VARCHAR2,
323 primary_filter OUT BOOLEAN,
324 secondary_filter OUT BOOLEAN,
325 objnum_count OUT NUMBER,
326 object_numbers OUT t_num_coll,
327 objnum_filter_attrname OUT VARCHAR2,
328 object_type_path OUT VARCHAR2);
329
330
331 -- GET_XML_INPUTS: Get inputs needed to invoke the XML renderer
332 -- PARAMETERS:
333 -- handle - Context handle from previous OPEN call.
334 -- objnum_function - text string containing invocation of a table
335 -- function to return object numbers, e.g.,
336 -- 'DBMS_METADATA.FETCH_OBJNUMS(10001)'
337 -- sortobjnum_function - text string containing invocation of a table
338 -- function to return a nested table of type
339 -- sys.ku$_ObjNumPairList, e.g.,
340 -- 'DBMS_METADATA.FETCH_SORTED_OBJNUMS(10001)'
341 -- stmt - SQL statement (NULL if no more stmts)
342 -- rowtag - row tag
343 -- xmltag - xmltag for XML object
344 -- object_count - object count
345 -- object_type_path- full path name of object type
346 -- seqno - seqno of object type in heterogeneous collection
347 -- callout - 0 = normal xml fetch
348 -- 1 = callout
349 -- 2 = object number fetch, no xml
350 -- 3 = obj#, dependent obj# fetch
351 -- 4 = xmlschema special case - just fetch .xsd document
352 -- 5 = parent_obj#, object_name fetch
353 -- 6 = fetch ku$_objgrant_t UDTs
354 -- parsed_items - Array of varchar with requested parse item names.
355 -- Values retrieved from query execution in prvtmeta.
356 -- bind_vars - table of bind variable values to be used in query
357 -- objnum_count - number of entries in object_numbers
358 -- object_numbers - table of object numbers
359 -- object_names - (OUT) table of object names
360
361 PROCEDURE GET_XML_INPUTS (
362 handle IN NUMBER,
363 objnum_function IN VARCHAR2,
364 sortobjnum_function IN VARCHAR2,
365 stmt OUT VARCHAR2,
366 rowtag OUT VARCHAR2,
367 xmltag OUT VARCHAR2,
368 object_count OUT NUMBER,
369 object_type_path OUT VARCHAR2,
370 seqno OUT NUMBER,
371 callout OUT NUMBER,
372 parsed_items OUT dbms_sql.Varchar2_Table,
373 bind_vars OUT dbms_sql.Varchar2_Table,
374 objnum_count OUT NUMBER,
375 object_numbers OUT t_num_coll,
376 object_names OUT dbms_sql.Varchar2_Table);
377
378 -- NEXT_OBJECT: Set the dbms_metadata_int state to point to the
379 -- next object type to be fetched. (For homogeneous object types
380 -- this is a no-op.)
381 -- PARAMETERS:
382 -- handle - Context handle from previous OPEN call.
383 -- skip_current - Skip the current step
384 -- IMPLICIT OUTPUTS:
385 -- On the first call, the root heterogenous object is initialized
389 -- and cur_script moves to the next type.
386 -- context_list(ctxind).cur_script - points to the next homogeneous
387 -- type to be queried.
388 -- If skip_current is TRUE, the current step is marked 'completed'
390
391 PROCEDURE next_object (
392 handle IN NUMBER,
393 skip_current IN BOOLEAN DEFAULT FALSE );
394
395 -- SET_OBJECTS_FETCHED: Set the count of objects fetched and their objnums
396 -- PARAMETERS:
397 -- handle - Context handle from previous OPEN call.
398 -- object_count - object count
399 -- object_numbers - table of objects numbers
400 -- dependent_objects - table of dependent object numbers
401 -- object_names - table of object names
402 -- object_schemas - table of object schema names
403
404 PROCEDURE SET_OBJECTS_FETCHED (
405 handle IN NUMBER,
406 object_count IN NUMBER,
407 object_numbers IN t_num_coll,
408 dependent_objects IN t_num_coll);
409
410 PROCEDURE SET_OBJECTS_FETCHED (
411 handle IN NUMBER,
412 object_count IN NUMBER,
413 object_numbers IN t_num_coll,
414 dependent_objects IN t_num_coll,
415 object_names IN dbms_sql.Varchar2_Table);
416
417 PROCEDURE SET_OBJECTS_FETCHED (
418 handle IN NUMBER,
419 object_count IN NUMBER,
420 object_schemas IN dbms_sql.Varchar2_Table,
421 object_names IN dbms_sql.Varchar2_Table,
422 object_levels IN t_num_coll);
423
424 -- MODIFY_VAT: Do MODIFY/REMAP for VIEWS_AS_TABLES step
425 -- PARAMETERS:
426 -- handle - Context handle from previous OPEN call.
427 -- ho_type - heterogeneous object type (e.g., TABLE_EXPORT)
428 -- path - path of step to modify
429 -- transform - transform/remap param name
430 -- name1 - param value
431 -- name2 - param value
432
433 PROCEDURE MODIFY_VAT(
434 handle IN NUMBER,
435 ho_type IN VARCHAR2,
436 path IN VARCHAR2,
437 transform IN VARCHAR2,
438 name1 IN VARCHAR2,
439 name2 IN VARCHAR2);
440
441 -- DO_TRANSFORM: Transform the XML doc using all added transforms
442 -- PARAMETERS:
443 -- handle - Context handle from previous OPEN call.
444 -- xmldoc - The XML document
445 -- doc - returned document as a CLOB
446 -- do_parse - TRUE = do parse transform
447
448 PROCEDURE DO_TRANSFORM (
449 handle IN NUMBER,
450 xmldoc IN CLOB,
451 doc IN OUT NOCOPY CLOB,
452 do_parse IN BOOLEAN DEFAULT FALSE);
453
454
455 -- DO_PARSE_TRANSFORM: Transform the XML doc with the parse transform
456 -- (used by FETCH_XML)
457 -- PARAMETERS:
458 -- handle - Context handle from previous OPEN call.
459 -- xmldoc - The XML document
460 -- doc - returned document as a CLOB
461
462 PROCEDURE DO_PARSE_TRANSFORM (
463 handle IN NUMBER,
464 xmldoc IN CLOB,
465 doc IN OUT NOCOPY CLOB);
466
467 -- GET_PARSE_DELIM: Get the parse delimiter
468 -- PARAMETERS: handle - Context handle from previous OPEN call.
469
470 FUNCTION GET_PARSE_DELIM (handle IN NUMBER)
471 RETURN VARCHAR2;
472
473 -- CLOSE: Cleanup all context associated with handle.
474 -- PARAMETERS: handle - Context handle from previous OPEN call.
475
476 PROCEDURE CLOSE (handle IN NUMBER);
477
478 -- OPENC: Specifies the type of object whose metadata is to be compared.
479 -- PARAMETERS:
480 -- object_type - Identifies the type of objects to be submitted; e.g.,
481 -- TABLE, INDEX, etc. May not be a heterogeneous
482 -- object type.
483 -- version - The version of the objects' DDL to be created.
484 -- Values can be 'COMPATIBLE' (default), 'LATEST' or
485 -- a specific version number.
486 -- RETURNS:
487 -- A handle to be used in subsequent calls to ADD_TRANSFORM, CONVERT,
488 -- PUT and CLOSE.
489 -- EXCEPTIONS:
490 -- INVALID_ARGVAL - a NULL or invalid value was supplied for an input
491 -- parameter.
492
493 FUNCTION openc (
494 object_type IN VARCHAR2,
495 version IN VARCHAR2,
496 model IN VARCHAR2,
497 public_func IN VARCHAR2,
498 current_user IN VARCHAR2)
499 RETURN NUMBER;
500
501 -- OPENW: Specifies the type of object whose metadata is to be submitted.
502 -- PARAMETERS:
503 -- object_type - Identifies the type of objects to be submitted; e.g.,
504 -- TABLE, INDEX, etc. May not be a heterogeneous
505 -- object type.
506 -- version - The version of the objects' DDL to be created.
507 -- Values can be 'COMPATIBLE' (default), 'LATEST' or a specific
508 -- version number.
509 -- model - The view of the metadata, such as Oracle proprietary,
510 -- ANSI99, etc. Currently only 'ORACLE' is supported.
511 -- public_func - Name of the public function in DBMS_METADATA called
515 -- A handle to be used in subsequent calls to ADD_TRANSFORM, CONVERT,
512 -- by the user; for error reporting.
513 --
514 -- RETURNS:
516 -- PUT and CLOSE.
517 -- EXCEPTIONS:
518 -- INVALID_ARGVAL - a NULL or invalid value was supplied for an input
519 -- parameter.
520
521 FUNCTION openw (
522 object_type IN VARCHAR2,
523 version IN VARCHAR2,
524 model IN VARCHAR2,
525 public_func IN VARCHAR2)
526 RETURN NUMBER;
527
528 -- SET_DEBUG: Set the internal debug switch.
529 -- PARAMETERS:
530 -- on_off - new switch state.
531 -- arg2 - unused argument to force the overloading of
532 -- this procedure (i.e., overloaded version of this
533 -- routine will match the common datapump interface.
534
535 PROCEDURE set_debug(
536 on_off IN BOOLEAN,
537 arg2 IN BOOLEAN DEFAULT TRUE);
538
539 -- SET_DEBUG: Enable Metadata tracing.
540 -- PARAMETERS:
541 -- debug_flags - trace flag bitvector (see prvtkupc.sql for bit defs).
542 PROCEDURE set_debug(
543 debug_flags IN BINARY_INTEGER);
544
545 -- PRINT_CTXS: For debugging: Print all active contexts
546
547 PROCEDURE print_ctxs;
548
549
550 -- compare: Specifies the type of object whose metadata is to be compared.
551 -- PARAMETERS:
552 -- handle - compare context handle
553 -- doc1 - first source document
554 -- doc2 - second source document
555 -- difdoc - return diff document
556 -- diffs - TRUE - diffs found
557 -- FALSE - doc1 == doc2
558 --
559 -- EXCEPTIONS:
560 -- INVALID_ARGVAL - a NULL or invalid value was supplied for an input
561 -- parameter.
562 PROCEDURE compare (
563 handle IN NUMBER,
564 doc1 IN CLOB,
565 doc2 IN CLOB,
566 difdoc IN OUT NOCOPY CLOB,
567 diffs OUT BOOLEAN);
568
569 -- IS_ATTR_VALID_ON_10:
570 --
571 -- PARAMETERS:
572 -- objnum - obj# of table
573 -- intcol_num - intcol# of column
574 FUNCTION is_attr_valid_on_10(
575 obj_num IN NUMBER,
576 intcol_num IN NUMBER)
577 RETURN NUMBER;
578
579 END DBMS_METADATA_INT;