Exploring the Synergy of OpenAI's ChatGPT and Oracle APEX: Transforming Oracle Applications

Exploring the Synergy of OpenAI's ChatGPT and Oracle APEX: Transforming Oracle Applications

By Michelle Skamene, Executive VP – Consulting Services, Insum


It's amazing to think about how rapidly AI has come to shape our world. Far from just a buzzword, AI has surged onto the scene, imprinting its potential on everything from household gadgets to corporate powerhouses. Now, we're on the cusp of a seismic shift - the dawn of the "AI era," if you will. Companies are faced with a clear fork in the road: embrace AI and harness its might or, well, brace for impact.

AI might seem like it's from a Star Trek episode, but the truth is, it's already woven into our everyday lives. Think about the personalized ads that pop up when you're online shopping or that handy voice assistant on your phone - that's AI doing its thing. And it's not just about future prospects anymore; for businesses (especially those of us in the Oracle ecosystem), AI isn't just knocking at the door - it's already in the living room.

But knowing AI is here and figuring out how to make it work for you are two different things. It's like a designer suit - it needs to be tailored to fit you. So, where do you start? Right here, with this article.

In this piece, I'll guide you through the tangible ways you can start using AI, particularly OpenAI's ChatGPT, in your business today. Whether you're looking to automate content creation, build chatbots, or simplify data analysis, ChatGPT might just be the tool that helps you make it happen. We'll be exploring how AI, Oracle's powerful suite of services, and Oracle APEX can come together to create a path to success in this new, AI-powered world.

A New Era of Content Generation

Data is exploding all around us. With every new product, every chat with a customer, and every tiny cog in our business machine, we're creating and collecting data. And it's not just about quantity; this data is a gold mine of opportunity, just waiting to be transformed into high-quality content.

And that content is indeed king. It's the driving force behind engaging audiences, conveying brand narratives, and ultimately, influencing customer behavior. Whether we're talking about product descriptions, blog posts, or customer emails, high-quality content is what makes your brand resonate with audiences and stand out in the crowded marketplace.

Enter ChatGPT. It can help create rich product descriptions straight from your product data, craft personalized customer emails that drive brand loyalty, or even whip up clear, concise documentation. It's all about translating the data you have into the content you need.

In this section, we're going to discover tangible ways you can use ChatGPT (and Oracle APEX) for your content generation and help unlock the true potential of your business data.

Figure 1: Content Generation with ChatGPT
Figure 1: Content Generation with ChatGPT

Use Case 1: Product descriptions

A captivating product description can significantly influence customer decision-making, persuading them not only to buy but also to value the product's unique attributes.

Traditionally, generating these descriptions can be a time-consuming and labor-intensive process, particularly for businesses with extensive product lines or frequent updates. However, with the integration of ChatGPT, this process can be vastly streamlined and automated, allowing businesses to keep pace with the dynamic nature of their products.

ChatGPT can take specific inputs, such as product features, use cases, and benefits, and transform these into coherent, engaging, and persuasive product descriptions that audiences recognize in a simpler, benefit-focused description. By inputting different parameters, businesses can command ChatGPT to generate descriptions that resonate with various target demographics.

Sample implementation

One way of easily integrating ChatGPT into your product management system is by leveraging Oracle APEX. It can seamlessly integrate with the data in your Oracle database and extend your applications with very little effort. The following example shows basic product information including name and basic features, maintained, for example, in Oracle E-Business Suite, being passed to the ChatGPT API, returning a product description that can be used immediately.

Once approved by the product management team, the product description can then be fed directly back to your system of record immediately for seamless integration.

Use Case 2: Personalized Customer Emails

In a world where price tags aren't the deal-breakers they once were, businesses need to find other ways to stand out from the crowd. The golden ticket? Personalization. Especially when it comes to customer communication. Personalized, engaging emails aren't just nice-to-haves. They are game-changers, enriching the customer journey and building those oh-so-important bonds of loyalty.

