1 PACKAGE BODY ad_dbms_metadata AS
2 /* $Header: adgrants.sql 120.67.12020000.13 2013/05/15 11:19:38 mkumandu ship $ */
3 -- OPEN: Specifies the type of object whose metadata is to be retrieved.
4
5 FUNCTION open (
6 object_type IN VARCHAR2,
7 version IN VARCHAR2 DEFAULT 'COMPATIBLE',
8 model IN VARCHAR2 DEFAULT 'ORACLE')
9 RETURN NUMBER IS
10 BEGIN
11 -- call the call dbms_metadata function
12 RETURN dbms_metadata.open(object_type, version, model);
13 END;
14
15
16
17 ---------------------------------------------------------------------
18 -- SET_FILTER: Specifies restrictions on the objects whose metadata
19 -- is to be retrieved.
20 -- This function is overloaded: the filter value can be a varchar2,
21 -- number or boolean. This is the varchar2 variant.
22 -- PARAMETERS:
23 -- handle - Context handle from previous OPEN call.
24 -- name - Name of the filter.
25 -- value - Value of the filter.
26
27 PROCEDURE set_filter (
28 handle IN NUMBER,
29 name IN VARCHAR2,
30 value IN VARCHAR2) IS
31 BEGIN
32
33 dbms_metadata.set_filter(handle,name,value);
34
35 END;
36
37
38 ---------------------------------------------------------------------
39 -- SET_FILTER: Specifies restrictions on the objects whose metadata
40 -- is to be retrieved.
41 -- This function is overloaded: the filter value can be a varchar2,
42 -- number or boolean. This is the number variant.
43 -- PARAMETERS:
44 -- handle - Context handle from previous OPEN call.
45 -- name - Name of the filter.
46 -- value - Value of the filter.
47 -- object_type_path- Path name of object types to which
48 -- the filter applies.
49
50 PROCEDURE set_filter (
51 handle IN NUMBER,
52 name IN VARCHAR2,
53 value IN BOOLEAN DEFAULT TRUE) IS
54 BEGIN
55 dbms_metadata.set_filter(handle,name,value);
56 END;
57
58
59
60 ---------------------------------------------------------------------
61 -- SET_COUNT: Specifies the number of objects to be returned in a single
62 -- FETCH_xxx call.
63 -- PARAMETERS:
64 -- handle - Context handle from previous OPEN call.
65 -- value - Number of objects to retrieve.
66 -- object_type_path- Path name of object types to which
67 -- the count applies.
68
69 PROCEDURE set_count (
70 handle IN NUMBER,
71 value IN NUMBER ) IS
72 BEGIN
73 dbms_metadata.set_count(handle,value);
74 END;
75
76
77
78 ---------------------------------------------------------------------
79 -- GET_QUERY: Return the text of the query (or queries) that will be
80 -- used by FETCH_xxx. Ths function is provided to aid
81 -- in debugging.
82 -- PARAMETERS: handle - Context handle from previous OPEN call.
83 -- RETURNS: Text of the query.
84
85 FUNCTION get_query (
86 handle IN NUMBER)
87 RETURN VARCHAR2 IS
88 BEGIN
89 RETURN dbms_metadata.get_query(handle);
90 END;
91
92
93 ---------------------------------------------------------------------
94 -- SET_PARSE_ITEM: Enables output parsing and specifies an object attribute
95 -- to be parsed and returned
96 -- PARAMETERS:
97 -- handle - Context handle from previous OPEN call.
98 -- name - Attribute name.
99 -- object_type- Object type to which the transform applies.
100
101 PROCEDURE set_parse_item (
102 handle IN NUMBER,
103 name IN VARCHAR2) IS
104 BEGIN
105 dbms_metadata.set_parse_item(handle,name);
106 END;
107
108
109 ---------------------------------------------------------------------
110 -- ADD_TRANSFORM : Specify a transform to be applied to the XML representation
111 -- of objects returned by FETCH_xxx.
112 -- PARAMETERS: handle - Context handle from previous OPEN call.
113 -- name - The name of the transform: Internal name like 'DDL'
114 -- or a URI pointing to a stylesheet
115 -- encoding- If name is a URI, specifies encoding of the target
116 -- stylesheet.
117 -- RETURNS: An opaque handle to the transform to be used in subsequent
118 -- calls to SET_TRANSFORM_PARAM.
119
120 FUNCTION add_transform (
121 handle IN NUMBER,
122 name IN VARCHAR2,
123 encoding IN VARCHAR2 DEFAULT NULL)
124 RETURN NUMBER IS
125 BEGIN
126 return dbms_metadata.add_transform(handle,name,encoding);
127 END;
128
129
130 ---------------------------------------------------------------------
131 -- SET_TRANSFORM_PARAM: Specifies a value for a parameter to the XSL-T
132 -- stylesheet identified by handle.
133 -- This procedure is overloaded: the parameter value can be varchar2,
134 -- boolean or numeric.
135 -- PARAMETERS:
136 -- transform_handle - Handle from previous ADD_TRANSFORM call.
137 -- name - Name of the parameter.
138 -- value - Boolean value for the parameter.
139 -- object_type - Object type to which the transform param applies.
140
141 PROCEDURE set_transform_param (
142 transform_handle IN NUMBER,
143 name IN VARCHAR2,
144 value IN VARCHAR2) IS
145 BEGIN
146 dbms_metadata.set_transform_param(transform_handle,name,value);
147 END;
148
149
150
151 ---------------------------------------------------------------------
152 -- SET_TRANSFORM_PARAM: Specifies a value for a parameter to the XSL-T
153 -- stylesheet identified by handle.
154 -- This procedure is overloaded: the parameter value can be varchar2,
155 -- boolean or numeric.
156 -- PARAMETERS:
157 -- transform_handle - Handle from previous ADD_TRANSFORM call.
158 -- name - Name of the parameter.
159 -- value - Boolean value for the parameter.
160 -- object_type - Object type to which the transform param applies.
161
162 PROCEDURE set_transform_param (
163 transform_handle IN NUMBER,
164 name IN VARCHAR2,
165 value IN BOOLEAN DEFAULT TRUE) IS
166 BEGIN
167 dbms_metadata.set_transform_param(transform_handle,name,value);
168 END;
169
170
171
172
173 ---------------------------------------------------------------------
174 -- SET_REMAP_PARAM: Specifies a value for a parameter to the XSL-T
175 -- stylesheet identified by handle.
176 -- PARAMETERS:
177 -- transform_handle - Handle from previous ADD_TRANSFORM call.
178 -- name - Name of the parameter.
179 -- old_value - Old value for the remapping
180 -- new_value - New value for the remapping
181 -- object_type - Object type to which the transform param applies.
182
183 PROCEDURE set_remap_param (
184 transform_handle IN NUMBER,
185 name IN VARCHAR2,
186 old_value IN VARCHAR2,
187 new_value IN VARCHAR2) IS
188 BEGIN
189 NULL;
190 -- dbms_metadata.set_remap_param(transform_handle,name,old_value,new_value);
191
192 END;
193
194
195
196 ---------------------------------------------------------------------
197 -- FETCH_XML: Return metadata for objects as XML documents. This version
198 -- can return multiple objects per call (when the SET_COUNT
199 -- 'value' parameter > 1).
200 -- PARAMETERS: handle - Context handle from previous OPEN call.
201 -- RETURNS: XML metadata for the objects as an XMLType, or NULL if all
202 -- objects have been fetched.
203 -- EXCEPTIONS: Throws an exception if DDL transform has been added
204
205 FUNCTION fetch_xml (handle IN NUMBER)
206 RETURN sys.XMLType IS
207 BEGIN
208
209 return dbms_metadata.fetch_xml(handle);
210 END;
211
212
213
214 ---------------------------------------------------------------------
215 -- FETCH_DDL: Return metadata for one object as DDL.
216 -- More than one DDL statement may be returned.
217 -- PARAMETERS: handle - Context handle from previous OPEN call.
218 -- RETURNS: Metadata for the object as one or more DDL statements
219 -- IMPLICIT PARAMETER: FETCH_DDL$_out - converted output
220 -- EXCEPTIONS: Throws an exception if DDL transform was not added.
221
222 FUNCTION fetch_ddl (
223 handle IN NUMBER)
224 RETURN sys.ku$_ddls IS
225 BEGIN
226
227 return dbms_metadata.fetch_ddl(handle);
228 END;
229
230
231
232 ---------------------------------------------------------------------
233 -- FETCH_CLOB: Return metadata for object (transformed or not) as a CLOB.
234 -- PARAMETERS: handle - Context handle from previous OPEN call.
235 -- RETURNS: XML metadata for the objects as a CLOB, or NULL if all
236 -- objects have been fetched.
237
238 FUNCTION fetch_clob (handle IN NUMBER)
239 RETURN CLOB IS
240 BEGIN
241 RETURN dbms_metadata.fetch_clob(handle);
242 END;
243
244
245 ---------------------------------------------------------------------
246 -- PROCEDURE FETCH_CLOB: Same as above but with IN/OUT NOCOPY CLOB. CLOB
247 -- must be pre-created prior to call.
248
249 PROCEDURE fetch_clob (
250 handle IN NUMBER,
251 xmldoc IN OUT NOCOPY CLOB) IS
252 BEGIN
253 dbms_metadata.fetch_clob(handle,xmldoc);
254 END;
255
256
257 ---------------------------------------------------------------------
258 -- CLOSE: Cleanup all context associated with handle.
259 -- PARAMETERS: handle - Context handle from previous OPEN call.
260
261 PROCEDURE CLOSE (handle IN NUMBER) IS
262 BEGIN
263 dbms_metadata.close(handle);
264 END;
265
266 ---------------------------------------------------------------------
267 -- GET_XML: Return the metadata for a single object as XML.
268 -- This interface is meant for casual browsing (e.g., from SQLPlus)
269 -- vs. the programmatic OPEN / FETCH / CLOSE interfaces above.
270 -- PARAMETERS:
271 -- object_type - The type of object to be retrieved.
272 -- name - Name of the object.
273 -- schema - Schema containing the object. Defaults to
274 -- the caller's schema.
275 -- version - The version of the objects' metadata.
276 -- model - The object model for the metadata.
277 -- transform - XSL-T transform to be applied.
278 -- RETURNS: Metadata for the object as an NCLOB.
279
280 FUNCTION get_xml (
281 object_type IN VARCHAR2,
282 name IN VARCHAR2,
283 schema IN VARCHAR2 DEFAULT NULL,
284 version IN VARCHAR2 DEFAULT 'COMPATIBLE',
285 model IN VARCHAR2 DEFAULT 'ORACLE',
286 transform IN VARCHAR2 DEFAULT NULL)
287 RETURN CLOB IS
288 BEGIN
289 RETURN dbms_metadata.get_xml(object_type,name,schema,version,model,transform);
290 END;
291
292 ---------------------------------------------------------------------
293 -- GET_DDL: Return the metadata for a single object as DDL.
294 -- This interface is meant for casual browsing (e.g., from SQLPlus)
295 -- vs. the programmatic OPEN / FETCH / CLOSE interfaces above.
296 -- PARAMETERS:
297 -- object_type - The type of object to be retrieved.
298 -- name - Name of the object.
299 -- schema - Schema containing the object. Defaults to
300 -- the caller's schema.
301 -- version - The version of the objects' metadata.
302 -- model - The object model for the metadata.
303 -- transform - XSL-T transform to be applied.
304 -- RETURNS: Metadata for the object transformed to DDL as a CLOB.
305
306 FUNCTION get_ddl (
307 object_type IN VARCHAR2,
308 name IN VARCHAR2,
309 schema IN VARCHAR2 DEFAULT NULL,
310 version IN VARCHAR2 DEFAULT 'COMPATIBLE',
311 model IN VARCHAR2 DEFAULT 'ORACLE',
312 transform IN VARCHAR2 DEFAULT 'DDL')
313 RETURN CLOB IS
314 BEGIN
315 RETURN dbms_metadata.get_ddl(object_type,name,schema,version,model,transform);
316 END;
317
318 ---------------------------------------------------------------------
319 -- GET_DEPENDENT_XML: Return the metadata for objects dependent on a
320 -- base object as XML.
321 -- This interface is meant for casual browsing (e.g., from SQLPlus)
322 -- vs. the programmatic OPEN / FETCH / CLOSE interfaces above.
323 -- PARAMETERS:
324 -- object_type - The type of object to be retrieved.
325 -- base_object_name- Name of the base object.
326 -- base_object_schema- Schema containing the base object. Defaults to
327 -- the caller's schema.
328 -- version - The version of the objects' metadata.
329 -- model - The object model for the metadata.
330 -- transform - XSL-T transform to be applied.
334 FUNCTION get_dependent_xml (
331 -- object_count - maximum number of objects to return
332 -- RETURNS: Metadata for the object as a CLOB.
333
335 object_type IN VARCHAR2,
336 base_object_name IN VARCHAR2,
337 base_object_schema IN VARCHAR2 DEFAULT NULL,
338 version IN VARCHAR2 DEFAULT 'COMPATIBLE',
339 model IN VARCHAR2 DEFAULT 'ORACLE',
340 transform IN VARCHAR2 DEFAULT NULL,
341 object_count IN NUMBER DEFAULT 10000)
342 RETURN CLOB IS
343 BEGIN
344 RETURN dbms_metadata.get_dependent_xml( object_type,
345 base_object_name,base_object_schema,
346 version,model,transform,
347 object_count);
348 END;
349
350 ---------------------------------------------------------------------
351 -- GET_DEPENDENT_DDL: Return the metadata for objects dependent on a
352 -- base object as DDL.
353 -- This interface is meant for casual browsing (e.g., from SQLPlus)
354 -- vs. the programmatic OPEN / FETCH / CLOSE interfaces above.
355 -- PARAMETERS:
356 -- object_type - The type of object to be retrieved.
357 -- base_object_name- Name of the base object.
358 -- base_object_schema- Schema containing the base object. Defaults to
359 -- the caller's schema.
360 -- version - The version of the objects' metadata.
361 -- model - The object model for the metadata.
362 -- transform - XSL-T transform to be applied.
363 -- object_count - maximum number of objects to return
364 -- RETURNS: Metadata for the object as a CLOB.
365
366 FUNCTION get_dependent_ddl (
367 object_type IN VARCHAR2,
368 base_object_name IN VARCHAR2,
369 base_object_schema IN VARCHAR2 DEFAULT NULL,
370 version IN VARCHAR2 DEFAULT 'COMPATIBLE',
371 model IN VARCHAR2 DEFAULT 'ORACLE',
372 transform IN VARCHAR2 DEFAULT 'DDL',
373 object_count IN NUMBER DEFAULT 10000)
374 RETURN CLOB IS
375 BEGIN
376 RETURN dbms_metadata.get_dependent_ddl(object_type,base_object_name,
377 base_object_schema, version,
378 model, transform, object_count);
379 END;
380
381
382 END AD_DBMS_METADATA;