Friday, 28 February 2025

Oracle SQL Developer: Hidden Gems, Tips and Tricks - Part2

Oracle SQL Developer: Tips & Tricks for the Developers.

Oracle SQL Developer is a powerful, free integrated development environment (IDE) for working with Oracle databases.

Many of the developers use it for writing queries and managing schemas, however there are numerous hidden features and tricks that can significantly boost productivity and efficiency.

We will see some of the best tips and how you can make most of the Oracle SQL Developer tool.

1. Customizing SQL Developer

Enabling Dark Mode & Custom Themes

SQL Developer supports UI customization, including dark mode. To enable it:

Navigate to Tools > Preferences.
Go to Code Editor > PL/SQL Syntax Colors.
Choose a dark color scheme or customize individual colors.

Customizing Keyboard Shortcuts

Use the following to increase efficiency, modify shortcuts:

Tools > Preferences > Shortcut Keys.
Assign frequently used functions (e.g., CTRL+ENTER for executing queries).

Using Code Templates & Snippets

You can use frequently used SQL patterns as templates:

Go to Tools > Preferences > Database > SQL Editor Code Templates.

Define a new template like:

SELECT * FROM $TABLE$ WHERE $COLUMN$ = '$VALUE$';

Use it in the editor by typing the shortcut and pressing TAB.

Setting Up Auto Format for SQL Queries

SQL Developer has a built-in formatter:

Tools > Preferences > Database > SQL Formatter.
Adjust settings (e.g., indentation, keyword casing).
Press CTRL+F7 to auto-format queries.

2.

Generating ER Diagrams Automatically

You can generate the visualization of the database structures with ER diagrams:

Right-click on a connection and select Data Modeler > Import > Data Dictionary.
Select tables and generate the diagram.

Tracking SQL History & Restoring Lost Queries

You can use this option in order to recover a lost query :

Use F8 to open SQL History and retrieve past queries.
Use View > Files to browse auto-saved scripts.

Using the Hierarchical Profiler to Optimize Queries

Find slow execution queries:

Enable profiling in Tools > Preferences > Database > PL/SQL Profiler.
Run DBMS_HPROF for performance analysis.

Running Multiple Queries in Parallel

Open multiple SQL worksheet tabs (CTRL+SHIFT+N).
Run different queries simultaneously using different connections.

3. Useful Tools and Utilities

Database Copy & Compare Tool

Compare databases without external tools:

Tools > Database Copy to duplicate schemas.
Tools > Database Diff to compare changes.

Exporting Query Results to Excel, JSON, and XML

Quickly export data:

Run a query, right-click the results, select Export.
Choose XLS, JSON, XML, CSV formats.

Using SQL Developer as an SSH Client

Connect to databases via SSH:

Tools > Preferences > Database > SSH.
Configure tunneling to access remote databases securely.

Executing SQL Scripts via Command-Line Interface (CLI)

Run SQL scripts from the command line:

sqldeveloper.exe -script my_script.sql

This is useful for automation and batch processing.

4. Debugging & Performance Tuning

Using the Explain Plan & Autotrace Effectively

Analyze query performance:

Press F10 to generate an execution plan.
Use Autotrace (CTRL+SHIFT+E) for real-time performance metrics.

Leveraging Real-Time SQL Monitoring

For long-running queries, use the following SQL Query

SELECT * FROM V$SQL_MONITOR;

This helps track execution times and bottlenecks.

Finding Unused Indexes and Optimizing Execution Plans

Identify unused indexes:

SELECT * FROM DBA_INDEX_USAGE;

This can help remove unnecessary indexes to improve performance.

5. Advanced Scripting and Automation

Creating and Running PL/SQL Debug Sessions

To debug stored procedures:

Enable debugging in Tools > Preferences > Debugger.
Right-click a PL/SQL procedure and select Run with Debugger.

Scheduling and Automating Database Tasks

Use DBMS_SCHEDULER to automate jobs:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name => 'MY_BATCH_JOB',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN my_procedure; END;',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=1',
    enabled => TRUE
  );
END;
/

Building Custom Reports in SQL Developer

Create reports under View > Reports and define custom queries to generate insights.