Now, here is where the magic happens: when customers feel like you are talking to them, and not at them, they feel valued. They feel seen. And ChatGPT? Well, it is a pro at creating this kind of tailored communication. Beyond just popping a customer's name at the top of an email, ChatGPT uses customer data - think in terms of purchase history, preferences, and behavior - to whip up emails that show a real understanding of the customer-brand relationship.

Imagine getting an email with a product recommendation that is so spot-on, it's as if it read your mind. Or a promotional offer that is right up your alley. Or even a thoughtful follow-up email about that latest purchase you made. With ChatGPT, each message can be sculpted to strike a chord with the individual customer, building a deeper, more personal connection. And this isn't just about making customers happy - it is good business. Better engagement, repeat purchases, and customer retention - they are all side effects of a well-executed personalization strategy.

Sample implementation

In the following example, on an Oracle APEX vacation property rental site, ChatGPT is leveraged to autogenerate highly personalized welcome emails for guests. By passing in very specific information about the trip duration, destination, and specific property, ChatGPT is instructed to craft an email that includes a trip itinerary for the ideal length, and even suggest packing instructions.

Figure 2: Personalized Email
Figure 2: Personalized Email

In all cases, while the output generally requires a layer of human review to ensure factual accuracy, it undeniably presents a significant timesaver. Handling the heavy lifting of content generation frees up precious time for refinement and fine-tuning, making the content creation process far more efficient.

Conversational Experiences with ChatGPT

Chatbots have transformed the customer interaction landscape, enabling companies to offer 24/7 support and personalized experiences. With ChatGPT, the possibilities for chatbot development extend further, promising more engaging and contextually aware interactions.

One of the most immediate use cases for ChatGPT is the provision of 24/7 customer support. ChatGPT-powered chatbots can be available round-the-clock, answering customer queries, resolving issues, and providing guidance. With its capability to understand context and generate human-like text, ChatGPT can provide support that feels personalized and intuitive, even during off-peak hours when live agents may not be available.

Sample Implementation

The following illustrates an Oracle APEX ChatGPT ChatBot implementation where customers of a vacation property rental site interact with ChatGPT via a chatbot implementation. Specific information about the user's trip, including property, location, and dates is passed through the API resulting in a human-like interaction.

While this example leverages the standard ChatGPT models, businesses could further augment the quality of the interaction by fine-tuning models using their own data.

Empowering Data Analysis with ChatGPT

As businesses navigate the sea of data within their Oracle applications, the challenge often lies not in the gathering of data, but in its interpretation. Transforming data into actionable insights is a critical step, one that often requires significant time and expertise. But what if we could expedite this?

When coupled with the robust capabilities of Oracle APEX, ChatGPT can revolutionize your data analysis processes. This power duo can sift through your business data, wherever it lives, turning raw figures into comprehensible narratives and meaningful insights. Whether you are seeking to understand sales patterns, customer behavior, or operational efficiency, they can guide you in making informed decisions.

ChatGPT as a Data Querying Tool in Oracle Database

In Oracle Database environments, ChatGPT can act as a sophisticated data querying tool.

With natural language interaction, users can ask complex queries about their data and ChatGPT can parse these queries and provide insightful responses.

While power users of your applications likely know their ins and outs, you might also have a vast number of users who want simple answers to their questions, and here, ChatGPT can truly shine.

Sample Implementation

In this example, data about a business's customers are passed to the model in simple text format. The user is provided a text box into which they can enter a question about the data, without having to know anything about SQL, PL/SQL, or how, in this case, to use Oracle APEX's powerful Interactive Reports.

Although the responses can be pretty impressive, it is crucial to remember that it is not infallible. A whole slew of factors can impact the quality of the output. The nature of the prompt, the specific model settings, and the context are some examples.

It is important to take the results with a pinch of salt and give them a once-over before using them in a business setting. But don't let that deter you. The ability to transform natural language into a potent query tool is nothing short of extraordinary.

Oracle APEX as a Catalyst for ChatGPT Integration

