Managing Soft Objects in Oracle SQLcl Projects: Understanding stage.softObjectIsolation

When working with Oracle SQLcl's project-based deployment framework, one of the critical configuration decisions you'll face is how to manage "soft objects" — database objects like packages, views, functions, and procedures that can be recreated without data loss. The stage.softObjectIsolation parameter in your project configuration file determines this behavior, and choosing the right approach can significantly impact your development workflow and deployment strategy.

What is stage.softObjectIsolation?

The stage.softObjectIsolation parameter controls where soft database objects are staged when using SQLcl's project structure with Liquibase-based deployments. This setting is configured in your .dbtools/project.config.json file:

"stage" : {
  "softObjectIsolation" : "change"
}

The parameter accepts two values:

1. Change - Feature-based isolation

2. Release - Release-based isolation

Feature-Based Isolation (softObjectIsolation: "change")

With feature-based isolation, soft objects are placed within individual feature change folders alongside their corresponding schema changes.

structure example

dist/releases/next/
├── changes/
│   ├── feature-1/
│   │   ├── tables/
│   │   ├── functions/     ← Soft objects here
│   │   └── stage.changelog.xml
│   ├── feature-2/
│   │   ├── views/          ← Soft objects here
│   │   └── stage.changelog.xml
└── release.changelog.xml

Use this option when:

• Small, isolated features with dedicated objects

• Clear boundaries between feature modules

• Each feature has its own set of non-overlapping objects

• You want complete feature isolation during development

Release-Based Isolation (softObjectIsolation: "release")

With release-based isolation, soft objects are extracted into a shared code/ folder at the release level, separate from feature-specific schema changes.

structure example

dist/releases/next/
├── changes/
│   ├── feature-1/
│   │   ├── tables/         ← Only hard objects here
│   │   └── stage.changelog.xml
│   ├── feature-2/
│   │   ├── tables/
│   │   └── stage.changelog.xml
├── code/
│   ├── schema/
│   │   ├── functions/      ← All soft objects here
│   │   │   └── get_display_name.sql
│   │   ├── views/
│   │   └── packages/
│   └── code.changelog.xml
└── release.changelog.xml

the deployment for release-based isolation is also a bit different:

<databaseChangeLog>
  <!-- Feature changes first (tables, constraints, etc.) -->
  <include file="changes/feature/init/stage.changelog.xml"/>
  <include file="changes/ticket-1/stage.changelog.xml"/>
  
  <!-- Shared code deployed last -->
  <include file="code/code.changelog.xml"/>
</databaseChangeLog>

The Problem: When Feature-Based Isolation Breaks Down

Our Experience

In our project, we initially used feature-based isolation. However, we encountered a significant challenge: multiple features needed to modify the same soft objects.

Practical Example

Consider a get_display_name() function that multiple features need to use or modify:

Feature A (User Management):

-- Needs get_display_name() to display user names
create or replace function get_display_name(
  p_name
)
return varchar2
as
begin
    return p_name;
end get_display_name;

Feature B (Improvement):

-- Also needs get_display_name() to display gym trainer names
create or replace function get_display_name(
  p_first_name  in users.first_name%type
, p_last_name   in users.last_name%type
)
return varchar2
as
begin
    return p_first_name || ' ' || p_last_name;
end get_display_name;

Feature C (Reporting):

-- Modifies get_display_name() to add formatting
create or replace function get_display_name(
  p_first_name  in users.first_name%type
, p_last_name   in users.last_name%type
)
return varchar2
as
begin
    return initcap(p_first_name || ' ' || p_last_name);
end get_display_name;

Problems Encountered:

1. Duplicate Object Definitions: The same function appeared in multiple feature folders, creating confusion about which version was authoritative.

2. Merge Conflicts: When different developers modified the same object in different feature branches, resolving conflicts became complex.

3. Deployment Order Issues: Features deployed in the wrong order could overwrite newer versions of soft objects with older ones.

4. Maintenance Overhead: Updating a shared function required changing it across multiple feature folders.

5. Testing Challenges: It was unclear which version of an object would be active in a given environment.

The Solution: Switching to Release-Based Isolation

Configuration Change

We modified our .dbtools/project.config.json:

"stage" : {
  "softObjectIsolation" : "release"  // Changed from "change"
}

Benefits Realized

1. Single Source of Truth: Each soft object exists in exactly one location — the code/ folder.

2. Cleaner Version Control: Soft objects have a clear version history in their dedicated location.

3. Simplified Merging: Conflicts are easier to resolve when objects aren't duplicated across features.

4. Logical Deployment Flow:

   • Feature changes (schema modifications) deploy first

   • Soft objects (code) deploy last, ensuring all dependencies exist

5. Release-Driven Code Management: The code/ folder represents the cumulative state of all soft objects for a release, not tied to individual features.

6. Easier Maintenance: Updating a function means changing one file

Best Practices and Recommendations

Choose Release-Based When:

• Objects are shared across multiple features

• You have complex interdependencies between code objects

• Multiple teams work on overlapping functionality

• You want clear separation between schema changes and code

• Release-level code consistency is important

Choose Feature-Based When:

• Features are truly isolated with no shared code

• You have a microservices-style architecture with separate schemas

• Complete feature independence is required

• Features may be deployed independently to different environments

Migration Tips

If you're switching from feature-based to release-based:

1. Backup First: Ensure your current state is committed to version control

2. Update Configuration: Change softObjectIsolation in project.config.json

3. Restage Objects: Re-run your staging process to reorganize the structure

4. Verify Changelog Order: Ensure the code/ folder is included last in your release changelog

5. Test Deployment: Run a test deployment to a development environment

6. Document the Change: Update team documentation about the new structure

Conclusion

The stage.softObjectIsolation parameter is a powerful configuration option that significantly impacts how your database code is organized and deployed. While feature-based isolation seems appealing for its modularity, real-world projects often have shared code dependencies that make release-based isolation more practical.

In my experience, switching to release-based isolation eliminated duplicate object definitions, simplified merge conflicts, and provided a clearer picture of our codebase at the release level. The code/ folder now serves as the authoritative source for all soft objects, while feature folders contain only the schema changes specific to each feature.

Choose the approach that matches your project's architecture and team workflow, but don't hesitate to switch if you encounter the same challenges we did with cross-feature object sharing.