You can save results of your tests into an ORACLE server table.
You can easily store results of your tests in a table in an ORACLE database:
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
If the package does not exist:
- CAT will attempt to create the procedure (with the name you specified,
AERO.CAT_TESTS_MGMTin the example above)
- It will also create a table with name
CAT_TEST_RESULTif 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.