Author: David Ray

Page: 1 2 3

Oracle Diagnostic Tools: SQL Test Case Builder

In this article we will discuss a powerful Oracle diagnostic tool called SQL Test Case Builder . It is provided by Oracle database 11g but is also back ported to Oracle 10.2.0.4.

SQL Test Case Builder is a powerful tool for producing a reproducible test case. It enables you to package up a complete test case including query, DDL, statistics, plan, etc so that you can reproduce the environment can be reproduced and debug the problem statement on another system.

Reproducible Test Case:

Obtaining a reproducible test case is amongst the most cumbersome task for effective bug resolution. Oracle SQL Test Case Builder ( TCB ) enables you to automatically gather as much information as possible and package. This allows a developer or a support engineer to reproduce the problem on his or her own machine quickly. You can also use SQL Test Case Builder to export SQL taking SQL statement as input.

SQL Test Case Builder: Input

The SQL Test Case Builder takes SQL object as an input. The SQL object will comprise of SQL text as well as the information required to compile it on a particular database instance.

SQL Test Case Builder: Test Case

As an Oracle diagnosis tool, the TCB compiles the problem SQL in a special capture mode to obtain the set of objects to export. The test case in a Test Case Builder captures below two types of information.

SQL Test Case Builder: Creating Test Case

The SQL object will be reloaded and all diagnostic information will be generated and gathered automatically for creating a SQL test case. This information will be made available to Oracle support and developers. You can create SQL test case in two ways.

1. Enterprise Manager:

In enterprise manager, the TCB is invoked on user-demand via Incident Packaging Service after a SQL incident occurred. You can also manually create an incident for a problem query for building test case purpose.

2. SQLPLUS :

In SQL PLUS you can directly invoke one of the PL/SQL API functions in the SQL Diagnostic package.

DBMS_SQLDIAG Package:

The dbms_sqldiag package contains all the new PL/SQL procedures that support SQL Test Case Builder. The dbms_sqldiag.import_sql_testcase procedure can be used to import a test case from a given directory. The dbms_sqldiag.export_sql_testcase procedure can be used to export a SQL test case for a given SQL statement to a given directory. Continued...

Page: 1 2 3

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Great Tips for Oracle Cancel-Based Recovery!!
   DBA Tips for Triggers in Oracle 11g!
   Inside Oracle Temporary Tables!!
   Oracle Security Guide: Beware of all DBAs having full OS access!!