AI Enhances Oracle Cloud HCM Support: Elevating Insight with the Oracle HCM Intel CLI

🤖 Oracle Gen AI + Public HCM Metadata = Limitless Insight

The Oracle HCM Intel CLI Tool is a professional-grade command-line assistant that uses your extracted HCM metadata and combines it with the capabilities of Oracle’s OCI Generative AI service (powered by Cohere) or OpenAI, or the LLM of your choice, giving you intelligent insights relative to HCM data structures. And it's all done securely and locally—no sensitive data leaves your environment.

By referencing public HCM data definitions, this project demonstrates just how powerfully Oracle Gen AI, and other AI offerings, can be applied in the enterprise to support analysts, developers, and architects with tasks such as:

• Generating SQL queries based on natural language
• Suggesting joins between key Oracle HCM tables
• Explaining and optimizing existing SQL
• Creating BI Publisher-ready templates
• Performing semantic metadata searches

👉 Project GitHub: oracle_hcm_intel_cli

👉 Demo: Watch Demo

---

🔐 Built With Practical Enterprise Needs in Mind

The tool includes features that any real-world implementation would benefit from:

• Encrypted .env handling using runtime secrets
• Markdown output for reporting or Copilot/Teams usage
• Audit mode for logging what metadata was passed to the LLM
• Modular provider support for OpenAI or Oracle Gen AI
• Interactive prompt chaining for analysts and non-developers

It’s optimized for real users working in real environments—offering flexibility without sacrificing security or precision.

---

🛠️ Powered by Oracle Technology, Honoring Oracle’s Vision

This CLI tool is powered by Oracle’s own public documentation and showcases the capabilities of Oracle Cloud Infrastructure’s Generative AI platform. It's a clear example of how AI and metadata can be responsibly applied in the enterprise, especially when building tooling around Oracle’s HCM ecosystem.

💡 Oracle’s Gen AI service is the star of the show here—it brings context, comprehension, and creative query generation to the hands of business users and technical professionals alike.

Earlier this month, I introduced a Metadata Extractor CLI Tool built to programmatically parse Oracle Cloud HCM’s public documentation and extract table and view metadata into JSON format. That foundational tool—available on GitHub at oracle_hcm_metadata_extractor—has enabled this powerful second act: the Oracle HCM Intel CLI.

---

🧪 Get Started

1. Extract metadata using: oracle_hcm_metadata_extractor

2. Query and explore with: oracle_hcm_intel_cli

Whether you’re working in HCM data architecture, reporting, or support—this is your AI-powered sidekick.

---

I hope this project inspires others to build upon Oracle’s cloud platform and apply Generative AI responsibly. The future of enterprise tooling is intelligent, secure, and deeply integrated—and with Oracle Gen AI, that future is now.

—
Julio @ OracleSpot.net

Supercharging Oracle HCM Research and Support with a CLI Tool to Obtain Structured Documentation Metadata

As an Oracle Cloud HCM customer and practitioner, I frequently rely on the Oracle Cloud Applications Documentation to understand how various tables and views are structured. Whether I’m building BI Publisher queries, supporting Fast Formulas, or troubleshooting integrations, knowing the exact column names and their meanings is essential.

But manually digging through dozens (or hundreds) of HTML pages is time-consuming and repetitive.

So, I decided to automate it in a way that would allow me to have conversations with this data versus clicking through hundreds of pages aimlessly looking for answers.

📌 What I Built

I created a Python-based command-line tool that extracts Oracle's publicly available HCM documentation, regarding tables and views, and outputs structured metadata for tables and views into clean JSON files. This helps streamline research, improve automation, and accelerate troubleshooting for internal support.

Here’s what it does:

• Parses the toc.js file from Oracle’s documentation site to extract all table/view URLs

• Visits each documentation page using a headless browser

• Extracts metadata such as:

  • For Tables: table name, description, details, columns (with data types), primary keys, and indexes

  • For Views: view name, description, details, column names, and SQL query

No more flipping through documentation pages—just clean, structured data ready for analysis or integration into support tools, with many useful use cases including those in the AI landscape.

---

🛠️ How It Works

Step 1: Extract Links from toc.js

The Oracle documentation includes a toc.js file which holds all the links to individual table and view pages. The tool first parses that file to build a list of URLs.

python oracle_hcm_cli_tool.py --toc toc.js --csv oracle_links.csv

Step 2: Convert CSV to JSON

For downstream processing, the CSV is converted to a simple JSON array of {name, url} pairs.

python oracle_hcm_cli_tool.py --csv oracle_links.csv --json oracle_links.json

Step 3: Extract Table and View Metadata

The final step launches a headless browser with Playwright and visits each page to extract structured metadata.

python oracle_hcm_cli_tool.py --json oracle_links.json --tables 

oracle_tables.json --views oracle_views.json

Or all three steps in one:

python oracle_hcm_cli_tool.py --toc toc.js --csv oracle_links.csv 

--json oracle_links.json --tables tables.json --views views.json

---

📦 Output Format

Here’s what the output looks like.

For Tables (tables.json)

{
  "table_name": "PER_ALL_PEOPLE_F",
  "url": "...",
  "description": "This table stores information about...",
  "details": "Schema: FUSION Object owner: PER Object type: TABLE Tablespace: APPS_TS_TX_DATA",
  "columns": [
    {
      "column_name": "PERSON_ID",
      "data_type": "NUMBER",
      "length": "",
      "precision": "18",
      "not_null": true,
      "description": "System generated surrogate key"
    },
    ...
  ],
  "primary_key": {
    "name": "PER_PEOPLE_F_PK",
    "columns": ["PERSON_ID", "EFFECTIVE_START_DATE", "EFFECTIVE_END_DATE"]
  },
  "indexes": [
    {
      "name": "PER_PEOPLE_F_U1",
      "uniqueness": "Unique",
      "columns": ["BUSINESS_GROUP_ID", "PERSON_NUMBER"]
    }
  ]
}

For Views (views.json)

{
  "view_name": "FAI_DEEP_LINKS_VL",
  "url": "...",
  "description": "This view shows deep link metadata...",
  "details": "Schema: FUSION Object owner: FAI Object type: VIEW",
  "columns": [
    { "column_name": "DEEP_LINK_ID" },
    { "column_name": "DEEP_LINK_CODE" },
    ...
  ],
  "sql_query": "SELECT ... FROM FAI_DEEP_LINKS_B b ..."
}

---

💡 Why This Is Useful

This tool is a game-changer if you:

• Frequently need to understand Oracle's schema to troubleshoot or write custom reports

• Work in integrations, BI, or payroll support and need faster insights

• Want to build dashboards or internal tools that visualize schema metadata

• Need a starting point for building generative AI agents or search interfaces that utilize documentation in order to drive intelligent insights

• This same approach can be followed for Oracle ERP, and other use cases, for Oracle customers

---

⚠️ Disclaimer

Disclaimer: 

This tool and blog post are independently created for research, education, and internal productivity enhancement by an Oracle customer. 

All references to Oracle® products and documentation are made strictly for educational and interoperability purposes. 

Oracle, Java, and MySQL are registered trademarks of Oracle and/or its affiliates. 

This tool is not affiliated with, endorsed by, or sponsored by Oracle Corporation.

Metadata shown (such as table names and column names) are extracted from publicly accessible Oracle documentation. 

No proprietary documentation or licensed software is redistributed or reproduced in full.

Always refer to Oracle Cloud Documentation for official and up-to-date content.

---

🧪 Want to Try It?

You can clone the tool and try it for yourself:

https://github.com/TheOwner-glitch/oracle_hcm_metadata_extractor

Happy automating!