Oracle Cloud SQL Studio: SQL Developer like Tool for Cloud ERP

| 3 minutes of reading | Getting Started Guides, Oracle Cloud ERP Automation, Oracle Cloud SQL Studio

BI Publisher limitations, ERP data management, ERP SQL querying, Fusion Toolkit, Fusion Toolkit license, Oracle BI Publisher, Oracle Cloud automation, Oracle cloud configuration, Oracle cloud data export, Oracle Cloud ERP, Oracle Cloud SQL Studio, Oracle ERP integration, Oracle ERP reporting, Oracle query optimization, Oracle SQL queries, portable SQL tool, SELECT SQL queries, SQL Developer alternative, SQL query automation, SQL query tool

Oracle Cloud SQL Studio, integrated with the powerful Fusion Toolkit, revolutionizes the way ERP consultants, developers, and IT administrators interact with Oracle Cloud ERP data. This user-friendly, portable Windows application eliminates the constraints of BI Publisher, providing a familiar, intuitive querying environment similar to Oracle’s popular SQL Developer.

This article guides you step-by-step through the process of configuring, launching, and efficiently using Oracle Cloud SQL Studio to execute SELECT-only SQL queries directly against your Oracle Cloud ERP instance.

Disclaimer: Oracle and SQL Developer are registered trademarks of Oracle Corporation. Fusion Toolkit and Oracle Cloud SQL Studio are independent products and not affiliated with or endorsed by Oracle.

Read the prerequisite for using fusion toolkit and more in the documentation.

Configuring Oracle Cloud SQL Studio

After downloading, extract your Fusion Toolkit to a folder on your local Windows machine where you have full read/write permissions.

Navigate into the Oracle_Cloud_SQL_Studio folder. Here, you’ll configure the Oracle environment credentials and your Fusion Toolkit license:

java -jar fusion-cli-toolkit.jar set-config \
    --username="YOUR_ORACLE_CLOUD_USERNAME" \
    --password="YOUR_STRONG_PASSWORD" \
    --hostname="https://YOUR_ORACLE_CLOUD_ENVIRONMENT.fa.ocs.oraclecloud.com:443" \
    --licenseKey="YOUR_ACTIVE_FUSION_TOOLKIT_LICENSE"

Upon successful configuration, you’ll receive confirmation:

Configuration saved successfully.

This setup ensures secure, encrypted storage of your sensitive credentials locally.

Launching Oracle Cloud SQL Studio

Now, launching Oracle Cloud SQL Studio is straightforward:

  1. Navigate to your Oracle_Cloud_SQL_Studio folder.
  2. Double-click on the file named Run_Oracle_Cloud_SQL_Studio.bat.

This opens the portable Oracle Cloud SQL Studio application, ready to connect to your specified Oracle Cloud ERP environment without additional installation.

Screenshot of Oracle Cloud SQL Studio - SQL Developper alternative by Swiss Fusion Toolkit

Running and Managing Queries

Upon launching, Oracle Cloud SQL Studio provides a streamlined interface familiar to users of Oracle’s SQL Developer.

Oracle Cloud SQL Studio comes pre-loaded with commonly used queries. Feel free to modify them or run them as is.

Creating Your Own Queries

To start a new SQL query:

  • Click the “New Query” button in the toolbar.
  • Enter your SELECT-only query into the intuitive editor.

Example Queries:

Query 1: Extract all your configured geographies and territories:

SELECT
    geo.GEOGRAPHY_ID, geo.COUNTRY_CODE, geo.PRIMARY_GEOGRAPHY_NAME,
    geo.LANGUAGE_CODE, geo.IDENTIFIER_VALUE, geo.*, terr.*, id.*
FROM
    HZ_EXP_GEOGRAPHIES_V geo
    LEFT JOIN HZ_EXP_TERR_GEOGRAPHIES_V terr on geo.geography_id = terr.geography_id
    LEFT JOIN HZ_GEOGRAPHY_IDENTIFIERS_VL id on geo.geography_id = id.geography_id
ORDER BY geo.geography_id DESC;

Query 2: Display all HCM actions available in your environment:

SELECT
    b.ACTION_CODE, b.ACTION_TYPE_CODE, tl.ACTION_NAME, tl.DESCRIPTION
FROM PER_ACTIONS_b b
INNER JOIN PER_ACTIONS_TL tl on b.ACTION_ID = tl.ACTION_ID  
WHERE tl.LANGUAGE='US';

Step 5: Executing Queries

After crafting your query, execute it using one of two methods:

  • Click the “Run Query” button in the toolbar.
  • Use the keyboard shortcut Ctrl + Enter.

Query execution typically completes within seconds. Oracle Cloud SQL Studio leverages Fusion Toolkit’s optimized interaction with Oracle BI Publisher to deliver swift performance.

Viewing Results

Results appear clearly structured in the “Results” table beneath your query editor. Column headers reflect your query’s selected fields, and rows display retrieved data.

Automatically, each query execution saves the results in CSV format within the SQL subfolder for future reference or further analysis.

Step 6: Handling Errors and Debugging

Should an issue occur, error messages provide clarity:

  • Displayed prominently above the results table.
  • Logged thoroughly within the “Log” panel.

This robust error handling significantly reduces debugging time by clearly indicating SQL syntax or logic errors.

Step 7: Benefits of Using Oracle Cloud SQL Studio

  • Bypass BI Publisher constraint: Automate the whole process of creating a data model and report to run a SELECT query.
  • Efficiency: Rapid SQL querying and result extraction for immediate analysis.
  • Ease of Use: Intuitive GUI with familiar SQL Developer-like functionality.
  • Secure Local Execution: Data never leaves your infrastructure.
  • Integrated Error Handling: Clear, immediate feedback on query issues.

Oracle Cloud SQL Studio, combined with Fusion Toolkit, delivers unparalleled efficiency and control in querying Oracle Cloud ERP data. By eliminating traditional barriers posed by BI Publisher and providing a secure, intuitive, and familiar interface, the toolkit significantly enhances productivity and accuracy.

Start leveraging Oracle Cloud SQL Studio today and experience next-generation querying tailored specifically for Oracle Cloud ERP.

Related Posts

Scroll to Top