How to generate a test case

The test case builder is part of the DBMS_SQLDIAG package. It can do a lot more than build test cases, the most basic functionality: how to generate the objects needed to run a SQL statement. Here’s a simple example, using the scott schema and a Windows server. There are numerous bad practices in the following, but it works. All from your SQL*Plus prompt.

Step 1: create a directory. The test case builder writes out a set of files to an Oracle directory. You need to create this, pointing to a suitable OS folder:

conn / as sysdba
host mkdir c:\tmp\diag
create directory diag as 'c:\tmp\diag';
grant all on directory diag to public;

Step 2: generate the test case files for your statement. This is one procedure call, by a user with suitable permissions:

conn / as sysdba
grant dba to scott;
conn scott/tiger
var tc clob
exec dbms_sqldiag.export_sql_testcase(directory=>'DIAG',sql_text=>'select * from emp natural join dept',testcase_name=>'mytest',testcase=>:tc)

Step 3: extract the DDL. From an OS prompt,

impdp scott/tiger directory=diag dumpfile=MYTESTDPEXP.DMP sqlfile=mytestddl.sql

Step 4: attach c:\tmp\diag\mytestddl.sql to your SR, or your topic on ORAfaq or the Oracle Forums.

This is merely a simple example, that will create the test schema with object statistics. Other options can include sample data, pl/sql source code, execution plans. You can read the docs to find out about all that:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sqldiag.htm#
—