The OpenAI set of LLM (Large Language Models) is available via extremely well-documented REST APIs. Getting started is simple, and clearly, developers have a number of frameworks and libraries they can use, including JavaScript, Python, Node.js, and more.

But in the Oracle eco-system, if you're looking for easy integrations between your data and any third-party REST-enabled service, you would do well to consider the amazing duo of Oracle APEX and ORDS.

Oracle APEX is Oracle's no-cost low-code platform. It is built to handle data securely, and that ticks a big box. It also runs anywhere Oracle Database runs; this means you can leverage ChatGPT capabilities across your entire Oracle ecosystem, and not only in isolated spots. In short, it truly is the perfect tool when it comes to leveraging AI tools such as ChatGPT in your Oracle environment.

Step 1: Your OpenAI Key

Get started at the OpenAI website here. Once you've signed up, you'll want to create a new secret key under the USER settings of your account.

Step 2: Understand the OpenAI API and its response

Once you're all signed up for OpenAI, the OpenAI Playground is where you'll want to be. It's the hub for test-driving calls to the OpenAI endpoints.

There are many parameters you can play with (think “model,” “temperature,” “max-length,” etc.). Experiment with different prompts and tweak those settings. It will give you a solid feel for how they operate and how they could serve your application.

When you're ready to make a POST request to the OpenAI API completions endpoint, there are a couple of parameters you cannot do without:

  • “mode:” The “Chat” mode is currently in Beta, but it's the one you want if you're looking to implement a chatbot. Other than the chatbot example in this article, we used the “Completions” mode. Each mode has a different REST endpoint.
  • “prompt:” This is the input text for the model. It's the springboard from which your model will generate a response. You'll spend most of your time working to get this one right!
  • “model:” This tells the API exactly which GPT-3 model you want to deploy.

All the other parameters are entirely optional.

Worth noting though: if you decide to skip setting the “completions” parameter, the API will automatically return a single completion. Similarly, if “max_tokens” isn't set, the API will default to returning 2048 tokens. So keep that in mind!

3. PL/SQL package to make the REST API call

The following simple package makes a single completion call to the "completions" OpenAI endpoint. Pass in the prompt and get the response. The temperature, model, and max tokens are hard-coded, and you would do well to add them in as additional parameters for your specific use cases.

create or replace package chatgpt_pkg is

Purpose: PL/SQL package for OpenAI API
Remarks: Allows us to post requests to OpenAI API

Who Date Description
—— ———- ————————————-
MS 14.1.2023 Created package

