Oracle Invalid Object Recompile Guard

id: oracle-invalid-object-recompile-guard
namespace: company.team

description: |
  Find Oracle schema objects left INVALID after a dependency DDL change (views,
  procedures, functions, packages, package bodies, triggers, types), recompile them
  with ALTER ... COMPILE, then read dba_objects again and fail if any are still INVALID.

inputs:
  - id: jdbc_url
    type: STRING
    displayName: Oracle JDBC URL
    defaults: "jdbc:oracle:thin:@//localhost:1521/FREEPDB1"

  - id: username
    type: STRING
    displayName: Database user
    description: A user that can read dba_objects (SELECT_CATALOG_ROLE or SELECT ANY
      DICTIONARY) and compile the objects it finds. A DBA role covers both. A
      user recompiling only its own schema needs no extra privilege.
    defaults: SYSTEM

  - id: password
    type: STRING
    displayName: Database password
    defaults: "{{ secret('ORACLE_PASSWORD') }}"

  - id: object_owners
    type: ARRAY
    itemType: STRING
    displayName: Schema owners to check
    description: Application schema owners whose objects are checked. System schemas
      are left out so a stray SYS or ORDS invalid object is never touched.
    defaults:
      - APP

  - id: auto_remediate
    type: BOOL
    displayName: Auto remediate
    description: Allow ALTER ... COMPILE to run. Off by default.
    defaults: false

  - id: dry_run
    type: BOOL
    displayName: Dry run
    description: Must be false, together with auto_remediate, for any recompile to run.
    defaults: true

variables:
  audit_sql: |
    SELECT owner, object_name, object_type, status,
           CASE
             WHEN object_type = 'PACKAGE BODY' THEN 'ALTER PACKAGE "' || owner || '"."' || object_name || '" COMPILE BODY'
             WHEN object_type = 'TYPE BODY'    THEN 'ALTER TYPE "'    || owner || '"."' || object_name || '" COMPILE BODY'
             ELSE 'ALTER ' || object_type || ' "' || owner || '"."' || object_name || '" COMPILE'
           END AS fix_sql
      FROM dba_objects
     WHERE status = 'INVALID'
       AND owner IN ('{{ inputs.object_owners | join("','") | upper }}')
       AND object_type IN ('VIEW','PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER','TYPE','TYPE BODY','MATERIALIZED VIEW')
     ORDER BY owner, object_type, object_name

