FusionRelay Explorer - Oracle Fusion SQL/Data Extension

Run SQL against Oracle Fusion apps databases (through BI Publisher) directly from VS Code. Use advanced features like an AI DB schema agent to generate SQL, autocomplete, data viewer, environment comparison, and more to enhance your SQL experience.

Features

• Run SQL Queries: Write and execute SQL queries directly from the editor against configured Fusion environments. Results display in interactive table below the query.
• SSO Authentication: Authenticate to Fusion environments using single sign-on (SSO) where supported; no extra licensing required for SSO access.
• Fetch Database Tables & Views: Retrieve and display lists of tables and views from an environment and refresh them from the sidebar.
• Search Tables, Views, and Columns: Search the database schema for specific tables or views, their columns, and desciptions (for tables/columns)
• Intellisense Autocomplete for SQL: Context-aware autocomplete for Oracle SQL keywords, table/view names (in FROM/JOIN/comma contexts), and columns (after typing alias. or table.). Autocomplete updates after typing a space or period.
• Data Viewer: View the results in an advanced table view with filters, jump to column, single row view, and sorting. Also export data to Excel, CSV, or open in Data Wrangler.
• Query Options: Configure query options in the sidebar, including Max Rows (wraps queries with a fetch-first limiter) and execution mode.
• Browse BI Publisher (BIP Explorer): Browse the BI Publisher catalog for an environment, and view folders and their contents.

Paid Features:

• Background Query Execution: Run long-running or large-result queries in background mode using the BI Publisher scheduler; results are downloaded when complete.
• Compare Environments: Run comparison queries against a source and target environment, select columns to compare, and view differences between result sets. Useful for comparing configurations and data sets.
• Open Data Model SQL: Download and open SQL from data model objects found in the BIP Explorer (supports picking among multiple SQL queries when present).
• Run BIP Reports: Execute reports from the BIP Explorer (with parameter prompts when required) using scheduled runs and download results.
• Run BICC PVOs (Coming Soon): Execute BICC PVOs from the extension and view the results in the data viewer. Coming in a future release.

AI Features (Requires GitHub Copilot as well as FusionRelay License):

• FusionSQLExpert Agent: A schema aware AI agent that understands your Oracle Fusion data model and writes accurate SQL for you. Available in the GitHub Copilot chat sidebar as a chat mode.
  • Write SQL from natural language descriptions - describe what data you need and get production ready SQL back instantly.
  • Automatic schema exploration - the agent inspects your connected Oracle Fusion schema to find the right tables and columns before generating queries.
  • One click query execution - let the agent run queries and display results directly in the FusionRelay results panel without leaving the chat.
  • Custom instructions - teach the agent about your environment with persistent instructions so it tailors output to your needs.

Licensing Details

• Some features (noted) require a paid license to use.
• No core feature is locked behind this to ensure everyone can enhance their SQL experience with Oracle Fusion apps.
• License details and purchases can be found here: FusionRelay.com

IMPORTANT Initial Setup Step

To use the extension, you must download the required BIP archive file and unarchive it in the /Shared/Custom folder of each BIP environment you want to use with FusionRelay Explorer.

• Download BIP Archive

After unarchiving, the extension will be able to access and run reports from those environments.

Commands

You can access the commands via the command palette (Ctrl+Shift+P or Cmd+Shift+P on Mac) by typing "FusionRelay Explorer" or using the menu in the sidebar.

• Run SQL Query: Runs a query against a selected environment.
• Run Comparison Query: Runs a query against a source and target environment, compares the results, and returns the differences based on selected columns.
• Fetch Database Tables: Fetches and displays the list of database tables.

Usage

1. Run SQL Query:

   • Open a SQL file or create a new one.
   • Write your SQL query.
   • Click the Run icon in the top right of the editor OR press Ctrl+Shift+P and type FusionRelay Explorer: Run SQL Query.
   • Select the environment to run the query against.
   • If you add parameters (like :P_Param1) to your sql, it will prompt you to fill out the values (these are cached by paramater name across queries as suggestions going forward)

2. Run Query in Background (Paid):

   • In the sidebar in the Query Options section, you can change the Execution Mode to Background
   • This will use the BIP Scheduler to run the query asynchronously, and when complete download the results
   • Allows for queries that either time out or return a result larger than the standard Syncronous mdoe allows

3. Compare Environments (Paid):

   • Open a SQL file or create a new one.
   • Write your SQL query.
   • Click the Comparison Query icon in the top right of the editor OR press Ctrl+Shift+P and type FusionRelay Explorer: Run Comparison Query.
   • Select the source and target environments.
   • Select the columns to compare on.

4. Fetch Database Tables:

   • Open the FusionRelay Explorer tab in the sidebar.
   • Expand Tables.
   • Click Refresh Tables.

5. Fetch Database Views:

   • Open the FusionRelay Explorer tab in the sidebar.
   • Expand Views.
   • Click Refresh Views.

6. Autocomplete:

   • Refreshes and shows after a space or period is typed
   • SQL Keywords will always show in autocomplete
   • Table and Views will show while in a FROM section of any query or subquery following FROM, JOIN, or a comma (for tables joined by where statements)
   • Columns of both Views and Tables will show after typing the table/view name or alias and then a period. So like in the example it would show after the select t1 here: SELECT t1. FROM table1 t1

7. Options:

   • In the sidebar of FusionRelay Explorer expand the OPTIONS menu which is at the bottom
   • Max Rows
     • By default with nothing entered is 100 if turned on
     • Adds a wrapper query with a "fetch first 100 rows only" to limit the output

