1 PACKAGE dbms_sqldiag AUTHID CURRENT_USER AS
2
3 -----------------------------------------------------------------------------
4 -- global constant declarations --
5 -----------------------------------------------------------------------------
6 --
7 -- sqldiag advisor name
8 --
9 ADV_SQL_DIAG_NAME CONSTANT VARCHAR2(18) := 'SQL Repair Advisor';
10
11 --
12 -- SQLDIAG advisor task scope parameter values
13 --
14 SCOPE_LIMITED CONSTANT VARCHAR2(7) := 'LIMITED';
15 SCOPE_COMPREHENSIVE CONSTANT VARCHAR2(13) := 'COMPREHENSIVE';
16
17 --
18 -- SQLDIAG advisor time_limit constants (in seconds)
19 --
20 TIME_LIMIT_DEFAULT CONSTANT NUMBER := 1800;
21
22 --
23 -- report type (possible values) constants
24 --
25 -- TYPE_TEXT: text report
26 -- TYPE_XML: XML report
27 -- TYPE_HTML: html report
28 --
29 TYPE_TEXT CONSTANT VARCHAR2(4) := 'TEXT' ;
30 TYPE_XML CONSTANT VARCHAR2(3) := 'XML' ;
31 TYPE_HTML CONSTANT VARCHAR2(4) := 'HTML' ;
32
33 --
34 -- report level (possible values) constants
35 --
36 -- LEVEL_BASIC: simple version of the report.
37 -- Just show info about the actions taken by
38 -- the advisor.
39 -- LEVEL_TYPICAL: show info about every statement
40 -- analyzed, including recs not implemented.
41 -- LEVEL_ALL: verbose report level, also give
42 -- annotations about statements skipped over.
43 --
44 LEVEL_TYPICAL CONSTANT VARCHAR2(7) := 'TYPICAL' ;
45 LEVEL_BASIC CONSTANT VARCHAR2(5) := 'BASIC' ;
46 LEVEL_ALL CONSTANT VARCHAR2(3) := 'ALL' ;
47
48 --
49 -- report section (possible values) constants
50 --
51 -- SECTION_SUMMARY - summary information
52 -- SECTION_FINDINGS - sql repair findings
53 -- SECTION_PLAN - explain plans
54 -- SECTION_INFORMATION - general information
55 -- SECTION_ERROR - statements with errors
56 -- SECTION_ALL - all statements
57 --
58 SECTION_SUMMARY CONSTANT VARCHAR2(7) := 'SUMMARY' ;
59 SECTION_FINDINGS CONSTANT VARCHAR2(8) := 'FINDINGS' ;
60 SECTION_PLANS CONSTANT VARCHAR2(5) := 'PLANS' ;
61 SECTION_INFORMATION CONSTANT VARCHAR2(11):= 'INFORMATION';
62 SECTION_ERRORS CONSTANT VARCHAR2(6) := 'ERRORS' ;
63 SECTION_ALL CONSTANT VARCHAR2(3) := 'ALL' ;
64
65 --
66 -- script section constants
67 --
68 REC_TYPE_ALL CONSTANT VARCHAR2(3) := 'ALL';
69 REC_TYPE_SQL_PROFILES CONSTANT VARCHAR2(8) := 'PROFILES';
70 REC_TYPE_STATS CONSTANT VARCHAR2(10) := 'STATISTICS';
71 REC_TYPE_INDEXES CONSTANT VARCHAR2(7) := 'INDEXES';
72
73 --
74 -- capture section constants
75 --
76 MODE_REPLACE_OLD_STATS CONSTANT NUMBER := 1;
77 MODE_ACCUMULATE_STATS CONSTANT NUMBER := 2;
78
79 --
80 -- problem type constants
81 --
82 -- PERFORMANCE - User suspects this is a performance problem
83 -- WRONG_RESULTS - User suspects the query is giving inconsistent results
84 -- COMPILATION_ERROR - User sees a crash in compilation
85 -- EXECUTION_ERROR - User sees a crash in execution
86 -- ALT_PLAN_GEN - Just explore all alternative plans
87 --
88 PROBLEM_TYPE_PERFORMANCE CONSTANT NUMBER := 1;
89 PROBLEM_TYPE_WRONG_RESULTS CONSTANT NUMBER := 2;
90 PROBLEM_TYPE_COMPILATION_ERROR CONSTANT NUMBER := 3;
91 PROBLEM_TYPE_EXECUTION_ERROR CONSTANT NUMBER := 4;
92 PROBLEM_TYPE_ALT_PLAN_GEN CONSTANT NUMBER := 5;
93
94 --
95 -- findings filter constants
96 --
97 -- All - Show all possible findings
98 -- VALIDATION - Show status of validation rules over structures
99 -- FEATURES - Show only features used by the query
100 -- FILTER_PLANS - Show the alternative plans generated by the advisor
101 -- CR_DIFF - Show difference between two plans
102 -- MASK_VARIANT - Mask info for testing, e.g., mask the cost of plans
103 -- OBJ_FEATURES - Show object trying features history
104 -- BASIC_INFO - Show features used, but not bug fix control info
105 --
106 SQLDIAG_FINDINGS_ALL CONSTANT NUMBER := 1;
107 SQLDIAG_FINDINGS_VALIDATION CONSTANT NUMBER := 2;
108 SQLDIAG_FINDINGS_FEATURES CONSTANT NUMBER := 3;
109 SQLDIAG_FINDINGS_FILTER_PLANS CONSTANT NUMBER := 4;
110 SQLDIAG_FINDINGS_CR_DIFF CONSTANT NUMBER := 5;
111 SQLDIAG_FINDINGS_MASK_VARIANT CONSTANT NUMBER := 6;
112 SQLDIAG_FINDINGS_OBJ_FEATURES CONSTANT NUMBER := 7;
113 SQLDIAG_FINDINGS_BASIC_INFO CONSTANT NUMBER := 8;
114
115 --
116 -- mask mode for filtering findings
117 --
118 SQLDIAG_MASK_NONE CONSTANT NUMBER := 1;
119 SQLDIAG_MASK_COST CONSTANT NUMBER := 2;
120
121 -----------------------------------------------------------------------------
122 -- procedure / function declarations --
123 -----------------------------------------------------------------------------
124
125 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
126 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
127 -- ------------------------------------------ --
128 -- SQL TEST CASE BUILDER PROCEDURES/FUNCTIONS --
129 -- ------------------------------------------ --
130 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
131 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
132
133 -------------------------- export_sql_testcase ------------------------
134 -- NAME:
135 -- export_sql_testcase
136 --
137 -- DESCRIPTION:
138 -- Export a SQL test case to a directory.
139 -- This variant of the API has to be provided with the SQL information
140 --
141 -- EXPLANATION:
142 --
143 -- SQL test case generates a set of files needed to help
144 -- reproduce a SQL problem on a different machine:
145 --
146 -- It contains:
147 --
148 -- 1. a dump file containing schemas objects and statistics (.dmp)
149 -- 2. the explain plan for the statements (in advanced mode)
150 -- 3. diagnostic information gathered on the offending statement
151 -- 4. an import script to execute to reload the objects.
152 -- 5. a SQL scripts to replay system statistics of the source
153 -- 6. A table of content file describing the SQL test case
154 -- metadata. (xxxxmain.xml)
155 --
156 -- Usually, you only need to reference the last file (metadata file)
157 -- for importing a test case.
158 --
159 -- The following is an example PL/SQL script for TCB IMPORT.
160 -- It uses the metadata file name (xxxxmain.xml) as an input argument
161 -- when calling the import API.
162 -- (You may have to modify this script for the right arguments)
163 --
164 -- grant connect, dba, resource, query rewrite to tcb identified by tcb;
165 --
166 -- create directory TCB_IMP_DIR as '<DIRECTORY_PATH_4_TCB_IMPORT>';
167 --
168 -- conn tcb/tcb;
169 --
170 -- exec dbms_sqldiag.import_sql_testcase(directory => 'TCB_IMP_DIR' ,
171 -- filename => '<TCB_METADATA>main.xml');
172 --
173 --
174 -- Note:
175 -- !!! You should not run TCB under user SYS !!!
176 -- Use another user, such as 'tcb', who can be granted sysdba privilege
177 --
178 -- .The <DIRECTORY_PATH_4_TCB_IMPORT> is the CURRENT directory where
179 -- all the TCB files have resided. It must be an OS path on local
180 -- machine, such as '/tmp/bug8010101'. It cannot be a path to other
181 -- machine, for example by mounting over a network file system.
182 --
183 -- .By default for TCB, the data is NOT exported
184 -- In some case data is required, for example, to diagnose wrong
185 -- result problem.
186 -- To export data, call export_sql_testcase() with
187 -- exportData=>TRUE
188 --
189 -- Note the data will be imported by default, unless turned OFF by
190 -- importData=>FALSE
191 --
192 -- .TCB includes PL/SQL package spec by default , but not
193 -- the PL/SQL package body.
194 -- You may need to have the package body as well, for exmaple,
195 -- to invoke the PL/SQL functions.
196 -- To export PL/SQL package body, call export_sql_testcase() with
197 -- exportPkgbody=>TRUE
198 -- To import PL/SQL package body, call import_sql_testcase() with
199 -- importPkgbody=>TRUE
200 --
201 -- .An example that you need to include PL/SQL package (body) is
202 -- you have VPD function defined in a package
203 --
204 -- PARAMETERS:
205 -- directory (IN) - directory to store the various
206 -- generated files
207 -- sql_text (IN) - text of the sql statement to explain
208 -- user_name (IN) - name of the user schema to use to
209 -- parse the sql, defaults to current user
210 -- bind_list (IN) - list of bind values associated to the
211 -- statement
212 -- exportEnvironment (IN) - TRUE if the compilation environment
213 -- should be exported
214 -- exportMetadata (IN) - TRUE if the definition of the objects
215 -- referenced in the SQL should be exported.
216 -- exportData (IN) - TRUE if the data of the objects referenced
217 -- in the SQL should be exported.
218 -- exportPkgbody (IN) - TRUE if the body of the packages
219 -- referenced in the SQL should be exported.
220 -- samplingPercent (IN) - if exportData is TRUE, specify the
221 -- sampling percentage to use to create
222 -- the dump file
223 -- ctrlOptions (IN) - opaque control parameters
224 -- timeLimit (IN) - how much time should we spend exporting
225 -- the SQL test case
226 -- testcase_name (IN) - an optional name for the SQL test case.
227 -- This is used to prefix all the generated
228 -- scripts.
229 -- testcase (OUT) - the resulting test case
230 -- preserveSchemaMapping
231 -- (IN) - TRUE if the schema(s) will NOT be re-mapped
232 -- from the original environment to the test
233 -- environment.
234 -- version (IN) - The version of database objects to be
235 -- extracted.
236 -- This option is only valid for Export.
237 -- Database objects or attributes that are
238 -- incompatible with the version will not be
239 -- extracted.
240 -- Legal values for this parameter are
241 -- as follows:
242 --
243 -- COMPATIBLE - (default) the version of the
244 -- metadata corresponds to the
245 -- database compatibility level
246 -- and the compatibility release
247 -- level for feature (as given
248 -- in the V$COMPATIBILITY view).
249 -- Database compatibility must
250 -- be set to 9.2 or higher.
251 -- LATEST - the version of the metadata
252 -- corresponds to the database
253 -- version.
254 -- specific database version
255 -- - for example, '10.0.0'.
256 -- In Oracle Database10g, this
257 -- value cannot be
258 -- lower than 10.0.0.
259 --
260 -----------------------------------------------------------------------------
261
262 PROCEDURE export_sql_testcase(
263 directory IN VARCHAR2,
264 sql_text IN CLOB,
265 user_name IN VARCHAR2 := NULL,
266 bind_list IN sql_binds := NULL,
267 exportEnvironment IN BOOLEAN := TRUE,
268 exportMetadata IN BOOLEAN := TRUE,
269 exportData IN BOOLEAN := FALSE,
270 exportPkgbody IN BOOLEAN := FALSE,
271 samplingPercent IN NUMBER := 100,
272 ctrlOptions IN VARCHAR2 := NULL,
273 timeLimit IN NUMBER :=
274 dbms_sqldiag.TIME_LIMIT_DEFAULT,
275 testcase_name IN VARCHAR2 := NULL,
276 testcase IN OUT NOCOPY CLOB,
277 preserveSchemaMapping IN BOOLEAN := FALSE,
278 version IN VARCHAR2 := 'COMPATIBLE'
279 );
280
281 -------------------------- export_sql_testcase ------------------------
282 -- NAME:
283 -- export_sql_testcase
284 --
285 -- DESCRIPTION:
286 -- Export a SQL test case to a directory.
287 -- This API extract the SQL information from an incident file.
288 --
289 -- PARAMETERS:
290 -- directory (IN) - directory to store the various
291 -- generated files
292 -- incident_id (IN) - the incident ID containing the
293 -- offending SQL
294 -- exportEnvironment (IN) - TRUE if the compilation environment
295 -- should be exported
296 -- exportMetadata (IN) - TRUE if the definition of the objects
297 -- referenced in the SQL should be exported.
301 -- referenced in the SQL should be exported.
298 -- exportData (IN) - TRUE if the data of the objects referenced
299 -- in the SQL should be exported.
300 -- exportPkgbody (IN) - TRUE if the body of the packages
302 -- samplingPercent (IN) - if exportData is TRUE, specify the
303 -- sampling percentage to use to create
304 -- the dump file
305 -- ctrlOptions (IN) - opaque control parameters
306 -- timeLimit (IN) - how much time should we spend exporting
307 -- the SQL test case
308 -- testcase_name (IN) - an optional name for the SQL test case.
309 -- This is used to prefix all the generated
310 -- scripts.
311 -- testcase (OUT) - the resulting test case
312 -- preserveSchemaMapping
313 -- (IN) - TRUE if the schema(s) will NOT be re-mapped
314 -- from the original environment to the test
315 -- environment.
316 -- version (IN) - The version of database objects to be
317 -- extracted.
318 -- This option is only valid for Export.
319 -- Database objects or attributes that are
320 -- incompatible with the version will not be
321 -- extracted.
322 -- Legal values for this parameter are
323 -- as follows:
324 --
325 -- COMPATIBLE - (default) the version of the
326 -- metadata corresponds to the
327 -- database compatibility level
328 -- and the compatibility release
329 -- level for feature (as given
330 -- in the V$COMPATIBILITY view).
331 -- Database compatibility must
332 -- be set to 9.2 or higher.
333 -- LATEST - the version of the metadata
334 -- corresponds to the database
335 -- version.
336 -- specific database version
337 -- - for example, '10.0.0'.
338 -- In Oracle Database10g, this
339 -- value cannot be
340 -- lower than 10.0.0.
341 --
342 -----------------------------------------------------------------------------
343 PROCEDURE export_sql_testcase(
344 directory IN VARCHAR2,
345 incident_id IN VARCHAR2,
346 exportEnvironment IN BOOLEAN := TRUE,
347 exportMetadata IN BOOLEAN := TRUE,
348 exportData IN BOOLEAN := FALSE,
349 exportPkgbody IN BOOLEAN := FALSE,
350 samplingPercent IN NUMBER := 100,
351 ctrlOptions IN VARCHAR2 := NULL,
352 timeLimit IN NUMBER :=
353 dbms_sqldiag.TIME_LIMIT_DEFAULT,
354 testcase_name IN VARCHAR2 := NULL,
355 testcase IN OUT NOCOPY CLOB,
356 preserveSchemaMapping IN BOOLEAN := FALSE,
357 version IN VARCHAR2 := 'COMPATIBLE'
358 );
359
360
361 -------------------------- export_sql_testcase ------------------------
362 -- NAME:
363 -- export_sql_testcase
364 --
365 -- DESCRIPTION:
366 -- Export a SQL test case to a directory.
367 -- This API allow the SQL Testcase to be generated from a cursor
368 -- present in the cursor cache.
369 -- Use v$sql to get the SQL identifier and the SQL hash value.
370 --
371 -- PARAMETERS:
372 -- directory (IN) - directory to store the various
373 -- generated files
374 -- sql_id (IN) - identifier of the statement in the
375 -- cursor cache
376 -- plan_hash_value (IN) - plan hash value of a particula plan
377 -- of the SQL
378 -- exportEnvironment (IN) - TRUE if the compilation environment
379 -- should be exported
380 -- exportMetadata (IN) - TRUE if the definition of the objects
381 -- referenced in the SQL should be exported.
382 -- exportData (IN) - TRUE if the data of the objects referenced
383 -- in the SQL should be exported.
384 -- exportPkgbody (IN) - TRUE if the body of the packages
385 -- referenced in the SQL should be exported.
386 -- samplingPercent (IN) - if exportData is TRUE, specify the
387 -- sampling percentage to use to create
388 -- the dump file
389 -- ctrlOptions (IN) - opaque control parameters
390 -- timeLimit (IN) - how much time should we spend exporting
391 -- the SQL test case
395 -- testcase (OUT) - the resulting test case
392 -- testcase_name (IN) - an optional name for the SQL test case.
393 -- This is used to prefix all the generated
394 -- scripts.
396 -- preserveSchemaMapping
397 -- (IN) - TRUE if the schema(s) will NOT be re-mapped
398 -- from the original environment to the test
399 -- environment.
400 -- version (IN) - The version of database objects to be
401 -- extracted.
402 -- This option is only valid for Export.
403 -- Database objects or attributes that are
404 -- incompatible with the version will not be
405 -- extracted.
406 -- Legal values for this parameter are
407 -- as follows:
408 --
409 -- COMPATIBLE - (default) the version of the
410 -- metadata corresponds to the
411 -- database compatibility level
412 -- and the compatibility release
413 -- level for feature (as given
414 -- in the V$COMPATIBILITY view).
415 -- Database compatibility must
416 -- be set to 9.2 or higher.
417 -- LATEST - the version of the metadata
418 -- corresponds to the database
419 -- version.
420 -- specific database version
421 -- - for example, '10.0.0'.
422 -- In Oracle Database10g, this
423 -- value cannot be
424 -- lower than 10.0.0.
425 --
426 -----------------------------------------------------------------------------
427 PROCEDURE export_sql_testcase(
428 directory IN VARCHAR2,
429 sql_id IN VARCHAR2,
430 plan_hash_value IN NUMBER := NULL,
431 exportEnvironment IN BOOLEAN := TRUE,
432 exportMetadata IN BOOLEAN := TRUE,
433 exportData IN BOOLEAN := FALSE,
434 exportPkgbody IN BOOLEAN := FALSE,
435 samplingPercent IN NUMBER := 100,
436 ctrlOptions IN VARCHAR2 := NULL,
437 timeLimit IN NUMBER :=
438 dbms_sqldiag.TIME_LIMIT_DEFAULT,
439 testcase_name IN VARCHAR2 := NULL,
440 testcase IN OUT NOCOPY CLOB,
441 preserveSchemaMapping IN BOOLEAN := FALSE,
442 version IN VARCHAR2 := 'COMPATIBLE'
443 );
444
445 -----------------------------------------------------------------------------
446 FUNCTION export_sql_testcase_dir_by_inc(
447 incident_id IN NUMBER,
448 directory IN VARCHAR2,
449 samplingPercent IN NUMBER := 0,
450 exportEnvironment IN BOOLEAN := TRUE,
451 exportMetadata IN BOOLEAN := TRUE,
452 exportPkgbody IN BOOLEAN := FALSE,
453 preserveSchemaMapping IN BOOLEAN := FALSE,
454 version IN VARCHAR2 := 'COMPATIBLE'
455 )
456 RETURN BOOLEAN;
457
458 FUNCTION export_sql_testcase_dir_by_txt(
459 incident_id IN NUMBER,
460 directory IN VARCHAR2,
461 sql_text IN CLOB,
462 user_name IN VARCHAR2 := NULL,
463 samplingPercent IN NUMBER := 0,
464 exportEnvironment IN BOOLEAN := TRUE,
465 exportMetadata IN BOOLEAN := TRUE,
466 exportPkgbody IN BOOLEAN := FALSE,
467 preserveSchemaMapping IN BOOLEAN := FALSE,
468 version IN VARCHAR2 := 'COMPATIBLE'
469 )
470 RETURN BOOLEAN;
471
472 --------------------- replay_sql_testcase -----------------------------------
473 -- NAME:
474 -- replay_sql_testcase
475 --
476 -- DESCRIPTION:
477 -- replay a SQL Test case.
478 -- This API allows the user to execute/replay the testcase sql after
479 -- the test case is imported using import_sql_testcase. See ctrlOptions
480 -- for different replay modes.
481 --
482 -- PARAMETERS:
483 -- directory (IN) - directory containing testcase files
484 -- sqlTestCase (IN) - an XML document describing the SQL test case
485 -- ctrlOptions (IN) - opaque control parameters
486 -- The replay of the testcase can be done in
487 -- following modes:
488 -- EXPLAIN - explains the statement without using OL
489 -- OUTLINE - explains the statement using OL
490 -- EXECUTION - execute the statement without using OL
491 -- OUTLINE EXECUTION - execute the statement using OL
492 -- Default mode for replay is EXPLAIN
496 -- Possible formats are: TEXT, XML and HTML.
493 -- This parameter should be specified xml format:
494 -- <parameter name="replay"> EXECUTE </parameter>
495 -- format (IN) - format of the replay report.
497 -- TEXT is the default format.
498 --
499 -- RETURNS:
500 -- This API returns a replay report in the text format. The report format
501 -- can be specified in the ctrlOptions. For e.g.
502 -- <parameter name="report_format"> XML </parameter>
503 -- The possible report formats are
504 -- TEXT, XML and HTML. The default is TEXT.
505 -----------------------------------------------------------------------------
506 FUNCTION replay_sql_testcase(
507 directory IN VARCHAR2,
508 sqlTestCase IN CLOB,
509 ctrlOptions IN VARCHAR2 := NULL,
510 format IN VARCHAR2 := 'TEXT')
511 RETURN CLOB;
512
513 --------------------- replay_sql_testcase -----------------------------------
514 -- NAME:
515 -- replay_sql_testcase
516 --
517 -- DESCRIPTION:
518 -- replay a SQL Test case from a directory and a file name
519 -- This API allows the user to execute/replay the testcase sql after
520 -- the test case is imported using import_sql_testcase. See ctrlOptions
521 -- for different replay modes.
522 --
523 -- PARAMETERS:
524 -- directory (IN) - directory containing testcase files
525 -- filename (IN) - the name of a file containing an XML
526 -- document describing the SQL test case
527 -- ctrlOptions (IN) - opaque control parameters
528 -- The replay of the testcase can be done in
529 -- following modes:
530 -- EXPLAIN - explains the statement without using OL
531 -- OUTLINE - explains the statement using OL
532 -- EXECUTION - execute the statement without using OL
533 -- OUTLINE EXECUTION - execute the statement using OL
534 -- Default mode for replay is EXPLAIN
535 -- This parameter should be specified xml format:
536 -- <parameter name="replay"> EXECUTE </parameter>
537 -- For read doc for other ctrlOptions parameters.
538 -- format (IN) - format of the replay report.
539 -- Possible formats are: TEXT, XML and HTML.
540 -- TEXT is the default format.
541 --
542 -- RETURNS:
543 -- This API returns a replay report in the text format. The report format
544 -- can be specified in the ctrlOptions. For e.g.
545 -- <parameter name="report_format"> XML </parameter>
546 -- The possible report formats are
547 -- TEXT, XML and HTML. The default is TEXT.
548 --
549 -----------------------------------------------------------------------------
550 FUNCTION replay_sql_testcase(
551 directory IN VARCHAR2,
552 filename IN VARCHAR2,
553 ctrlOptions IN VARCHAR2 := NULL,
554 format IN VARCHAR2 := 'TEXT')
555 RETURN CLOB;
556
557 --------------------- import_sql_testcase -----------------------------
558 -- NAME:
559 -- import_sql_testcase
560 --
561 -- DESCRIPTION:
562 -- Import a SQL Test case into a schema
563 --
564 -- EXPLANATION:
565 --
566 -- SQL test case contains a set of files needed to help
567 -- reproduce a SQL problem on a different machine.
568 --
569 -- It contains:
570 --
571 -- 1. a dump file containing schemas objects and statistics (.dmp)
572 -- 2. the explain plan for the statements (in advanced mode)
573 -- 3. diagnostic information gathered on the offending statement
574 -- 4. an import script to execute to reload the objects.
575 -- 5. a SQL scripts to replay system statistics of the source
576 -- 6. A table of content file describing the SQL test case
577 -- metadata. (xxxxmain.xml)
578 --
579 -- Usually, you only need to reference the last file (metadata file)
580 -- for importing a test case.
581 --
582 -- The following is an example PL/SQL script for TCB IMPORT.
583 -- It uses the metadata file name (xxxxmain.xml) as an input argument
584 -- when calling the import API.
585 -- (You may have to modify this script for the right arguments)
586 --
587 -- grant connect, dba, resource, query rewrite to tcb identified by tcb;
588 --
589 -- create directory TCB_IMP_DIR as '<DIRECTORY_PATH_4_TCB_IMPORT>';
590 --
591 -- conn tcb/tcb;
592 --
593 -- exec dbms_sqldiag.import_sql_testcase(directory => 'TCB_IMP_DIR' ,
594 -- filename => '<TCB_METADATA>main.xml');
595 --
596 --
597 -- Note:
598 -- !!! You should not run TCB under user SYS !!!
599 -- Use another user, such as 'tcb', who can be granted sysdba privilege
600 --
601 -- .The <DIRECTORY_PATH_4_TCB_IMPORT> is the CURRENT directory where
602 -- all the TCB files have resided. It must be an OS path on local
603 -- machine, such as '/tmp/bug8010101'. It cannot be a path to other
607 -- In some case data is required, for example, to diagnose wrong
604 -- machine, for example by mounting over a network file system.
605 --
606 -- .By default for TCB, the data is NOT exported
608 -- result problem.
609 -- To export data, call export_sql_testcase() with
610 -- exportData=>TRUE
611 --
612 -- Note the data will be imported by default, unless turned OFF by
613 -- importData=>FALSE
614 --
615 -- .TCB includes PL/SQL package spec by default , but not
616 -- the PL/SQL package body.
617 -- You may need to have the package body as well, for exmaple,
618 -- to invoke the PL/SQL functions.
619 -- To export PL/SQL package body, call export_sql_testcase() with
620 -- exportPkgbody=>TRUE
621 -- To import PL/SQL package body, call import_sql_testcase() with
622 -- importPkgbody=>TRUE
623 --
624 -- .An example that you need to include PL/SQL package (body) is
625 -- you have VPD function defined in a package
626 --
627 -- PARAMETERS:
628 -- directory (IN) - directory containing testcase files
629 -- sqlTestCase (IN) - an XML document describing the SQL test case
630 -- importEnvironment (IN) - TRUE if the compilation environment
631 -- should be imported
632 -- importMetadata (IN) - TRUE if the definition of the objects
633 -- referenced in the SQL should be imported.
634 -- importData (IN) - TRUE if the data of the objects referenced
635 -- in the SQL should be imported.
636 -- importPkgbody (IN) - TRUE if the body of the packages referenced
637 -- in the SQL should be imported.
638 -- importDiagnosis (IN) - TRUE if the diagnostic information
639 -- associated to the task should be imported
640 -- ignoreStorage (IN) - TRUE if the storage attributes should be
641 -- ignored
642 -- ctrlOptions (IN) - opaque control parameters
643 -- preserveSchemaMapping
644 -- (IN) - TRUE if the schema(s) will NOT be re-mapped
645 -- from the original environment to the test
646 -- environment.
647 -----------------------------------------------------------------------------
648 PROCEDURE import_sql_testcase(
649 directory IN VARCHAR2,
650 sqlTestCase IN CLOB,
651 importEnvironment IN BOOLEAN := TRUE,
652 importMetadata IN BOOLEAN := TRUE,
653 importData IN BOOLEAN := TRUE,
654 importPkgbody IN BOOLEAN := FALSE,
655 importDiagnosis IN BOOLEAN := TRUE,
656 ignoreStorage IN BOOLEAN := TRUE,
657 ctrlOptions IN VARCHAR2 := NULL,
658 preserveSchemaMapping IN BOOLEAN := FALSE);
659
660
661 --------------------- import_sql_testcase -----------------------------
662 -- NAME:
663 -- import_sql_testcase
664 --
665 -- DESCRIPTION:
666 -- Import a SQL Test case into a schema from a directory and a file name
667 --
668 -- PARAMETERS:
669 -- directory (IN) - directory containing testcase files
670 -- filename (IN) - the name of a file containing an XML
671 -- document describing the SQL test case
672 -- importEnvironment (IN) - TRUE if the compilation environment
673 -- should be imported
674 -- importMetadata (IN) - TRUE if the definition of the objects
675 -- referenced in the SQL should be imported.
676 -- importData (IN) - TRUE if the data of the objects referenced
677 -- in the SQL should be imported.
678 -- importPkgbody (IN) - TRUE if the body of the packages referenced
679 -- in the SQL should be imported.
680 -- importDiagnosis (IN) - TRUE if the diagnostic information
681 -- associated to the task should be imported
682 -- ignoreStorage (IN) - TRUE if the storage attributes should be
683 -- ignored
684 -- ctrlOptions (IN) - opaque control parameters
685 -- preserveSchemaMapping
686 -- (IN) - TRUE if the schema(s) will NOT be re-mapped
687 -- from the original environment to the test
688 -- environment.
689 -----------------------------------------------------------------------------
690 PROCEDURE import_sql_testcase(
691 directory IN VARCHAR2,
692 filename IN VARCHAR2,
693 importEnvironment IN BOOLEAN := TRUE,
694 importMetadata IN BOOLEAN := TRUE,
695 importData IN BOOLEAN := TRUE,
696 importPkgbody IN BOOLEAN := FALSE,
697 importDiagnosis IN BOOLEAN := TRUE,
698 ignoreStorage IN BOOLEAN := TRUE,
699 ctrlOptions IN VARCHAR2 := NULL,
700 preserveSchemaMapping IN BOOLEAN := FALSE);
701
702 ------------------------ explain_sql_testcase -----------------------------
703 FUNCTION explain_sql_testcase(
704 sqlTestCase IN CLOB)
705 RETURN CLOB;
706
710 --
707 ----------------------------- incidentid_2_sql --------------------------
708 -- NAME:
709 -- incidentid_2_sql:
711 -- DESCRIPTION:
712 -- Initialize a sql_setrow from an incident ID.
713 -- Given a valid incident ID this function parses the trace file and
714 -- extract as much information as possible about the SQL that causes
715 -- the generation of this incident (SQL text, user name, binds, etc...).
716 --
717 -- PARAMETERS
718 -- incident_id (IN) - Identifier of the incident
719 -- sql_stmt (OUT) - the resulting SQL
720 -- problem_type (OUT) - tentative type of SQL problem (currently
721 -- among PROBLEM_TYPE_COMPILATION_ERROR and
722 -- PROBLEM_TYPE_EXECUTION_ERROR)
723 -- err_code (OUT) - error code if any otherwise it is set to
724 -- null
725 -- err_mesg (OUT) - error message if any otherwise it is set to
726 -- null
727 --
728 -- RETURN:
729 -- VOID
730 -----------------------------------------------------------------------------
731 PROCEDURE incidentid_2_sql(
732 incident_id IN VARCHAR2,
733 sql_stmt OUT SQLSET_ROW,
734 problem_type OUT NUMBER,
735 err_code OUT BINARY_INTEGER,
736 err_mesg OUT VARCHAR2);
737
738 ----------------------------- getSql --------------------------
739 -- NAME:
740 -- getsql:
741 --
742 -- DESCRIPTION:
743 -- load a sql_setrow from the trace file associated to an
744 -- the given incident ID.
745 --
746 -- PARAMETERS
747 -- incident_id (IN) - Identifier of the incident
748 --
749 -- RETURN:
750 -- a sqlset_row containing the SQL statement
751 -----------------------------------------------------------------------------
752 FUNCTION getsql(
753 incident_id IN VARCHAR2)
754 RETURN SQLSET_ROW;
755
756 ------------------------ set_tcb_tracing ------------------------------------
757 -- NAME:
758 -- set_tcb_tracing - enable/disable TCB tracing
759 --
760 -- DESCRIPTION:
761 -- This function enable/disble TCB tracing
762 -- (for Oracle Support/Development use only)
763 --
764 -- PARAMETERS:
765 -- status (IN) - status to set
766 -----------------------------------------------------------------------------
767 PROCEDURE set_tcb_tracing(status IN BOOLEAN := TRUE);
768
769 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
770 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
771 -- ------------------------------------- --
772 -- SQL DIAG ADVISOR PROCEDURES/FUNCTIONS --
773 -- ------------------------------------- --
774 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
775 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
776
777 ----------------------------- create_diagnosis_task -------------------------
778 FUNCTION create_diagnosis_task(
779 sql_text IN CLOB,
780 bind_list IN sql_binds := NULL,
781 user_name IN VARCHAR2 := NULL,
782 scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
783 time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
784 task_name IN VARCHAR2 := NULL,
785 description IN VARCHAR2 := NULL,
786 problem_type IN NUMBER := PROBLEM_TYPE_PERFORMANCE)
787 RETURN VARCHAR2;
788
789 ----------------------------- create_diagnosis_task -------------------------
790 FUNCTION create_diagnosis_task(
791 sql_id IN VARCHAR2,
792 plan_hash_value IN NUMBER := NULL,
793 scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
794 time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
795 task_name IN VARCHAR2 := NULL,
796 description IN VARCHAR2 := NULL,
797 problem_type IN NUMBER := PROBLEM_TYPE_PERFORMANCE)
798 RETURN VARCHAR2;
799
800 ----------------------------- create_diagnosis_task -------------------------
801 FUNCTION create_diagnosis_task(
802 sqlset_name IN VARCHAR2,
803 basic_filter IN VARCHAR2 := NULL,
804 object_filter IN VARCHAR2 := NULL,
805 rank1 IN VARCHAR2 := NULL,
806 rank2 IN VARCHAR2 := NULL,
807 rank3 IN VARCHAR2 := NULL,
808 result_percentage IN NUMBER := NULL,
809 result_limit IN NUMBER := NULL,
810 scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
811 time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
812 task_name IN VARCHAR2 := NULL,
813 description IN VARCHAR2 := NULL,
814 plan_filter IN VARCHAR2 := 'MAX_ELAPSED_TIME',
815 sqlset_owner IN VARCHAR2 := NULL,
816 problem_type IN NUMBER := PROBLEM_TYPE_PERFORMANCE)
817 RETURN VARCHAR2;
818
819 ----------------------------- drop_diagnosis_task ---------------------------
820 PROCEDURE drop_diagnosis_task(
821 task_name IN VARCHAR2);
822
823 ----------------------------- execute_diagnosis_task ------------------------
824 PROCEDURE execute_diagnosis_task(
825 task_name IN VARCHAR2);
826
830
827 ---------------------------- interrupt_diagnosis_task -----------------------
828 PROCEDURE interrupt_diagnosis_task(
829 task_name IN VARCHAR2);
831 ------------------------------ cancel_diagnosis_task ------------------------
832 PROCEDURE cancel_diagnosis_task(
833 task_name IN VARCHAR2);
834
835 ------------------------------ reset_diagnosis_task -------------------------
836 PROCEDURE reset_diagnosis_task(
837 task_name IN VARCHAR2);
838
839 ------------------------------ resume_diagnosis_task ------------------------
840 PROCEDURE resume_diagnosis_task(
841 task_name IN VARCHAR2);
842
843 ------------------------------- report_diagnosis_task -----------------------
844 FUNCTION report_diagnosis_task(
845 task_name IN VARCHAR2,
846 type IN VARCHAR2 := TYPE_TEXT,
847 level IN VARCHAR2 := LEVEL_TYPICAL,
848 section IN VARCHAR2 := SECTION_FINDINGS,
849 object_id IN NUMBER := NULL,
850 result_limit IN NUMBER := NULL,
851 owner_name IN VARCHAR2 := NULL)
852 RETURN CLOB;
853
854 -------------------------- set_diagnosis_task_parameter ---------------------
855 PROCEDURE set_diagnosis_task_parameter(
856 task_name IN VARCHAR2,
857 parameter IN VARCHAR2,
858 value IN NUMBER);
859
860
861 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
862 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
863 -- ------------------------------------------ --
864 -- SQL PATCH SUPPORT FUNCTIONS --
865 -- ------------------------------------------ --
866 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
867 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
868
869 -- NAME: accept_sql_patch - accept a sqldiag recommended SQL patch,
870 -- FUNCTION version
871 -- PURPOSE: This procedure accepts a SQL patch as recommended by the
872 -- specified SQL tuning task.
873 -- INPUTS: task_name - (REQUIRED) The name of the SQL tuning task.
874 -- object_id - The identifier of the advisor framework object
875 -- representing the SQL statement associated
876 -- to the tuning task.
877 -- name - This is the name of the patch. It cannot contain
878 -- double quotation marks. The name is case sensitive.
879 -- If not specified, the system will generate a unique
880 -- name for the SQL patch.
881 -- description - A user specified string describing the purpose
882 -- of this SQL patch. Max size of description is 500.
883 -- category - This is the category name which must match the
884 -- value of the SQLTUNE_CATEGORY parameter in a
885 -- session
886 -- for the session to use this patch. It defaults
887 -- to the value "DEFAULT". This is also the default
888 -- of the SQLTUNE_CATEGORY parameter. The category
889 -- must be a valid Oracle identifier. The category
890 -- name specified is always converted to upper case.
891 -- The combination of the normalized SQL text and
892 -- category name create a unique key for a patch.
893 -- An accept will fail if this combination is
894 -- duplicated.
895 -- task_owner - Owner of the tuning task. This is an optional
896 -- parameter that has to be specified to accept
897 -- a SQL Patch associated to a tuning task owned
898 -- by another user. The current user is the default
899 -- value.
900 -- replace - If the patch already exists, it will be
901 -- replaced if this argument is TRUE.
902 -- It is an error to pass a name that is already
903 -- being used for another signature/category pair,
904 -- even with replace set to TRUE.
905 -- force_match - If TRUE this causes SQL Patchs
906 -- to target all SQL statements which have the same
907 -- text after normalizing all literal values into
908 -- bind variables. (Note that if a combination of
909 -- literal values and bind values is used in a
910 -- SQL statement, no bind transformation occurs.)
911 -- This is analogous to the matching algorithm
912 -- used by the "FORCE" option of the
913 -- CURSOR_SHARING parameter. If FALSE, literals are
914 -- not transformed. This is analogous to the
915 -- matching algorithm used by the "EXACT" option of
916 -- the CURSOR_SHARING parameter.
917 -- RETURNS: name - The name of the SQL patch.
918 --
919 -- REQUIRES: "CREATE ANY SQL PATCH" privilege
920 --
921 FUNCTION accept_sql_patch(
922 task_name IN VARCHAR2,
926 category IN VARCHAR2 := NULL,
923 object_id IN NUMBER := NULL,
924 name IN VARCHAR2 := NULL,
925 description IN VARCHAR2 := NULL,
927 task_owner IN VARCHAR2 := NULL,
928 replace IN BOOLEAN := FALSE,
929 force_match IN BOOLEAN := FALSE)
930 RETURN VARCHAR2;
931
932 -- NAME: accept_sql_patch - accept a sqldiag recommended SQL patch,
933 -- PROCEDURE version
934 -- PURPOSE: This procedure accepts a SQL patch as recommended by the
935 -- specified SQL tuning task.
936 -- INPUTS: task_name - (REQUIRED) The name of the SQL tuning task.
937 -- object_id - Identifier of the advisor framework
938 -- object representing the SQL statement associated
939 -- to the tuning task.
940 -- name - This is the name of the patch. It
941 -- cannot contain double quotation marks. The name is
942 -- case sensitive.
943 -- description - A user specified string describing the purpose
944 -- of this SQL patch. Max size of description is 500.
945 -- category - This is the category name which must match the
946 -- value of the SQLTUNE_CATEGORY parameter in a
947 -- session
948 -- for the session to use this patch. It defaults
949 -- to the value "DEFAULT". This is also the default
950 -- of the SQLTUNE_CATEGORY parameter. The category
951 -- must be a valid Oracle identifier. The category
952 -- name specified is always converted to upper case.
953 -- The combination of the normalized SQL text and
954 -- category name create a unique key for a patch.
955 -- An accept will fail if this combination is
956 -- duplicated.
957 -- task_owner - Owner of the tuning task. This is an optional
958 -- parameter that has to be specified to accept
959 -- a SQL Patch associated to a tuning task owned
960 -- by another user. The current user is the default
961 -- value.
962 -- replace - If the patch already exists, it will be
963 -- replaced if this argument is TRUE.
964 -- It is an error to pass a name that is already
965 -- being used for another signature/category pair,
966 -- even with replace set to TRUE.
967 -- force_match - If TRUE this causes SQL Patchs
968 -- to target all SQL statements which have the same
969 -- text after normalizing all literal values into
970 -- bind variables. (Note that if a combination of
971 -- literal values and bind values is used in a
972 -- SQL statement, no bind transformation occurs.)
973 -- This is analogous to the matching algorithm
974 -- used by the "FORCE" option of the
975 -- CURSOR_SHARING parameter. If FALSE, literals are
976 -- not transformed. This is analogous to the
977 -- matching algorithm used by the "EXACT" option of
978 -- the CURSOR_SHARING parameter.
979 --
980 -- REQUIRES: "CREATE ANY SQL PATCH" privilege
981 --
982 PROCEDURE accept_sql_patch(
983 task_name IN VARCHAR2,
984 object_id IN NUMBER := NULL,
985 name IN VARCHAR2 := NULL,
986 description IN VARCHAR2 := NULL,
987 category IN VARCHAR2 := NULL,
988 task_owner IN VARCHAR2 := NULL,
989 replace IN BOOLEAN := FALSE,
990 force_match IN BOOLEAN := FALSE);
991
992 -- NAME: drop_sql_patch - drop a SQL patch
993 -- PURPOSE: This procedure drops the named SQL patch from the database.
994 -- INPUTS: name - (REQUIRED)Name of patch to be dropped. The name
995 -- is case sensitive.
996 -- ignore - Ignore errors due to object not existing.
997 -- REQUIRES: "DROP ANY SQL PATCH" privilege
998 --
999 PROCEDURE drop_sql_patch(
1000 name IN VARCHAR2,
1001 ignore IN BOOLEAN := FALSE);
1002
1003 -- NAME: alter_sql_patch - alter a SQL patch attribute
1004 -- PURPOSE: This procedure alters specific attributes of an existing
1005 -- SQL patch object. The following attributes can be altered
1006 -- (using these attribute names):
1007 -- "STATUS" -> can be set to "ENABLED" or "DISABLED"
1008 -- "NAME" -> can be reset to a valid name (must be
1009 -- a valid Oracle identifier and must be
1010 -- unique).
1011 -- "DESCRIPTION" -> can be set to any string of size no
1012 -- more than 500
1013 -- "CATEGORY" -> can be reset to a valid category name (must
1014 -- be valid Oracle identifier and must be unique
1015 -- when combined with normalized SQL text)
1016 -- INPUTS: name - (REQUIRED)Name of SQL patch to alter. The name
1017 -- is case sensitive.
1018 -- attribute_name - (REQUIRED)The attribute name to alter (case
1019 -- insensitive).
1020 -- See list above for valid attribute names.
1021 -- value - (REQUIRED)The new value of the attribute. See list
1022 -- above for valid attribute values.
1023 -- REQUIRES: "ALTER ANY SQL PATCH" privilege
1024 --
1025 PROCEDURE alter_sql_patch(
1026 name IN VARCHAR2,
1027 attribute_name IN VARCHAR2,
1028 value IN VARCHAR2);
1029
1030 -------------------------------- dump_trace ---------------------------------
1031 -- NAME:
1032 -- dump_trace - Dump Optimizer Trace
1033 --
1034 -- DESCRIPTION:
1035 -- This procedure dumps the optimizer or compiler trace for a give SQL
1036 -- statement identified by a SQL ID and an optional child number.
1037 --
1038 -- PARAMETERS:
1039 -- p_sql_id (IN) - identifier of the statement in the cursor
1040 -- cache
1041 -- p_child_number (IN) - child number
1042 -- p_component (IN) - component name
1043 -- Valid values are Optimizer and Compiler
1044 -- The default is Optimizer
1045 -- p_file_id (IN) - file identifier
1046 -----------------------------------------------------------------------------
1047 PROCEDURE dump_trace(
1048 p_sql_id IN varchar2,
1049 p_child_number IN number DEFAULT 0,
1050 p_component IN varchar2 DEFAULT 'Optimizer',
1051 p_file_id IN varchar2 DEFAULT null);
1052
1053 -------------------------------- get_fix_control ----------------------------
1054 -- NAME:
1055 -- get_fix_control - Get Fix Control
1056 --
1057 -- DESCRIPTION:
1058 -- This function returns the value of fix control for a given
1062 -- bug_number (IN) - bug number
1059 -- bug number.
1060 --
1061 -- PARAMETERS:
1063 -----------------------------------------------------------------------------
1064 FUNCTION get_fix_control(bug_number IN NUMBER)
1065 RETURN NUMBER;
1066
1067 ------------------------------- load_sqlset_from_tcb ------------------------
1068 -- NAME:
1069 -- load_sqlset_from_tcb - Load a SQLSET from Test Case Builder file
1070 --
1071 -- DESCRIPTION:
1072 -- This function loads a sqlset created from TCB sql object file and
1073 -- returns the loaded sqlset name.
1074 --
1075 -- The sqlset can later be used as input for SQL repair advisor etc.
1076 --
1077 -- NOTE:
1078 -- The TCB sql object file is usually named something like: xxxxsql.xml.
1079 -- It contains the sql_text, parsing_schema, optimizer environment etc
1080 -- from the original environment where the test case was created.
1081 --
1082 -- For example:
1083 -- ------------
1084 -- <SQL_OBJECT>
1085 -- <SQL_ID>6qanqm2xvq94u</SQL_ID>
1086 -- <SQL_TEXT>explain plan for
1087 -- select unit_cost, sold
1088 -- from costs c,
1089 -- ...
1090 -- where c.prod_id = v.prod_id
1091 -- </SQL_TEXT>
1092 -- <PARSING_SCHEMA_NAME>SH</PARSING_SCHEMA_NAME>
1093 -- <MODULE>SQL*Plus</MODULE>
1094 -- <OPTIMIZER_ENV> E289FB89E12 ... </OPTIMIZER_ENV>
1095 -- <PLAN_HASH_VALUE> ... </PLAN_HASH_VALUE>
1096 -- </SQL_OBJECT>
1097 --
1098 -- PARAMETERS:
1099 -- directory (IN) - directory containing testcase files
1100 -- filename (IN) - the name of a file containing the sql
1101 -- object
1102 -- sqlset_name (IN OUT) - a sqlset_row containing the SQL statement
1103 -----------------------------------------------------------------------------
1104 FUNCTION load_sqlset_from_tcb(
1105 directory IN VARCHAR2,
1106 filename IN VARCHAR2,
1107 sqlset_name IN VARCHAR2 DEFAULT NULL)
1108 RETURN VARCHAR2;
1109
1110
1111 ----------------------------- create_stgtab_sqlpatch ------------------------
1112 PROCEDURE create_stgtab_sqlpatch(
1113 table_name IN VARCHAR2,
1114 schema_name IN VARCHAR2 := NULL,
1115 tablespace_name IN VARCHAR2 := NULL);
1116
1117 ------------------------------ pack_stgtab_sqlpatch -------------------------
1118 PROCEDURE pack_stgtab_sqlpatch(
1119 patch_name IN VARCHAR2 := '%',
1120 patch_category IN VARCHAR2 := 'DEFAULT',
1121 staging_table_name IN VARCHAR2,
1122 staging_schema_owner IN VARCHAR2 := NULL);
1123
1124 ---------------------------- unpack_stgtab_sqlpatch -------------------------
1125 PROCEDURE unpack_stgtab_sqlpatch(
1126 patch_name IN VARCHAR2 := '%',
1127 patch_category IN VARCHAR2 := '%',
1128 replace IN BOOLEAN,
1129 staging_table_name IN VARCHAR2,
1130 staging_schema_owner IN VARCHAR2 := NULL);
1131
1132 END dbms_sqldiag;