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!

Leveraging Oracle Cloud’s Generative AI with Cohere: A Hands-On Approach

After days of tinkering and fine-tuning, I'm excited to share something I’ve been working on — OCI Generative AI Cohere Integration with external services. If you're someone who works with AI, Oracle Cloud, or just enjoys diving into new tech, this project might be something you'll find interesting.

What's the Project About?

In short, it's a simple yet powerful integration of Oracle Cloud’s Generative AI service with Cohere's language model by exposing it to external usage in order not to be confined to the chat experience within the OCI console. The goal is to provide an easy-to-use interface for AI-driven responses via a Flask API or function-based approach. Whether you're running the service locally or integrating it into your own applications, this project is meant to be a starting point for building smarter, AI-powered experiences that allow you the flexibility of using your preferred development platform outside of the playground in your Oracle OCI Tenancy.

Why This Project?

If you’re like me, you enjoy playing with new tools but don’t always want to reinvent the wheel every time you build something. That’s why I wanted to simplify integrating Oracle's powerful AI services into everyday applications. The project takes the heavy lifting out of the process and provides a clean, easy-to-use API for interacting with Oracle’s AI models.

But it doesn’t stop there. Whether you're building out something bigger or just playing around with ideas, this project is designed to be flexible and easily extendable.

Here’s What You Get:

• Flask API: If you’re building a web-based application and want easy interaction with the Generative AI service, the Flask API is ready to go. You can send requests, get responses, and scale up as needed.
• Function-based API: For those who don’t need a full web API, you can call the AI service directly from a function that you can import into your Python projects. Perfect for smaller integrations or experimenting within your own apps.
• Configuration Flexibility: With environment variables and a configuration file, you have full control over things like model parameters and Oracle Cloud credentials.

How to Use It?

1. Set up your environment: Whether you're using the Flask API or the function-based setup, you’ll need to configure your environment with Oracle Cloud credentials and some basic settings.
2. Get running: Once configured, you can easily call the API or function with any message you want to send to the AI model, and you’ll get a detailed response in return.

I’ve added full instructions on the project’s GitHub page, including setup steps, environment variables, and how to run both approaches.

What’s Next?

The goal is to keep expanding this project. Right now, it’s all about integration, but as AI services evolve, I want to keep adding new features and I likely will build apps that consume these interfacing endpoints in order to use the power of the Cohere LLM. Expect more updates on this as I add new functionality and more tutorials to help everyone get the most out of the platform.

Feel free to dive into the project, contribute, or just ask questions. I’m always happy to connect with fellow developers who are exploring AI and Oracle Cloud.

Check it out on GitHub: OCI Generative AICohere Integration

I've added some screenshots below showing the interactions with the OCI Gen AI service via Postman by utilizing the Flask API, allowing extensibility outside of the chat experience in OCI which is the default experience. 

By utilizing the API approach you can take advantage of the power of the Cohere LLM through Oracle OCI in your applications and data science tools while maintaining your security assurances of being inside of your OCI tenancy.

Thanks for reading, and happy coding!

OCI Gen AI Playground

Click on the images to expand them for ease of viewing

Postman Interaction with Flask API Exposing the OCI Gen AI Chat Service

OCI Gen AI Traffic from the API

Oracle Linux and MySQL, Kafka and Python Flask Monitoring API

In this entry we will dig deeper into the previous post that dealt with invoking an Oracle MySQL stored procedure using Python. The focus of this entry is creating a Python API to monitor Kafka and MySQL running on an Oracle Linux VM. This API can be invoked from a User Interface that will allow the user to check the statuses of these different components.

To create a Python API that will execute the commands on your Oracle Linux or RHEL system to check the status of MySQL, Zookeeper, and Kafka, you can use the subprocess module in Python to run shell commands. 

Below is an example of how you can implement this.

Step-by-Step Implementation:

• Create Python Functions to Check Status: Each function will execute the corresponding system command using subprocess.run and return the output.
• Set Up Flask API: We'll use Flask to create a simple API that the UI can call to retrieve the status.

Python Code:

import subprocess
from flask import Flask, jsonify
app = Flask(__name__)

# Function to check MySQL status
def check_mysql_status():
try:
result = subprocess.run(['sudo', 'systemctl', 'status', 'mysqld'], capture_output=True, text=True)
return result.stdout
except subprocess.CalledProcessError as e:
return str(e)

# Function to check Zookeeper status
def check_zookeeper_status():
try:
result = subprocess.run(['sudo', 'systemctl', 'status', 'zookeeper'], capture_output=True, text=True)
return result.stdout
except subprocess.CalledProcessError as e:
return str(e)

# Function to check Kafka status
def check_kafka_status():
try:
result = subprocess.run(['sudo', 'systemctl', 'status', 'kafka'], capture_output=True, text=True)
return result.stdout
except subprocess.CalledProcessError as e:
return str(e)

# Flask API route to get MySQL status
@app.route('/status/mysql', methods=['GET'])
def get_mysql_status():
status = check_mysql_status()
return jsonify({'service': 'MySQL', 'status': status})

# Flask API route to get Zookeeper status
@app.route('/status/zookeeper', methods=['GET'])
def get_zookeeper_status():
status = check_zookeeper_status()
return jsonify({'service': 'Zookeeper', 'status': status})

# Flask API route to get Kafka status
@app.route('/status/kafka', methods=['GET'])
def get_kafka_status():
status = check_kafka_status()
return jsonify({'service': 'Kafka', 'status': status})

