Coding with the AI Powered APEX Assistant on Oracle APEX

June 14, 2024 | 7 minute read
Roopesh Thokala
Senior Product Manager, Oracle APEX
Text Size 100%:

In today's fast-paced development environment, efficiency is key. The new built-in AI Powered APEX Assistant on Oracle APEX not only speeds up coding but also significantly simplifies it. Let's explore how this innovative APEX Assistant works and how it can benefit you.

Create Generative AI Service under Workspace Utilities

Oracle APEX 24.1 now supports various popular generative AI Providers, such as OCI Generative AI, OpenAI, and Cohere. To utilize the APEX Assistant, developers must first configure the generative AI service through the Workspace Utilities page in the Application Builder. Here's how you can set it up:

Configuring Generative AI Services

  1. Navigate to Workspace Utilities under App Builder and click on Generative AI (Configure Generative AI Services).
  2. This action will bring up the Generative Services page, displaying a list of already configured AI services. To create a new generative AI service, click the Create button.
  3. Now, You'll see a list of AI providers, including OpenAI, OCI Generative AI, and Cohere. Choose your desired AI provider from the list. Then, enter the required details in the Credentials section. 
  4. If you wish to use this Generative AI Service inside App Builder, for example, in the APEX Assistant, make sure to set "Used by App Builder" to Yes. In a workspace, only one Generative AI Service can have "Used by App Builder" as Yes.

Configure Generative AI Service

Important Note

You need to have API access to any of the provided AI services. Please ensure you have the API credentials when setting up your AI provider.

AI-Enhanced Code Editor

Split Interface for Optimal Workflow. Once you activate the AI-powered APEX Assistant, your code editor interface will transform into a dual-pane layout. The left side, occupying 60% of the screen, is your traditional code editor. The right side, taking up 40%, is dedicated to the APEX Assistant. This split design ensures you have ample space for coding while keeping APEX Assistant at your fingertips.
 

APEX Assistant Intro

APEX Assistant Features

The APEX Assistant is equipped with several handy features to streamline your workflow:

  • Clear Chat Button: Easily clear your chat history with a single click. When a user enters a message, all the previous messages in the chat are sent to the Generative AI Service, which is how the Generative AI Service maintains the context of the conversation. After finishing a specific task, clearing the chat is recommended to minimize the number of tokens sent to the Generative AI Service.
  • Quick Actions: Use "Use Selection," "Improve Selection," and "Explain Selection" to interact with your code efficiently.
  • Comprehensive Menu Options: The SQL Editor's assistant includes a menu offering two options: "Query Builder" and "General Assistance." These tools provide targeted help whether you need to construct complex queries or seek general guidance.

Real-World Examples

The APEX Assistant is designed to understand the language of the Code Editor, such as JavaScript, SQL, and PL/SQL. Here are a few examples to illustrate its capabilities:

Example 1: Interactive Report SQL Editor

The new APEX Assistant in SQL Editor brings advanced features designed to streamline and enhance interactions. Here’s how it stands out:

Automatic SQL Selection with APEX Assistant: With a click on the APEX Assistant, the existing SQL in your code editor is automatically selected.

Natural Language Query Translation: Unlike other code editors that only offer General Assistance, the SQL Code Editor’s Query Builder can translate natural language prompts into SQL queries. For example, you can type "Show all orders from last month," and the Query Builder will generate the appropriate SQL code.

Let's dive into the powerful features of the APEX Assistant in SQL Editor by providing quick prompts in its chat widget.

Prompts:

  1. sum of amount due for patient procedures for the preceding 24 months grouped by month with months shows as YYYY-MM

    SQL Generator prompt 1
  2. List of patient names, procedure descriptions, total amount, and doctor names for patient procedures in the past 60 days most recent ones first

    SQL Generator prompt 2
  3. What's the difference between a join and a left outer join?

    While working on our app, we can ask the APEX assistant for help with any questions or doubts we have about SQL. Like having a personal SQL coach and mentor, it 
    explains the answer with concrete examples to help us learn.

    Query Assistance for SQL Joins

These prompts demonstrate how the SQL Editor's Query Builder can translate natural language into SQL queries, making complex tasks more straightforward and intuitive.

Additionally, if we have an error in the SQL code and need to correct a line of code, click "Help me fix this" for instant AI assistance. This feature ensures you can quickly resolve issues with AI guidance even if you encounter issues.

Fixing errors in code

 

Example 2: Simple JavaScript Function Generation

Let us create a simple JavaScript function using the code editor available with 

  1. In the Code Editor, Click APEX Assistant and then enter the following prompt.
    Prompt: Show me a function, no explanation.

    General Assistance Prompt1
     
  2. The APEX Assistant will generate a code block for you. You'll see "Copy" and "Insert" buttons alongside the code block. Click "Insert" to place the code in your cursor's position or in your selected area in the code Editor.

    general assistance explaination

    After selecting your code block in the APEX Code editor, you will have the option to refine it further. Here’s how you can make the most out of these features:

    Once the generated function or code is selected, the "Use Selection," "Improve Selection," and "Explain Selection" buttons will become available for further action.

    Explain the code selection   Explain the selection 2
     
  3. After selecting the code in the Code Editor, entering the prompt below will replace the function name with foo.
    Prompt: Please rename the function to foo.
    Click "Insert" to replace the selected code with the newly renamed function.

    Insert AI Generated Code
     
  4. Reselecting the code will initiate a new conversation, ensuring that your previous interactions don’t impact your current interaction. That means whatever happened before will not be sent to the AI Service with the next prompt.

    Use Selection

    Tip: Clear Chat for a fresh start
     
  5. It’s a good practice to periodically use the "Clear Chat" option. This helps avoid sending unnecessary lines of code to the AI, ensuring a clean and efficient processing experience.

    clear chat

The AI-enabled Code Editor can also assist with HTML and CSS code, making it easy to generate, improve, and explain it.

Conclusion

In conclusion, as Oracle APEX developers, our SQL skills allow us to work with any data. Now, with the AI-powered Assistant, we can write, learn, debug SQL, and even write PL/SQL code, regardless of our skill level. APEX Assistant in the Code Editors on Oracle APEX is designed to make coding more intuitive, efficient, and powerful. Integrating APEX Assistant directly into the Code Editors lets you focus on what you do best—writing great code—while leaving repetitive and mundane tasks to the AI. Try it today and experience the future of coding!

Roopesh Thokala

Senior Product Manager, Oracle APEX

Roopesh Thokala, a Senior Product Manager at Oracle APEX. He is responsible for Product Management activities for APEX, including APEX on Autonomous Databases and APEX Services, with a primary focus on the JAPAC Region. He excels in customer evangelism and takes pride in promptly resolving customer issues. Prior to his role in the APEX Team, Roopesh served as an APEX Developer, Solution Engineer, and Solution Architect. He also actively supports various Oracle APEX user groups across the JAPAC region.

Show more
Oracle Chatbot
Disconnected