SQL Test Case Builder

SQL Test Case Builder capture the information for particular SQL related problem, along with the exact environment under which the problem occurred, so that the problem can be reproduced and tested on a separate Oracle database instance. Once the test case is ready you can upload the problem to Oracle Support to enable support personnel to reproduce and troubleshoot the problem.


select sql_id from v$sql where PARSING_SCHEMA_NAME='SCOTT';

SQL_ID
-------------
g4y6nw3tts7cc
dyk4dprp70d74 <<<----- suppose this a problematic
3j6nhsu0j4xyu
37pkxuzm9xccp
767pug2dbpqpc
5zpttftp1y265
d6vwqbw6r2ffk
cw6vxf0kbz3v1
7hys3h7ysgf9m
b43dczn8urtpp
8gs1427tszy68

11 rows selected.



SQL> CREATE DIRECTORY sql_test_dir AS '/home/oracle/testcase';

Directory created.



from sys


DECLARE
as clob;
BEGIN
DBMS_SQLDIAG.EXPORT_SQL_TESTCASE(
DIRECTORY=>'SQL_TEST_DIR',
SQL_ID=>'dyk4dprp70d74',
TESTCASE=>as);
END;




oracle@apt-amd-02:~> cd /home/oracle/testcase

oracle@apt-amd-02:~/testcase> ls -ltr
total 208
-rw-r--r-- 1 oracle oinstall 2316 2011-10-06 08:14 README.txt
-rw-r--r-- 1 oracle oinstall 1170 2011-10-06 08:14 oratcb1_0032017B0001sql.xml
-rw-r--r-- 1 oracle oinstall 411 2011-10-06 08:14 oratcb1_0032017B0001ol.xml
-rw-r--r-- 1 oracle oinstall 4945 2011-10-06 08:14 oratcb1_0032017B0001dpexp.sql
-rw-r----- 1 oracle oinstall 155648 2011-10-06 08:17 oratcb1_0032017B0001dpexp.dmp
-rw-r--r-- 1 oracle oinstall 522 2011-10-06 08:17 oratcb1_0032017B0001dpexp.log
-rw-r--r-- 1 oracle oinstall 417 2011-10-06 08:17 oratcb1_0032017B0001xpls.sql
-rw-r--r-- 1 oracle oinstall 637 2011-10-06 08:17 oratcb1_0032017B0001xplo.sql
-rw-r--r-- 1 oracle oinstall 440 2011-10-06 08:17 oratcb1_0032017B0001xplf.sql
-rw-r--r-- 1 oracle oinstall 388 2011-10-06 08:17 oratcb1_0032017B0001ssimp.sql
-rw-r--r-- 1 oracle oinstall 4008 2011-10-06 08:17 oratcb1_0032017B0001dpimp.sql
-rw-r--r-- 1 oracle oinstall 1776 2011-10-06 08:17 oratcb1_0032017B0001xpl.txt
-rw-r--r-- 1 oracle oinstall 1764 2011-10-06 08:17 oratcb1_0032017B0001main.xml
==========


DECLARE
V_SQL CLOB := 'select * from scott.emp where sal between 100 and 3000';
V_ANUJTESTCASE CLOB;

BEGIN
DBMS_SQLDIAG.EXPORT_SQL_TESTCASE (
DIRECTORY => 'SQL_TEST_DIR',
SQL_TEXT => V_SQL,
USER_NAME => 'SCOTT',
BIND_LIST => NULL,
-- EXPORTENVIRONMENT => TRUE,
-- EXPORTMETADATA => TRUE,
-- EXPORTDATA => TRUE,
-- SAMPLINGPERCENT => 100,
-- CTRLOPTIONS => NULL,
-- TIMELIMIT => 0,
TESTCASE_NAME => 'RETURN_NAMES', -----<<<<<<<<< generated scripts prefix
TESTCASE => V_ANUJTESTCASE );
END;

PL/SQL procedure successfully completed.


-rw-r--r-- 1 oracle oinstall 2316 2011-10-06 09:24 README.txt
-rw-r--r-- 1 oracle oinstall 208 2011-10-06 09:24 RETURN_NAMESsql.xml
-rw-r--r-- 1 oracle oinstall 444 2011-10-06 09:24 RETURN_NAMESol.xml
-rw-r--r-- 1 oracle oinstall 4718 2011-10-06 09:24 RETURN_NAMESdpexp.sql
-rw-r--r-- 1 oracle oinstall 1047 2011-10-06 09:26 RETURN_NAMESdpexp.log
-rw-r----- 1 oracle oinstall 200704 2011-10-06 09:26 RETURN_NAMESdpexp.dmp
-rw-r--r-- 1 oracle oinstall 431 2011-10-06 09:26 RETURN_NAMESxpls.sql
-rw-r--r-- 1 oracle oinstall 688 2011-10-06 09:26 RETURN_NAMESxplo.sql
-rw-r--r-- 1 oracle oinstall 454 2011-10-06 09:26 RETURN_NAMESxplf.sql
-rw-r--r-- 1 oracle oinstall 388 2011-10-06 09:26 RETURN_NAMESssimp.sql
-rw-r--r-- 1 oracle oinstall 3770 2011-10-06 09:26 RETURN_NAMESdpimp.sql
-rw-r--r-- 1 oracle oinstall 2444 2011-10-06 09:26 RETURN_NAMESxpl.txt
-rw-r--r-- 1 oracle oinstall 1668 2011-10-06 09:26 RETURN_NAMESmain.xml


