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 — Your custom packages often call IFS built-in APIs (lu_api, IFS_APIS, etc.), which are hard to test in isolation.
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. It's pure business logic. The my_util_api package handles IFS integration. Now you can unit test logic without touching IFS.

Setting Up utPLSQL

utPLSQL is a battle-tested unit testing framework for Oracle PL/SQL. It's lightweight, integrates with CI/CD, and follows TDD principles.

Installation

1. Download utPLSQL from GitHub:

2. Install in your database (as SYS or a DBA):

3. Grant privileges to your dev schema:

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

Running Your First Test

Create a simple test package:

Run tests in SQL*Plus:

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

When you need to test code that calls IFS APIs, use mocks. IFS's Developer Studio includes built-in mocking annotations for custom packages.

Using @MockPackage Annotation

Here's a real-world scenario: testing a package that retrieves customer data via IFS API:

Testing with @OptionalMocks

IFS Developer Studio also supports @OptionalMocks for cases where you want some mocked methods but need the original logic for others:

Advanced: Mock Data Strategies

Strategy 1: Use Negative or Reserved IDs

Never use real data in tests. Use negative or out-of-range IDs that don't exist in production:

Strategy 2: Create Test Data Builders

For complex scenarios, create reusable builders:

Integrating with CI/CD

Using utPLSQL-CLI

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

GitHub Actions Example

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 for Unit Testing — It's lightweight, follows industry standards, and integrates seamlessly with CI/CD.

3. Mock External Dependencies — Use IFS's built-in mocking annotations (@MockPackage, @OptionalMocks) or create your own mocks for APIs you can't control.

4. Use Test Data Builders — Create reusable functions that generate test data with negative/reserved IDs so you never touch production data.

5. Integrate with CI/CD — Run tests on every commit using utPLSQL-CLI. Make failing tests block deployments.

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 Fearlessly — Once you have tests, you can safely refactor without fear of breaking things.

Next Steps

• Install utPLSQL in your dev 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! 🚀