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.

Output Test Results into a Table

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 any ORACLE client tool you use and run this script to create the table:

CREATE TABLE AERO.CAT_TEST_RESULT
(
    TEST_RESULT_ID NUMBER GENERATED by default on null as IDENTITY PRIMARY KEY,
    EXECUTION_GUID CHAR(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,
    RAW_MESSAGE NCLOB NULL,
    EXCEPTION NCLOB NULL,
    DESCRIPTION NVARCHAR2(2000) 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,
    KEY NVARCHAR2(200) NULL,
    METADATA NVARCHAR2(300) NULL,
    TAGS NVARCHAR2(1000) NULL,
    SUITE NVARCHAR2(200) NULL,
    TEST_CASE NVARCHAR2(200) NULL,
    TEST_ORDER INT NULL,
    NAME NVARCHAR2(300) NULL,
    TIMEOUT INT NULL,
    MAXIMUM_ERRORS_LOGGED INT NULL,
    THREAD_NUMBER INT NOT NULL,
    MESSAGE NCLOB NULL,
    NUMBER_OF_ERRORS INT NULL,
    LOG_NUMBER_OF_ERRORS INT
);

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. CAT process must have INSERT permission on the table.

The structure of the table is not fixed. CAT checks the structure of the table you provided in the project file and adapts to it. That means - if you are not interested into some columns, just drop them. Do you want only three columns, e.g. FullName, TestResult and StartedOn? No problem, CAT will happily save the results into your table with those three columns only. See the complete list of properties supported on the output.

You can add additional custom columns to the table, but they must be either nullable or must have defined a default value. To avoid conflict with any new future columns, use some prefix or suffix for your column names (name of your company, underscore, …).

Use Package / Stored Procedure Instead

You might prefer a package / procedure.It allows you to perform additional actions, such as enrich your own logs, run other processes when a specific test(s) fail, …

Here is the script. Again, you can change name of the package (but NOT name of the stored procedure!), name of the table it uses, omit some parameters you don’t need, use your naming conventions, …

CREATE OR REPLACE PACKAGE CAT_TEST_MGMT AS
PROCEDURE SAVE_TEST_RESULT(
        C_EXECUTION_GUID CHAR,
        C_PROJECT NVARCHAR2,
        C_FULL_NAME NVARCHAR2,
        C_TEST_RESULT VARCHAR2,
        C_STARTED_ON TIMESTAMP,
        C_FINISHED_ON TIMESTAMP,
        C_RAW_MESSAGE NCLOB,
        C_EXCEPTION NCLOB,
        C_DESCRIPTION NVARCHAR2,
        C_FIRST_DATA_SOURCE NVARCHAR2,
        C_FIRST_QUERY NCLOB,
        C_SECOND_DATA_SOURCE NVARCHAR2,
        C_SECOND_QUERY NCLOB,
        C_EXPECTATION VARCHAR2,
        C_KEY NVARCHAR2,
        C_METADATA NVARCHAR2,
        C_TAGS NVARCHAR2,
        C_SUITE NVARCHAR2,
        C_TEST_CASE NVARCHAR2,
        C_TEST_ORDER INT,
        C_NAME NVARCHAR2,
        C_TIMEOUT INT,
        C_MAXIMUM_ERRORS_LOGGED INT,
        C_THREAD_NUMBER INT,
        C_MESSAGE NCLOB,
        C_NUMBER_OF_ERRORS INT,
        C_LOG_NUMBER_OF_ERRORS INT
);
END;

and the body:

CREATE OR REPLACE PACKAGE BODY CAT_TEST_MGMT AS
PROCEDURE SAVE_TEST_RESULT(
    C_EXECUTION_GUID CHAR,
    C_PROJECT NVARCHAR2,
    C_FULL_NAME NVARCHAR2,
    C_TEST_RESULT VARCHAR2,
    C_STARTED_ON TIMESTAMP,
    C_FINISHED_ON TIMESTAMP,
    C_RAW_MESSAGE NCLOB,
    C_EXCEPTION NCLOB,
    C_DESCRIPTION NVARCHAR2,
    C_FIRST_DATA_SOURCE NVARCHAR2,
    C_FIRST_QUERY NCLOB,
    C_SECOND_DATA_SOURCE NVARCHAR2,
    C_SECOND_QUERY NCLOB,
    C_EXPECTATION VARCHAR2,
    C_KEY NVARCHAR2,
    C_METADATA NVARCHAR2,
    C_TAGS NVARCHAR2,
    C_SUITE NVARCHAR2,
    C_TEST_CASE NVARCHAR2,
    C_TEST_ORDER INT,
    C_NAME NVARCHAR2,
    C_TIMEOUT INT,
    C_MAXIMUM_ERRORS_LOGGED INT,
    C_THREAD_NUMBER INT,
    C_MESSAGE NCLOB,
    C_NUMBER_OF_ERRORS INT,
    C_LOG_NUMBER_OF_ERRORS INT
) IS
BEGIN
        INSERT INTO CAT_TEST_RESULT
        (
            EXECUTION_GUID, PROJECT, FULL_NAME, TEST_RESULT, STARTED_ON, FINISHED_ON,
            RAW_MESSAGE, EXCEPTION, DESCRIPTION, FIRST_DATA_SOURCE, FIRST_QUERY,
            SECOND_DATA_SOURCE, SECOND_QUERY, EXPECTATION, KEY, METADATA, TAGS,
            SUITE, TEST_CASE, TEST_ORDER, NAME, TIMEOUT, MAXIMUM_ERRORS_LOGGED,
            THREAD_NUMBER, MESSAGE, NUMBER_OF_ERRORS, LOG_NUMBER_OF_ERRORS
        )
        VALUES(
            C_EXECUTION_GUID, C_PROJECT, C_FULL_NAME, C_TEST_RESULT, C_STARTED_ON, C_FINISHED_ON,
            C_RAW_MESSAGE, C_EXCEPTION, C_DESCRIPTION, C_FIRST_DATA_SOURCE, C_FIRST_QUERY,
            C_SECOND_DATA_SOURCE, C_SECOND_QUERY, C_EXPECTATION, C_KEY, C_METADATA, C_TAGS,
            C_SUITE, C_TEST_CASE, C_TEST_ORDER, C_NAME, C_TIMEOUT, C_MAXIMUM_ERRORS_LOGGED,
            C_THREAD_NUMBER, C_MESSAGE, C_NUMBER_OF_ERRORS, C_LOG_NUMBER_OF_ERRORS
        );
END SAVE_TEST_RESULT;
END;

The list of parameters of the procedure is NOT fixed. CAT again checks the metadata of the procedure before it executes tests. CAT will call the procedure with only the parameters you defined.

How Does it Work?

Before CAT starts executing tests, it “prepares” a command for saving test results - one for each thread. It checks the metadata of table/procedure you specified in the project file and prepares INSERT command or command for running the package/procedure. CAT uses only columns/parameters that are needed. If the table or procedure does not exist, CAT will attempt to create it (and will fail if it does not have permissions).

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).

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 →