Search This Blog

Showing posts with label Efficiency. Show all posts
Showing posts with label Efficiency. Show all posts

Monday, July 7, 2025

Introducing My Microsoft Copilot Agent for Oracle ERP & HCM Metadata

Earlier, I shared a blog post detailing a Python-based CLI tool that leveraged Oracle Cloud HCM metadata and generative AI to provide SQL generation, metadata explanation, and table join suggestions. Building on that foundation, I’ve now brought the experience directly into Microsoft 365 using a Copilot Agent integrated with SharePoint.

This new solution allows users to interact naturally with metadata from Oracle ERP and HCM—without ever leaving the Microsoft ecosystem.


What This Copilot Agent Does

This Copilot Agent acts as a metadata consultant within your Microsoft 365 environment. It enables:

  • Natural language discovery of relevant Oracle Cloud tables and columns
  • Contextual SQL generation based on business terms
  • Join recommendations using known key fields like person_id, assignment_id, etc.
  • Explanations of tables, columns, and relationships
  • Starter query generation for BI Publisher reports


Why CSV Metadata Format?

During development, I found that Microsoft Copilot currently does not support JSON-based data sources for grounding

As a result, I converted my metadata files to CSV format to ensure compatibility.

This included structured metadata for tables and columns sourced from both Oracle ERP and HCM Cloud.


Copilot Agent Instructions

Agent Purpose:

You are an intelligent enterprise metadata consultant designed to assist Oracle ERP and HCM users.
You use structured metadata stored in SharePoint to help users explore, understand, and query Oracle Cloud Applications datasets.


Behavioral Instructions (Copilot Agent):

Understand the Metadata:
Use metadata stored in the provided SharePoint folder:

ERP and HCM Tables Metadata

  • Table_Metadata.csv: Contains table-level descriptions and possible usage context.
  • Columns_Metadata.csv Contain schema-level information, including table name, column name, and column descriptions.

First load the Table_Metadata.csv, then load the Columns_Metadata files.
Use the shared table_Id to join columns to their corresponding tables.


Tasks You Can Perform:

  • Suggest which tables or columns are most relevant to a user's query
  • Generate optimized SQL queries based on natural language prompts
  • Recommend joins using shared fields such as person_id, assignment_id, etc.
  • Explain what a specific table or column is used for in business terms
  • Summarize metadata for one or more objects when asked to “explain” or “describe”
  • Help build starter queries for Oracle BI Publisher (BIP) reports
  • Support semantic search (e.g., a search for "payroll balances" should find related metadata even if it’s not an exact match)
  • Act as an expert Oracle Cloud ERP and HCM analyst and developer, capable of solving advanced metadata questions and building queries based on complex requirements


How to Complete the Tasks:

  • Always refer to metadata found in the provided SharePoint files
  • Never fabricate or guess metadata
  • If no matching result is found, say: “I could not find relevant metadata for your request based on the provided files.”
  • If the user provides multiple keywords (e.g., “payroll, salary”), treat them as individual context terms
  • Scan for matches across both table names and descriptions and column descriptions
  • Use exact column name and table name matches where possible
  • Suggest joins using shared fields such as assignment_id, person_id, location_id
  • Prefer documented relationships where available
  • When generating SQL, use clear formatting and include comments if needed
  • When a user says “HCM only” or “Exclude ERP,” make sure results match
  • Clearly state which app (ERP or HCM) an object is part of when helpful
  • When asked to return specific fields (e.g., “name, email, location”), find which columns correspond to those descriptions and which tables they belong to
  • Ensure final responses are concise, technical, and clearly grounded in real metadata


Known Limitations

While this Copilot Agent adds tremendous value, it still has some important limitations:

  • It can hallucinate: If metadata isn’t found due to vague prompts, the agent may fabricate plausible-sounding but incorrect information
  • It requires clear prompting: Users get the best results when they use specific, well-structured queries
  • File linking is not perfect: Even though metadata is grounded in CSV files, deep linking between them can still pose a challenge

Despite these caveats, the Copilot Agent demonstrates how far we can go by bringing structured enterprise metadata and AI together inside the tools we use every day.

Sunday, July 6, 2025

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 GitHuboracle_hcm_intel_cli
DemoWatch 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!

Tuesday, June 24, 2025

Introducing the Oracle HCM Fast Formula Agent – Built with AI to Help You Work Smarter

🚀 Introducing the Oracle HCM Fast Formula Agent – Built with AI to Help You Work Smarter

After months of juggling fast formulas, fielding questions from HR teams, and trying to decode cryptic payroll logic at 2AM, I decided it was time to build something better.

Today, I'm happy to share an open-source project that came from that journey:

👉 Oracle HCM Fast Formula Agent – A smart, simple tool that uses Generative AI to help you generate, validate, explain, and modify Oracle HCM Fast Formulas, and more!

🎥 Check out a quick demo - Watch on Loom

💡 Why I Built It