• Execution Mode
  • Synchronous (default)
  • Background (Paid) - uses BIP scheduler to run the query asynchronously

8. Browse BI Publisher:

   • Open the FusionRelay Explorer tab in the sidebar
   • Expand the "BIP Exploer" section
   • Choose an environment and browse through the folders of the BIP catalog

9. Open Data Model SQL (Paid)

   • While in the BIP Explorer
   • Open a data model object
   • It will download the object and open the SQL in a new tab
   • If there are multiple SQL queries in the DM, it will let you choose which to open

10. Run BIP Report (Paid)

• While in the BIP Explorer
• Click on a Report object
• You will be prompted if you want to run the reports and for any parameters the report has
• It will run as a scheduled report immediately, download, and display the data results

11. Use FusionSQLExpert AI Agent (Requires GitHub Copilot)

• Open the GitHub Copilot Chat sidebar
• In the mode dropdown, choose the FusionSQLExpert agent
• Describe what data you need in plain English, for example: "Show me all open purchase orders for supplier Acme Corp from the last 90 days"
• The agent will explore your schema, generate a SQL query, and optionally execute it
• Results from agent-executed queries appear automatically in the FusionRelay results panel
• You can also copy the generated SQL into your editor and run it manually

Quick Setup Guide - FusionRelay Explorer

First Time Setup

Note:
Before proceeding, ensure you have uploaded the FusionRelay folder (containing its reports) to the /Shared/Custom directory of your BI Publisher environment. This step is required for the extension to access and run reports. Complete this upload before starting configuration or running queries. Instructions above.

1. Configure Your Environments

Open Siderbar and Run, then Add Environments

OR

Open VS Code Settings (⌘+, or Ctrl+,) and search for "FusionRelay Explorer" Add your Oracle Fusion environments:

{
  "fusionRelayExplorer.environments": [
    {
      "displayName": "DEV",
      "host": "mycompany-dev.fa.us6.oraclecloud.com"
    },
    {
      "displayName": "PROD",
      "host": "mycompany.fa.us6.oraclecloud.com"
    }
  ]
}

Important:

• Host should NOT include https://
• Host must end at .com

2. Run Your First Query

1. Open or create a .sql file
2. Write your SQL query
3. Run command: FusionRelay Explorer - Run SQL Query
4. Select environment
5. Authenticate when prompted (first time only)

Authentication

How It Works

1. First query triggers authentication
2. Cross platform app auto-installs (if needed)
3. Browser opens for Oracle SSO
4. Token cached locally
5. Token auto-refreshes when expired

Quick Commands

You can add these as keyboard shortcuts in VS Code for easy access. To do so, got to Settings → Keyboard Shortcuts and search for the command name.

Configuration Options

Max Rows Limit

In the extension sidebar → Options:

• Toggle "Max Rows" to limit results
• Set number of rows (default: 100)
• Applies FETCH FIRST n ROWS ONLY to queries

Override BI Publisher Folder Path

To use a custom BI Publisher folder path, set the fusionRelayExplorer.customReportPath in VS Code settings.

• For if you need to unarchive the BIP catalog to a different location than the default /Custom/FusionRelay
• Example: /Custom/MyOrg/FusionRelay
• You may need to edit the report in the folder to enure it is pointing to the correct data model after moving to a custom path

Common Tasks

Execute a Query

SELECT * FROM employees WHERE department = 'IT'

1. Select the SQL
2. Run command or right-click → Run SQL Query
3. Choose environment
4. View results in CSV viewer

Compare Environments

SELECT employee_id, name, salary FROM employees

1. Run "Run Comparison Query"
2. Select source environment (e.g., DEV)
3. Select target environment (e.g., PROD)
4. Choose columns to compare
5. View differences

Use Parameters

SELECT * FROM employees 
WHERE hire_date > :start_date 
AND department = :dept_name

1. Run query
2. Extension prompts for :start_date and :dept_name
3. Enter values
4. Query executes with substitutions

Output Files

Results are cached to:

~/Downloads/FusionRelayExplorer Temp Outputs/

Format: Output_YYYY-MM-DD_HH-MM-SS.csv

Files auto-delete after 24 hours.

Export Options

From CSV viewer:

• Export as CSV: Save copy anywhere
• Export as Excel: Convert to .xlsx
• Open in Data Wrangler: Advanced data analysis

Troubleshooting

"No environments configured"

→ Add environments in VS Code settings or in sidebar

Authentication fails

→ First install takes ~30 seconds → Check internet connection → Check host format (no https://, ends at .com) → Verify network connection → Try deleting token file and re-authenticating

Query errors

→ Verify SQL syntax → Check table/column names → Review error message details

Tips & Tricks

1. Table Search: Use search to quickly find tables in sidebar
2. Column Copy: Right-click columns to copy names
3. Multiple Queries: Use parameters to run same query with different values
4. Large Results: Enable "Max Rows" for big datasets
5. Background Execution: Use paid background mode for long queries
6. Jump to Column: Use the Jump to Column feature in Results viewer for quick navigation

Additional Resources

• Extension Settings: FusionRelay Explorer Sidebar → Main Section → Settings
• Command Palette: Cmd/Ctrl+Shift+P → "FusionRelay"
• Sidebar: Activity Bar → FusionRelay Explorer icon

Bugs

• If you encounter any bugs, you can report them to bugs@fusionrelay.com