Using Oracle Select AI in an (APEX) Application

This is a follow-up post on Oracle Select AI. In my previous post I explained how to setup Oracle Select AI, and how that enables you to chat with your database. In this post I will take it one step further and show you how Select AI can be used in an application. I can be an application built in any technology. Read along and benefit from the below.

The Secret is Simple

As you cannot use Select AI itself from within an application, Oracle added the “GENERATE” function to the DBMS_CLOUD_AI package. This function provides AI translation in a stateless manner. With your existing AI profile, you can use this function to perform the supported actions such as showsql, narrate, or chat. The default action is showsql.

DBMS_CLOUD_AI.GENERATE(
                    prompt => 'what is the total number of customers’, 
                    profile_name => 'openai_gpt35’, 
                    action => 'showsql')

The “GENERATE” function does NOT support the runsql action. If you supply the runsql action, it returns the following error:

ORA-20000: runsql action is not supported by generate function ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line xxxx 
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD_AI", line 2696 ORA-06512: at line x

The reason for this is that allowing arbitrary SQL execution within an AI generation context could pose security risks. With the GENERATE function you will get the SQL Statement that you need, however there is some work to be done in order to actually run the statement in your application.

So what are your Options?

In order to run the SQL that was returned by the “GENERATE” function there are several options. In this post I will provide (in my opinion) the easiest. This is to use a Classic APEX Report, which is based on a Function that returns a SQL Statement. It works almost instant, after you provide the PROMPT that can be entered by the user.

And that is really it. Nothing complex. What is debatable is the format which you show the query result to the user. Maybe you don’t want a table, maybe a diagram or a form makes more sense, but that is something for another post.