tasks:
  - id: audit
    type: io.kestra.plugin.jdbc.oracle.Query
    description: Read-only. Lists recompilable objects in the checked schemas whose
      dba_objects status is INVALID, with the exact ALTER ... COMPILE for each.
    url: "{{ inputs.jdbc_url }}"
    username: "{{ inputs.username }}"
    password: "{{ inputs.password }}"
    fetchType: FETCH
    sql: "{{ render(vars.audit_sql) }}"

  - id: fixes
    type: io.kestra.plugin.core.output.OutputValues
    description: The deduplicated ALTER ... COMPILE statements for the invalid objects.
    values:
      sql: "{{ outputs.audit.rows | jq('map(.FIX_SQL) | unique') | first | toJson }}"

  - id: summary
    type: io.kestra.plugin.core.log.Log
    message: >-
      Invalid object audit on {{ inputs.jdbc_url }}: {{ outputs.audit.size }}
      invalid object(s) in schemas {{ inputs.object_owners |
      jq('map(ascii_upcase) | join(", ")') | first }}. {{ outputs.audit.rows |
      jq('map(.OWNER + "." + .OBJECT_NAME + " [" + .OBJECT_TYPE + "]") | join(",
      ")') | first }}

  - id: route
    type: io.kestra.plugin.core.flow.If
    condition: "{{ outputs.audit.size > 0 }}"
    then:
      - id: alert
        type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
        url: "{{ secret('SLACK_WEBHOOK_URL') }}"
        payload: |
          {{ {'text': 'Oracle ' ~ inputs.jdbc_url ~ ': ' ~ outputs.audit.size ~ ' invalid schema object(s) found: ' ~ (outputs.audit.rows | jq('map(.OWNER + "." + .OBJECT_NAME + " (" + .OBJECT_TYPE + ")") | join(", ")') | first) ~ '.' ~ (inputs.auto_remediate and not inputs.dry_run ? ' Recompiling now.' : ' No change applied (auto_remediate=' ~ inputs.auto_remediate ~ ', dry_run=' ~ inputs.dry_run ~ ').')} | toJson }}

      - id: remediate_gate
        type: io.kestra.plugin.core.flow.If
        condition: "{{ inputs.auto_remediate and not inputs.dry_run }}"
        then:
          - id: remediate
            type: io.kestra.plugin.core.flow.Loop
            description: First recompile pass. Sequential on purpose, so interdependent
              objects do not compile against each other in parallel.
            concurrencyLimit: 1
            values: "{{ outputs.fixes.values.sql }}"
            tasks:
              - id: recompile
                type: io.kestra.plugin.jdbc.oracle.Query
                description: ALTER ... COMPILE for one object. allowFailure, so an object that
                  cannot compile yet does not stop the pass.
                allowFailure: true
                url: "{{ inputs.jdbc_url }}"
                username: "{{ inputs.username }}"
                password: "{{ inputs.password }}"
                fetchType: NONE
                sql: "{{ item.value }}"

          - id: remediate_second
            type: io.kestra.plugin.core.flow.Loop
            description: Second recompile pass over the same set. A dependency compiled in
              the first pass can validate an object that failed earlier only
              because its order was wrong.
            concurrencyLimit: 1
            values: "{{ outputs.fixes.values.sql }}"
            tasks:
              - id: recompile_retry
                type: io.kestra.plugin.jdbc.oracle.Query
                description: Re-run of ALTER ... COMPILE. Recompiling an already valid object is
                  a no-op.
                allowFailure: true
                url: "{{ inputs.jdbc_url }}"
                username: "{{ inputs.username }}"
                password: "{{ inputs.password }}"
                fetchType: NONE
                sql: "{{ item.value }}"

          - id: reaudit
            type: io.kestra.plugin.jdbc.oracle.Query
            description: Read dba_objects again after both passes.
            url: "{{ inputs.jdbc_url }}"
            username: "{{ inputs.username }}"
            password: "{{ inputs.password }}"
            fetchType: FETCH
            sql: "{{ render(vars.audit_sql) }}"

          - id: verify_result
            type: io.kestra.plugin.core.flow.If
            condition: "{{ outputs.reaudit.size > 0 }}"
            then:
              - id: remediation_failed
                type: io.kestra.plugin.core.execution.Fail
                errorMessage: >-
                  {{ outputs.reaudit.size }} object(s) still INVALID after two
                  recompile passes: {{ outputs.reaudit.rows | jq('map(.OWNER +
                  "." + .OBJECT_NAME + " (" + .OBJECT_TYPE + ")") | join(", ")')
                  | first }}. A recompile cannot fix an object whose dependency
                  is gone or renamed, so the underlying DDL needs a code change.
            else:
              - id: remediated
                type: io.kestra.plugin.core.log.Log
                message: "Recompiled and verified: {{ fromJson(outputs.fixes.values.sql) |
                  length }} object(s) back to VALID."
    else:
      - id: clean
        type: io.kestra.plugin.core.log.Log
        message: "No INVALID objects in the checked schemas. Nothing to recompile."

errors:
  - id: alert_flow_failure
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    url: "{{ secret('SLACK_WEBHOOK_URL') }}"
    payload: |
      {{ {'text': 'Oracle invalid-object recompile guard failed on ' ~ inputs.jdbc_url ~ ' (execution ' ~ execution.id ~ '). Objects may still be INVALID. Check the logs.'} | toJson }}

triggers:
  - id: daily
    type: io.kestra.plugin.core.trigger.Schedule
    description: Shipped disabled and always in safe mode (report only).
    cron: "0 6 * * *"
    disabled: true
    inputs:
      auto_remediate: false
      dry_run: true