If you are a developer or a power user who has spent real time in building queries, reports or even troubleshooting data in Oracle Fusion, you are already know that there's no easy way to run a SQL query against Fusion database and see the results. The only out-of-the-box way is to create a data model in BI Publisher and test your queries, lookup and analyze the data.
This is because Fusion is SaaS and we don't get a database connection like other on-prem systems might be able to provide; but this limitation slows us down since we have to go through multiple steps just to query the data.
I'd been wondering what if there's a simple tool that lets you connect to Fusion environments, run your queries against it and simply see the results. A simple basic framework that just works.
I'd been working on building such a solution and it's now live for everyone to use.
Introducing Fusion Query Studio - A VS Code extension that lets you connect to a Fusion environment and run your queries against it from within the editor.
Fusion Query Studio features
Multiple saved connections: Store your multiple Fusion environments (Dev, Test, and Prod etc.) side by side with passwords encrypted in the Windows Credential Vault.
Live results table: The results table provides features such as sort by columns, null values highlighting etc.
Query history: The extension stores your last 50 queries automatically. You can access them from Query History panel and re-run without retyping it.
Simple CSV export: Export the query results to a file with a single click.
AI Chat integration: Ask VS Code's native AI Chat (GitHub Copilot) to build and run a query for you through the extension. AI will ask/tell you which connection it used, show you the actual SQL it built, and it will run it in background using the Fusion Query Studio extension and present you the results. You can also copy-paste the AI-generated query in the Query Editor panel, run it and see the results in the results panel.
SSO-based login: This feature is not available in the current version but it's on the roadmap.
Getting Started
1. Install the extension:
Search for Fusion Query Studio in VS Code Marketplace and Install the extension
Install:
2. Launch the extension:
You'll see the Fusion Query Studio icon show up in the activity bar on the left side of VS Code.
3. Add a connection:
In the Connections panel, click the + button.
Follow the 4 step wizard and provide below details:
Connection name: Connection name of your choice.
Base URL: Your Fusion environment's URL. e.g. https://fa-ebfa-dev1.us1234.oraclecloud.com
If you want to use Basic Authentication, select Basic Auth method:
Username: User name of the account under which the SQL queries will be executed.
Password: Password of the above user. This gets encrypted and stored in the Windows Credential Vault. It's never written to disk in plaintext.
You will see a confirmation that says the connection is saved.
If you want to use Single sign-on (SSO) method, select SSO method:
You will be presented with a browser pop-up and will be taken to the login page of your Fusion POD. Login using your SSO credentials, as you usually do to login to your Fusion environment:
Once the login is successful, the browser window will be automatically closed and you will see a confirmation message in the VS Code:
Please Note: In case you see an error message that says 'Could not verify/deploy utility report', then you may need to verify a few things:
- Verify if your connection details are correct (Base URL, Username and Password)
- Verify that the User account you are using has roles/permissions to create/execute BI publisher reports.
4. Test the connection:
Click ▶ icon in front of your connection name (Fusion_Dev in my example) to test the connection.
If there are no errors, we should see the confirmation message.
5. Edit / Delete the connection:
You can Edit or Delete the connection by clicking the respective icons in front of your connection name.
6. Open the Query Panel:
Click Open Query Panel icon ▶ in the toolbar to open the Query Panel. This is the most easy and common way to access the query panel.
Optionally, you can run the command Fusion Query Studio: Open Query Panel from the Command Palette (Ctrl+Shift+P) to do the same.
From Toolbar:
From Command Palette:
You should be presented with the Query Panel as shown below:
7. Run a query: Select the desired connection from drop-down, write your SQL in the top panel and hit the Run button (or press Ctrl+Enter).This will execute your SQL query and present the results to you in the results panel.
Things to remember:
- You can control how many rows come back by adjusting the Limit value.
- Please skip the trailing semicolon. Don't end your query with ;
- Click Cancel to stop any running query.
- Adjust the divider between the editor and the results pane to resize it.
8. Let AI Chat write the query for you:
Open VS Code's built-in AI Chat (using GitHub Copilot) and describe your requirements. Fusion Query Studio seamlessly plugs into the AI Chat of VS Code.
It will ask you which saved connection to use, will build the query and show it to you before running it, and finally present you the results.
- Describe the requirements of your query and ask it to run it using using Fusion Query Studio:
- Review the Connection details, verify the query and allow AI Chat to run the query using Fusion Query Studio:
- AI Chat will show you the entire query, run it against the selected connection and show you the results:
Query:
Results:
- You can also copy the AI-generated query and run it manually in the Fusion Query Studio
Copy the query:
Paste it in Query Panel:
Run the query and see the results:
- You can click on CSV button to download the query result in a CSV format
- Query History panel stores your last 50 queries. You can click on any of them and they will be automatically pasted in Query Panel so that you can rerun without rewriting them.
Try it out
If you're building against Oracle Fusion Cloud environments and you're constantly following multi-step process in BI Analytics, just to test your query or validate the data - give Fusion Query Studio a shot. It's available for free on the VS Code Marketplace. If you run into anything or have ideas for what it should do next, please reach out to me at amodjjoshi@gmail.com. Happy Querying 😊
