Writing Testable PL/SQL in IFS: Unit Testing Custom Packages

If you've worked on IFS custom PL/SQL, you know the pain: your business logic is tightly coupled to IFS APIs, the database state is messy, and testing feels impossible. You end up deploying to a test environment, crossing your fingers, and hoping nothing breaks in production.

It doesn't have to be this way.

This guide shows you how to write testable PL/SQL in IFS using utPLSQL, a modern unit testing framework that brings TDD (Test-Driven Development) practices to Oracle. You'll learn how to isolate your logic from IFS dependencies, mock those dependencies, and build a safety net of automated tests that make refactoring and new features actually feasible.

The Problem with IFS Custom PL/SQL

Before diving into solutions, let's be honest about the challenge:

1. Tight coupling to framework APIs — Customer packages often call generated APIs such as Purchase_Order_API; they need installed IFS metadata and representative state for integration tests.
2. Complex database state — Tests require specific data in specific states, and cleaning up after tests is messy.
3. No test harness — IFS Developer Studio has limited testing tools. You're often writing code without safety nets.
4. Integration over unit tests — Most developers resort to integration tests (deploy → manual check), which are slow and unreliable.

Solution: Separate Logic from Framework

The key insight: Write your business logic as pure PL/SQL functions and procedures that don't depend on IFS APIs. Then, create thin wrapper packages that call your logic and integrate with IFS.

Example pattern:

The my_util_logic package contains zero IFS dependencies. The adapter name is deliberately customer-owned: its implementation must be resolved against the actual standard API in the target release. Unit-test the calculation package in isolation, then integration-test the adapter against a representative IFS environment.

Setting Up utPLSQL

utPLSQL is a widely used open-source unit testing framework for Oracle PL/SQL. It can integrate with CI/CD, but it is not an IFS-delivered component. Confirm Oracle/IFS support policy, licensing, schema ownership, and security before installing it. In an IFS-managed Cloud service you cannot assume permission to install third-party database frameworks; an isolated customer-controlled Oracle test database can still test pure PL/SQL packages.

Installation

1. Download utPLSQL from GitHub:

2. Have the database/platform owner install the reviewed release into its dedicated framework schema, following that release's official installation guide. Do not paste privileged credentials into a shell history:

3. Grant only the documented runner privileges to a dedicated test schema. Exact objects/grants differ by utPLSQL release; the following historic names must not be treated as a universal recipe:

4. Optional: Install CLI client (for CI/CD):

Running Your First Test

Create a simple test package:

Run tests in SQL*Plus/SQLcl using the API supported by the installed utPLSQL version:

Expected output:

My Util Logic
  Calculates GOLD discount correctly
  Calculates SILVER discount correctly
  Calculates default discount correctly

Finished in .342 seconds
3 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)

Mocking IFS API Calls

utPLSQL does not provide a --%MockPackage annotation, and IFS Developer Studio does not rewrite calls to standard packages for tests. PL/SQL dependencies are statically resolved, so arbitrary package mocking is not as transparent as it is in some object-oriented languages.

Use a Functional Seam

Pass already-retrieved values into a pure function and keep the IFS call in a thin adapter:

Test the Adapter as an Integration

Deploy the thin adapter to a controlled IFS test environment, arrange business data through public IFS APIs or supported test fixtures, call the adapter, and assert the observable business result. This verifies grants, generated API signatures, state transitions, and transaction behaviour that a mock would hide. For a customer-owned dependency, a replaceable adapter package or compile-time test implementation is possible, but keep that indirection explicit and never shadow an IFS standard package in the application schema.

Advanced: Mock Data Strategies

Strategy 1: Use Isolated, Owned Test Fixtures

Negative or out-of-range IDs are not automatically safe: IFS keys are often text or composite, and validation may reject them. For pure package tests, use a test-owned fixture table in an isolated schema. For IFS integration tests, create valid records through public business APIs in a dedicated company/site and tag them with a run ID.

Strategy 2: Create Test Data Builders

For complex scenarios, create reusable builders:

The builder example uses customer-owned test fixtures; it is not permission to insert into IFS base tables. Integration builders should call standard APIs and perform supported cleanup because an IFS API or background operation may cross the unit-test transaction boundary.

Integrating with CI/CD

Using utPLSQL-CLI

The command-line client makes it easy to run tests in CI/CD pipelines:

The CLI connection and reporter syntax above follows utPLSQL CLI 3.x; pin and verify a specific framework/CLI pair in your toolchain. Use the target release's documentation when upgrading it.

GitHub Actions Example

An Oracle XE container is not an IFS environment and does not contain generated IFS APIs. It can run pure packages if licensing and image distribution are handled correctly, but adapter/integration suites need a representative, authorised IFS test build.

Real-World Example: Testing IFS Order Processing

Here's a complete example of testing a realistic IFS custom order processing package:

Key Takeaways

1. Separate Logic from Framework — Write pure PL/SQL functions that don't depend on IFS APIs. This makes them immediately testable.

2. Use utPLSQL where the environment permits it — It provides a mature PL/SQL test runner, but is a separately governed third-party database framework, not an IFS Cloud feature.

3. Create explicit seams — Keep pure decisions separate from thin IFS adapters; there are no built-in @MockPackage or @OptionalMocks annotations.

4. Use governed test-data builders — use owned fixtures for unit tests and public IFS APIs in a dedicated integration-test company/site; negative IDs are not a safety boundary.

5. Integrate with CI/CD — run pure unit tests on each relevant change and gate deployment with representative IFS integration tests; keep database credentials in a wallet or approved secret mechanism.

6. Write Tests Early — Not necessarily TDD, but at least write tests before you deploy to production. The safety net is invaluable.

7. Test Edge Cases — Zero values, null inputs, boundary conditions. These are where bugs hide.

8. Keep Tests Fast — Fast tests get run more often. Use rollback to savepoint instead of manual cleanup.

9. Document with Assertions — Your tests are executable documentation. Use meaningful test names and assertions.

10. Refactor with evidence — tests reduce risk, but still combine them with Update Analyzer, source review, and end-to-end IFS process testing.

Next Steps

• Confirm whether utPLSQL is permitted, then install it only in an approved test database
• Convert one custom package to use the logic + integration pattern
• Write 5-10 unit tests for the logic
• Run tests daily as part of your build
• Celebrate when tests catch a bug before production

Testing IFS custom PL/SQL is hard, but it's not impossible. Start small, embrace the patterns in this guide, and watch your code quality soar.

Happy testing! 🚀