How to use APEX Assistant (AI Chat Capability) 🤖

Introduction:

APEX Assistant adds AI chat capability to all APEX code editors to assist you with writing SQL queries, debugging code, etc.

Pre-Requisite:

Before you can use APEX Assistant, you must create a Generative AI Service and enable the Used by App Builder setting. see my last blog post on how to configure Gen AI service in Oracle APEX using Open AI

To use APEX Assistant, log in to Apex workspace –> Go to the SQL Workshop -> go to SQL Commands and click on APEX Assistant. See my above blog post.

Here you will see APEX Assistant has 2 menu options:

• Query Builder - Use Query Builder to get back a simple query.

  In Query Builder mode, the AI assumes all questions are in the context of the customer schema in the database. For example, if you request that the AI create SQL for tables that do not exist in the database, APEX Assistant’s responses will not be constructive.

• General Assistance - Use General Assistance for general conversation, and technical questions such as “Explain this” or “Improve this code.” In General Assistance mode, APEX Assistant prompts you with default options such as Use Selection, Improve Selection, and Explain Selection.

Let’s see how Query Builder works.

1. From the menu, select Query Builder
2. In the code editor, select the query and click Use Selection

3. You will see the query is APEX wizard

4. Type, your message here, enter the average salary of the employee by department name, and click send a message

5. APEX Assistant responds with a query. This is amazing!

6. Here you can copy or insert to copy or insert the response into Code Editor. I click insert and you can see the code in Code Editor.

7. Now you can Run this code to get the result as well if you have this table in your schema.

8. I would like to show you another best features of APEX assistant which is Chat Widget memory (the context of the chat). For example, I asked how many belong to the department, Given the context in the chat, it already knew I was talking about employees. This is great!

9. Now click Clear Chat.

Now Let’s see how General Assistance works:

1. From the menu, select General Assistance
2. Apex Assistant displays three options: - Use selection - Improve selection - Explain selection

1. In the Code Editor, select the query and click Explain selection. APEX Assistant describes the query.

1. Click Clear Chat to clear the chat windows.
2. Next, I select the query and click on Use selection, APEX Assistant responds with How can I assist you with it?

1. Now I click on Improve and the assistant improves the query and specifies what improvements were made.

1. Now let me test one more thing, by purpose I made a mistake and I removed the semicolon after the manager used the below query and then selected Improve selection.

SELECT ID,
     firstname,
     lastname,
     COUNTRY,
     email,
     position,
     manager
     GENDER
FROM customer
WHERE country = 'INDIA'

APEX assistant Improves the query and also corrects the query.

1. Again, clear the chat.

So you can see how interesting is this APEX Assistant which can help in query generation, query correction, and query Improvement.