Final Conclusion :

Oracle SQL Developer is much more than just an SQL editor. By leveraging these useful tips, you can significantly boost your efficiency and productivity.

Whether you’re customizing the UI, automating tasks, or optimizing queries, these tips will make SQL Developer a powerhouse for database professionals.

Oracle SQL Developer - Hidden Gems, Tips and Tricks - Part1

Oracle SQL Developer: Hidden Gems, Tips & Tricks

In this post, we will see some of the features which are available in Oracle SQL Developer which can be utilized in building stuffs. we’ll explore some lesser-known features, shortcuts, and productivity hacks that can significantly improve your workflow.

Oracle SQL Developer is a powerful tool for database development and administration, yet many users only scratch the surface of its capabilities.

1. Customizing SQL Developer for Efficiency

Using Code Templates & Snippets
Enabling Dark Mode & Custom Themes
Customizing Keyboard Shortcuts
Setting Up Auto Format for SQL Queries

2. Features You Might Not Be Using

Generating ER Diagrams Automatically
Running Multiple Queries in Parallel
Tracking SQL History & Restoring Lost Queries
Using the Hierarchical Profiler to Optimize Queries

3. Key Tools and Utilities

Database Copy & Compare Tool
Exporting Query Results to Excel, JSON, and XML
Using SQL Developer as an SSH Client
Executing SQL Scripts via the Command-Line Interface (CLI)

4. Debugging & Performance Tuning

Using the Explain Plan & Autotrace Effectively
Leveraging Real-Time SQL Monitoring
Finding Unused Indexes and Optimizing Execution Plans

5. Advanced Scripting and Automation

Creating and Running PL/SQL Debug Sessions
Scheduling and Automating Database Tasks
Building Custom Reports in SQL Developer

By mastering these features, you can turn Oracle SQL Developer into a productivity powerhouse tool.

In the next post we will see deeper into some of the features....

How to Export Database in Oracle SQL Developer

Here are the steps to for exporting the data in Oracle SQL Developer. These are very simple steps which can be performed to extract any of the database objects.

To export a database in Oracle SQL Developer, follow these steps:

Method 1: Export Using Data Pump

Open Oracle SQL Developer and connect to your database.
Open DBA Panel:
- Go to View → DBA.
- Right-click your connection and select Add Connection.
- Expand the connection and navigate to Data Pump → Export Jobs.
Create an Export Job:
- Right-click Export Jobs and select New Export Job.
- Choose Full, Schema, Table, or Tablespace Export.
- Configure the directory, file name, and options (compression, parallelism, etc.).
- Click Start to export.

Method 2: Export Schema/Tables Using SQL Developer GUI

Connect to the database in SQL Developer.
Go to Tools → Database Export.
Select Export Options:
- Choose whether to export a full schema, specific tables, or objects.
- Set output format (SQL script, CSV, etc.).
Choose Save Location:
- Specify file name and directory for the exported file.
Click Finish to generate the export file.

Method 3: Export Data Only (CSV, Excel, etc.)

Right-click a table and select Export.
Choose the format (CSV, Excel, JSON, XML, etc.).
Specify the file location.
Click Next → Finish.

These are some of the methods which can be leveraged for the database export. Try and test it out with these methods and choose the best that suits you.

Wednesday, 12 February 2025

Oracle Fusion SQL Queries and API Payload Samples

Here are some of the SQL queries for extracting data from Oracle Fusions and API payload samples for integrations.

Note : These are for reference only. please refer to the latest updates/upgrades to these API's

SQL Queries for Common Fusion Data Extraction

1. Fetching Supplier Details (Fusion Financials - ERP)

SELECT supplier_name, supplier_number, taxpayer_id, status
FROM POZ_SUPPLIERS_VL
WHERE status = 'ACTIVE'
ORDER BY supplier_name;

2. Fetching Employee Details (Fusion HCM)

SELECT person_id, full_name, person_number, email_address, hire_date, assignment_status
FROM PER_PEOPLE_F_VL
WHERE TRUNC(hire_date) >= TRUNC(SYSDATE) - 30  -- Last 30 days hires
ORDER BY hire_date DESC;

