8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 11g » Here

DBMS_SQLDIAG : Export and Import Test Cases

The DBMS_SQLDIAG package can be used to export and import SQL test cases.

Related articles.

Setup

We create two test users. Notice we grant the DBA role to the second user. We need that for the imports to run successfully.

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

drop user testuser1 cascade;
create user testuser1 identified by testuser1 quota unlimited on users;
grant connect, resource to testuser1;
grant select_catalog_role to testuser1;

drop user testuser2 cascade;
create user testuser2 identified by testuser2 quota unlimited on users;
grant connect, resource to testuser2;
grant select_catalog_role to testuser2;
grant dba to testuser2;

We need directory objects for the export and import operations. Make sure the directories exists on the operating system.

create or replace directory testcase1_dir as '/tmp/testcase1';
grant read, write on directory testcase1_dir to testuser1, testuser2;

create or replace directory testcase2_dir as '/tmp/testcase2';
grant read, write on directory testcase2_dir to testuser1, testuser2;

create or replace directory testcase3_dir as '/tmp/testcase3';
grant read, write on directory testcase3_dir to testuser1, testuser2;

We connect to the TESTUSER1 user and create the EMP table.

conn testuser1/testuser1@//localhost:1521/pdb1

create table emp (
  empno    number(4) constraint pk_emp primary key,
  ename    varchar2(10),
  job      varchar2(9),
  mgr      number(4),
  hiredate date,
  sal      number(7,2),
  comm     number(7,2),
  deptno   number(2) );

