SelectAI – the APEX version

Posted on Updated on

I’ve written a few blog posts about the new Select AI feature on the Oracle Database. In this post, I’ll explore how to use this within APEX, because you have to do things in a different way.

The previous posts on Select AI are:

We have seen in my previous posts how the PL/SQL package called DBMS_CLOUD_AI was used to create a profile. This profile provided details of what provided to use (Cohere or OpenAI in my examples), and what metadata (schemas, tables, etc) to send to the LLM. When you look at the DBMS_CLOUD_AI PL/SQL package it only contains seven functions (at time of writing this post). Most of these functions are for managing the profile, such as creating, deleting, enabling, disabling and setting the profile attributes. But there is one other important function called GENERATE. This function can be used to send your request to the LLM.

Why is the DBMS_CLOUD_AI.GENERATE function needed? We have seen in my previous posts using Select AI using common SQL tools such as SQL Developer, SQLcl and SQL Developer extension for VSCode. When using these tools we need to enable the SQL session to use Select AI by setting the profile. When using APEX or creating your own PL/SQL functions, etc. You’ll still need to set the profile, using

EXEC DBMS_CLOUD_AI.set_profile('OPEN_AI');

We can now use the DBMS_CLOUD_AI.GENERATE function to run our equivalent Select AI queries. We can use this to run most of the options for Select AI including showsql, narrate and chat. It’s important to note here that runsql is not supported. This was the default action when using Select AI. Instead, you obtain the necessary SQL using showsql, and you can then execute the returned SQL yourself in your PL/SQL code.

Here are a few examples from my previous posts: 

SELECT DBMS_CLOUD_AI.GENERATE(prompt       => 'what customer is the largest by sales',
                              profile_name => 'OPEN_AI',
                              action       => 'showsql')
FROM dual;

SELECT DBMS_CLOUD_AI.GENERATE(prompt       => 'how many customers in San Francisco are married',
                              profile_name => 'OPEN_AI',
                              action       => 'narrate')
FROM dual;

SELECT DBMS_CLOUD_AI.GENERATE(prompt       => 'who is the president of ireland',
                              profile_name => 'OPEN_AI',
                              action       => 'chat')
FROM dual;

If using Oracle 23c or higher you no longer need to include the FROM DUAL;

This entry was posted in AI, PL/SQL, SelectAI and tagged , , , .