Working in the Oracle Cloud HCM space, I saw firsthand how challenging it can be to:

  • Interpret legacy Fast Formulas written years ago
  • Validate logic across hundreds of conditional paths
  • Explain formula outcomes to business users
  • Generate new formulas based on complex rules — under tight deadlines

And with the rise of AI, I thought: Why not build an assistant that understands Oracle HCM and speaks "formula"?

⚙️ What It Does

With this Flask-based app, you can:

Generate new Fast Formulas from plain-English descriptions
Explain existing formulas in readable, line-by-line language
Validate and suggest corrections for logic/syntax errors
Modify formulas based on user input
Extend the tool with new advanced actions using a plugin system, like the included Summarize advanced action!

It even has a clean admin panel and a dark mode toggle because... well, late-night debugging deserves a good theme.

📦 Plugin Architecture

Want to add your own action like summarize_formula or compare_logic_blocks? Just drop it in the plugins/ folder. No changes to the main app required. It's built for extensibility.

🐳 Docker Ready

Whether you’re developing locally or deploying in the cloud, the app comes with a Dockerfile that runs the app via Gunicorn for production-grade performance.

🔗 Get Started

Clone it, run it, or fork it for your own use:

👉 GitHub: https://github.com/TheOwner-glitch/hcm-fast-formula-agent
📹 Demo: Watch the video

🙌 A Call to the Oracle Community

I built this for consultants, analysts, and developers who need to move fast and support critical Oracle HCM needs in the Fast Formula space, but more importantly to share an example of how AI Powered application development can be compelling and reduce the need for conditional and hardcoded business logic by instead leveraging the power of generative AI  to interpret natural language. If you try it out and find it helpful — or have ideas for improving it — I’d love to collaborate.

Thanks for reading. Now go automate your fast formula headaches away.

— Julio

Saturday, June 21, 2025

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



Friday, March 28, 2025

How to Create and Manage a Microsoft Teams Copilot Agent

Creating and managing a Teams Copilot Agent can significantly enhance your team's productivity and streamline various tasks. In this blog post, we'll walk you through the steps to set up and configure your agent, along with some useful tips to optimize its performance.

Pre-requisites

Before you start, ensure you have access to Copilot in Teams. You may need to be part of the ACL Group that manages access to the agent. Additionally, users in this group must have access to the SharePoint folder where the source data is located.

Step-by-Step Guide

1. Navigate to Copilot in Teams

In Teams, go to the Copilot section under "Chats."

2. Create an Agent

Click on "Create an Agent" and then select "Configure."



3. Select a Template

You can choose a template that will auto-populate instructions and other fields. These instructions essentially define the persona your agent will assume.

Tip: Pick a template of the kind of agent you want, then place the instructions template in ChatGPT or Copilot or OCI Gen AI and ask it to create a persona based on a given context of your choosing.

4. Point the Agent at Source Data

In the "Knowledge" section, point the agent at the source data. Place the files that will serve as the content for the agent in SharePoint or Teams. You can add up to 20 sources in SharePoint.

Tip: Avoid versioning files; store the latest version of the document. Organize the content and folders the agent is pointing to for higher accuracy.



5. Configure Agent Capabilities

If you want to build an agent that generates images or writes code exclusively, check the respective boxes. For a general-purpose conversational agent, leave these boxes unchecked.

6. Set Starter Prompts

These are prompts users will see when they start a conversation with the agent. You can hardcode common prompts to get users started.



7. Create the Agent

Once all the information is filled out, hit "Create" (top right).

8. Set Access Controls

Enter the ACLs to expose the agent to specific users. Copy the link to the agent and share it with the people who will access it (they also need to have Copilot).

Tip: Individual users may not work depending on your company policies; only ACL groups.

Editing an Agent

To edit an agent, go back to "Create an Agent," click on "View all agents," and navigate to the one you want to edit. You can change the instructions or access settings as needed.

If you are using Microsoft Teams, you can create very useful Agents on top of your existing data and make research, discovery and troubleshooting more engaging, it is a great way to assist new employees or help enhance productivity in general!

Thursday, March 13, 2025

Leveraging the Oracle Autonomous Database and AI for Effective Content Moderation in Online Communities

Today, managing online communities and forums can be a daunting task. From duplicate questions to unaccepted answers, and from unchanged idea statuses to duplicate ideas, the challenges are numerous. Even with active management, the manual effort required by product teams can be overwhelming. However, AI-driven content moderation offers a promising solution to these pain points. In this blog post, we'll explore how AI can be used to streamline content moderation and enhance the user experience in any online community.

Addressing Duplicate Content