if __name__ == "__main__":
app.run(host='0.0.0.0', port=5000)

Explanation:

• subprocess.run: Executes the systemctl commands to check the status of MySQL, Zookeeper, and Kafka. The capture_output=True argument captures the output, while text=True ensures the output is returned as a string.
• Flask: Provides an API endpoint for each service, which the UI can call to check the respective statuses.
• Routes: Each API route (/status/mysql, /status/zookeeper, /status/kafka) responds to a GET request and returns the status of the requested service in JSON format.

Running the API:

To run the Flask API, ensure Flask is installed:

pip install Flask

To Start the Application:

python your_script_name.py

Creating the UI:

For the UI, you can use any front-end technology (HTML, React, etc.) and have buttons that call these API endpoints to display the status of each service.

For example:

• A button for MySQL could call /status/mysql.
• A button for Kafka could call /status/kafka.
• A button for Zookeeper could call /status/zookeeper.

Note on Permissions:

Ensure that the user running the Python script has the appropriate permissions to run the systemctl commands using sudo. You may need to modify the sudoers file to allow passwordless sudo for these commands.

Connecting to an Oracle MySQL Database and Invoking a Stored Procedure using Python

In this entry, we will explore how to use Python to connect to an Oracle MySQL database and invoke a stored procedure. This is particularly useful when you're looking to interact with your database programmatically—whether it's for inserting data, querying, or managing business logic through stored procedures.

We will walk through a Python script that connects to the database, generates some random test data, in this case telemetry data for school busses, and invokes a stored procedure to insert the data into multiple tables. Below is the full Python code, and we’ll break down each part of the code in detail to help you understand how it works.

This assumes that you have Oracle MySQL Running, a stored procedure to call, and the proper credentials to access your database, you can modify this script and use a stored procedure of your own!

Here's the full Python script that connects to the MySQL database, generates random test data, and calls the stored procedure InsertEvents.

Script:

import mysql.connector

from mysql.connector import errorcode

import random

import datetime

# Database connection parameters

config = {

    'user': 'yourusername',

    'password': 'yourpassword',

    'host': 'ip address of vm running mysql',

    'port': port you are using,

    'database': 'name of your mysql database',

}

# Function to generate random test data

def generate_test_data():

    asset_id = random.randint(1000, 9999)

    happened_at_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')

    latitude = round(random.uniform(-90, 90), 6)

    longitude = round(random.uniform(-180, 180), 6)

    heading_degrees = random.randint(0, 360)

    accuracy_meters = round(random.uniform(0.5, 20.0), 2)

    geofence = 'TestGeofence' + str(random.randint(1, 100))

    gps_speed = round(random.uniform(0, 30), 2)

    ecu_speed = round(random.uniform(0, 30), 2)

    return (asset_id, happened_at_time, latitude, longitude, heading_degrees, accuracy_meters, geofence, gps_speed, ecu_speed)

# Function to insert event data by calling the stored procedure InsertEvents

def insert_event_data(cursor, data):

    try:

        cursor.callproc('InsertEvents', data)

        print(f"Successfully inserted event for asset_id {data[0]}")

    except mysql.connector.Error as err:

        print(f"Error: {err}")

        return False

    return True

# Main function to connect to the database and insert events

def main():

    try:

        # Connect to the database

        cnx = mysql.connector.connect(**config)

        cursor = cnx.cursor()

        # Ask the user how many events to generate

        num_events = int(input("Enter the number of events to generate: "))

        # Generate and insert the specified number of test events

        for _ in range(num_events):

            test_data = generate_test_data()

            if insert_event_data(cursor, test_data):

                cnx.commit()

            else:

                cnx.rollback()

        cursor.close()

        cnx.close()

    except mysql.connector.Error as err:

        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:

            print("Something is wrong with your username and/or password")

        elif err.errno == errorcode.ER_BAD_DB_ERROR:

            print("Database does not exist or is not reachable")

        else:

            print(err)

if __name__ == "__main__":

    main()

Inserting data using the stored procedure:

The insert_event_data() function calls the stored procedure InsertEvents and passes in the data.

cursor.callproc('InsertEvents', data): This method invokes the stored procedure InsertEvents with the data tuple as input parameters.

If the procedure is successful, a success message is printed; otherwise, an error message is displayed.

Connecting to the MySQL Database:

The main () function handles the database connection and inserts a user-specified number of events.

The mysql.connector.connect(**config) establishes a connection to the MySQL database using the provided configuration.

The script asks the user to input the number of events they wish to generate (this will prompt the user in the console).

It then generates the random data and inserts it by invoking the stored procedure. The transactions are committed or rolled back based on whether the insertion was successful.

To run the script:

Make sure your database connection parameters are correct.

Ensure that the stored procedure, like InsertEvents, is defined in your MySQL database.

Run the Python script, which will prompt you to enter the number of events to generate and insert them into the database.

Wrapping up:

This script demonstrates a simple yet powerful way to connect to an Oracle MySQL database using Python and invoke a stored procedure. With a little adjustment, you can use it for various operations, such as fetching data, updating records, or automating tasks. By leveraging Python and MySQL Connector, you can efficiently manage your data and workflows, at no cost! Remember that you can create an always free account in Oracle Cloud Infrastructure (OCI), and provision an always free Compute instance, I recommend Linux and then running MySQL and Python there, all free!