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

Oracle Fusion Cloud lets report developers write physical SQL against application tables, but not in the same way as an on-premises Oracle database. In a normal Fusion SaaS tenant, you do not open SQL Developer, point it at the production database listener, and sign in with a database account. The supported reporting path for physical SQL is Oracle Analytics Publisher / BI Publisher, using the application database data sources exposed inside the Fusion environment.

That distinction matters. OTBI uses a semantic model and generates logical SQL; BI Publisher can execute physical SQL against Fusion application objects; BICC is designed for larger outbound extracts; and tools such as FusionLens SQL can provide a developer-friendly SQL editor while still working through Fusion's reporting layer rather than a customer-accessible database listener.

The Supported Ways to Work with Fusion Data

BI Publisher: the physical SQL path

In Fusion Cloud, BI Publisher data models can contain SQL Query data sets. The data source depends on the application pillar. Common Oracle-documented examples include ApplicationDB_HCM for Human Capital Management and ApplicationDB_FSCM for Financials, Supply Chain, Project Management, and Procurement. Sales uses its own application data source.

-- BI Publisher / ApplicationDB_HCM
SELECT p.person_number,
       n.display_name,
       a.assignment_number
FROM   per_all_people_f p
JOIN   per_person_names_f n
       ON n.person_id = p.person_id
      AND n.name_type = 'GLOBAL'
      AND TRUNC(:p_as_of_date) BETWEEN n.effective_start_date
                            AND n.effective_end_date
JOIN   per_all_assignments_m a
       ON a.person_id = p.person_id
      AND a.assignment_type IN ('E','C','N','P')
      AND a.effective_latest_change = 'Y'
      AND TRUNC(:p_as_of_date) BETWEEN a.effective_start_date
                            AND a.effective_end_date
WHERE  TRUNC(:p_as_of_date) BETWEEN p.effective_start_date
                          AND p.effective_end_date

The source version incorrectly selected PER_ALL_PEOPLE_F.FULL_NAME. Person display/full names belong to PER_PERSON_NAMES_F (or a related name view), so the corrected starter query joins the name object explicitly.

OTBI: useful, but it is not direct database SQL

OTBI analyses are built from subject areas in the BI semantic layer. The Advanced tab shows the logical SQL sent to the BI Server. That SQL references presentation-layer subject-area columns, not physical Fusion table names in the same form used by BI Publisher. BI administrators can inspect session logs to see the physical SQL generated downstream, but the Advanced tab itself is not a physical-SQL discovery tool.

SQL Developer: where it does fit

Oracle documents SQL Developer access for databases that customers actually own or are explicitly given credentials for — for example an Autonomous Data Warehouse used by Fusion Data Intelligence. That is different from the source Fusion SaaS transactional database. If your organization extracts or replicates Fusion data into ADW, ATP, an on-premises Oracle database, or another warehouse, SQL Developer can of course query that target normally.

Schema Naming Patterns: Useful Hints, Not Universal Rules

Fusion object suffixes are helpful, but they are conventions rather than a substitute for reading the object definition. The safest workflow is: use the suffix as a clue, then confirm the object's columns, primary key, foreign keys, and view definition in Oracle documentation.

Effective-Date SQL: Current, As-of, and History Are Different Questions

A date-effective object stores versions across time. The query must decide whether it needs today's state, an as-of snapshot, or every historical period. For multi-change objects such as PER_ALL_ASSIGNMENTS_M, an as-of date can still intersect more than one physical same-day row unless the latest-change logic is included.

Current / as-of snapshot

-- Bind :p_as_of_date as a DATE
WHERE TRUNC(:p_as_of_date) BETWEEN x.effective_start_date
                        AND x.effective_end_date

PER_ALL_ASSIGNMENTS_M final state for the effective day

WHERE TRUNC(:p_as_of_date) BETWEEN a.effective_start_date
                        AND a.effective_end_date
AND   a.effective_latest_change = 'Y'

History overlapping a report range

