Mastering SQL for Oracle Fusion HCM – Chapter 3 : Oracle SQL Developer Setup

 

✅ Chapter 3. Oracle SQL Developer and BI Tools Setup

Oracle SQL Developer Setup – SQL for Fusion HCM

Learn how to set up Oracle SQL Developer and connect it to Fusion HCM data sources. Includes BI Publisher and HCM Extract tools for SQL execution.

---

🔹 Introduction

To query Fusion HCM data using SQL, you need the right tools. Oracle SQL Developer is a free and powerful IDE for writing and testing SQL queries. In the context of Fusion HCM (a SaaS product), you don’t connect directly to the production database—but you can use tools like BI Publisher, HCM Extracts, and OTBI to run SQL securely within the application.

This chapter walks you through:

• Setting up Oracle SQL Developer (for practice and sandbox access)

• Using SQL in BI Publisher

• Running SQL in HCM Extracts and Value Sets

---

🔹 Theoretical Concepts

🧰 What is Oracle SQL Developer?

Oracle SQL Developer is a graphical tool that lets you:

• Write and test SQL queries

• Browse tables and views

• Run reports

• View execution plans

👉 Note: In Fusion Cloud, you won’t use SQL Developer to connect to the live database, but it’s great for:

• Testing queries using sample schemas

• Practicing joins, functions, and performance tuning

---

🔧 Tools to Use SQL in Fusion HCM

Tool	Description	SQL Usage
BI Publisher	Reporting tool in Fusion	Write custom SQL queries for data models
HCM Extracts	Data export tool	SQL in fast formulas or filters
Value Sets	Used in parameter fields	Create table-based value sets with SQL
OTBI	Embedded reporting tool	Uses subject areas (no raw SQL) but supports logical expressions

---

🔹 SQL Developer Installation Guide

✅ Step 1: Download & Install

• Go to: Oracle SQL Developer Download

• Choose the version based on your OS

• Unzip and run sqldeveloper.exe (no installation needed)

✅ Step 2: Connect to Sample Database

You can use:

• Oracle XE (Express Edition) locally

• Live SQL at Try Now for browser-based practice

SELECT * FROM   employees; 

🔹 Real-Time Scenario (BI Publisher)

📁 Scenario:

“You need to create a report showing all active employees with email addresses in Oracle BI Publisher.”

✅ Steps to Set Up SQL in BI Publisher

1. Login to Oracle Fusion

2. Navigate to: Tools → Reports and Analytics

3. Go to Catalog → Shared Folders → Custom

4. Click New → Data Model

5. Select SQL Query, and enter:

SELECT person_number,
       full_name,
       email_address
FROM   per_all_people_f
WHERE  TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date 

6. Save → Create Report → Layout → Run

💡 This is how SQL is used securely in Fusion via the application layer.

---

🔹 SQL in Value Sets

Use SQL to create parameter-driven reports or extracts.

SELECT department_name,
       department_id
FROM   hr_all_departments
WHERE  active_flag = 'Y'
ORDER  BY department_name 

Use this in a table-based value set attached to a report or extract parameter.

---

🔹 Best Practices and Notes

• Never try to connect SQL Developer directly to Fusion Cloud DB—access is restricted.

• Use BI Publisher and Extracts for secure SQL execution.

• Practice on Oracle Live SQL or XE Database for skill development.

• Reuse query logic from SQL Developer in BI tools.

• Enable SQL Tracing in BI Publisher for debugging.

---

🔹 Summary

✅ Oracle SQL Developer is ideal for offline SQL learning and testing.
✅ Use BI Publisher, Value Sets, and HCM Extracts to run SQL in Fusion HCM.
✅ Install Oracle XE or use LiveSQL for practical exercises.
✅ Understand tool boundaries: Fusion Cloud doesn’t allow direct DB access.
✅ Set up custom SQL reports securely via BI tools.

---

🔹 Next Steps

• Previous Chapter:  Mastering SQL for Oracle Fusion HCM – Chapter 2 : Oracle Fusion HCM Architecture

• Next Chapter:   Mastering SQL for Oracle Fusion HCM – Chapter 4 : SELECT Statement in SQL

---

Tags: #SQLDeveloper, #OracleFusionTools, #BI_Publisher, #HCMExtracts, #TableValueSets, #OracleLiveSQL