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 18.104.22.168 and 22.214.171.124).
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:
Oracle Certified Master DBA