3. Fetching Invoice Details (Fusion ERP - Payables)

SELECT invoice_id, invoice_number, supplier_name, invoice_amount, invoice_status
FROM AP_INVOICES_V
WHERE invoice_status = 'Validated'
ORDER BY invoice_date DESC;

4. Fetching Purchase Orders (Fusion SCM)

SELECT po_header_id, po_number, supplier_name, total_amount, po_status
FROM PO_HEADERS_VL
WHERE po_status = 'APPROVED'
ORDER BY creation_date DESC;

5. Fetching Journal Entries (Fusion Financials - GL)

SELECT je_header_id, je_batch_id, je_category, ledger_name, period_name, accounted_dr, accounted_cr
FROM GL_JE_HEADERS
WHERE period_name = 'JAN-2025';

Oracle Fusion API Payload Samples

Fusion APIs are REST-based and use JSON for communication. Below are examples of how to interact with them.

1. Fetching Supplier Data via REST API

API Endpoint:

http
GET /fscmRestApi/resources/11.13.18.05/suppliers?q=SupplierName='ABC Corp'

Response (JSON):

json
{
  "items": [
    {
      "SupplierId": 100000,
      "SupplierName": "Corp",
      "SupplierNumber": "SUP12345",
      "TaxpayerId": "TX987654",
      "Status": "ACTIVE"
    }
  ]
}

2. Fetching Employee Data via HCM REST API

API Endpoint:

http
GET /hcmRestApi/resources/11.13.18.05/workers?q=PersonNumber='EMP00123'

Response (JSON):

{
  "items": [
    {
      "PersonId": 10000,
      "FullName": "Doe",
      "PersonNumber": "EMP00123",
      "Email": "doe@example.com",
      "HireDate": "2024-01-15",
      "AssignmentStatus": "ACTIVE"
    }
  ]
}

3. Creating a New Supplier Using REST API

API Endpoint:

http
POST /fscmRestApi/resources/11.13.18.05/suppliers

Request Body (JSON):

json
{
  "SupplierName": "Tech Solutions",
  "SupplierNumber": "SUP123",
  "TaxpayerId": "TX55555",
  "Status": "ACTIVE"
}

Response (JSON):

json
{
  "SupplierId": 1111,
  "SupplierName": "Tech Solutions",
  "SupplierNumber": "SUP1111",
  "Status": "ACTIVE"
}

4. Creating an Invoice via REST API

API Endpoint:

http
POST /fscmRestApi/resources/11.13.18.05/invoices

Request Body (JSON):

json
{
  "InvoiceNumber": "INV1111",
  "SupplierNumber": "SUP12345",
  "InvoiceAmount": 1200,
  "Currency": "USD",
  "InvoiceDate": "2024-02-10"
}

Response (JSON):

json
{
  "InvoiceId": 45678,
  "InvoiceNumber": "INV1111",
  "InvoiceAmount": 1200,
  "Status": "Validated"
}

5.Updating Employee Email via HCM REST API

API Endpoint:

http
PATCH /hcmRestApi/resources/11.13.18.05/workers/10234

Request Body (JSON):

json
{
  "Email": "doe@newcompany.com"
}

Response (JSON):

json
{
  "PersonId": 111,
  "FullName": "Doe",
  "Email": "doe@newcompany.com"
}

Oracle Fusion : Tables, Views & Integration Strategies

Oracle Fusion Applications store data in underlying tables, but direct table access is not typically provided to end-users. Instead, Oracle offers public views and APIs for data retrieval and integration.

1. Base Tables vs. Interface Tables

Base tables store transactional and master data (e.g., HCM_EMPLOYEES, PO_HEADERS_ALL).
Interface tables (staging tables) facilitate data imports (e.g., AP_INVOICES_INTERFACE for invoices).
OTBI and BIP Reports for extracting Fusion data without direct table access.

2. Key Tables & Views for Common Modules

Here are some critical tables and views in Oracle Fusion for different modules:

Fusion Financials (ERP)

AP_INVOICES_ALL – Stores invoice details.
AP_SUPPLIERS – Holds supplier information.
GL_JE_HEADERS & GL_JE_LINES – Stores journal entries.

