Automated-Testing-For-Oracle-Functions

Create your own custom testing framework using PL/SQL and SQL scripts.

1. Structure of Custom Testing Framework

Create a testing framework by writing your own test scripts to validate the functions. The main elements of this testing framework would be:

1. Test cases: Written in PL/SQL, containing inputs and expected outputs.
2. Test execution: A script that calls the function with specific inputs and checks if the result matches the expected output.
3. Logging: Store test results (pass/fail) in a logging table.
4. Reporting: Output test results to the console or store them in a table for further analysis.

2. Creating a Test Procedure for Oracle Functions

Step-by-step guide for setting up your own test procedure without utPLSQL.

Step 1: Create a Test Log Table

Create a table to log the results of each test case. This allows you to review which tests passed or failed.

CREATE TABLE test_results (
    test_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    test_name VARCHAR2(100),
    expected_output VARCHAR2(100),
    actual_output VARCHAR2(100),
    test_status VARCHAR2(10),
    run_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP
);

Step 2: Create a Test Case Procedure

For each function you want to test, write a procedure that compares the function’s actual output with the expected result.

Here’s a sample test case for a function my_function that multiplies an input value by 2.

CREATE OR REPLACE PROCEDURE test_my_function(input_value NUMBER, expected_output NUMBER) IS
   actual_output NUMBER;
   test_status   VARCHAR2(10);
BEGIN
   -- Call the function you want to test
   actual_output := my_function(input_value);

   -- Check if the actual output matches the expected output
   IF actual_output = expected_output THEN
      test_status := 'PASS';
   ELSE
      test_status := 'FAIL';
   END IF;

   -- Insert the test result into the test_results table
   INSERT INTO test_results (test_name, expected_output, actual_output, test_status)
   VALUES ('test_my_function for input ' || input_value, expected_output, actual_output, test_status);

   COMMIT;
END;
/

Step 3: Create a Master Procedure to Run All Tests

Create a master procedure that runs all the test cases and logs the results.

CREATE OR REPLACE PROCEDURE run_all_tests IS
BEGIN
   -- Clear previous test results
   DELETE FROM test_results;
   COMMIT;

   -- Run individual test cases
   test_my_function(5, 10);   -- Expecting 10
   test_my_function(0, 0);    -- Expecting 0
   test_my_function(-2, -4);  -- Expecting -4

   -- Add more test cases as needed

END;
/

Step 4: Query Test Results

After running the tests, you can query the test_results table to see which tests passed or failed.

SELECT * FROM test_results;

This will show:

• The test name.
• The expected output.
• The actual output.
• Whether the test passed or failed.

3. Automate Testing in Batch Mode

You can create a SQL script that runs all tests automatically. Here’s an example of a batch script (run_tests.sql):

BEGIN
   -- Run all the tests
   run_all_tests;
END;
/

-- Show test results
SELECT * FROM test_results;
/

You can then execute this script from your SQL client or integrate it into a Continuous Integration (CI) pipeline to automate the test execution.

4. Handling Edge Cases

Make sure to write tests that cover various scenarios such as:

• Boundary values: Test with minimum and maximum values.
• NULL inputs: Verify how the function behaves when given null values.
• Exception handling: If your function throws exceptions, ensure you handle and log these cases in your test procedure.

5. Test Reporting and Output

You can output the results of each test directly to the console or log them in a table as shown above. Optionally, you can format the output for easier reading:

SET SERVEROUTPUT ON;
BEGIN
   FOR r IN (SELECT * FROM test_results) LOOP
      DBMS_OUTPUT.PUT_LINE('Test: ' || r.test_name || ' | Status: ' || r.test_status);
   END LOOP;
END;
/

6. Continuous Integration (CI) Pipeline Integration

Once you have a SQL script that runs the tests and produces results, you can integrate it into your CI pipeline (like Jenkins, GitLab CI, etc.). Here's an example Jenkins pipeline step that runs the test script and outputs results.

pipeline {
    agent any
    stages {
        stage('Run Oracle Function Tests') {
            steps {
                script {
                    sh 'sqlplus user/pass@oracledb @run_tests.sql'
                }
            }
        }
    }
}

Summary of Custom Framework

1. Create a test logging table to store the results of each test case.
2. Write PL/SQL procedures for each function you want to test, comparing actual results with expected outputs.
3. Run all tests in a master procedure, which can be executed manually or as part of a CI pipeline.
4. Log results to a table and display the results in a report.