Oracle Fusion SQL Development Comes to VS Code: A Modern Alternative to Traditional Tools
Introduction
If you’ve spent significant time building BI Publisher data models in Oracle Fusion, you’ve probably developed a workflow that looks something like this:
- Write SQL in BI Publisher’s editor or a separate tool such as SQLConnect or Notepad++.
- Copy it into Oracle Fusion if you are using Notepad++.
- Run the query.
- Wait for the results.
- Fix an error.
- Run it again.
- Save a local copy just in case.
- Switch environments from Dev to Test to Prod. If you have a limited number of pod connections in your tool, you have to delete an existing environment, create a new one, and repeat this multiple times.
- Repeat.
It works, but should it be so hard? Is this how modern engineering teams build software, data pipelines, or analytics solutions today?
Over the last few years, data engineers and Oracle Fusion developers have standardized on VS Code and adopted Git-based workflows. AI-powered development tools like GitHub Copilot and Claude Code have become part of the everyday development experience.
Why should Oracle Fusion SQL development alone remain tied to browser-based workflows, manual testing cycles, copy-paste deployments, and disconnected tooling?
That’s exactly why BI Connector built its VS Code Extension. Our new BI Connector VS Code Extension enables you to build, test, version, and deploy Oracle Fusion SQL directly from Visual Studio Code, bringing Fusion development into the same environment where modern engineering teams like yours already work.
Oracle Fusion SQL Development – The Existing Workflow
The challenge isn’t writing SQL itself but everything around it: discovering the right tables, testing queries, comparing environments, tracking changes, collaborating with teammates, and deploying safely.
| Task | Current Workflow |
| Writing SQL | BI Publisher editor, SQLConnect, Notepad++, or niche Fusion SQL tools |
| Testing | Manual execution inside BI Publisher or via SQLConnect |
| Versioning | Local folders or shared drives |
| Environment Switching | Manual environment switching |
| Deployment | Manual deployment steps, catalog migrations, or custom automation that is disconnected from day-to-day Fusion SQL development. |
| Collaboration | Email attachments, shared files, or collaboration tools |
| Debugging | Trial-and-error execution |
As shown in the table above, a typical Oracle Fusion SQL development cycle often starts long before any SQL is written. Before creating a query, you may spend considerable time navigating Oracle Fusion’s complex data model, identifying the right tables, understanding join relationships, validating column names, and cross-referencing documentation, existing reports, or previous data models to ensure you’re pulling the correct data.
Writing SQL
Once you’re ready to write SQL, the workflow itself is often fragmented. Some developers work directly in the BI Publisher editor, while others prefer tools such as SQL Developer, Notepad++, SQLConnect, or other Fusion-specific SQL utilities.
Testing
Regardless of where you write the query, testing typically requires switching into BI Publisher or SQLConnect, manually executing the query, reviewing the results, making adjustments, and repeating the process. Even a small syntax correction, parameter change, or join modification can trigger another round of testing.
Version Management
How about version management? That’s equally disconnected. Many teams still maintain SQL files in local folders, shared drives, or collaboration platforms. Sharing changes often involves sending files back and forth through email or messaging tools, and comparing versions becomes a manual exercise rather than a structured source-control process with clear change history and rollback capabilities.
Environment Switching
When multiple Oracle Fusion environments are involved, you frequently need to validate queries across development, testing, UAT, and production pods. This often means manually switching environments, logging in repeatedly, and ensuring you’re connected to the correct pod before running or validating a query.
Deployment
After validating a query, you often need to migrate BI Publisher data models and related catalog objects across development, testing, and production environments. Depending on your organization’s processes, this may involve manual updates, catalog exports and imports, or custom deployment mechanisms.
Even when custom automation exists, it is frequently disconnected from the SQL development workflow, forcing you to switch between multiple tools and processes before changes reach production.
Debugging
When a query fails or returns unexpected results, there are limited tools available to isolate the problem quickly. You often rely on trial-and-error troubleshooting, repeatedly modifying queries, rerunning tests, validating parameters, and comparing outputs until you identify the root cause.
None of these individual activities is particularly difficult on its own. The challenge is that they happen across multiple disconnected tools, environments, and processes. Over time, the context switching, manual effort, repetitive validation cycles, and fragmented workflows consume far more time than the actual task of writing SQL.
Problems With the Current Oracle Fusion SQL Development Setup
1. Oracle Fusion’s Data Model Is Massive
If you’ve written Oracle Fusion SQL, you already know that one of the biggest challenges is figuring out where the data lives.
Unlike traditional operational databases, where you often have direct access to the schema and documentation, Oracle Fusion sits behind multiple abstraction layers. You frequently need to navigate thousands of tables and views spanning Finance, Projects, Procurement, HCM, SCM, CX, and other modules before you can even begin building a report.
Finding the right data is rarely straightforward.
For example, a seemingly simple business question, such as “Show me all active employees and their managers” or “List invoice approval details,” can require you to:
- Identify multiple tables,
- Understand how Oracle Fusion stores historical and current records,
- Validate security-enabled views, and
- Determine the correct join paths between business objects.
Oracle Fusion’s schema was designed to support a highly configurable enterprise application. So, you regularly encounter flexfields, translation tables, date-effective records, and module-specific naming conventions that are not always intuitive to someone unfamiliar with the platform.
As a result, a significant portion of report development time is often spent on schema discovery rather than query development. For teams that do not work with Oracle Fusion every day, this learning curve can be substantial. Even if you’re an experienced SQL developer, you find that understanding Oracle Fusion’s underlying data structures takes considerably longer than writing the query itself.
2. BI Publisher Was Never Designed to Be a Modern SQL IDE
BI Publisher is a powerful reporting platform, but it was never intended to function as a modern developer workspace.
If you’ve spent enough time writing Fusion SQL in the BI Publisher editor, you must be frustrated with:
- Small SQL editor windows
- Constant scrolling
- Limited formatting capabilities
- Lack of autocomplete feature
- Difficult organization of SQL files and report assets
- Limited support for collaborative development
- Weak debugging experience
- Lack of Git-based workflows
- Friction in day-to-day development
As queries become more complex, you can see that the productivity drops quickly. To work around some of
BI Publisher’s limitations, developers often write and test SQL in external tools like SQLConnect before moving it back into Oracle Fusion.
These tools can simplify query execution and troubleshooting, but they often introduce their own workflow constraints. You’d still find yourself switching between multiple tools, environments, and processes rather than working within a single, integrated development experience.
3. Testing Cycles Are Slow
Testing an Oracle Fusion SQL query isn’t as simple as clicking “Run” and reviewing the results.
A typical Fusion SQL testing workflow involves:
- Modifying the SQL,
- Executing the query,
- Reviewing the output,
- Validating that the results match the underlying business transaction,
- Fixing issues,
- Repeating the process until the query behaves as expected, and
- Testing in test and production environments.
The challenge is that even small changes can require another round of validation. This validation process is critical because many production reporting issues originate from error-prone or poorly optimized SQL. A new join, an additional filter, or a parameter adjustment can alter the results in unexpected ways.
For example, imagine you’re building a workforce headcount report and join an assignment table to pull each employee’s department, grade, or position. In Oracle Fusion, many of these tables are effective-dated (the _F suffix), meaning they store a separate row for every historical version of a record; every promotion, transfer, or salary change is kept as its own dated row.
The query executes successfully. No errors. The results look reasonable until the business starts validating the numbers.
HR notices the same employee appearing several times. Finance sees headcount inflated well above the actual roster. An executive dashboard suddenly reports more active employees than the organization actually has.
What happened?
Effective-dated tables store multiple versions of the same business object, one for each historical period, defined by EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. If a query does not restrict the join to a single point in time, For example:
AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE, an employee can be matched once for every version of their record, resulting in duplicate rows, inflated counts, and incorrect aggregations.
The problem is particularly difficult to diagnose because the query executes successfully and the results often appear reasonable at first glance. Identifying the root cause frequently requires validating record counts, comparing the output against existing reports, reviewing join conditions, and tracing the issue back to what initially seemed like a harmless, effective-dated join.
A small change intended to enrich a report with extra attributes can quickly turn into hours of troubleshooting.
Testing often remains a highly iterative process. Some teams report that they still spend a lot of time validating configurations, investigating discrepancies between environments, troubleshooting unexpected results, and performing manual data checks before they can confidently move a query forward.
4. Environment Management Becomes a Project of Its Own
If you’re an Oracle Fusion developer, you rarely work with a single environment. Before a query reaches production, you need to develop, test, validate, and compare across multiple Fusion pods such as DEV, TEST, UAT, and PROD.
In BI Publisher, this typically means logging into separate environments, locating the appropriate data model, executing the query, reviewing the results, and repeating the process in another pod to confirm consistency. If a report returns different results between environments, you must determine whether the issue is caused by data differences, configuration changes, security settings, parameters, or the SQL itself.
For example, a query may return 1,250 records in a TEST environment but 1,310 records in UAT. Before deployment, you need to determine whether the difference is expected, a configuration has changed, or the query itself needs adjustment. This often requires additional testing, manual comparisons, and coordination with business users or administrators.
These validation steps are necessary, but they consume time that could otherwise be spent developing and improving reports. Even external tools like SQLConnect require you to switch between instance IDs, credentials, and environments simply to validate changes.
5. Version Control Is Usually an Afterthought
Most Oracle Fusion SQL development workflows were never designed around modern source control practices. You still have to rely on a combination of local file copies, shared folders, email attachments, and collaboration tools to manage changes to SQL queries and BI Publisher data models.
For instance, it is common to find files named:
- Revenue_Report_Final.sql
- Revenue_Report_Final_v2.sql
- Revenue_Report_Final_v2_Updated.sql
Seems manageable, doesn’t it? What if the number of developers, reports, and environments grows? The following questions become harder to answer:
- Who changed this query?
- What exactly changed?
- When was it changed?
- Why was it changed?
- Which version is currently deployed in production?
- How do we roll back if something breaks?
Without a structured source-control process, you often spend time manually comparing files, tracking down the latest version of a query, or coordinating changes through meetings, emails, and chat messages.
While the rest of the engineering teams have largely standardized on Git-based workflows that enable developers to create branches, review changes through pull requests, maintain a complete history of modifications, collaborate safely across teams, and roll back to a previous version when needed, the Fusion SQL development remains outside the ecosystem.
The result? Your team misses out on the traceability, collaboration, governance, and deployment confidence that modern source control provides.
6. AI Tools Have Been Largely Out of Reach
With the rise of GitHub Copilot and Claude Code, AI-assisted development has transformed how developers write software, build data pipelines, and create SQL queries.
Today, developers routinely use AI to generate boilerplate code, explain unfamiliar logic, identify optimization opportunities, create test cases, and accelerate day-to-day development tasks. For many engineering teams, AI has become a natural part of the development workflow.
But the current Fusion SQL setup does not support it. The reasons?
- The query development often takes place inside BI Publisher or across a collection of disconnected tools that were never designed for AI-assisted workflows.
- Frequently switching between browser-based editors, local files, testing tools, and Oracle Fusion environments makes it difficult for AI assistants to understand the full context of the work being performed.
As a result, many Oracle Fusion developers are unable to take full advantage of the productivity gains that AI is delivering elsewhere in the organization. Tasks such as generating SQL, validating joins, explaining query logic, or optimizing existing reports often remain highly manual, despite the availability of tools that could significantly accelerate them.
The Solution? Bringing Oracle Fusion SQL Development Into VS Code
If you look closely at all the challenges we’ve discussed, a common theme emerges.
The problem is that Oracle Fusion SQL development happens across multiple disconnected tools, environments, and processes.
While the rest of the engineering codes are built inside a modern development environment that provides source control, AI assistance, environment management, collaboration, and deployment capabilities in a single workspace, why not Fusion SQL?
This is exactly where the BI Connector VS Code Extension comes in.
Instead of forcing you to jump between multiple tools, the extension brings the entire Oracle Fusion SQL development lifecycle directly into Visual Studio Code.
Why VS Code?
Today, most developers have standardized their engineering workflows on VS Code. By bringing Oracle Fusion SQL development into VS Code, they can work within the same environment they already use for data engineering, software development, and analytics projects while remaining connected to Oracle Fusion.
The result is a more centralized and developer-friendly workflow that eliminates much of the context switching that traditionally slows Oracle Fusion SQL development.
How BI Connector VS Code Extension Improves the Current Workflow
1. Discover Fusion Metadata Without Leaving Your Editor
As you already know, one of the biggest productivity challenges in Oracle Fusion development is schema discovery.
The BI Connector VS Code Extension brings that information directly into the development environment. You can browse tables, columns, indexes, and constraints without leaving VS Code, making it easier to understand Oracle Fusion’s data model while writing SQL.
Instead of jumping between documentation, existing reports, and external references, you can explore metadata in a workspace where the work is actually happening.
2. Bring Git-Based Development to Oracle Fusion SQL
The BI Connector VS Code Extension works naturally with Git-based workflows, allowing Oracle Fusion SQL development to align with the same source-control practices already used across software engineering, analytics engineering, and data platform teams.
Instead of relying on local file copies, shared folders, or manually maintained versions, teams can track changes, review code, compare revisions, and collaborate using established engineering processes.
3. Use AI tools like Claude Code and GitHub Copilot to Generate and Refine Fusion SQL
One of the most significant shifts in modern Fusion SQL development will be the AI-assisted coding. The BI Connector VS Code Extension integrates naturally with tools such as GitHub, Copilot, and Claude Code, allowing you to generate, refine, and optimize SQL without leaving your development environment.
Since you can access Oracle Fusion metadata directly within VS Code, AI tools have more context to work with when generating queries, suggesting joins, explaining logic, or helping troubleshoot issues.
This can be particularly valuable when navigating Oracle Fusion’s complex data model and spending hours trying to understand relationships between tables.
You can also use BI Connector’s Oracle Fusion Query Vault, which provides a library of ready-to-use SQL queries for Finance, HCM, and SCM that you can customize for your own reporting requirements.
4. Connect Development and Deployment
Development and deployment are often treated as separate activities in Oracle Fusion reporting workflows. The BI Connector VS Code Extension helps bridge that gap by allowing you to publish queries directly as BI Publisher data models from within VS Code.
Instead of moving between multiple tools to complete the development cycle, you can move from writing and testing SQL to publishing it within the same workspace.
The result is a more connected workflow with fewer manual steps, less context switching, and a development experience that feels much closer to the tools and practices used across the rest of the engineering organization.
Why This Matters Beyond SQL Development – The Modernization Factor
At first glance, this might seem like a better way to write Oracle Fusion SQL. In reality, the impact goes much further.
As Oracle Fusion data increasingly feeds data warehouses, Power BI dashboards, Tableau reports, analytics platforms, and AI data pipelines, Fusion SQL development can no longer exist in a silo. It needs to fit into the same engineering ecosystem as the rest of the organization.
Bringing Oracle Fusion SQL development into VS Code helps make that possible.
1. Better Collaboration Across Teams
Oracle Fusion reporting is no longer owned solely by ERP teams. Today, multiple teams often work with the same data:
- Oracle Fusion developers
- Data engineers
- Analytics engineers
- BI developers
- Data architects
Working from a common development environment makes collaboration easier and reduces handoffs between teams.
2. Reduced Dependency on Tribal Knowledge
Many organizations have a small number of Oracle Fusion specialists who understand the underlying schema and reporting architecture. A modern development workflow makes it easier to:
- Share knowledge
- Document logic
- Onboard new developers
- Reduce reliance on a handful of experts
3. Improved Governance and Maintainability
As reporting portfolios grow, managing hundreds of SQL queries and data models becomes increasingly difficult. Standardized development practices help teams:
- Track changes more effectively
- Maintain consistency across reports
- Reduce operational risk
- Manage reporting assets at scale
4. Better Alignment with Enterprise Data Initiatives
Most organizations are investing in:
- Data warehouses
- Microsoft Fabric
- Azure Data Factory
- Snowflake
- Databricks
- AI and analytics initiatives
Bringing Oracle Fusion SQL development into the same engineering workflow ensures reporting development evolves alongside your organization’s broader data platform strategy rather than remaining an isolated process.
Ultimately, this is not just about writing SQL faster. It’s about making Oracle Fusion reporting development easier to scale, maintain, and integrate with the rest of the modern data ecosystem.
Oracle Fusion SQL Development Is Entering a New Era
For a long time, Oracle Fusion reporting development followed its own path. While software engineering, data engineering, and analytics teams adopted modern development environments, Oracle Fusion SQL development remained tied to reporting-centric tools and workflows.
That gap is beginning to close.
The BI Connector VS Code Extension allows Oracle Fusion reporting to participate in the same ecosystem of tooling, collaboration, automation, and AI that has transformed the way modern engineering teams work.
This is more than a new SQL editor. It represents a shift in how Oracle Fusion reports are developed, maintained, and delivered.
More importantly, Oracle Fusion reporting no longer exists in isolation. The SQL queries and BI Publisher data models that you build with the BI Connector VS Code Extension often serve as the foundation for downstream analytics, reporting, and data integration initiatives.
You can use the Oracle Fusion data to build pipelines to BI tools like Power BI or Tableau, move data to data platforms and warehouses like Microsoft Fabric, Azure Data Factory, Snowflake, and Databricks.
So, if your organization is investing in modern data platforms, analytics initiatives, and AI-driven solutions, Oracle Fusion reporting can become a more integrated part of the broader data and engineering landscape.
What’s Next?
If you’re evaluating ways to modernize Oracle Fusion SQL development, the next step is understanding how different tools compare.
If you’d like to explore the extension itself, learn more about the BI Connector VS Code Extension for Oracle Fusion developers. You can also download the extension from the Visual Studio Marketplace or add the BI Connector extension to your VS Code from within the VS Code application.
