FusionRelay Explorer - Oracle Fusion SQL/Data Extension

FusionRelay Explorer allows exploring Oracle Fusion's data via SQL directly from your editor and more!

Features

• Run SQL Queries: Write and execute SQL queries directly from the editor against configured Fusion environments. Results display in a split view or a new tab.
• 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, 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 result View (Split Screen or New Tab).
• 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.

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.
   • Press Ctrl+Shift+P and type FusionRelay Explorer: Run SQL Query or open the FusionRelay Explorer tab in the sidebar and click Run 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.
   • Press Ctrl+Shift+P and type FusionRelay Explorer: Run Comparison Query.
   • Select the source and target environments.
   • Select the columns to compare.

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
   • View
     • Split Screen (default) or New Tab changes how the query results table will display

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

Quick Setup Guide - FusionRelay Explorer

🚀 First Time Setup

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": "DEV2",
      "host": "ejmmdev2.fa.us6.oraclecloud.com"
    },
    {
      "displayName": "PROD",
      "host": "ejmmproduction.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)

📝 Environment Configuration

Quick Access

• Open extension sidebar → Options → "Configure Environments" button
• Or: Settings → Extensions → FusionRelay Explorer

Example Configurations

Single Environment:

{
  "fusionRelayExplorer.environments": [
    {
      "displayName": "Production",
      "host": "mycompany.fa.us6.oraclecloud.com"
    }
  ]
}

Multiple Environments:

{
  "fusionRelayExplorer.environments": [
    {
      "displayName": "DEV",
      "host": "dev.fa.us6.oraclecloud.com"
    },
    {
      "displayName": "TEST",
      "host": "test.fa.us6.oraclecloud.com"
    },
    {
      "displayName": "PROD",
      "host": "prod.fa.us6.oraclecloud.com"
    }
  ]
}

🔐 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

🔧 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

View Options

Choose where results open:

• Split Screen: Opens beside current editor
• New Tab: Opens in separate tab

🎯 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 saved 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

📚 Additional Resources

• Extension Settings: Settings → FusionRelay Explorer
• 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