Fusion View: AP_INVOICE_V (for secured access).

Fusion HCM (Human Capital Management)

PER_ALL_PEOPLE_F – Employee records (date-tracked).
PER_JOBS_F – Job details.
Fusion View: PER_PEOPLE_F_VL (secured employee data view).

Fusion SCM (Supply Chain Management)

PO_HEADERS_ALL – Purchase order details.
INV_MATERIAL_TXNS – Inventory transactions.
Fusion View: PO_HEADERS_VL.

3. Extracting Data from Oracle Fusion

Here are some integration approaches:

A. Using BI Publisher (BIP) for Table Extraction

Create a Data Model in BIP.
Use SQL queries to fetch data from Fusion Views (e.g., SELECT * FROM PER_PEOPLE_F_VL).
Schedule reports for automated data extraction in CSV/Excel format.

B. File-Based Data Import (FBDI) for Bulk Data Loads

Oracle provides FBDI templates for mass data uploads.
Data is staged in interface tables before processing into base tables.
Example: Importing invoices using AP_INVOICES_INTERFACE.

C. Oracle REST & SOAP APIs for Real-time Integration

Use REST APIs for fetching Fusion data:

GET /fscmRestApi/resources/11.13.18.05/suppliers

Example: Fetching employee details via HCM REST API:

GET /hcmRestApi/resources/11.13.18.05/workers?q=PersonNumber='44444'

D. Oracle Integration Cloud (OIC) for Automated Sync

OIC connects Oracle Fusion with external apps.
Prebuilt adapters for ERP, HCM, and SCM.
Uses orchestration workflows for automation.

Oracle Fusion Technical Integration: A Guide for Developers

Oracle Fusion Technical Integration: A Guide for Developers

This blog post will provide you basic details of the Oracle Fusion Integrations related details.

Oracle Fusion Applications provide a robust, scalable platform for enterprise solutions, but seamless integration is key to maximizing their potential.

Here we will explore the technical aspects of integrating Oracle Fusion with third-party applications, on-premise systems, and cloud services.

Understanding Oracle Fusion Integration Architecture
- Overview of Oracle Fusion Middleware
- Integration tools: Oracle Integration Cloud (OIC), SOA Suite, REST & SOAP Web Services
Key Integration Methods in Oracle Fusion
- Web Services (REST and SOAP APIs)
- File-Based Data Import (FBDI)
- Application Composer & Groovy Scripts
- Business Intelligence Publisher (BIP) for custom reporting
Using Oracle Integration Cloud (OIC) for Seamless Connectivity
- Prebuilt adapters for ERP, HCM, and SCM
- Developing custom integrations with OIC
- Managing security and authentication in OIC
Best Practices for Oracle Fusion Integration
- Handling data transformation and mapping
- Error handling and logging strategies
- Performance optimization techniques
Real-World Use Cases & Case Studies
- Automating HR data synchronization with third-party payroll systems
- Integrating Oracle Fusion ERP with legacy systems
- Connecting Fusion SCM with external logistics provider

Thursday, 6 February 2025

Oracle SQL Queries for various modules

This post will provide you with some of the useful Oracle Queries used across the modules in the Oracle Fusion/ERP.

Oracle Fusion applications spans across multiple enterprise modules such as

1. Oracle Financials,

2. Oracle Procurement,

3. Oracle HCM (Human Capital Management)

4. Oracle SCM (Supply Chain Management) and others.

Here’s are the list of useful Oracle queries categorized by module. These queries help in analyzing, reporting, auditing, and troubleshooting in Oracle Fusion.

Please note these are sample queries only. Modify accordingly as per your requirements.

General Queries for Oracle Fusion

1. Check User Roles & Responsibilities

SELECT u.user_name, r.role_name
FROM fnd_user u
JOIN fnd_user_roles ur ON u.user_id = ur.user_id
JOIN fnd_roles r ON ur.role_id = r.role_id
WHERE u.user_name = <Enter your username>

2. Find Last Login Details of a User

SELECT user_name, last_logon_date
FROM fnd_user
WHERE user_name = <Enter your username>

3. Retrieve Ledger Information

SELECT ledger_id, name, short_name, currency_code
FROM gl_ledgers;

