oracle-validation.yml

name: Oracle SQL Validation

# Compiles the application schema and runs the utPLSQL corpus against a live
# Oracle Database 23ai Free instance.
#
# The corpus guard runs FIRST and fails the job when sql/tests/ is empty. That
# is deliberate: this workflow previously printed "no utPLSQL tests — skipping"
# and exited 0, so a green badge meant "nothing was tested". A skip is now a
# failure.

on:
  push:
    paths:
      - "sql/**"
      - "scripts/validate-test-corpus.sh"
      - ".github/workflows/oracle-validation.yml"
  pull_request:
    paths:
      - "sql/**"
      - "scripts/validate-test-corpus.sh"
      - ".github/workflows/oracle-validation.yml"
  workflow_dispatch:

permissions:
  contents: read

jobs:
  corpus-guard:
    name: utPLSQL corpus present
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - run: bash scripts/validate-test-corpus.sh

  oracle-validation:
    name: Compile + EXPLAIN_MVIEW + utPLSQL
    needs: corpus-guard
    runs-on: ubuntu-latest

    services:
      oracle:
        image: gvenzl/oracle-free:23-slim
        env:
          ORACLE_PASSWORD: oracle
          APP_USER: plsql
          APP_USER_PASSWORD: plsql
        ports:
          - 1521:1521
        options: >-
          --health-cmd "healthcheck.sh"
          --health-interval 10s
          --health-timeout 5s
          --health-retries 30

    steps:
      - uses: actions/checkout@v4

      - name: Grant privileges to the app schema
        run: |
          docker exec -i "${{ job.services.oracle.id }}" \
            sqlplus -S -L system/oracle@localhost/FREEPDB1 <<'SQL'
          WHENEVER SQLERROR EXIT FAILURE
          GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE, CREATE SEQUENCE,
                CREATE VIEW, CREATE MATERIALIZED VIEW, CREATE TYPE,
                UNLIMITED TABLESPACE TO plsql;
          GRANT EXECUTE ON DBMS_ASSERT TO plsql;
          EXIT
          SQL

      - name: Copy SQL into the DB container
        run: docker cp sql "${{ job.services.oracle.id }}:/tmp/sql"

      - name: Install the application schema
        run: |
          set -euo pipefail
          shopt -s nullglob
          for f in sql/app/*.sql sql/app/*.pks sql/app/*.pkb; do
            echo "=== Installing $f ==="
            docker exec -i "${{ job.services.oracle.id }}" \
              sqlplus -S -L plsql/plsql@localhost/FREEPDB1 "@/tmp/$f"
          done

      - name: Fail on any invalid object
        # A package that compiles with errors still "installs". Catch it here
        # rather than letting the tests fail with a confusing ORA-06508.
        run: |
          docker exec -i "${{ job.services.oracle.id }}" \
            sqlplus -S -L plsql/plsql@localhost/FREEPDB1 <<'SQL'
          WHENEVER SQLERROR EXIT FAILURE
          SET SERVEROUTPUT ON SIZE UNLIMITED
          DECLARE
             l_bad PLS_INTEGER;
          BEGIN
             SELECT COUNT(*) INTO l_bad FROM user_objects WHERE status <> 'VALID';
             IF l_bad > 0 THEN
                FOR r IN (SELECT name, type, line, position, text
                            FROM user_errors ORDER BY name, sequence) LOOP
                   DBMS_OUTPUT.PUT_LINE(r.type||' '||r.name||' '||r.line||':'||r.position||' '||r.text);
                END LOOP;
                RAISE_APPLICATION_ERROR(-20001, l_bad || ' invalid object(s) after install');
             END IF;
          END;
          /
          EXIT
          SQL

      - name: Run validation fixtures (compile + EXPLAIN_MVIEW)
        run: |
          set -euo pipefail
          shopt -s nullglob
          files=(sql/validation/*.sql)
          if [ ${#files[@]} -eq 0 ]; then
            echo "::error::no validation fixtures under sql/validation/"; exit 1
          fi
          for f in "${files[@]}"; do
            echo "=== Running $f ==="
            docker exec -i "${{ job.services.oracle.id }}" \
              sqlplus -S -L plsql/plsql@localhost/FREEPDB1 "@/tmp/$f"
          done

      - name: Install utPLSQL
        run: |
          set -euo pipefail
          curl -sSL -o /tmp/utPLSQL.zip \
            https://github.com/utPLSQL/utPLSQL/releases/latest/download/utPLSQL.zip
          unzip -q /tmp/utPLSQL.zip -d /tmp/ut
          docker cp /tmp/ut "${{ job.services.oracle.id }}:/tmp/ut"
          docker exec -i "${{ job.services.oracle.id }}" bash -lc \
            "cd /tmp/ut/utPLSQL/source && sqlplus -S -L sys/oracle@localhost/FREEPDB1 as sysdba @install_headless.sql ut3 ut3 users"

      - name: Install the utPLSQL test packages
        run: |
          set -euo pipefail
          shopt -s nullglob
          tests=(sql/tests/*.pks sql/tests/*.pkb)
          if [ ${#tests[@]} -eq 0 ]; then
            echo "::error::no utPLSQL test packages under sql/tests/"; exit 1
          fi
          for t in "${tests[@]}"; do
            echo "=== Installing $t ==="
            docker exec -i "${{ job.services.oracle.id }}" \
              sqlplus -S -L plsql/plsql@localhost/FREEPDB1 "@/tmp/$t"
          done

      - name: Run utPLSQL suites
        run: |
          set -euo pipefail
          docker exec -i "${{ job.services.oracle.id }}" \
            sqlplus -S -L plsql/plsql@localhost/FREEPDB1 <<'SQL' | tee /tmp/ut.log
          SET SERVEROUTPUT ON SIZE UNLIMITED
          SET FEEDBACK OFF HEADING OFF PAGESIZE 0 LINESIZE 200 TRIMSPOOL ON
          BEGIN
             ut.run(ut_documentation_reporter());
          END;
          /
          EXIT
          SQL

          # The reporter's summary line is the authoritative pass/fail signal.
          # Absent or non-zero failures/errors -> fail the job.
          if ! grep -qE '[0-9]+ tests?, 0 failed, 0 errored' /tmp/ut.log; then
            echo "::error::utPLSQL reported failures, errors, or produced no summary"
            grep -E 'tests?,|Failures:|Errors:' /tmp/ut.log || true
            exit 1
          fi
          echo "utPLSQL suites passed."