One of the most common issues in online communities is the presence of duplicate ideas or questions. Here's a methodology to tackle this problem using the Oracle ADB and Gen AI Service in OCI:

  1. Database Integration: Connect your community's database to an AI service. For instance, if your community is hosted on an autonomous database (ADB), you can set up a trust between the ADB and an AI service (like the OCI Gen AI Service) natively.
  2. Creating Views: Develop views that represent the community categories, containing idea numbers, names, and descriptions, for example, as metadata for the AI Service to utilize.
  3. AI-Powered Duplicate Detection: Create a stored procedure (API) that calls the AI service to identify similar ideas/questions already created. Based on a predefined accuracy/confidence level, the AI can flag potential duplicates.
  4. Automated Comments: Develop an API to automatically comment on flagged ideas, informing users about the duplication and providing links to similar ideas or threads.
  5. User Feedback and Model Training: Allow users to challenge the duplicate flag. These challenges can be used to train a custom model over time (you can start with Oracle's AutoML feature with very low effort), improving accuracy with minimal effort. This would be done at the client end.
  6. Nightly Jobs: Schedule nightly jobs to run within the database to perform this true-up, using AI services and APIs to perform regular content moderation.

Enhancing Search Validation

To further strengthen content moderation, consider the following steps:

  1. REST Enablement: REST enable the stored procedure API using a service like Oracle Rest Data Services (ORDS), which is native within the ADB.
  2. Improved Search Validation: Integrate this API as a validation step when users create new ideas or questions in your website or app. This step can enhance the existing search validation by considering different languages, descriptions, and metadata, ensuring a more accurate detection of duplicates.

Flagging Existing Capabilities

Another opportunity for AI-driven content moderation is to flag ideas/questions/enhancements that suggest features already available in your product:

  1. Repository Integration: Expose a repository of current capabilities to the AI service.
  2. Automated Comments: Develop an API to automatically comment on flagged ideas, providing links to documentation and informing users about existing features.

Conclusion

AI-driven content moderation offers a powerful solution to the challenges faced by online communities, including for product teams that crowdsource content to improve their software solutions. By leveraging AI, communities can reduce manual effort, enhance user experience, and ensure more accurate and efficient content management. Whether it's detecting duplicate content, improving search validation, or flagging existing capabilities, the ADB and OCI Gen AI Service can transform the way online communities are managed, driving growth and success.

Unlocking the Power of RPA in Oracle Integration Cloud

We know that automation is key to staying competitive. Robotic Process Automation (RPA) has emerged as a powerful tool to streamline operations, reduce manual effort, and enhance efficiency. Oracle Integration Cloud (OIC) has taken a significant step forward by integrating RPA capabilities, offering a seamless and unified experience for users. In this blog post, we'll explore the benefits of OIC's RPA feature and how it compares to non-native options such as Blue Prism.

Seamless Integration with Oracle Ecosystem

One of the standout features of RPA in Oracle Integration Cloud is its native integration within the Oracle ecosystem, particularly the Oracle Fusion applications like ERP Cloud. This integration allows for a unified, drag-and-drop experience to create intelligent business automations. By leveraging other Oracle services like Visual Builder Cloud Service (VBCS) and Process Cloud Service (PCS), OIC's RPA enables businesses to design automations that are part of the overall system architecture rather than "external solutions".

Enhanced Capabilities and Flexibility

OIC's RPA is designed to automate manual repetitive tasks without the need for APIs or data loads, making it a versatile tool for various use cases. Whether it's invoking APIs, manipulating files, or automating cascading changes in projects, OIC's RPA offers the flexibility to adapt to different business needs. This capability fosters collaboration between business and IT stakeholders, ensuring that automations are efficient and effective.

The RPA feature in OIC promotes collaboration between business and IT teams, enabling them to create automations that are integrated into the design process. This approach not only enhances efficiency but also ensures that automations are aligned with business objectives. By reducing the reliance on third-party tools outside of the Oracle ecosystem, OIC's RPA simplifies the automation process and provides a more cohesive strategy.

Comparing OIC's RPA with Blue Prism

While Blue Prism is a popular RPA tool, it requires additional integration efforts when used with Oracle Cloud.

Integration: OIC's RPA is natively integrated within the Oracle ecosystem, reducing the need for third-party tools and simplifying the automation process. In contrast, Blue Prism requires additional setup and may not offer the same level of seamless integration.

Unified Platform: OIC's RPA is part of a unified platform that includes other Oracle services, providing a more cohesive automation strategy. Blue Prism, on the other hand, may require additional integration efforts to achieve the same level of cohesion. Additionally, OIC adapters to ERP cloud, for example, are tested by Oracle in coordination with the ERP team, ensuring better backwards compatibility than when using external tools.

Scalability and Flexibility: OIC's RPA can be easily scaled and adapted to various business needs, offering flexibility in how automations are designed and implemented, where the RPA bots can be invoked in flows during an integration orchestration or user action from VBCS, as examples.

Conclusion

Oracle Integration Cloud's RPA feature offers a powerful and seamless solution for businesses looking to automate their processes. With its native integration within the Oracle ecosystem, enhanced capabilities, and unified platform, OIC's RPA stands out as a superior option compared to non-native tools like Blue Prism. By leveraging OIC's RPA, businesses can streamline operations, reduce manual effort, and enhance efficiency, ultimately driving growth and success.

You can find out more about OIC's RPA capability, which is now generally available, at What's New in Oracle Integration 25.02

Friday, October 4, 2024

Oracle Application Express (APEX) - Generative AI Capabilities

Oracle Application Express (APEX) continues to revolutionize the way developers create web applications with its new Generative AI assistant. This cutting-edge feature integrates artificial intelligence into APEX’s low-code environment, allowing developers to accelerate the development process like never before. With the APEX Generative AI assistant, users can now generate SQL queries, PL/SQL and JavaScript code, and even create applications simply by describing their requirements in plain language. This means less time spent writing code and more time focused on refining application logic and design.

By bridging the gap between natural language and complex code generation, the AI assistant significantly reduces the learning curve for new developers while enhancing the efficiency of experienced ones. As Oracle APEX continues to evolve, the inclusion of AI-powered features sets a new standard for rapid application development, providing a powerful toolset that enhances productivity and creativity across all skill levels.

This introduction of generative AI in APEX showcases Oracle’s commitment to integrating advanced technologies that make development more accessible, efficient, and intuitive. Whether you're a seasoned developer or just beginning your journey with APEX, the Generative AI assistant opens up new possibilities for creating robust, data-rich applications faster than ever before.

Let us take a look at how to setup the Generative AI feature and what are some of the uses cases!

Note: click the images below to expand them for ease of use and readability! 

Setup


In order to take advantage of these features, we need to allow APEX to interact with an LLM via the API layer, and to do that we want to navigate to the "Workspace Utilities" area then select the "Generative AI" option.



Once we are there, you can create a trust between your APEX instance and an LLM, in the screenshot below we see the trust setup with Open AI, using an API key that I have access to, and I also show the options available to you besides Open AI, which are Cohere and Oracle's OCI Generative AI Service (which I recommend specially if you already use Oracle Cloud Infrastructure, to take advantage of all the security you already have in place in your Virtual Cloud Network!).



APEX Assistant - SQL Workshop


Now for the exciting part! Within the SQL Workshop, you will see an option to click on the "APEX Assistant" which will open a panel on the right side, allowing you to access the query builder feature!


In the above we see the assistant joining two tables for us, in this example! We simply asked whether two tables in the current schema could be joined, and it provided the SQL statement to do just that! This can be very useful when seeking assistance in writing complex SQL for a data lineage you may not be very familiar with, and it is also something you cannot easily accomplish in another tool, like Chat GPT, because the APEX Assistant has access to the metadata in your database directly, making things easier!

Besides the query builder feature, we have the general assistance mode, where we can ask it to develop code for us, modify and optimize code, etc. In this example we ask it to write a PLSQL stored procedure:


Notice how the "insert" feature will drop the code provided into your development canvas automatically!

In this next example, we switched the language to JavaScript, and asked a question a bit more complex:


Create Applications using Gen AI


Besides the AI Assistant in the SQL Workshop, there's another very useful feature, this time in the App Builder!

Here another option is introduced, to use a conversational agent to generate apps, in addition to existing options like creating an app from a file!



This is a powerful feature to get you started with your application, and I am excited to see how it evolves in the feature allowing for more customization and increased usability, but it is definitely a step in the right direction!

Conclusion


As we saw, there's plenty of new features to be excited about in the way of Generative AI within APEX, and I am very excited to see how these features evolve over time, but they are certainly already powerful, particularly the Assistant in the SQL Workshop, and if you use APEX already, there is no reason not to jump on these very cool features!

Tuesday, October 1, 2024

UML Diagrams For Productivity and Clarity

Following up on the last two entries regarding using Python to interact with Oracle MySQL and Oracle Linux, I want to introduce a tool called PlantUML, which is a fantastic tool for creating visual aids to help your development journey, and we will create a simple UML diagram for the Python UI that we discussed in the last entry, for monitoring various services. We will also take a look at a couple of Class Diagrams for a couple of Kafka Consumer and Producer services.

Before getting to the diagram and the UML code, let's talk about how to run PlantUML locally on your machine using VS Code.

VS Code

Install VS code, it’s free.

https://code.visualstudio.com/download

Plant UML Extension


In VS code, go to the extensions marketplace, and install the PlantUML Extension.

You also need to have Java JRE on your machine, install that as well from: https://www.java.com/en/download/manual.jsp


Diagram Previews

To use it, create a new text file in VS Code, and then select PlantUml as the language. Then paste the UML code, and hit “alt + d”, to open the preview screen. You can then copy images.




Now, let's look at some examples, including one following up on our Python Monitoring UI referenced in the last blog entry.

UI Diagram


@startuml

!define RECTANGLE_SIZE_SIZE_RECTANGLE 12
skinparam componentStyle rectangle
skinparam rectangle {
  BackgroundColor<<main>> LightGray
  BackgroundColor<<option>> White
  BorderColor<<main>> Black
  BorderColor<<option>> Gray
}

rectangle "Python UI - Homepage" <<main>> {
    rectangle "Kafka Status" <<option>> 
    rectangle "Zookeeper Status" <<option>> 
    rectangle "MySQL Status" <<option>> 
    rectangle "Map View" <<option>> 
}

@enduml



Class Diagram - Consumer

Diagram depicting a consumer service in a Kafka architecture for a vehicle telemetry system.

@startuml

class KafkaConsumer {

    +consumeMessage()

    +parseJSON(String message)

    +processData(SchoolBusData busData)

}

class SchoolBusData {

    +happenedAtTime: String

    +assetId: String

    +latitude: float

    +longitude: float

    +headingDegrees: int

    +accuracyMeters: float

    +gpsSpeedMetersPerSecond: float

    +ecuSpeedMetersPerSecond: float

}

class DatabaseConnector {

    +insertSchoolBusData(SchoolBusData busData)

}

KafkaConsumer --> SchoolBusData : Parses

KafkaConsumer --> DatabaseConnector : Inserts into

DatabaseConnector --> MySQLDatabase : Stores

 

class MySQLDatabase {

    +save()

}

@enduml


Class Diagram - Producer

@startuml

class KafkaProducer {

    +produceMessage()

    +serializeToJSON(SchoolBusData busData)

    +sendToKafka(String jsonMessage)

}

class SchoolBusData {

    +happenedAtTime: String

    +assetId: String

    +latitude: float

    +longitude: float

    +headingDegrees: int

    +accuracyMeters: float

    +gpsSpeedMetersPerSecond: float

    +ecuSpeedMetersPerSecond: float

    +generateSampleData(): SchoolBusData

}

KafkaProducer --> SchoolBusData : Generates

KafkaProducer --> KafkaTopic : Sends message

class KafkaTopic {

    +receiveMessage(String jsonMessage)

}

@enduml


These are a couple of types of diagrams that you can create using PlantUML in VS Code, in another entry we will be covering a couple of different diagrams, including a sequence diagram, regarding a proposed architecture for getting data out of Oracle Fusion Cloud.

Sunday, March 24, 2024

Oracle OCI Gen AI Services and Enhancing Developer Productivity

Let’s talk about Oracle’s OCI Gen AI Service, Generative AI Service | Oracle [oracle.com], in the context of the developer productivity opportunities that exist because of it which can transform development shops to be more efficient all-around!

I am currently exploring the OCI service for areas such as the below, and will write a follow-up entry relative to my findings:

  • Code generation and auto-completion: With generative AI, the potential to write code using AI to greatly speed up building extensions and integrations will be a game changer, much like how data scientists can now write R & Python code exponentially faster using ChatGPT like services, so can writing extensions and integrations become far less manual for you.
    • More time could be spent on design, unit testing and other aspects versus manual development.
  • Code refactoring & bug fixing: Asking the AI to code review, improve, make suggestions around implementation for custom code is something already available for high code programming languages like C#, Python, PLSQL and Java in tools like ChatGPT, and this can increase quality and reduce bugs.
    • You can embed AI reviews to your peer review process, as well as during the build process to optimize performance and reduce logic errors.
    • To help with KT’s when flexing with staff or when a developer is touching a code base they did not previously own, the developer can ask the AI service to explain logic, speeding up the learning process exponentially.
  • Automated Test Generation: having the ability for the AI to generate test scenarios and test cases, in supported frameworks, based on the implementation and logic would save time and reduce defects.
    • We can ask the AI to interpret code and suggest unit test scenarios and even build them (depending on the framework).
  • Code comparisons: rather than using tools like Beyond Compare, to manually inspect differences in code bases, you can ask the AI to inspect it for you and produce a comparison report with intelligence built in (meaning, really explain what is different, not just highlight text differences).
  • Code notation & summarization: imagine uploading code to the AI service and asking for a detailed implementation report with steps and explanations, and even a technical design and graphical support such as sequence diagrams.
    • This would be very helpful for custom code where technical designs were not clearly documented or not documented at all (more prevalent now due to the Agile methodology putting less emphasis on documentation) and useful for onboarding new developers and support staff, for product delivery to hand off artifacts to operations, etc.
    • The time savings from not having to write detailed technical designs would be fantastic, and in general to speed up any developer working on a case by asking the AI to summarize and explain sections of the code.
I can produce many other scenarios, but I think I’ve made my point.

So, why Oracle since there’s similar services in the industry? I believe the Oracle services can have a competitive advantage because Oracle AI data models should yield higher accuracy with frameworks such as Oracle JET, Java, PLSQL, Fast Formulas, etc. since Oracle owns those frameworks and uses the AI service internally as part of their own DevOps processes, and the AI model could learn overtime as your developers utilize the service, and it would learn from usage, on top of Oracle’s tuning of the service over time. Lastly, it would be within your secure VCN and OCI environment, so privacy and security should not be a concern if you already have an OCI tenancy, and this is a major factor for many wishing to adopt Gen AI safely.

I think the usage of this, for the moment, is limited to high code frameworks such as Java, PLSQL, C#, Oracle JET (JavaScript), Python etc. but we would be very interested in extending the usability and benefits to middleware technology such as Oracle Integration, for the same reasons listed in this blog.

To that end, I have raised an Idea in Customer connect for the integration of the OCI Gen AI Service to Oracle Integration Gen 3 (OIC) going back to September of last year, so please support this idea over in Cloud Customer Connect by voting and commenting on it (Idea Number: 713448): Generative AI for OIC — Cloud Customer Connect (oracle.com) [community.oracle.com]

Stay tuned for my findings over the next few weeks, as I explore the Gen AI service for these use cases!

Oracle Generative AI Strategy and Options

The software industry continues to innovate and iterate upon AI capabilities, and Oracle is clearly investing heavily in this space as well, with very exciting developments being announced recently.

Below are highly informative strategy updates that you may want to review relative to Oracle’s AI strategy and recent developments.
The below graphic shows Oracle's AI Technical Stack and where recent investments have been made:


Click on the image to maximize it

These AI services are the same used by Oracle internally to develop AI capabilities in the Fusion applications and Fusion analytics, etc. now exposed for customers to utilize as well.

Something that Greg mentions in the first video, is the GenAI Agents beta recently launched, that is a service that allows you to have a conversation with your data within your autonomous database. Also, there's a new feature now called "Autonomous database select AI", also seen above, here's a GREAT blog about it: Introducing Select AI - Natural Language to SQL Generation on Autonomous Database (oracle.com)

I think that both the GenAI Agents and the Select AI feature should be considered as part of any modern Data Strategy, particularly when the data sources are Oracle applications (such as ERP & HCM), once your Autonomous Datawarehouse (ADW) has the Fusion data in it via BICC, you can use these features there without moving the data to a third-party tool to do similar operations (which can increase your cost of ownership and decrease technology technical stack harmony (meaning using too many vendor products unnecessarily)).

Imagine transforming part of your workforce from writing reports to being able to have conversations with the data without A) Having to move it elsewhere B) Having to spend a lot of time writing complex queries and designing intricate reports. Their workload could shift from designing and building reports, to tuning the data model and talking with the data, and this could then be expanded to end users over time, where internal teams would then focus on data model tuning, and everyone else is just talking with the data.