Oracle Fusion Financials (GL, AP, AR, FA)

4. Get GL Journals Details

SELECT journal_name, ledger_id, period_name, balance_type, creation_date
FROM gl_je_headers
WHERE period_name = 'JAN-2024';

5 Find Invoices Pending for Payment (AP)

SELECT invoice_num, supplier_name, invoice_amount, due_date, payment_status
FROM ap_invoices_all ai
JOIN ap_suppliers asup ON ai.vendor_id = asup.vendor_id
WHERE ai.payment_status = 'Unpaid';

6 List AR Customer Transactions

SELECT trx_number, customer_name, invoice_amount, balance_due, invoice_date
FROM ar_transactions_all
WHERE balance_due > 0;

7 Fixed Assets Details

SELECT asset_number, asset_category, cost, depreciation_expense
FROM fa_assets;

Oracle Fusion Procurement (PO, Supplier, Payables)

8 Find Purchase Orders by Supplier

SELECT po.po_number, po.po_date, s.supplier_name, po.total_amount, po.status
FROM po_headers_all po
JOIN po_vendors s ON po.vendor_id = s.vendor_id
WHERE s.supplier_name = <Enter your supplier name>

9 Query for PO Approval Status

SELECT po_number, approval_status, creation_date
FROM po_headers_all
WHERE approval_status IN ('In Progress', 'Rejected');

10. Supplier Information Query

SELECT vendor_name, vendor_number, vendor_type, tax_reference
FROM po_vendors
WHERE status = 'ACTIVE';

Oracle Fusion HCM (Human Capital Management)

11 Employee Details Query

SELECT person_number, full_name, job_title, department_name, hire_date, salary
FROM per_all_people_f
WHERE current_flag = 'Y';

12 Retrieve Employee Salary Information

SELECT employee_name, salary, currency, grade, effective_start_date
FROM per_pay_proposals
WHERE effective_start_date > SYSDATE - 365;

13 Get User’s Assigned Roles (HCM Security)

SELECT u.user_name, r.role_name, u.start_date, u.end_date
FROM fnd_user u
JOIN fnd_user_roles ur ON u.user_id = ur.user_id
JOIN fnd_roles r ON ur.role_id = r.role_id
WHERE u.user_name = 'EMPLOYEE_NAME';

Oracle Fusion SCM (Supply Chain Management)

14 Fetch Inventory Transactions

SELECT transaction_id, item_number, quantity, transaction_date, subinventory_code
FROM mtl_material_transactions
WHERE transaction_date >= TRUNC(SYSDATE) - 30;

15 Query to List On-Hand Inventory

SELECT item_number, item_description, organization_code, on_hand_quantity
FROM mtl_onhand_quantities
WHERE on_hand_quantity > 0;

16 Fetch Sales Order Details

SELECT order_number, customer_name, order_status, total_amount, creation_date
FROM oe_order_headers_all
WHERE order_status IN ('Entered', 'Awaiting Shipping');

Oracle Fusion Technical Queries

17 Find ESS Scheduled Processes

SELECT process_id, process_name, status, submitted_date, completion_date
FROM fnd_concurrent_requests
WHERE status IN ('Running', 'Error', 'Completed');

18 Query for BI Publisher Reports Details

SELECT report_name, report_path, last_run_date
FROM bip_reports
WHERE last_run_date >= TRUNC(SYSDATE) - 7;

19 Fetch UCM (Content Management) Upload History

SELECT document_id, document_name, uploaded_by, uploaded_date
FROM ucm_documents
WHERE uploaded_date >= TRUNC(SYSDATE) - 30;

Other Useful Queries

20 Find List of Legal Entities in Fusion

SELECT legal_entity_id, name, registration_number, status
FROM xle_entity_profiles
WHERE status = 'Active';

21 Retrieve Open Requisitions (Procurement)

SELECT requisition_number, requester, amount, approval_status
FROM por_requisitions_all
WHERE approval_status = 'Pending Approval';

22 Identify Stuck Transactions in Interface Tables

SELECT interface_id, error_message, creation_date
FROM gl_interface_errors
WHERE error_message IS NOT NULL;