PL/SQL Test MCP

MCP server for IFS Cloud PL/SQL unit testing — mirrors the architecture of integration-testing-mcp and delegates deploy/run steps to ifs-f1-codegen-dev.

Develop MCP to identify if a function/procedure is unit testable and generate the test.

Workflow

flowchart TD
    A[checkUnitTestability] --> B{Testable?}
    B -->|No| C[annotateIgnoreUnitTest]
    B -->|Yes| D[generateUnitTest]
    D --> E[generate_code / generate_and_deploy via ifs-f1-codegen-dev]
    E --> F[Verify test passes]
    C --> E

Related MCP server: mcp-playwright-test

Tools

Supported @IgnoreUnitTest reasons

Cursor integration

The server is published to npm as @vaallk/plsql-test-mcp. Add this to your Cursor MCP settings — no local clone or build required:

{
  "mcpServers": {
    "plsql-test": {
      "command": "npx",
      "args": ["-y", "@vaallk/plsql-test-mcp@0.1.12"],
      "env": {
        "IFS_WORKSPACE": "C:\\path\\to\\your\\workspace"
      }
    }
  }
}

IFS_WORKSPACE is machine-specific — point it at your own IFS workspace root. Use @latest instead of a pinned version to always pick up the newest release on restart.

Local development config

To run against a local build instead of the published package:

{
  "mcpServers": {
    "plsql-test": {
      "command": "node",
      "args": ["C:/ifsapps-new/plsql-test-mcp/dist/index.js"],
      "env": {
        "IFS_WORKSPACE": "C:\\path\\to\\your\\workspace"
      }
    }
  }
}

Development

cd c:/ifsapps-new/plsql-test-mcp
npm install
npm run build
npm start

Deploy-safe generation (v0.1.4)

The generator avoids patterns that break AV_*_TST compilation:

• No UTF-8 BOM on written .pltst files

• VARCHAR2(2000) for all generated IS-section locals (bare VARCHAR2 causes PLS-00215)

• Pre-write validation rejects bare VARCHAR2, BOM, and post-loop generated tests

• Skips %ROWTYPE return types and SELECT * INTO %ROWTYPE (manual test required)

• Skips CRUD modify methods using Get_Object_By_Id___ + Unpack___

• Expression SELECT columns (round(cast(...))) mapped to safe mock column names

• FOR loop column order matches IFS convention: expected_ | input_param_

• Not-found cases stay inside the FOR loop (no post-loop statements)

• Skips @IgnoreUnitTest when the method already has a UNITTEST block

• Special mocks for Get_Wp_Id, Has_Skills_Assigned_In_Turn, Get_Fault_Id_From_Record_Id, Get_AOS_Days, Get_Hm_Contract_Id_By_Barcode

Derived value mapping (v0.1.12)

Getters that fetch a value but return a derived label/status via a post-fetch IF/ELSE are detected so the test asserts the mapped result, not the raw fetched value:

• Column null-check — SELECT col then IF col IS NULL THEN 'A' ELSE 'B' (e.g. Get_Measurment_Status → 'Pending'/'Signed'). The mock uses one non-null row and one NULL row.

• Count positive — SELECT COUNT(*) then IF cnt > 0 THEN 'A' ELSE 'B' (e.g. Is_Part_Warnings_Exist → 'TRUE'/'FALSE'). The mock provides matching rows for the "present" key and a missing key for the "absent" case.

Both direct-RETURN and result-variable assignment styles are supported; ELSIF multi-branch mappings are left for manual tests.

Batch coverage example

processUnitTestCoverage({
  sourceFile: "C:/ifsapps-new/workspace/adcom/source/adcom/database/AvFault.plsql",
  workspace: "C:/ifsapps-new/workspace",
  jiraKey: "PJZ-12345",
  confirmed: true
})

Example

Analyze McprActivityRelation.plsql:

checkUnitTestability({
  sourceFile: "C:/ifsapps-new/workspace/prjrep/source/prjrep/database/McprActivityRelation.plsql",
  methodName: "Is_Circular_Link___"
})

Generate test:

generateUnitTest({
  sourceFile: ".../McprActivityRelation.plsql",
  methodName: "Is_Circular_Link___",
  workspace: "C:/ifsapps-new/workspace",
  jiraKey: "PJZ-12345",
  confirmed: true
})

Run via ifs-f1-codegen-dev:

generate_and_deploy({
  input_files: [".../McprActivityRelation.plsql", ".../McprActivityRelation.pltst"],
  environment_key: "26r1-dev-lkp",
  workspace: "C:/ifsapps-new/workspace",
  confirmed: true
})

Note on plvst

IFS uses .pltst files for both .plsql and .plsvc unit tests. No separate .plvst extension exists in the codebase — this MCP targets .pltst.

Related

• integration-testing-mcp — reference MCP architecture

• ifs-f1-codegen-dev — code generation, SQLcl deploy, live DB inspect