SQL Test Case Builder generate test case automatically(自动生成SQL所需测试数据)

Why SQL Test Case Builder?
For most SQL problems, the single most important factor for a speedy bug resolution is to obtain a reproducible test case. However, this is normally the longest and most painful step for customers. The goal of the SQL Test Case Builder (TCB) is to automatically gather as much information as possible related to a SQL incident (problem) and package it in a way that allows a developer or a support engineer to reproduce the problem on his or her own machine quickly.
At a very high-level, SQL Test Case Builder can be seen as a way to export a SQL. Currently, Oracle export (expdp) takes a schema or a set of tables and exports all the dependents objects. SQL Test Case Builder provides the same service but takes a SQL statement as input.
How do I use the SQL Test Case Builder?
The task of creating a SQL test case can be performed in two ways:
From EM (Enterprise Manager), where TCB is invoked on user-demand via IPS (Incident Packaging Service) after a SQL incident occurred. The user can also manually create an incident for a problem query for building test case purpose.
From SQLPLUS, where you can directly invoke one of the PL/SQL API functions in the SQL Diagnostic package. We will give examples of using the APIs below.
Take a example

create table test as select rownum id,'x'||rownum name from dual connect by level<=1000; create table test_stat(max_id int,utime date); create or replace function get_maxid return number is l_m number; begin select nvl(max(id),0) into l_m from test; return l_m; end; create or replace procedure p_gather is begin insert into test_stat values(get_maxid(),sysdate); commit; end; / anbob@ORA11204>exec p_gather;
PL/SQL procedure successfully completed.
anbob@ORA11204>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
anbob@ORA11204>select * from test_stat;
MAX_ID UTIME
-------------------- -------------------
1000 2013-10-15 13:43:53
anbob@ORA11204>set role dba;
Role set.
anbob@ORA11204>create directory test_res as '/backup/test_res';
Directory created.
anbob@ORA11204>select sql_id,substr(sql_text,1,30) subtext from v$sqlarea where sql_text like '%p_gather%';
SQL_ID        SUBTEXT
------------- ------------------------------------------------------------------------------------------------------------------------
7z0591kuksa9m select sql_id,substr(sql_text,
62y74k9sk37z2 BEGIN p_gather; END;
anbob@ORA11204>var vcase clob;
anbob@ORA11204>exec dbms_sqldiag.export_sql_testcase(directory=>'TEST_RES', sql_id=>'62y74k9sk37z2',testcase_name=>'p_gather test',testcase=>:vcase);
BEGIN dbms_sqldiag.export_sql_testcase(directory=>'TEST_RES', sql_id=>'62y74k9sk37z2',testcase_name=>'p_gather test',testcase=>:vcase); END;
*
ERROR at line 1:
ORA-44003: invalid SQL name
ORA-06512: at "SYS.DBMS_SQLDIAG", line 520
ORA-06512: at line 1
Notice:
testcase_name  spaces are not allowed.
anbob@ORA11204>exec dbms_sqldiag.export_sql_testcase(directory=>'TEST_RES', sql_id=>'62y74k9sk37z2',testcase_name=>'p_gather_test',testcase=>:vcase);
BEGIN dbms_sqldiag.export_sql_testcase(directory=>'TEST_RES', sql_id=>'62y74k9sk37z2',testcase_name=>'p_gather_test',testcase=>:vcase); END;
*
ERROR at line 1:
ORA-20000: SQL testcase generation failed with error code
16953:
ORA-16953: Type of SQL statement not supported
ORA-06512: at "SYS.DBMS_SQLDIAG", line 520
ORA-06512: at line 1
anbob@ORA11204>update test_stat set max_id=get_maxid,utime=sysdate;
anbob@ORA11204>select sql_id,substr(sql_text,1,30) subtext from v$sqlarea where sql_text like '%get_maxid%';
SQL_ID        SUBTEXT
------------- -----------------------------------------------
8bt1x18dkhat1 update test_stat set max_id=ge
...
anbob@ORA11204>var vcase clob
anbob@ORA11204>exec dbms_sqldiag.export_sql_testcase(directory=>'TEST_RES', sql_id=>'8bt1x18dkhat1',testcase_name=>'gather_test',testcase=>:vcase);
PL/SQL procedure successfully completed.

[oracle@db231 ~]$ cd /backup/test_res/
[oracle@db231 test_res]$ ll
total 348
-rw-r----- 1 oracle oinstall 184320 Oct 15 15:27 gather_testdpexp.dmp
-rw-r----- 1 oracle oinstall 807 Oct 15 15:27 gather_testdpexp.log
-rw-r--r-- 1 oracle oinstall 4839 Oct 15 15:27 gather_testdpexp.sql
-rw-r--r-- 1 oracle oinstall 4187 Oct 15 15:27 gather_testdpimp.sql
-rw-r--r-- 1 oracle oinstall 1791 Oct 15 15:27 gather_testmain.xml
-rw-r--r-- 1 oracle oinstall 249 Oct 15 15:27 gather_testol.xml
-rw-r--r-- 1 oracle oinstall 2768 Oct 15 15:27 gather_testREADME.txt
-rw-r--r-- 1 oracle oinstall 1172 Oct 15 15:27 gather_testsql.xml
-rw-r--r-- 1 oracle oinstall 847 Oct 15 15:27 gather_testssimp.sql
-rw-r--r-- 1 oracle oinstall 100553 Oct 15 15:27 gather_test.trc
-rw-r--r-- 1 oracle oinstall 45 Oct 15 15:27 gather_testts.xml
-rw-r--r-- 1 oracle oinstall 458 Oct 15 15:27 gather_testxplf.sql
-rw-r--r-- 1 oracle oinstall 719 Oct 15 15:27 gather_testxplo.sql
-rw-r--r-- 1 oracle oinstall 432 Oct 15 15:27 gather_testxpls.sql
-rw-r--r-- 1 oracle oinstall 2663 Oct 15 15:27 gather_testxpl.txt
gather_testxpl.txt -- execution plans
gather_test.trc -- trace file as 10053 trace
Extract the DDL from gather_testdpexp.dmp
[oracle@db231 test_res]$ impdp system/oracle directory=TEST_RES dumpfile=p_gather_testdpexp.dmp sqlfile= p_gather.ddl.sql
Tip:
you can copy all the files under the export directory to your test environment

anbob@ORA11204>conn weejar
Enter password:
Connected.
weejar@ORA11204>exec dbms_sqldiag.import_sql_testcase(directory=>'TEST_RES', filename=>'gather_testmain.xml');
PL/SQL procedure successfully completed.
tip:
filename is  <testcase_name>main.xml .
weejar@ORA11204>select * from tab;
TNAME                          TABTYPE            CLUSTERID
------------------------------ ------- --------------------
TEST_STAT                      TABLE
weejar@ORA11204>select object_name,object_type from user_objects;
OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
TEST_STAT                      TABLE
GET_MAXID                      FUNCTION

Note:
function get_maxid used table TEST does not cascade export, you can to specify query statement sql_text the simplest form would be something like:

sys@ORA11204>exec dbms_sqldiag.export_sql_testcase(directory=>'TEST_RES',
sql_text=>'select .....',
testcase_name=>'gather_test',
testcase=>:vcase);

references http://www.orafaq.com/node/2660