Exploring AI Capabilities in Oracle Database: A Simple Example

I've decided to put together this guide to demonstrate how effortlessly and swiftly you can leverage AI within Oracle Database. Everything covered here is readily available in our documentation, accessible at:

Oracle Autonomous Database AI Documentation

In the steps outlined below, I'll walk you through configuring and creating a simple test table to work with. Use your imagination to envision how this can benefit your business and kickstart your AI journey.

• The first step is to create a user that will serve as the schema and profile owner:

CREATE USER PP IDENTIFIED BY xxxxx;
GRANT EXECUTE ON DBMS_CLOUD_AI TO PP;        

• As an ADMIN user, create an ACL to access your AI provider (in my case, Cohere, which offers a free trial):

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

--Check if the ACL was created successfully
SELECT * FROM DBA_NETWORK_ACLS WHERE host = 'api.cohere.ai';
        

• Now, also as an ADMIN, create a credential using your API key:

EXEC DBMS_CLOUD.CREATE_CREDENTIAL('OPENAI_CRED', 'OPENAI', 'XYZXYZXYZXYZXYZXYZ');        

• Let's create an example table to use in our case:

CREATE TABLE PP.cars (
    name VARCHAR2(30),
    color VARCHAR2(30),
    price NUMBER
);

COMMENT ON COLUMN cars.name IS 'Car Name';
COMMENT ON COLUMN cars.color IS 'Car Color';
COMMENT ON COLUMN cars.price IS 'Car Price';

INSERT ALL 
INTO PP.cars (name, color, price) VALUES ('Volvo XC 60', 'Blue', 50000)
INTO PP.cars (name, color, price) VALUES ('Mercedes C300', 'Red', 40000)
INTO PP.cars (name, color, price) VALUES ('Audi A5', 'Brown', 30000)
INTO PP.cars (name, color, price) VALUES ('BMW X5', 'Silver', 70000)
INTO PP.cars (name, color, price) VALUES ('Volvo CX 90', 'Yellow', 100000)
SELECT 1 FROM dual;

COMMIT;
        

• Now, let's create a profile as the PP user, noting that you will set the table you want to use in AI:

--Create Profile
BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
      'COHERE_CARS',
      '{"provider": "cohere",
        "credential_name": "COHERE_CRED",
        "object_list": [{"owner": "PP", "name": "CARS"}]
       }');
       END;
/
select * from DBA_CLOUD_AI_PROFILES;        

• In your session as the PP user, set the profile you just created:

EXEC DBMS_CLOUD_AI.SET_PROFILE('COHERE_CARS');        

• Now you can just have fun using commands like below:

SQL> select ai how many cars exist;

   COUNT
________
       5

SQL> select ai showsql how many cars exist;

RESPONSE
________________________________________
SELECT COUNT(*) AS count
FROM PP.CARS

SQL> select ai what colors are cars;

COLOR
_________
Blue
Red
Brown
Silver
Yellow


SQL> select ai what price are cars;

NAME                 PRICE COLOR
________________ _________ _________
Volvo XC 60          50000 Blue
Mercedes C300        40000 Red
Audi A5              30000 Brown
BMW X5               70000 Silver
Volvo CX 90         100000 Yellow


SQL> select ai which car is the most expensive;

NAME               PRICE
______________ _________
Volvo CX 90       100000


SQL> select ai narrate how many cars exist;

RESPONSE
____________________________________________________________________
Based on the provided information, there are three columns (`NAME`, `PRICE`, `COLOR`) in the `CARS` table and thus there are potentially many cars, depending on the number of rows in the table. To see how many cars there are in the database, you can use a SQL query to count the number of rows in the table:
```sql
SELECT COUNT(*) AS total_cars
FROM PP.CARS;
```

This query will return a result set containing a single row with a column named `total_cars` that contains the total number of rows in the `CARS` table. Therefore, the number of cars in your database.




select ai chat how many cars exist;

select ai which car is the most expensive; 
        

We've just scratched the surface of what's possible when combining the power of AI with the versatility of Oracle Database. By seamlessly integrating AI capabilities into your database workflows, you can unlock new insights, automate tasks, and propel your business forward with greater efficiency and intelligence.

Experiment with the examples provided, explore the documentation further, and dare to imagine the endless possibilities AI can offer your organization. Whether it's analyzing data, generating insights, or enhancing user experiences, Oracle's AI features are here to support you on your journey towards innovation and success.

Start your AI journey today and witness the transformative impact it can have on your business operations. Embrace the future with Oracle Database and AI at your fingertips!