How to generate your test case automatically

One often sees requests for test cases, typically when a user has a SQL performance issue. So many users appear to have problems with producing a suitable test case that Uncle Oracle provides routine to automate this. There are no longer any excuses for not providing a test case. Here is an intro to how to do it (code tested on and

The test case builder is part of the DBMS_SQLDIAG package. It can do a lot more than build test cases, which is what I'm going to write about here. I'll describe 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.

Step 5: see how everyone says "Gosh! what an amazingly clever person, to provide all the code necessary to create the perfect test case"

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:
John Watson
Oracle Certified Master DBA


Nice. I see myself returning to this page in the future. I also see moderators in the forum linking n00bs to this page. I further see those n00bs complaining that "conn / as sysdba" won't accept my password, or "The DBA won't let me have DBA role ... or CREATE DIRECTORY ... or PUBLIC access to a Directory". I recall you mentioned something about bad practices ... :-)

John, do you have a Dev-friendly version? What is the minimum privs a bog-standard Dev would need to ask their DBA for?