--- title: "Oracle" description: "Results into an Oracle table or through a package procedure — scripts and specifics" url: "https://docs.justcat.it/reference/outputs/oracle/" --- # Oracle ## When to use it When results should be kept across runs and reported on — see [Database outputs](https://docs.justcat.it/reference/outputs/database-outputs/ "Database outputs") for what every database output does: one row per test as it finishes, columns matched by name, objects created when CAT may. This page has what is specific to Oracle: the scripts and the procedure form. The target is an `Oracle@1` data source. ## How to set it **Properties** **Data sources** Name : AeroDWH Provider : Oracle@1 Connection string : %AERO_CONNECTION_STRING% **Output** Database : AeroDWH Table : AERO.CAT_TEST_RESULT **YAML** ```yaml Data sources: - Name: AeroDWH Provider: Oracle@1 Connection string: "%AERO_CONNECTION_STRING%" Output: - Database: AeroDWH Table: AERO.CAT_TEST_RESULT ``` or, calling a procedure for every result. On Oracle `Procedure` names the **package**; the procedure inside it must be called `SAVE_TEST_RESULT` — that name is fixed, the package name is yours: **Properties** Database : AeroDWH Procedure : AERO.CAT_TEST_MGMT **YAML** ```yaml Output: - Database: AeroDWH Procedure: AERO.CAT_TEST_MGMT ``` The account needs `INSERT` on the table, or `EXECUTE` on the package. If the object does not exist and the account may create it, CAT creates it — the package together with a `CAT_TEST_RESULT` table in the package's schema. ## What it looks like One row per test, the columns of your table — see the [rows of the sample run](https://docs.justcat.it/reference/outputs/database-outputs/#what-it-looks-like "What it looks like") on Database outputs. ## Details ### Table Any subset of these columns works; custom columns must be nullable or have a default. ```sql 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 ); ``` ### Package Parameters matched by name with the prefix `C_`, any subset — CAT reads the procedure's parameters before the run and passes only those. The specification: ```sql 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: ```sql 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; ``` ### Specifics * Each thread of the run holds its own connection; results are written as the tests finish. * `EXECUTION_GUID` is a `CHAR(36)` text of the GUID; `STARTED_ON` and `FINISHED_ON` are `TIMESTAMP`, UTC; long texts are `NCLOB`. * The output has not been exercised on Linux; the same ODP.NET Core driver is used, so it is expected to work. ## Related * [Database outputs](https://docs.justcat.it/reference/outputs/database-outputs/ "Database outputs") — the shared mechanics. * [Properties](https://docs.justcat.it/reference/outputs/properties/ "Properties") — the column names CAT recognizes. * [Oracle@1](https://docs.justcat.it/reference/data-sources/providers/oracle-1/ "Oracle@1") — the data source.