Additionally, this would all be happening within the secure boundaries of your OCI tenancy, reducing concerns around privacy and security that often worries the mind!

Real life examples for those using ERP and HCM that could be made possible in the near future:

%sql

SELECT AI how many invoices are past due

SELECT AI how many suppliers do we consistently not pay on time, and what are the reasons

SELECT AI how many expenses will be past due by next week

SELECT AI how many people under my organization may retire over the next 5 years

SELECT AI how many people under my organization will lose vacation by end of year

No more reports, just conversations with the data..!

Tuesday, April 25, 2023

Low Code Development and Oracle APEX

Low code development is in fashion, and there's many offerings in the market that allow the power of low code development to be unlocked by the enterprise. The allure of low code is the time to market, where solutions, particularly web apps, can be quickly created by following a wizard, and can really be done by non-developers or "citizen" developers. Due to its benefits, many companies have adopted a low code strategy to supplement their existing software engineering offerings, where possible, specially where subject matter experts in the business could leverage low code in a self-service capacity, if the requirements aren't too complex.

Oracle APEX is available at no extra cost with your Oracle database, additionally, if you have an Autonomous database (in OCI), APEX (and ORDS - Oracle Rest Data services), already comes available and enabled in the autonomous databases. This allows you to create applications very quickly on top of the data you already have in your database, minimizing data movement, simplifying security and support, and more. For more information regarding APEX, and to try it for free, you can visit https://apex.oracle.com/en/.

