ORACLE

You can save results of your tests into an ORACLE server table.

Introduction

You can easily store results of your tests in a table in an ORACLE database:

ORACLE CAT output in a table

You can then easily create nice reports using your favorite reporting platform.

How to Instruct CAT to Save Test Results

CAT saves results to a database you have defined in Data Sources section of your project file. (If you load list of data sources from an external provider, that will also work.)

Data Sources:
- Name: AeroDWH
  Provider: Oracle@1
  ConnectionString: "%MY_DWH_CONNECTION_STRING%"
Tests:
- Name: .....   # abbreviated
Ouptut:
- Database: AeroDWH # name of an existing data source
  Table: AERO.CAT_TEST_RESULT

In this case, CAT will insert results of tests into the table of your choice. If the table does not exist, CAT will attempt to create it.

Second option is to use a package:

Ouptut:
- Database: AeroDWH # name of an existing data source
  Procedure: AERO.CAT_TESTS_MGMT

In this case, CAT will use a procedure SAVE_TEST_RESULT in the chosen package to save the results of tests into a table. Feel free to tweak the procedure to suite your needs. Name of the package is configurable (using the Procedure keyword), name of the procedure is fixed - it must be SAVE_TEST_RESULT.

If the package does not exist:

  • CAT will attempt to create the procedure (with the name you specified, AERO.CAT_TESTS_MGMT in the example above)
  • It will also create a table with name CAT_TEST_RESULT if it does not exist.

I Want to Create Table / Procedure Manually

Makes perfect sense. If CAT has only reading permissions on the database, which is highly recommended, it will not be able to create the table or procedure if it does not exist. Just run SSMS or other client tool you use and run this script to create the table:

CREATE TABLE CAT_TEST_RESULT
(
  TEST_RESULT_ID NUMBER GENERATED by default on null as IDENTITY PRIMARY KEY,
  EXECUTION_GUID VARCHAR2(36) NOT NULL,
  PROJECT NVARCHAR2(200) NOT NULL,
  FULL_NAME NVARCHAR2(700) NOT NULL,
  TEST_RESULT VARCHAR2(30) NOT NULL,
  STARTED_ON TIMESTAMP NOT NULL,
  FINISHED_ON TIMESTAMP NOT NULL,
  MESSAGE NCLOB NULL,
  SUITE NVARCHAR2(200) NULL,
  TEST_CASE NVARCHAR2(200) NULL,
  TEST_ORDER INT NULL,
  NAME NVARCHAR2(300) NULL,
  FIRST_DATA_SOURCE NVARCHAR2(200) NOT NULL,
  FIRST_QUERY NCLOB NOT NULL,
  SECOND_DATA_SOURCE NVARCHAR2(200) NULL,
  SECOND_QUERY NCLOB NULL,
  EXPECTATION VARCHAR2(50) NOT NULL,
  TIMEOUT INT NULL,
  MAXIMUM_ERRORS_LOGGED INT NULL,
  THREAD_NUMBER INT NOT NULL
)

Don’t forget CAT is highly configurable, the name of the table can be anything. Change it in your script, change it in the Outputs section of the project file.

The structure of the table is fixed. If you use Table: in the outputs configuration, CAT expects table exactly in that format. It may have additional nullable columns or columns with defaults though.

You might prefer a package/procedure - here is the script. Again, you can change name of the package (but not of the procedure) and name of the table it uses:

CREATE OR REPLACE PACKAGE CAT_TEST_MGMT AS
PROCEDURE SAVE_TEST_RESULT(
      c_EXECUTION_GUID VARCHAR2,
      c_PROJECT NVARCHAR2,
      c_FULL_NAME NVARCHAR2,
      c_TEST_RESULT VARCHAR2,
      c_STARTED_ON TIMESTAMP,
      c_FINISHED_ON TIMESTAMP,
      c_MESSAGE NCLOB,
      c_SUITE NVARCHAR2,
      c_TEST_CASE NVARCHAR2,
      c_TEST_ORDER INT,
      c_NAME NVARCHAR2,
      c_FIRST_DATA_SOURCE NVARCHAR2,
      c_FIRST_QUERY NCLOB,
      c_SECOND_DATA_SOURCE NVARCHAR2,
      c_SECOND_QUERY NCLOB,
      c_EXPECTATION VARCHAR2,
      c_TIMEOUT INT,
      c_MAXIMUM_ERRORS_LOGGED INT,
      c_THREAD_NUMBER INT
);
END

and the body:

CREATE OR REPLACE PACKAGE BODY CAT_TEST_MGMT AS
PROCEDURE SAVE_TEST_RESULT(
        c_EXECUTION_GUID VARCHAR2,
        c_PROJECT NVARCHAR2,
        c_FULL_NAME NVARCHAR2,
        c_TEST_RESULT VARCHAR2,
        c_STARTED_ON TIMESTAMP,
        c_FINISHED_ON TIMESTAMP,
        c_MESSAGE NCLOB,
        c_SUITE NVARCHAR2,
        c_TEST_CASE NVARCHAR2,
        c_TEST_ORDER INT,
        c_NAME NVARCHAR2,
        c_FIRST_DATA_SOURCE NVARCHAR2,
        c_FIRST_QUERY NCLOB,
        c_SECOND_DATA_SOURCE NVARCHAR2,
        c_SECOND_QUERY NCLOB,
        c_EXPECTATION VARCHAR2,
        c_TIMEOUT INT,
        c_MAXIMUM_ERRORS_LOGGED INT,
        c_THREAD_NUMBER INT
) IS
BEGIN
        INSERT INTO CAT_TEST_RESULT
        (EXECUTION_GUID, PROJECT, FULL_NAME, TEST_RESULT, STARTED_ON, FINISHED_ON,
        MESSAGE, SUITE, TEST_CASE, TEST_ORDER, NAME, FIRST_DATA_SOURCE, FIRST_QUERY, SECOND_DATA_SOURCE,
        SECOND_QUERY, EXPECTATION, TIMEOUT, MAXIMUM_ERRORS_LOGGED, THREAD_NUMBER)
        VALUES(c_EXECUTION_GUID, c_PROJECT, c_FULL_NAME, c_TEST_RESULT, c_STARTED_ON, c_FINISHED_ON,
        c_MESSAGE, c_SUITE, c_TEST_CASE, c_TEST_ORDER, c_NAME, c_FIRST_DATA_SOURCE, c_FIRST_QUERY, c_SECOND_DATA_SOURCE,
        c_SECOND_QUERY, c_EXPECTATION, c_TIMEOUT, c_MAXIMUM_ERRORS_LOGGED, c_THREAD_NUMBER);
END SAVE_TEST_RESULT;
END;

The name and the list of parameters of the procedure is fixed. CAT will call it with those. If you want to store only a subset of the information, adjust the table and the procedure body. The name of the package may be anything, just change it in the script and in the Outputs section in your project file.

How Does it Work?

Immediately after a test is executed, CAT attempts to either insert a row in the results table (if you configured with Table: tablename) or execute a procedure (if you configured Procedure: ProcedureName). CAT expects a fixed list of columns for the table. If some of them are missing, the INSERT command CAT runs will fail. The same for the stored procedure - CAT expects a fixed set of input parameters. But inside the procedure, you can already do whatever you want (and thus e.g., save ony subset of the information the procedure received via parameters).

This feature works even if you run tests in parallel - each thread keeps its own connection to the database for storing outputs.

← Troubleshooting
Oracle@1 →