insert into emp values (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
insert into emp values (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
insert into emp values (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
insert into emp values (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
insert into emp values (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
insert into emp values (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
insert into emp values (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
insert into emp values (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
insert into emp values (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
insert into emp values (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
insert into emp values (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
commit;

exec dbms_stats.gather_table_stats(null, 'emp');

Export SQL Test Case

There are several procedures to export SQL test cases. The export contains all objects necessary to run the statement, which in this case is just the EMP table. By default the rows from any dependent tables are not exported. There are parameters to alter the contents of the export, but in these examples we'll keep things simple.

In this example we use the EXPORT_SQL_TESTCASE procedure to export a SQL test case for a specific SQL statement. In this case the SQL statement is hard coded, but it could be returned from the V$SQL view or AWR. There is a BIND_LIST parameter to supply bind variables if needed.

conn testuser1/testuser1@//localhost:1521/pdb1

variable testcase clob;

begin
  sys.dbms_sqldiag.export_sql_testcase (
    directory => 'TESTCASE1_DIR',
    sql_text  => 'select * from emp',
    testcase  => :testcase);
end;
/

If we check the directory on the database server we can see the files generated by the export.

[oracle@localhost testcase1]$ ls
oratcb_3_a2dk8bdn0ujx7_1_00A038C30001diag.trc   oratcb_3_a2dk8bdn0ujx7_1_00A038C30001ol.xml      oratcb_3_a2dk8bdn0ujx7_1_00A038C30001ts.xml
oratcb_3_a2dk8bdn0ujx7_1_00A038C30001dpexp.dmp  oratcb_3_a2dk8bdn0ujx7_1_00A038C30001prmimp.sql  oratcb_3_a2dk8bdn0ujx7_1_00A038C30001xplf.sql
oratcb_3_a2dk8bdn0ujx7_1_00A038C30001dpexp.log  oratcb_3_a2dk8bdn0ujx7_1_00A038C30001README.txt  oratcb_3_a2dk8bdn0ujx7_1_00A038C30001xplo.sql
oratcb_3_a2dk8bdn0ujx7_1_00A038C30001dpexp.sql  oratcb_3_a2dk8bdn0ujx7_1_00A038C30001smrpt.html  oratcb_3_a2dk8bdn0ujx7_1_00A038C30001xpls.sql
oratcb_3_a2dk8bdn0ujx7_1_00A038C30001dpimp.sql  oratcb_3_a2dk8bdn0ujx7_1_00A038C30001sql.xml
oratcb_3_a2dk8bdn0ujx7_1_00A038C30001main.xml   oratcb_3_a2dk8bdn0ujx7_1_00A038C30001ssimp.sql
[oracle@localhost testcase1]$

In the next example we use a different overload of the EXPORT_SQL_TESTCASE procedure to export a SQL test case for the SQL statement identified by the specified SQL_ID. First we need to get the SQL_ID.

select * from emp;

select sql_id from v$sql where sql_text = 'select * from emp';

SQL_ID
-------------
a2dk8bdn0ujx7

SQL>

Now we can export the SQL test case using the SQL_ID and a different directory. We will include the data with this SQL test case.

variable testcase clob;

begin
  sys.dbms_sqldiag.export_sql_testcase (
    directory  => 'TESTCASE2_DIR',
    sql_id     => 'a2dk8bdn0ujx7',
    exportdata => true,
    testcase   => :testcase);
end;
/

It is also possible to export a SQL test case based on an ADR incident. You can read more about ADR incidents here.

variable testcase clob;

begin
  sys.dbms_sqldiag.export_sql_testcase (
    directory   => 'TESTCASE3_DIR',
    incident_id => '71593',
    testcase    => :testcase);
end;
/

There are also the EXPORT_SQL_TESTCASE_DIR_BY_INC and EXPORT_SQL_TESTCASE_DIR_BY_TXT functions, which are variations of the procedures we've already seen.

In all cases, the resulting files can be zipped up and sent to Oracle Support, or another system for them to be loaded, so the problem can be recreated.

Import SQL Test Case

The IMPORT_SQL_TESTCASE procedure was added to the DBMS_SQLDIAG package in Oracle 11.2. Not surprisingly, it imports a previously exported SQL test case into a new schema. It has similar parameters to the EXPORT_SQL_TESTCASE procedures to control what information is imported, but the defaults import everything from the exported SQL test case.

We connect to the TESTUSER2 user and import the SQL test case using the "*mail.xml" file from the first directory, which doesn't include the table data.

conn testuser2/testuser2@//localhost:1521/pdb1

begin
  sys.dbms_sqldiag.import_sql_testcase (
    directory => 'TESTCASE1_DIR',
    filename  => 'oratcb_3_a2dk8bdn0ujx7_1_00A038C30001main.xml');
end;
/

The table has been imported, but it contains no data.

select * from emp;

no rows selected

SQL>

Despite the data not being present, the table statistics have been preserved, including the high/low values for the columns.

column table_name format a12

select table_name, num_rows, avg_row_len
from user_tables;

TABLE_NAME     NUM_ROWS AVG_ROW_LEN
------------ ---------- -----------
EMP                  14          38

SQL>


column column_name format a12
column high_value format a16
column low_value format a16

select table_name,
       column_name,
       case
         when column_name in ('EMPNO', 'MGR', 'SAL', 'COMM', 'DEPTNO') then
           to_char(utl_raw.cast_to_number(high_value))
         when column_name in ('ENAME', 'JOB') then
           utl_raw.cast_to_varchar2(high_value)
         else
           null
       end as high_value,
       case
         when column_name in ('EMPNO', 'MGR', 'SAL', 'COMM', 'DEPTNO') then
           to_char(utl_raw.cast_to_number(low_value))
         when column_name in ('ENAME', 'JOB') then
           utl_raw.cast_to_varchar2(low_value)
         else
           null
       end as low_value
from   user_tab_cols;

TABLE_NAME   COLUMN_NAME  HIGH_VALUE       LOW_VALUE
------------ ------------ ---------------- ----------------
EMP          EMPNO        7934             7369
EMP          ENAME        WARD             ADAMS
EMP          JOB          SALESMAN         ANALYST
EMP          MGR          7902             7566
EMP          HIREDATE
EMP          SAL          5000             800
EMP          COMM         1400             0
EMP          DEPTNO       30               10

8 rows selected.

SQL>

Drop the EMP table and load the SQL test case from the second directory, which includes the table data.

drop table emp purge;

begin
  sys.dbms_sqldiag.import_sql_testcase (
    directory => 'TESTCASE2_DIR',
    filename  => 'oratcb_3_a2dk8bdn0ujx7_2_0196BED80001main.xml');
end;
/

Now the EMP table has been imported into this schema along with its data.

select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL>

Considerations

Whenever we export any data from our system we need to think of the security implications. Passing SQL test cases to other companies could expose private data, or intellectual property owned by your company. As we've seen in the above example, even when we omit the table data, the statistics could expose personal information.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.