WHERE x.effective_start_date <= :p_end_date
AND   x.effective_end_date   >= :p_start_date

Three Safe Starter Queries

The original guide used ALL_TABLES, ALL_TAB_COLUMNS, and ALL_CONSTRAINTS as if database dictionary access were a guaranteed Fusion SaaS feature. That is not a portable assumption for a managed reporting connection. Use Oracle's published Tables and Views metadata — or a schema navigator — for discovery, then test known application objects with small, scoped SQL.

1. HCM: current worker assignments

SELECT p.person_number,
       n.display_name,
       a.assignment_number,
       a.assignment_status_type
FROM   per_all_people_f p
JOIN   per_person_names_f n
       ON n.person_id = p.person_id
      AND n.name_type = 'GLOBAL'
      AND TRUNC(:p_as_of_date) BETWEEN n.effective_start_date
                                   AND n.effective_end_date
JOIN   per_all_assignments_m a
       ON a.person_id = p.person_id
      AND a.assignment_type IN ('E','C','N','P')
      AND a.effective_latest_change = 'Y'
      AND TRUNC(:p_as_of_date) BETWEEN a.effective_start_date
                                   AND a.effective_end_date
WHERE  TRUNC(:p_as_of_date) BETWEEN p.effective_start_date
                                AND p.effective_end_date
FETCH FIRST 100 ROWS ONLY

2. Financials: recent AP invoices for one Business Unit

SELECT ai.invoice_id,
       ai.invoice_num,
       ai.invoice_date,
       ai.invoice_currency_code,
       ai.invoice_amount,
       ai.payment_status_flag
FROM   ap_invoices_all ai
WHERE  ai.org_id = :p_business_unit_id
AND    ai.invoice_date >= TRUNC(:p_from_date)
AND    ai.invoice_date <  TRUNC(:p_to_date) + 1
AND    ai.cancelled_date IS NULL
ORDER BY ai.invoice_date DESC
FETCH FIRST 100 ROWS ONLY

3. Procurement: purchase orders for one Procurement BU

SELECT ph.po_header_id,
       ph.segment1       AS po_number,
       ph.type_lookup_code,
       ph.document_status,
       ph.approved_flag,
       ph.currency_code
FROM   po_headers_all ph
WHERE  ph.prc_bu_id = :p_prc_bu_id
AND    ph.creation_date >= TRUNC(:p_from_date)
AND    ph.creation_date <  TRUNC(:p_to_date) + 1
ORDER BY ph.creation_date DESC
FETCH FIRST 100 ROWS ONLY

Notice that each module uses its own real scoping column. AP_INVOICES_ALL uses ORG_ID for the Business Unit; Purchasing uses PRC_BU_ID. This is why "every _ALL table needs ORG_ID" is not a valid Fusion rule.

Physical SQL Security: A Correct Query Can Still Be an Insecure Report

This is one of the most important BI Publisher differences from OTBI. Oracle explicitly documents that a physical SQL data model selecting directly from a base table is not automatically subject to Fusion data-security restrictions. Where Oracle provides secured list views, those views can apply the security profiles assigned to the user running the report.

For HCM, Oracle documents secured list views such as PER_PERSON_SECURED_LIST_V, PER_ASSIGNMENT_SECURED_LIST_V, and CMP_SALARY_SECURED_LIST_V. Use the security model appropriate to the report rather than assuming physical table SQL inherits OTBI security.

A Better Workflow for Fusion SQL Development

Final Thoughts

The key to running SQL successfully on Oracle Fusion Cloud is understanding the boundary between the SaaS application and its reporting interfaces. BI Publisher is the practical physical-SQL engine; OTBI is a secured semantic-query layer; BICC is the better fit for larger extracts; and a separately replicated warehouse is where traditional SQL Developer-style database access belongs.

Once the access model is clear, the remaining work is classic Fusion data-model discipline: verify the physical object, preserve the intended grain, apply effective dating correctly, use the right language layer, scope each module with its actual business keys, and treat data security as a first-class design requirement.