I have observed where companies, who already own Oracle Database licenses and have a large footprint in the space, bypass Oracle APEX and jump on paid low code offerings from other vendors, and whereas this post will not focus heavily on what Oracle APEX is, because it's very well documented as it has been around for over 20 years already, there's definitely content that those trying to jump on APEX or even experienced users may not be aware of, and will be shared here.

The APEX Product Managers are Jayson Hanes and Monica Godoy, you can find them on LinkedIn and follow them for news, etc.


Below you will find information about the APEX Roadmap, training, events, and more:

  1. APEX 23.1 is out, the feature set can be viewed at https://apex.oracle.com/en/platform/features/whats-new-231/.
  2. Past Roadmap and Upcoming Road (see Appendix A)
  3. Training and Certifications (see Appendix B)
  4. Events calendar for APEX: https://apex.oracle.com/pls/apex/r/grand_prix/apex-events/events?session=6298691857842
  5. APEX Office Hours with Oracle (see Appendix C)
  6. APEX Idea Lab (Different from the Cloud Customer Connect Idea Lab) (see Appendix D)
  7. Cost model in OCI (see Appendix E)
Appendix A (Roadmaps)

Past Roadmap:



Upcoming Roadmap (Post 23.1):



Appendix B (Training)

You can try APEX for free at: https://apex.oracle.com/en/