procedure get_completion (
in_parm1 in varchar2,
out_plan out clob

end chatgpt_pkg;

create or replace package body chatgpt_pkg is

g_gpt_api_url constant varchar2(255) := 'https://api.openai.com/v1/completions';
g_api_secret constant varchar2(255) := 'YOUR API KEY HERE';

procedure get_completion (
in_parm1 in varchar2,
out_plan out clob
l_result clob;
/* the parameters in the request could/should be variables for a more flexible call */
l_request_text varchar2(4000) := '{“prompt”: “'||in_parm1||'”, “model”: “text-davinci-003″,”temperature”:0.7,”max_tokens”:2000}';
l_json_table clob;
l_status_code number;


apex_web_service.g_request_headers.delete(); — clear the header
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'application/json';
apex_web_service.g_request_headers(2).name := 'Authorization';
apex_web_service.g_request_headers(2).value := 'Bearer '||g_api_secret;

l_result := apex_web_service.make_rest_request(
p_url => g_gpt_api_url,
p_http_method => 'POST',


— Get the status code from the response
l_status_code := apex_web_service.g_status_code;

— Raise an exception if the status code is not 200
IF l_status_code != 200 THEN
raise_application_error(-20000, 'API request failed with status code: ' || l_status_code);

for i in (select * from json_table(l_result, '$.choices[*]'
columns (
value varchar2(4000) path '$.text'

end loop;

end get_completion;

end chatgpt_pkg;

Step 4: Oracle APEX implementation

We leveraged this package in several ways in the examples in this article. For the generation of emails in the APEXbnb example, we leverage Oracle APEX Automations to schedule them based on trip dates. Looping through all trips in the coming 3 days, we pass in details of the trip and ask for a specific email format. This then gets logged to a table for approval.

In the data analysis example, data from the Oracle APEX Interactive Report is passed in, along with the user's query.

In other examples, the package is called from a page process based on user interaction.

A word about the prompt

The importance of a well-crafted prompt when making calls to ChatGPT simply can't be overstated. It sets the stage and provides context for the AI, acting as a launchpad from which the model begins its generation process. An ambiguous or poorly defined prompt might lead to unclear or irrelevant outputs, while a specific and well-structured prompt can significantly enhance the quality of the generated text. Therefore, when engaging with ChatGPT, remember that the power of the conversation is in your hands - or rather, in your prompt! Craft it with care and precision, and don't hesitate to try out a few variations to get the output you want.

Potential Concerns and Limitations of ChatGPT in Enterprise Applications

Ah, the “but.” Isn't there always a “but.” In all the excitement around AI, and specifically OpenAI's ChatGPT, it's easy to overlook the potential hiccups along the way. But understanding the potential pitfalls and issues is crucial, if not more so, than getting too excited about the potential benefits.

So, let's take a moment, sit back, and take a cold, hard look at the "but" - the potential concerns and limitations when it comes to integrating ChatGPT into our enterprise applications.

Data Privacy and Security

First up, we have the biggie: data privacy and security. AI models like ChatGPT have a voracious appetite for data. They eat it for breakfast, lunch, and dinner. And that's all well and good until you realize that they could potentially stumble upon sensitive info. That is a no-no. We have to make sure that ChatGPT does not access, store, or even glimpse sensitive information. Be wary of any data you use in your integration. That's potentially one area where Oracle APEX comes into play. Thanks to its robust but flexible authentication, authorization, and access control framework, administrators can help mitigate the risks.

Quality Control and Oversight

ChatGPT's text can sometimes be so human-like it's eerie. But here's the catch: it is not perfect. It can sometimes misunderstand context or deliver a response that's a little... off. That's why it is crucial to have quality control in place. Think regular audits and feedback loops to ensure the model is continuously learning and improving, and that no GPT auto-generated content goes out to the world without some form of audit or, at minimum, a disclaimer.

Training Data Dependence

ChatGPT is like a sponge. It absorbs everything from its training data. The good news? It can provide incredibly relevant responses. The bad news? If it's fed biased or incorrect data, it might end up with some pretty off-base responses. It is a little like feeding your kids: give them the good stuff, and they will thrive.

Managing User Expectations

We've all been there. You hear about this fantastic new tool, you're excited, you're ready, and then... it doesn't quite live up to the hype. As awesome as ChatGPT is, we have to be clear: it's not a replacement for humans (yet!). It's a tool, a very cool one, designed to help us do things better.

Understanding these "buts" helps us plan better. By knowing what we might face, we can come up with a game plan to mitigate these issues.


The integration of OpenAI's ChatGPT into the Oracle ecosystem opens a world of opportunities for users and developers alike. Be it automating content generation in Oracle E-Business Suite, creating intuitive chatbots for Hyperion EPM, or simplifying data analysis in Oracle Database, ChatGPT, in conjunction with Oracle APEX, can bring innovation, efficiency, and user-friendliness to your Oracle-based solutions. As AI continues to evolve, the synergy between these technologies is set to grow, paving the way for a future of smarter, more effective business tools.

Michelle Skamene

Michelle Skamene

Michelle Skamene is Executive Vice President at Insum Solutions, a Montreal-based Oracle APEX consultancy. An Oracle ACE with a passion for technology, Michelle has championed Oracle APEX development since 2004. Throughout her career, she has excelled at delivering high-quality projects that consistently go above and beyond client expectations. Michelle remains at the forefront of tech advancements, infusing her work with innovation. Her contributions to the tech community and her commitment to excellence solidify her reputation as a trusted expert in the field.