More example


SQL> create user vihaan identified by vihaan123 default tablespace users;

User created.


SQL> grant connect, resource, plustrace to vihaan ;

Grant succeeded.



connect vihaan/vihaan123

create table vihaan (id number,
text varchar2(100)
);


insert into vihaan values (1, rpad('X',100,'X'));

commit;

SQL> exec dbms_stats.gather_table_stats(user, 'VIHAAN');

PL/SQL procedure successfully completed.


SQL> select /* DEMO_TEST */ count(*) from vihaan where vihaan.id=1;

COUNT(*)
----------
1



1. Create directory to hold the SQL test case files.


sqlplus> !mkdir -p /home/oracle/testcase

connect / as sysdba

CREATE DIRECTORY sql_test_dir AS '/home/oracle/testcase';

Directory created.

SQL> connect / as sysdba
Connected.
SQL> grant read, write on directory sql_test_dir to vihaan ;

Grant succeeded.



connect vihaan/vihaan123

var tc1 clob

declare
v_sqltext clob;
begin
v_sqltext:='select count(*) from vihaan where vihaan.id=1';
dbms_sqldiag.export_sql_testcase(
directory => 'SQL_TEST_DIR',
sql_text => v_sqltext,
exportenvironment => true,
exportmetadata => true,
exportdata => true,
samplingpercent => 100,
testcase_name => 'tc1',
testcase => :tc1);
end;
/

PL/SQL procedure successfully completed.





set long 10000
set longchunksize 10000
set pages 1000
set lines 1000
print tc1


TC1
-----------------------------------------------------------------------








]>


tc1
1
0
TRUE
TRUE
TRUE
11.2.0.1.0

tc1ol.xmlXML
OBJECT_LIST


tc1sql.xmlXML
INFORMATION


tc1dpexp.sqlSQL
EXPORT_SCRIPT


tc1dpimp.sqlSQL
EXPORT_DATA


tc1dpexp.dmpDATAPUMP
EXPORT_DATA


tc1ssimp.sqlSQL
EXPORT_ENVIRONMENT


tc1xpls.sqlSQL
INFORMATION


tc1xplo.sqlSQL
INFORMATION


tc1xplf.sqlSQL
INFORMATION


tc1xpl.txtSQL
INFORMATION


tc1main.xmlXML
INFORMATION






oracle@apt-amd-02:/opt/app/oracle/diag/rdbms/orcl/orcl/trace> ls -ltr /home/oracle/testcase

-rw-r--r-- 1 oracle oinstall 200 2011-10-06 10:39 tc1sql.xml
-rw-r--r-- 1 oracle oinstall 4548 2011-10-06 10:39 tc1dpexp.sql
-rw-r--r-- 1 oracle oinstall 823 2011-10-06 10:41 tc1dpexp.log
-rw-r----- 1 oracle oinstall 159744 2011-10-06 10:41 tc1dpexp.dmp
-rw-r--r-- 1 oracle oinstall 422 2011-10-06 10:41 tc1xpls.sql
-rw-r--r-- 1 oracle oinstall 682 2011-10-06 10:41 tc1xplo.sql
-rw-r--r-- 1 oracle oinstall 445 2011-10-06 10:41 tc1xplf.sql
-rw-r--r-- 1 oracle oinstall 388 2011-10-06 10:41 tc1ssimp.sql
-rw-r--r-- 1 oracle oinstall 3753 2011-10-06 10:41 tc1dpimp.sql
-rw-r--r-- 1 oracle oinstall 2143 2011-10-06 10:41 tc1xpl.txt
-rw-r--r-- 1 oracle oinstall 1559 2011-10-06 10:41 tc1main.xml


============================



select sql_id, plan_hash_value, sql_fulltext
from v$sql
where regexp_like(sql_fulltext, 'DEMO_TEST');

SQL_ID PLAN_HASH_VALUE SQL_FULLTEXT
------------- --------------- --------------------------------------------------------------------------------
gzh3yszy2pb47 4100046689 select /* DEMO_TEST */ count(*) from vihaan where vihaan.id=1
g24qk1vzzqr7n 903671040 select sql_id, plan_hash_value, sql_fulltext
from v$sql
where regexp_like(sql_fu




var tc2 clob

begin
dbms_sqldiag.export_sql_testcase(
directory => 'SQL_TEST_DIR',
sql_id => '&&Anuj_sqlid',
plan_hash_value => &&Anuj_hash,
exportdata => true,
testcase_name => 'tc2',
testcase => :tc2);
end;
/