There’s also professional training available, as seen below.


Appendix C (Office Hours)


Appendix D (Ideas)


Appendix E (OCI Cost)


If you own Oracle Databases and want a low code solution that uses data from your Oracle databases primarily (although APEX can connect to other sources via API's and "DB Links"), then APEX is truly your best bet, among all the low code solutions.

Tuesday, April 2, 2019

Technology Alignment from Within

IT and OT integration promotes a consolidated view of information management within an enterprise, which is a key element for that high-performance environment where every stakeholder/user has the right information, in the right format, and at the right time in order to make the best possible decision, with the least amount of effort. Steenstrup tells us this in the article, IT and Operational Technology Alignment Innovation Key Initiative Overview, and I am a firm believer of it. The technology organization must be aligned as to give decision makers the power to make optimal decisions with the least amount of effort, technology should enable them to do so, and it should provide power and give access to knowledge efficiently.

However, as Steenstrup describes, that is simply the end goal, but there are many other benefits when your technology organization is aligned, and acts as one:

1- There will be cost reduction opportunities in terms of future integration projects, the procurement of software and the management of its life cycle, cost of software licensing, external and internal costs of software support, and more.

2- Risk will be reduced, less malware intrusion and fewer internal errors will result from a more cohesive technology organization. This emanates from the enhancements bestowed upon cyber security when IT security resources are shared with OT staff in order to implement holistic security measures, and in other areas such as version control, patching and upgrades, which should happen in coordinated fashion.

3- Lastly, there’s also the fact that when standards are communicated and shared, and there is a platform in place where said standards can be accessed, then organizations will reduce risk and shorten project timelines and hybrid projects, that require both IT and OT involvement, will meet far fewer obstacles. This can be achieved by having an agreed upon approach where a common set of standards and architecture plans are shared and utilized.

So, how ready is your organization to facilitate this integration between Informational Technology and Operational Technology? As Steenstrup explains, a few factors should be considered to determine this.

1- Does senior management understand what it means to evolve OT systems against the cost and risk of not managing them correctly?

2- You must be clear as to what OT is from your organization’s point of view, this way you can identify which groups in your organization manage, use, own or should own OT processes.

3- Assess opportunities to push for the alignment we have been discussing, like a major change in technology platforms.

This was definitely a valuable article for me and it gave me a lot to think about. It is an important topic and every enterprise strives to make this assimilation/integration happen in various ways, and I believe it is definitively a work in progress that is never quite completed.


References


Steenstrup, Kristian. IT and Operational Technology Alignment Innovation Key Initiative Overview. Gartner. July 22nd, 2011.

Saturday, March 23, 2019

Enterprise Architecture and the Need for EA Agility

What enterprise doesn’t need management and technology practices that improve its performance by enabling it to see holistic and integrated views of their strategic direction, business practices, information flows and technology resources? Without current and future enterprise views, it becomes hard to manage the transition from current state to the future operating state, while adding more duplication and disjuncture to existing systems.
Alignment within an enterprise leads to the optimization of the usage of resources. This is where EA comes in, it helps ensure enterprise activities are not disjointed and helps reduce duplication of processes and applications. It also leads to a complete approach to management, planning and documentation; creating aligned strategy and business driven views of the enterprise. EA also should help ensure that investments have a linkage to strategic goals and business outcomes are obtained in the most efficient manner, if your EA is not doing this for you, then it isn’t being effective (Bernard, 2012, Chapter 1).
How does EA promote this alignment exactly? It draws from accepted standards and promotes the use of non-proprietary commercial solutions, enhancing integration and allowing for the “plug and play” of components, promoting agility to address change (if your EA has shifted to allow for this). EA can ensure separate sub-architectures are not needed to reflect different levels of the enterprise and it will enable you to provide ongoing capability and coordinated views of the strategic direction of the enterprise, and in regards to the business services, information flows and resources. An effective EA allows decision makers to make optimal judgments by obtaining coordinated information. This capability is aided by the standard language and methodology that EA brings, which all stakeholders can speak, in alignment (Bernard, 2012, Chapter 3).
How do we know whether our EA practice is actually delivering on the items we have outlined? Defining and tracking EA business-value metrics (qualitative and quantitative) improves the value proposition and impact of EA efforts (Brand & Smith, 2018). Projects guided by EA principles, road-maps, guidance and standards can also be measured directly and indirectly. “The direct measure of the impact of standards is the number of exceptions or waivers that are granted during the architecture assurance process. The indirect measure of the impact of standards is the business outcomes” (Burke & Burton, 2017, p. 9).
So, we know what EA is, what it does or should do. However, why is EA not effective in every organization? And why is it perceived by many, including decision makers that I have interacted with, as cumbersome, slow, and counterproductive? That is perhaps a direct result of the traditional EA vs. the Outcome-Driven EA dilemma.
An Outcome-Driven EA allows us to directly address the business outcomes of highest priority to the enterprise by aligning deliverables to the business direction, and vision, and the highest value added deliverables. In contrast, the traditional EA is too focused on collecting the current state architecture, which generates large amounts of objects and relationships that do not have a direct linkage to business outcomes, which wastes time and resources. The Outcome-Driven EA follows a methodology that simplifies it's development by creating a stage plan that exclusively targets the delivery of stakeholder value. Lastly, measurable outcomes are relevant; we should seek observable change in key business performance metrics within a defined time-frame (Bernard, 2012, Chapter 4).
How does this get enforced? At the center of the Outcome-Driven EA is the architect, rather than documenting the current state endlessly, and “filling out” the traditional framework, they should focus on developing the EA by targeting a limited set of outcomes and framing deliverables that are directly linked to business outcomes and key business disruptions. The architect’s focus should be exclusively on the deliverables required to address the target business outcomes, which can be achieved by effectively executing the stage plan through the re-focused EA delivery life-cycle (Burke & Burton, 2017).
However, the traditional EA is not without merit, and components of it must be leveraged. The current view of the architecture is important since it provides a set of baseline artifacts that can be used for planning and decision making, as a reference. The current view of the enterprise helps clearly show associations, dependencies and performance gaps in the enterprise’s business requirements and current capabilities. We can also observe which resources are being used in which LOB (line of business) to support the achievement of their strategic goals (Bernard, 2012, Chapters 1, 3, 7). There is great value in capturing the current operating environment because resources may not be aligned to the strategic goals and business services that the enterprise wishes to target, furthermore, significant duplication in functions and tools may exist. Even a traditional framework will be able to help map how to close gaps between the current and future state by utilizing the information from the current view, like having the blue prints to a home. Additionally, in documenting the current view of the enterprise, performance gaps that were not visible before may emerge (Allega & Burton, 2018).
That being said, organizations should forego the traditional EA approach given the issues mentioned. Additionally, with the traditional approach, the architect’s focus is on completing the framework and “filling everything out”, leaving little room for innovation. Instead, organizations should streamline EA development and focus on deliverables that directly address the highest priority business outcomes (Burke & Burton, 2017). The best way to accomplish this is to integrate agile practices with your outcome-driven EA, by doing so leaders will embrace innovation and take advantage of digital technology by using the design capability of EA in order to harness the creativity and talent already in their organization, to help create optimal solutions. An agile EA will also drive digital innovation by using design thinking created by lean techniques (Blosch, Burton & Walker, 2017). Agile focuses on rapid testing of market opportunities, experience and engagement, as well as experimentation led by disruptive innovation and collaboration across partner ecosystems; it also focuses on outcomes and sees failures as an opportunity to learn. Lastly, from a technology perspective, this EA will use reusable components and solution-driven techniques (Blosch, Osmond & Norton, 2016). In short, the Agile, Outcome-Driven EA, will enable the enterprise to truly embrace the digital era and survive major disruptions, where traditional EA may not be up to the challenge and be seen as an impediment, as we discussed earlier.

The above guidance is solid, however, we must note that the impact of agile practices in your EA won't be as great without having an outcome driven EA that seeks to support the business strategy and objectives that are mapped to the main business disruptions that your organization is facing (Burke & Burton, 2017), this means you have to know what those are. The key is to focus on producing signature-ready deliverables that drive business change; using Agile is great but efforts must be focused on high value deliverables that are mapped to business objectives. Agile can speed up delivery on items but the focus should remain on deliverables that address the highest-priority business outcomes that help address key disruptions and concerns of key stakeholders. To this end, agile practices should be combined with a design capability that helps drive digital innovation which can maximize the creativity and talent within the organization to help create optimal solutions (Blosch, Burton & Walker, 2017). If the aforementioned is not accomplished by using the EA, or rather, if the EA does not facilitate the accomplishment of said strategic goals and helps address business disruption, then the business will find ways to "address" those concerns by working around the EA, and the vicious cycle of shadow IT, duplication of systems, convoluted processes, lack of governance, etc. will inevitably ensue.

References


Allega, P., Burton, B. (2018). Massively Disruptive Events Require a Nonlinear Approach to Enterprise Architecture. Gartner. February.

Bernard, S. A. (2012). An introduction to enterprise architecture. Chapters 1, 3, 4, 7. AuthorHouse.

Blosch, M., Burton, B., Walker, M. (2017). Build the Design Capability of Your EA Practice to Drive Digital Innovation. Gartner. February.

Blosch, M., Osmond, N., Norton, D. (2016). Enterprise Architects Combine Design Thinking, Lean Startup and Agile to Drive Digital Innovation. Gartner. February.

Brand, S., Smith, M. (2018). EA Business-Value Metrics You Must Have Today. Gartner. May.

Burke, B., Burton, B. (2017). Stage Planning a Business-Outcome-Driven Enterprise Architecture. Gartner. p. 9. March.