Ora-lytics

SelectAI – the beginning of a journey

Posted on March 16, 2024 Updated on April 19, 2024

Oracle released Select AI a few months ago, and with any new product it is always a good idea to give it a little time to fix any “bugs” or “features”. To a certain extent, the release of this capability is a long time behind the marketplace. Similar products have been available in different ways, in different products, in different languages, etc for some time now. I’m not going to get into the benefits of this feature/product, as lots have been written about this and most of those are just rehashing the documentation and the marketing materials created for the release. But over all this time, Oracle seems to have been focused on deploying generative AI and LLM related features into their vast collection of applications. Yes, they have done some really cool work with those applications. But during that period the everyday developer, outside of those Apps development teams, has been left waiting for too long to get proper access to this functionality. In most cases, they have gone elsewhere. One thing Oracle does need to address is the public messaging around certain behavioural aspects of Select AI. There has been some contradictory information between what it says in the documentation and what the various Product Managers are saying. This is a problem, as it just confuses customers who will then use something else.

I’m building a particular application that utilizes various OCI products, including some of their AI products, to create a hands-free way of interacting with data and is suitable for those who have various physical and visual impairments. Should I consider including Select AI? Let’s see if it is up to the task.

Let’s get on with setting up and using Select AI. This post focuses on getting it set-up and running with some basic commands, plus a few warnings too as it isn’t all that it’s made out to be! Check out my other posts that explore different aspects (most other posts only show one or two statements), and some of the issues you need to watch out for, as it may not entirely live up to expectations.

The first thing you need to be aware of, this functionality is only available on an ADW/ATP on Oracle Cloud. At some point, we might have it on-premises, but that might be a while coming as I’m sure the developers are still working on improving how it works (and yes it does need some work).

Step 1 – Connect as ADMIN of ADW/ATP

As the ADMIN user for the database, you need to set-up a few things for other users of the Database before they can use Select AI.

Firstly we add the schema which will be using Select AI to the Access Control List. This will allow them to reach things outside of the Database. The following illustrates adding the BRENDAN schema to the list and allowing HTTP calls to the Cohere API interface.

BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
         host => 'api.cohere.ai',
         ace => xs$ace_type(privilege_list => xs$name_list('http'),
         principal_name => 'BRENDAN',
         principal_type => xs_acl.ptype_db)
    );
END;

Next, we need to grant some privileges to some PL/SQL packages.

grant execute on DBMS_CLOUD_AI to BRENDAN;
grant execute on DBMS_CLOUD to BRENDAN;

That’s the admin steps

Step 2 – Connect to your Schema/User – Cohere Example (see OpenAI later in this post)

In my BRENDAN schema, I need to create a Credential.

BEGIN
   -- DBMS_CLOUD.DROP_CREDENTIAL (credential_name => 'COHERE_CRED');

   DBMS_CLOUD.CREATE_CREDENTIAL(
       credential_name => 'COHERE_CRED',
       username => 'COHERE',
       password => '...' );
END;

Next, you need to create a CLOUD_AI profile.

BEGIN
   --DBMS_CLOUD_AI.drop_profile(profile_name => 'COHERE_AI');
  
   DBMS_CLOUD_AI.create_profile(
      profile_name => 'COHERE_AI',
      attributes => '{"provider": "cohere",
                      "credential_name": "COHERE_CRED",
                      "object_list": [{"owner": "SH", "name": "customers"},  
                                      {"owner": "SH", "name": "sales"},
                                      {"owner": "SH", "name": "products"},
                                      {"owner": "SH", "name": "countries"},
                                      {"owner": "SH", "name": "channels"},
                                      {"owner": "SH", "name": "promotions"},
                                      {"owner": "SH", "name": "times"}]
       }');
END;

When creating the CLOUD_AI profile for your schema, you can list the objects/tables you want to expose to the Cohere or OpenAI models. In theory (so the documentation says) it shares various metadata about these objects/tables, which the models in turn interpret, and use this to formulate their response. I said in theory, as that is what the documentation says, but the PMs on a recent webcast said it did use things like primary keys, foreign keys, etc. There are many other challenges here, and I’ll come back to those at a later time.

At this point, you are all set up to use Select AI.

Step 3 – See if you can get Select AI to work!

Before you can use Select AI, you need to enable it for your session. To do this run,

EXEC DBMS_CLOUD_AI.set_profile('COHERE_AI');

If you start a new session/connection or your session/connection gets reset, you will need to run the above command again.

No onto the fun or less fun part. The Fun part is using it and getting results displayed back to you. When this happens (i.e. when it works) it can look like magic is happening. For example here are some commands that worked for me.

select ai how many customers exist;
select AI which customer is the biggest;
select AI what customer is the largest by revenue;
select AI what customer is the largest by sales;

The real challenge with using Select AI is crafting a statement that works i.e. a query is run in the Database and the results are displayed back to you. This can be a real challenge. There are many blog posts out there with lots of examples of using Select AI, along with all the ‘canned’ examples in the documentation and in demos from PMs. I’ve tried all that I could find, and most/all of them didn’t work for me. Something isn’t working correctly behind the scenes. For example here are some examples of statements that didn’t work for me.

select AI how many customers in San Francisco are married; 
select AI what is our best selling product by country; 
select AI what is our biggest selling product by country; 
select AI how many items with the product sub category of Cameras were sold in 1998; 
select AI what customer is the biggest; 
select AI which customer is the largest by revenue; 

Yet some of these statements (above) have been given in docs/posts/demos as working. For a little surprise, have a look at the comment at the bottom of this post.

Don’t let this put you off from trying it. What I’ve shown here is just one part of what Select AI can do. Check out my next post on Select AI where I’ll show examples of the other features, which work and can be used to build some interesting solutions for your users.

Set-up for OpenAI

The steps I’ve given above are for using Cohere. A few others can be used including the popular OpenAI. The setup is very similar to what I’ve shown above and the main difference is the Hostname, OpenAI API key and username. See here for how to get an OpenAI API key.

As ADMIN run.

BEGIN  
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
         host => 'api.openai.com',
         ace  => xs$ace_type(privilege_list => xs$name_list('http'),
                             principal_name => 'BRENDAN',
                             principal_type => xs_acl.ptype_db)
   );
END;

Then in your Schema/user.

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

  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OPENAI_CRED',
    username => '.....',
    password => '...' );
END;

BEGIN
  DBMS_CLOUD_AI.drop_profile(profile_name => 'OPEN_AI');
  DBMS_CLOUD_AI.create_profile(
      profile_name => 'OPEN_AI',
      attributes => '{"provider": "openai",
                      "credential_name": "OPENAI_CRED",
                      "object_list": [{"owner": "SH", "name": "customers"},  
                                      {"owner": "SH", "name": "sales"},
                                      {"owner": "SH", "name": "products"},
                                      {"owner": "SH", "name": "countries"},
                                      {"owner": "SH", "name": "channels"},
                                      {"owner": "SH", "name": "promotions"},
                                      {"owner": "SH", "name": "times"}]
       }');
END;

And then run the following before trying any use Select AI.

EXEC DBMS_CLOUD_AI.set_profile('OPEN_AI');

If you look earlier in this post, I listed some questions that couldn’t be answered using Cohere. When I switched to using OpenAPI, all of these worked for me. The question then is, which LLM should you use? based on this simple experiment use Open API and avoid Cohere. But things might be different for you and at a later time when Cohere has time to improve.

Check out the other posts about Select AI.

• SelectAI – the beginning of a journey
• SelectAI – Doing something useful
• SelectAI – Can metadata help
• SelectAI – the APEX version