AI lets you chat with your data on Oracle Database

AI is a transformative force that is shaping our lives today. Once only domain experts understood the complex science and mathematics, but now it has become an integral part of everyone’s everyday life. Generative AI (GenAI) has captured global attention, especially from companies. Companies are seeking to leverage AI to bring more productivity and simplify the tasks for their employees. And Oracle is here to help them do it. Oracle is at the forefront of research and development to bring AI to businesses and users without having to worry about technological complexities.

Oracle has been a pioneer in this space by providing the requisite technology to run those intensive Large Learning Models (LLMs) for GenAI. Oracle Databases have also evolved to use the basic structure to hold the data required to generate context and provide the output, i.e., Vector Datatype. Oracle Database 23ai, which is the latest release can now do AI Vector Search that enables you to store data in the vector format that gives to the ability to do proximity search and generate relevant results from files, text and more.

But, I’m not here to talk about AI Vector Search, I wanted to talk about Select AI. As a business user, you don’t care where the data is, you care about the information from that data. Writing relevant SQL to get insights isn’t easy. You not only have to have a great understanding of your data but also you need to know how to efficiently write your SQL statements, especially if are dealing with large datasets. So what if I told you that you don’t have to do that anymore and it can be offloaded to AI, i.e., writing SQL? I know, mind-blowing! Now, let me show you how this can be done. For this blog, I am using Autonomous Database Dedicated (ADB-D), but you can use Autonomous Database Serverless too.

Get Phani Turlapati’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

I used a data set that I generated using AI. It's a flight data set that consists of flight information and passenger information (see below for a sample dataset).

Some pre-work is needed to set up the profile. First, you create a user and grant DBMS_CLOUD_AI package access to the user. Then you let the database access the LLM models from OpenAI, Cohere, etc. or train your model with OCI GenAI service and use that model.

-- create the user (as ADMIN)
grant execute on DBMS_CLOUD_AI to PHANI;

-- allow your database to access the api from LLM provider of your choice (as ADMIN)
begin  
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
         host => 'api.cohere.ai',
         ace  => xs$ace_type(privilege_list => xs$name_list('http'),
                             principal_name => 'PHANI',
                             principal_type => xs_acl.ptype_db)
   );
end;
/

Create credentials that you’ll use in your database to access those models. Once data is loaded, create an AI profile, add objects that you’d like to chat with, and start chatting with your data.

--create credetials to authenticate the request 
exec DBMS_CLOUD.CREATE_CREDENTIAL('COHERE_CRED', 'cohere', 'your_api_token');

-- verify if data is loaded and accurate
select * from AIRLINE_FLIGHT_DATA;
select * from AIRLINE_PASSENGER_DATA;

--drop any ai profile that was previously created
begin
    dbms_cloud_ai.DROP_PROFILE(
        profile_name => 'cohere'
    );
end;
/

-- create profile 
begin
    dbms_cloud_ai.CREATE_PROFILE(
        'cohere',
        '{ "provider" : "cohere",
           "credential_name" : "cohere_cred",
           "object_list" : [
                            {"owner" : "PHANI", "name" : "AIRLINE_FLIGHT_DATA"},
                            {"owner" : "PHANI", "name" : "AIRLINE_PASSENGER_DATA"}
                           ]
        }'
    );
end;
/

-- enable profile 
begin
    dbms_cloud_ai.ENABLE_PROFILE(
        profile_name => 'cohere'
    );
end;
/

--generate SQL for a prompt 
select dbms_cloud_ai.GENERATE(
    prompt => 'what is the flight number where the average price paid is highest ',
    profile_name => 'cohere',
    action => 'showsql'
) from dual;

-- verify those SQL and results they generate 
SELECT count(*) AS flights_originating_from_DFW
FROM "PHANI"."AIRLINE_FLIGHT_DATA"
WHERE "STARTING_AIRPORT" = upper('dfw');

SELECT "PHANI"."AIRLINE_FLIGHT_DATA"."FLIGHT_NUMBER" 
FROM "PHANI"."AIRLINE_FLIGHT_DATA" 
WHERE "STARTING_AIRPORT" = 'DFW' AND "DESTINATION_AIRPORT" = 'SFO';

SELECT AVG(NUMBER_OF_PASSENGERS) AS avg_passengers_on_flight
FROM PHANI.AIRLINE_FLIGHT_DATA
WHERE "STARTING_AIRPORT" = 'DFW'
AND "DESTINATION_AIRPORT" = 'SFO';

SELECT count(*) AS flights_count
FROM "PHANI"."AIRLINE_FLIGHT_DATA"
WHERE "STARTING_AIRPORT" = upper('dfw') AND "DESTINATION_AIRPORT" = upper('sfo');

-- set profile to use "select ai"
EXEC DBMS_CLOUD_AI.set_profile('COHERE');

-- start chatting with your data
select ai how many flight originate from 'DFW'

select ai how many flights run between dallas and san francisco airports (use airport codes)

select ai how many passangers were on flights on average in flights between dallas and san francisco airports (use airport codes)

select ai which is the busiest airport (use airport codes)

select ai what is the average price paid on each flight originating from dallas (use airport code)

select ai are there any international flights going out of united states airports or coming into united states airports (use airport codes)

select ai what is the flight number where the average price paid is highest 

select ai which passanger has paid the highest price

Some screenshots from my testing. It's pretty cool that you can chat with your data and get relevant answers from your data.

My observation: A lot of the queries worked, but there was some trouble applying masks, joining tables, and more complex questions, but I am attributing that to pre-trained free models, I hope that production-level models will be more accurate in generating better insights!

About the Author:

Phani Turlapati is a Product Manager at Oracle Inc. He’s been with Oracle for over 7 years. He started at the Solution Engineering Hub at Oracle focusing on various design solutions to help customer to value-add to their businesses. He’s a part of the Database PM Team focusing on all things database. Before Oracle, he had worked as a Software Engineer developing applications on the Java Platform.

The blog is my view and is not an official Oracle communication.

Feel free to comment on the above post. Your feedback is valuable!