How to Run SQL Queries on Oracle Fusion Cloud: A Developer's Guide

Oracle Fusion Cloud has one of the most complex schemas in enterprise software - tens of thousands of tables, widespread use of date-effective records, and multi-org data structures. Writing SQL against it requires understanding both how to access the data and how the schema is structured.

This guide covers the primary methods for running SQL on Oracle Fusion Cloud, the key schema patterns every developer needs to know, and practical query examples to get started.

How to Access Oracle Fusion Data with SQL

There are four main methods for running SQL queries against Oracle Fusion Cloud data:

BI Publisher Data Models

The most common method for production reporting. BI Publisher data models accept SQL queries and run them in the context of a named database connection - typically the FUSION JDBC connection. The query runs with the BI Publisher service account's database privileges.

-- Example BI Publisher data model query
SELECT p.full_name, a.assignment_number
FROM per_all_people_f p
JOIN per_all_assignments_m a ON a.person_id = p.person_id
WHERE TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
  AND TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
  AND a.primary_flag = 'Y'

OTBI Direct Database Analysis

OTBI Answers generates SQL automatically from subject area selections. You can view the generated SQL using the Advanced tab - a useful way to discover table names and join conditions before writing direct SQL.

Oracle SQL Developer (Direct DB Connection)

For development and testing, Oracle SQL Developer can connect directly to the Fusion database (with appropriate network access and credentials). This is typically used by implementation consultants and database administrators - not available in all cloud configurations.

FusionLens SQL (SQL Editor for Oracle Fusion Cloud)

A SQL editor that connects directly to Oracle Fusion Cloud — with a schema browser, autocomplete for Fusion tables and columns, and one-click BI Publisher-ready SQL output.

Key Oracle Fusion Schema Patterns

Oracle Fusion uses consistent naming conventions that tell you how to query a table correctly before you look at its structure.

Pattern	Meaning	Required Filter
_F suffix	Date-effective - stores one row per date range	BETWEEN effective_start_date AND effective_end_date
_M suffix	Versioned - also date-effective, common in HCM	BETWEEN effective_start_date AND effective_end_date
_ALL suffix	Multi-org - spans all business units and org units	Filter by ORG_ID for a specific business unit
_B suffix	Base table - language-independent data	Join to _TL table for translated text fields
_TL suffix	Translation table - holds language-specific names	AND LANGUAGE = 'US'

The Effective-Date Join Pattern

The most important pattern to master in Oracle Fusion SQL. Always apply it to every date-effective table in your query:

-- Current record only
WHERE TRUNC(SYSDATE) BETWEEN table.effective_start_date
                          AND table.effective_end_date

-- Record as of a specific date
WHERE :as_of_date BETWEEN table.effective_start_date
                       AND table.effective_end_date

-- All records in a date range (history report)
WHERE table.effective_start_date <= :end_date
  AND table.effective_end_date   >= :start_date

Forgetting this predicate is the most common cause of unexpected row multiplication in Oracle Fusion SQL queries.

Starter Queries to Explore the Schema

Find Tables by Keyword

SELECT table_name, num_rows
FROM all_tables
WHERE table_name LIKE '%SUPPLIER%'
  AND owner = 'FUSION'
ORDER BY num_rows DESC NULLS LAST

Explore Table Columns

SELECT column_name, data_type, data_length, nullable
FROM all_tab_columns
WHERE table_name = 'PO_HEADERS_ALL'
  AND owner = 'FUSION'
ORDER BY column_id

Find Foreign Key Relationships

SELECT
    ac.table_name          AS child_table,
    acc.column_name        AS child_column,
    arc.table_name         AS parent_table,
    arcc.column_name       AS parent_column
FROM
    all_constraints ac
    JOIN all_cons_columns acc
        ON acc.constraint_name = ac.constraint_name
    JOIN all_constraints arc
        ON arc.constraint_name = ac.r_constraint_name
    JOIN all_cons_columns arcc
        ON arcc.constraint_name = arc.constraint_name
WHERE
    ac.constraint_type = 'R'
    AND ac.table_name = 'PO_LINES_ALL'
ORDER BY
    acc.column_name

Most Common Oracle Fusion SQL Mistakes

Missing effective-date predicates on _F and _M tables

Returns one row per historical version of each record. A 1,000-person headcount query may return 15,000 rows if each person averaged 15 date changes over their employment history.

Querying _ALL tables without ORG_ID filter

Returns data from all business units and operating units. Always add org_id or business unit filter when the report is for a specific legal entity.

Joining _B tables without the _TL translation table

Text fields like names and descriptions often live in the _TL translation table. Querying the _B base table alone returns NULL for these columns.

Explore Oracle Fusion SQL with FusionLens SQL

🔍

Schema Navigator

Browse Oracle Fusion tables with column descriptions and data types - no documentation hunting required.

⚡

SQL Autocomplete

Autocomplete that knows Oracle Fusion table names, column names, and syntax — so you spend less time guessing and more time writing.

🔒

Secure Connectivity

Connect to Oracle Fusion securely - credentials are encrypted and never stored alongside query history.

Try FusionLens SQL Free