Oracle Autonomous Database 23ai introduces Select AI, combining the power of generative AI with your database metadata to generate and execute SQL queries based on natural language input.
By configuring an AI profile in Oracle Database, you’ll be able to use the select ai command to chat with a selection of LLM backends, ask detailed questions about data and schemas, and use natural language to generate queries that are tailored to your schema.
In this article, we’ll learn about the functionality of the select ai command, including the following variants:
- select ai — Ask questions about your data by generating and running a query
- select ai chat — General purpose AI chat
- select ai showsql — Generate queries about your data using natural language
- select ai narrate — Return the generated queries results in natural language
Create an Autonomous Database instance
To use Select AI with Oracle Database, you’ll need an Autonomous Database instance, either in the cloud or using the adb-free container image. If you don’t already have a database instance, check out my step-by-step guide on how to get started with Autonomous Database with OCI Free tier Here.
The guide walks you through setting up an OCI (Free Tier) account to creating and connecting to your free-tier Autonomous Database instance. After signing up for a cloud account, it should only take a few minutes to get a database instance up and running!
Two license-included serverless instances of Autonomous Database are included in Oracle Cloud Infrastructure’s Always Free account tier. To get started with Autonomous Database, sign up for an OCI Free Tier account.
Add sample data to chat with the database about
Once the database instance is ready, we’ll install a simple customer-product-order schema into the database, where a customer can have multiple orders against various products.
You can find a copy of the schema, along with sample rows Here.
-- Simple customer/product/order schema. A customer may have one or more orders for various products.
create table customers (
customer_id number generated always as identity primary key,
first_name varchar2(50) not null,
last_name varchar2(50) not null
);
create table products (
product_id number generated always as identity primary key,
product_name varchar2(100) not null,
price number(10,2) not null
);
create table orders (
order_id number generated always as identity primary key,
customer_id number not null,
product_id number not null,
quantity number not null,
order_date date default sysdate,
total_amount number(10,2) not null,
constraint fk_customer foreign key (customer_id) references customers(customer_id),
constraint fk_product foreign key (product_id) references products(product_id)
);Configure an LLM for Select AI
Select AI requires an LLM backend — in this example, we’ll configure Select AI to use Oracle Cloud Infrastructure (OCI) Generative AI Service. If you’d like to use another AI provider, Select AI also supports OpenAI, Anthropic, Cohere, and other backends.
NOTE: You may need to apply the following grants for your database user to execute functions the DBMS_CLOUD and DBMS_CLOUD_AI packages.
GRANT EXECUTE ON DBMS_CLOUD TO <username>;
GRANT EXECUTE ON DBMS_CLOUD_AI TO <username>;First, we’ll add a credential to authenticate to OCI, using an OCI user OCID, tenancy OCID, private key, and fingerprint. If you don’t know your user information, you can find or generate your private key on the OCI console user homepage.
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'GENAI_CRED',
user_ocid => 'Your User OCID',
tenancy_ocid => 'Your Tenancy OCID',
private_key => 'Your Private Key in PEM format',
fingerprint => 'Your Private Key Fingerprint'
);
END;
/You can find the documentation for the DBMS_CLOUD.CREATE_CREDENTIAL procedure Here.
Next, we’ll create an AI profile using the credential, and grant it access to the tables in the customer-product-order schema. Note that we use the oci provider for our AI profile, signaling to the database that we’ll be connecting to the OCI Generative AI backend.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GENAI',
attributes =>'{"provider": "oci",
"credential_name": "GENAI_CRED",
"object_list": [{"owner": "ADMIN", "name": "customers"},
{"owner": "ADMIN", "name": "products"},
{"owner": "ADMIN", "name": "orders"}]
}');
END;
/You can find the full set of AI profile parameters from the DBMS_CLOUD_AI package Here.
Finally, we’ll configure the current database session to use our new AI profile. Note that this is only resident for the duration of your session.
EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');Chatting with our Autonomous Database
Now that our AI profile is configured, we can start chatting with our database, generating queries and asking questions about the data.
Let’s ask a simple question to verify that the AI profile was configured correctly using the select ai chat command. The chat command can be used for general purpose LLM interactions.
select ai chat what is Autonomous Database;You should get a response back explaining what Oracle Autonomous Database is from the AI profile’s LLM backend. If you get the ORA-00923: FROM keyword not found where expected error, ensure your AI profile is set by running select
DBMS_CLOUD_AI.get_profile() and exec DBMS_CLOUD_AI.set_profile('GENAI')Let’s ask the database a question specific to our data, like what our top three most profitable products are. Note that the question can be free form, but should relate to the data in our schema for the best results.
select ai what are my top three most profitable products;If you loaded the sample data, you should get a result similar to the following:
To view the generated query, we’ll use the select ai showsql command. Let’s try this for another question.
select ai showsql what are the top 5 products I shipped the most units of;
-- Database AI response, if you loaded the sample schema:
SELECT p."PRODUCT_NAME" AS product_name, SUM(o."QUANTITY") AS total_units
FROM "ADMIN"."ORDERS" o
JOIN "ADMIN"."PRODUCTS" p ON o."PRODUCT_ID" = p."PRODUCT_ID"
GROUP BY p."PRODUCT_NAME"
ORDER BY total_units DESC
FETCH FIRST 5 ROWS ONLYFinally, let’s use select ai narrate to run the previous query, but instead return the results as natural language.
select ai narrate what are the top 5 products I shipped the most units of;
-- Response
Here are the top 5 products I shipped the most units of:
1. Wireless Mouse - 8 units
2. External Hard Drive - 6 units
3. Headphones - 4 units
4. Smartphone - 3 units
5. Tablet - 3 unitsOnce you’re done, you can drop the AI profile using the EXEC DBMS_CLOUD_AI.DROP_PROFILE('GENAI') command to clean up.
That’s the basics of Select AI with Oracle Database! The ability to chat with your database and leverage its internal schemas as input can provide huge leaps in productivity and the ability to better understand your data model.
Questions? Let me know in the comments, or reach out directly.
Leave a Reply