Introducing Oracle Select AI

Last week a presented on APEX World24 where I introduced Oracle Select AI to the audience. The first part of the session was all about what is Select AI, how to you set it up, and how to use it. In this blogpost I will share my findings on how Select AI works and how you can set it up on readily prepared data from Oracle, so you can start playing around. Using Oracle Select AI’s NLP feature, a marketing manager can generate insights using simple language:

"Show me the top 10 products by sales in the last quarter."

Oracle Select AI translates this request into an SQL query, executes it, and presents the results in an easy-to-understand format. In order to understand this, please have a look at the image below.

Setup Oracle select ai

It is very easy to setup Oracle Select AI in you database. There are just a few steps involved. Let me explain them.

Oracle Autonomous Database uses a large language model (LLM) to translate natural language to SQL. You can choose the LLM to use for your application. I choose to use my OpenAI account. If you do not have any, you can sign up for one. In either case, you would need a to create a new API secret key. The secret key is used to sign requests to OpenAI’s API. You will need it when creating a credential later.

The first thing you need to do is grant the moviestream user permissions to make REST calls to api.open.com. This is done by adding the user to the network Access Control List (ACL). For more details, see the DBMS_NETWORK_ACL_ADMIN PL/SQL Package documentation.

BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE (
  HOST         => 'api.openai.com',
  LOWER_PORT   => 443,
  UPPER_PORT   => 443,
  ACE          => xs$ace_type(
      PRIVILEGE_LIST => xs$name_list('http'),
      PRINCIPAL_NAME => 'MOVIESTREAM',
      PRINCIPAL_TYPE => xs_acl.ptype_db));
END;
/

Next grant the EXECUTE privilege on the DBMS_CLOUD_AI package to the user who wants to use Select AI.By default, only ADMIN user is granted the EXECUTE privilege. The ADMIN user can grant EXECUTE privilege to other users.

GRANT EXECUTE ON DBMS_CLOUD_AI TO <USER>;

Then create a credential to enable access to your AI provider

BEGIN
  DBMS_CLOUD.DROP_CREDENTIAL (
   credential_name  => 'OPENAI_CRED');

  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OPENAI_CRED',
    username => 'OPENAI',
    password => 'enter-your-LLM-secret-key-here' );
END;

Finally you DBMS_CLOUD_AI.CREATE_PROFILE to create an AI profile. The following example with the OpenAI provider creates an AI profile called openai_gpt35.

BEGIN 
DBMS_CLOUD_AI.CREATE_PROFILE( 
   profile_name => 'openai_gpt35’, 
   attributes => '{"provider": "openai”, 
                 "credential_name": "OPENAI_CRED", 
                 "object_list": 
                [{"owner": "MOVIESTREAM", "name": "movies"}, 
                 ……………………
                 {"owner": "MOVIESTREAM", "name": "pizza_shop"},
                 {"owner": ”<OWNER-NAME>", "name": ”<OBJECT-NAME"},
                 {"owner": "] }’ 
); 
END; 
/

As you can have multiple Select AI profiles. You need to pick the one to use for your session. Use the DBMS_CLOUD_AI.SET_PROFILE procedure to specify which profile to use as follows:

BEGIN
    DBMS_CLOUD_AI.SET_PROFILE(profile_name => 'openai_gpt35');
END;

You can use the AI keyword in a query with Oracle clients such as SQL Developer. Use AI as the keyword in a SELECT statement for interacting with the database using natural language prompts. The AI keyword in a SELECT statement instructs the SQL execution engine to use the LLM identified in the active AI profile to process natural language and to generate SQL.

Note that you can use several different keywords with Select AI:

• 1.chat: Use for general AI chat.
• 2.runsql [default]: Use to ask a question and get a structured result.
• 3.narrate: Use to ask a question and get a conversational result.
• 4.showsql: Use to show the SQL code that was used to produce the result.

The image below show the use of showsql

Conclusion

Select AI opens up the database to users without any SQL knowledge. They can now just chat with the database as if it was a real person. If this was too fast, you can go to this Oracle live lab and implement all of this for yourself: https://apexapps.oracle.com/pls/apex/r/dbpm/livelabs/run-workshop?p210_wid=3831