Examples of Using Select AI
Explore integrating Oracle's Select AI with various supported AI providers to generate, run, and explain SQL from natural language prompts or chat with the LLM.
- Example: Select AI Actions
These examples showcase common Select AI actions and guide you through setting up your profile with different AI providers to leverage those actions. - Example: Select AI with OpenAI
This example shows how you can use OpenAI to generate SQL statements from natural language prompts. - Example: Select AI with Cohere
This example shows how you can use Cohere to generate SQL statements from natural language prompts. - Example: Select AI with Azure OpenAI Service
The following examples shows how you can enable access to Azure OpenAI Service using your API key or use Azure OpenAI Service Principle, create an AI profile, and generate SQL from natural language prompts. - Example: Select AI with OCI Generative AI
These examples show how you can access OCI Generative AI using your OCI API key or Resource Principal, create an AI profile, and generate, run, and explain SQL from natural language prompts or chat using the OCI Generative AI LLMs. - Example: Select AI with Google
This example shows how you can use Google to generate, run, and explain SQL from natural language prompts or chat using the Google Gemini LLM. - Example: Set Up and Use Select AI with RAG
This example guides you through setting up credentials, configuring network access, and creating a vector index for integrating OCI Generative AI vector store cloud services with OpenAI using Oracle Autonomous Database. - Example: Improve SQL Query Generation with Table and Column Comments
This example demonstrates how comments in database tables and columns can improve the generation of SQL queries from natural language prompts.
Example: Select AI Actions
These examples showcase common Select AI actions and guide you through setting up your profile with different AI providers to leverage those actions.
The following example illustrates actions such as
runsql, showsql, narrate,
chat, and explainsql that you can perform with
SELECT AI. These examples use the sh schema
with AI provider and profile attributes set in the DBMS_CLOUD_AI.CREATE_PROFILE function.
SQL> select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
SQL> select ai showsql how many customers exist;
RESPONSE
----------------------------------------------------
SELECT COUNT(*) AS total_customers
FROM SH.CUSTOMERS
SQL> select ai narrate how many customers exist;
RESPONSE
------------------------------------------------------
There are a total of 55,500 customers in the database.
SQL> select ai chat how many customers exist;
RESPONSE
--------------------------------------------------------------------------------
It is impossible to determine the exact number of customers that exist as it con
stantly changes due to various factors such as population growth, new businesses
, and customer turnover. Additionally, the term "customer" can refer to individu
als, businesses, or organizations, making it difficult to provide a specific num
ber.
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
- 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
- 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
Remember to adjust the table and column names based on your actual schema if they differ from the example.
Feel free to ask if you have more questions related to SQL or database in general.Parent topic: Examples of Using Select AI
Example: Select AI with OpenAI
This example shows how you can use OpenAI to generate SQL statements from natural language prompts.
Note:
Only a DBA can runEXECUTE privileges and network ACL procedure.
--Grants EXECUTE privilege to ADB_USER
--
SQL> grant execute on DBMS_CLOUD_AI to ADB_USER;
-- Grant Network ACL for OpenAI endpoint
--
SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api.openai.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create Credential for AI provider
--
SQL> EXEC DBMS_CLOUD.CREATE_CREDENTIAL('OPENAI_CRED', 'OPENAI', '<your api token>');
PL/SQL procedure successfully completed.
--
-- Create AI profile
--SQL> BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
'OPENAI',
'{"provider": "openai",
"credential_name": "OPENAI_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"conversation": "true"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('OPENAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"OPENAI"
--
-- Use AI
--
SQL> select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
SQL> select ai how many customers in San Francisco are married;
MARRIED_CUSTOMERS
-----------------
18
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS married_customers_count
FROM SH.CUSTOMERS c
WHERE c.CUST_CITY = 'San Francisco'
AND c.CUST_MARITAL_STATUS = 'Married'
SQL> select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
--------------------------------------------------------------------------------
The top 3 customers in San Francisco are:
1. Hector Colven - Total amount sold: $52,025.99
2. Milburn Klemm - Total amount sold: $50,842.28
3. Gavin Xie - Total amount sold: $48,677.18
SQL> select ai chat what is Autonomous Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous Database is a cloud-based database service provided by Oracle. It is
designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous Database uses machine learning
and automation to optimize performance, security, and availability, allowing us
ers to focus on their applications and data rather than database administration
tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomo
us Database provides high performance, scalability, and reliability, making it a
n ideal choice for modern cloud-based applications.
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
- 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
- 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
Remember to adjust the table and column names based on your actual schema if they differ from the example.
Feel free to ask if you have more questions related to SQL or database in general.
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('OPENAI');
PL/SQL procedure successfully completed.Parent topic: Examples of Using Select AI
Example: Select AI with Cohere
This example shows how you can use Cohere to generate SQL statements from natural language prompts.
Note:
Only a DBA can runEXECUTE privileges and network ACL procedure.
--Grants EXECUTE privilege to ADB_USER
--
SQL>grant execute on DBMS_CLOUD_AI to ADB_USER;
--
-- Create Credential for AI provider
--
SQL> EXEC DBMS_CLOUD.CREATE_CREDENTIAL('COHERE_CRED', 'COHERE', '<your api token>');
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for Cohere endpoint
--
SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api.cohere.ai',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--SQL> BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
'COHERE',
'{"provider": "cohere",
"credential_name": "COHERE_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "sales"},
{"owner": "SH", "name": "products"},
{"owner": "SH", "name": "countries"}]
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('COHERE');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"COHERE"
--
-- Use AI
--
SQL> select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('COHERE');
PL/SQL procedure successfully completed.Parent topic: Examples of Using Select AI
Example: Select AI with Azure OpenAI Service
The following examples shows how you can enable access to Azure OpenAI Service using your API key or use Azure OpenAI Service Principle, create an AI profile, and generate SQL from natural language prompts.
-- Create Credential for AI integration
--
SQL> EXEC DBMS_CLOUD.CREATE_CREDENTIAL('AZURE_CRED', 'AZUREAI', '<your api token>');
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for OpenAI endpoint
--SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => '<azure_resource_name>.openai.azure.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADMIN',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
SQL> BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
'AZUREAI',
'{"provider": "azure",
"azure_resource_name": "<azure_resource_name>",
"azure_deployment_name": "<azure_deployment_name>"
"credential_name": "AZURE_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"conversation": "true"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"AZUREAI"
--
-- Use AI
--
SQL> select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
SQL> select ai how many customers in San Francisco are married;
MARRIED_CUSTOMERS
-----------------
18
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS married_customers_count
FROM SH.CUSTOMERS c
WHERE c.CUST_CITY = 'San Francisco'
AND c.CUST_MARITAL_STATUS = 'Married'
SQL> select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
--------------------------------------------------------------------------------
The top 3 customers in San Francisco are:
1. Hector Colven - Total amount sold: $52,025.99
2. Milburn Klemm - Total amount sold: $50,842.28
3. Gavin Xie - Total amount sold: $48,677.18
SQL> select ai chat what is Autonomous Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous Database is a cloud-based database service provided by Oracle. It is
designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous Database uses machine learning
and automation to optimize performance, security, and availability, allowing us
ers to focus on their applications and data rather than database administration
tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomo
us Database provides high performance, scalability, and reliability, making it a
n ideal choice for modern cloud-based applications.
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
- 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
- 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
Remember to adjust the table and column names based on your actual schema if they differ from the example.
Feel free to ask if you have more questions related to SQL or database in general.
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.Example: Select AI with Azure OpenAI Service Principle
Connect as a database administrator to provide access to Azure service
principle authentication and then grant the network ACL permissions to the user
(ADB_USER) who wants to use Select AI. To provide access to
Azure resources, see Use Azure Service Principal to Access Azure Resources.
Note:
Only a DBA user can runEXECUTE privileges and network ACL
procedure.
-- Connect as ADMIN user and enable Azure service principal authentication.
BEGIN
DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(provider => 'AZURE',
params => JSON_OBJECT('azure_tenantid' value 'azure_tenantid'));
END;
/
-- Copy the consent url from cloud_integrations view and consents the ADB-S application.
SQL> select param_value from CLOUD_INTEGRATIONS where param_name = 'azure_consent_url';
PARAM_VALUE
--------------------------------------------------------------------------------
https://login.microsoftonline.com/<tenant_id>/oauth2/v2.0/authorize?client_id=<client_id>&response_type=code&scope=User.read
-- On the Azure OpenAI IAM console, search for the Azure application name and assign the permission to the application.
-- You can get the application name in the cloud_integrations view.
SQL> select param_value from CLOUD_INTEGRATIONS where param_name = 'azure_app_name';
PARAM_VALUE
--------------------------------------------------------------------------------
ADBS_APP_DATABASE_OCID
--
-- Grant Network ACL for Azure OpenAI endpoint
--SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'azure_resource_name.openai.azure.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--SQL> BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
'AZUREAI',
'{"provider": "azure",
"credential_name": "AZURE$PA",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"azure_resource_name": "<azure_resource_name>",
"azure_deployment_name": "<azure_deployment_name>"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"AZUREAI"
--
-- Use AI
--
SQL> select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
SQL> select ai how many customers in San Francisco are married;
MARRIED_CUSTOMERS
-----------------
18
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS married_customers_count
FROM SH.CUSTOMERS c
WHERE c.CUST_CITY = 'San Francisco'
AND c.CUST_MARITAL_STATUS = 'Married'
SQL> select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
--------------------------------------------------------------------------------
The top 3 customers in San Francisco are:
1. Hector Colven - Total amount sold: $52,025.99
2. Milburn Klemm - Total amount sold: $50,842.28
3. Gavin Xie - Total amount sold: $48,677.18
SQL> select ai chat what is Autonomous Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous Database is a cloud-based database service provided by Oracle. It is
designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous Database uses machine learning
and automation to optimize performance, security, and availability, allowing us
ers to focus on their applications and data rather than database administration
tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomo
us Database provides high performance, scalability, and reliability, making it a
n ideal choice for modern cloud-based applications.
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
- 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
- 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
Remember to adjust the table and column names based on your actual schema if they differ from the example.
Feel free to ask if you have more questions related to SQL or database in general.
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.Parent topic: Examples of Using Select AI
Example: Select AI with OCI Generative AI
These examples show how you can access OCI Generative AI using your OCI API key or Resource Principal, create an AI profile, and generate, run, and explain SQL from natural language prompts or chat using the OCI Generative AI LLMs.
Note:
OCI Generative AI usesmeta.llama-3-70b-instruct as the default model if you do not
specify the model_name. To learn more about the parameters, see
Profile Attributes.
-- Create Credential with OCI API key
--
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'GENAI_CRED',
user_ocid => 'ocid1.user.oc1..aaaa...',
tenancy_ocid => 'ocid1.tenancy.oc1..aaaa...',
private_key => '<your_api_key>',
fingerprint => '<your_fingerprint>'
);
END;
/
--
-- Create AI profile
--
SQL> BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GENAI',
attributes =>'{"provider": "oci",
"credential_name": "GENAI_CRED"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"GENAI"
--
-- Use AI
--
SQL> select ai chat what is Autonomous Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous Database is a cloud-based database service provided by Oracle. It is
designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous Database uses machine learning
and automation to optimize performance, security, and availability, allowing us
ers to focus on their applications and data rather than database administration
tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomo
us Database provides high performance, scalability, and reliability, making it a
n ideal choice for modern cloud-based applications.
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('GENAI');
PL/SQL procedure successfully completed.Example 5-1 Select AI with OCI Generative AI Resource Principal
To use resource principal with OCI Generative AI, Oracle Cloud Infrastructure tenancy administrator must grant access for Generative AI resources to a dynamic group. See Perform Prerequisites to Use Resource Principal with Autonomous Database to provide access to a dynamic group.
Set the required policies to obtain access to all Generative AI
resources. See Getting Access to Generative
AI to know more about Generative AI policies.
-
To get access to all Generative AI resources in the entire tenancy, use the following policy:
allow group <your-group-name> to manage generative-ai-family in tenancy -
To get access to all Generative AI resources in your compartment, use the following policy:
allow group <your-group-name> to manage generative-ai-family in compartment <your-compartment-name>
Connect as an administrator and enable OCI resource principal. See ENABLE_PRINCIPAL_AUTH Procedure to configure the parameters.
Note:
OCI Generative AI usesmeta.llama-3-70b-instruct as the default model if you do not
specify the model. To learn more about the parameters, see Profile Attributes.
-- Connect as Administrator user and enable OCI resource principal.
BEGIN
DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(provider => 'OCI');
END;
/
--
-- Create AI profile
--SQL>BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GENAI',
attributes =>'{"provider": "oci",
"credential_name": "OCI$RESOURCE_PRINCIPAL"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL>EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"GENAI"
-- Use AI
SQL> select ai chat what is Autonomous Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous Database is a cloud-based database service provided by Oracle. It is
designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous Database uses machine learning
and automation to optimize performance, security, and availability, allowing us
ers to focus on their applications and data rather than database administration
tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomo
us Database provides high performance, scalability, and reliability, making it a
n ideal choice for modern cloud-based applications.
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('GENAI');
PL/SQL procedure successfully completed.Example: Select AI with OCI
Generative AI Using LLAMA Model
This example showcases the
chat feature of the LLAMA model from OCI Generative AI. It
highlights the model's capabilities through two prompts: generating a friendly email
to customers for movie recommendations and generate an introductory paragraph on
rock
climbing.
SQL> BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'GENAI_CRED',
user_ocid => 'ocid1.user.oc1..aaa',
tenancy_ocid => 'ocid1.tenancy.oc1..aaa',
private_key => '<your_api_key>',
fingerprint => '<your_fingerprint>'
);
END;
/
PL/SQL procedure successfully completed.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'GENAI',
attributes =>'{"provider": "oci",
"model": "meta.llama-2-70b-chat",
"oci_runtimetype":"LLAMA",
"credential_name": "GENAI_CRED",
"oci_compartment_id": "ocid1.compartment.oc1..."}');
END;
/
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');
PL/SQL procedure successfully completed.
SQL> set linesize 150
SQL> SELECT AI chat Generate a friendly email to customer Gilbert Lee with two action-thriller movie recommendations available through our MovieStream service;
SQL>
RESPONSE
------------------------------------------------------------------------------------------------------------------------------------------------------
.
Subject: Action-packed movie recommendations for you!
Dear Gilbert,
I hope this email finds you well! I wanted to reach out to you today to recommend two action-thriller movies that are currently available on our Movie
Stream service. I think you'll really enjoy them!
The first movie I recommend is "John Wick" starring Keanu Reeves. This movie follows the story of a retired hitman who seeks vengeance against a power
ful crime lord and his army of assassins. The action scenes are intense and non-stop, and Keanu Reeves delivers an outstanding performance.
RESPONSE
------------------------------------------------------------------------------------------------------------------------------------------------------
The second movie I recommend is "Mission: Impossible - Fallout" starring Tom Cruise. This movie follows Ethan Hunt and his team as they try to prevent
a global catastrophe. The action scenes are heart-stopping and the stunts are truly impressive. Tom Cruise once again proves why he's one of the grea
test action stars of all time.
Both of these movies are sure to keep you on the edge of your seat and provide plenty of thrills and excitement. They're available to stream now on Mo
vieStream, so be sure to check them out!
If you have any questions or need assistance with MovieStream, please don't hesitate to reach out to me. I'm always here to help.
Thank you for being a valued customer, and I hope you enjoy the movies!
RESPONSE
------------------------------------------------------------------------------------------------------------------------------------------------------
Best regards,
[Your Name]
MovieStream Customer Service
SQL> SELECT AI chat Write an enthusiastic introductory paragraph on how to get started with rock climbing with Athletes as the target audience;
RESPONSE
------------------------------------------------------------------------------------------------------------------------------------------------------
Rock climbing is an exhilarating and challenging sport that's perfect for athletes looking to push their limits and test their strength, endurance, an
d mental toughness. Whether you're a seasoned athlete or just starting out, rock climbing offers a unique and rewarding experience that will have you
hooked from the very first climb. With its combination of physical and mental challenges, rock climbing is a great way to build strength, improve flex
ibility, and develop problem-solving skills. Plus, with the supportive community of climbers and the breathtaking views from the top of the climb, you
'll be hooked from the very first climb. So, if you're ready to take on a new challenge and experience the thrill of adventure, then it's time to get
started with rock climbing!Using OCI Generative AI with the Default Model
The following example uses the default OCI Generative AI Chat Model,
meta.llama-3-70b-instruct. OCI Generative AI uses
meta.llama-3-70b-instruct as the default model if you do not
specify the
model.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_DEFAULT',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADMIN"}]
}');
END;
/Using OCI Generative AI with Chat Model
The
following example uses cohere.command-r-plus as the OCI Generative
AI Chat
Model.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_COHERE_COMMAND_R_PLUS',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADMIN"}],
"model": "cohere.command-r-plus"
}');
END;
/Using OCI Generative AI with Chat Model Endpoint ID
The following example demonstrates how to specify the OCI Generative AI
Chat Model endpoint ID instead of the
model.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_CHAT_ENDPOINT',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADMIN"}],
"oci_endpoint_id": "<endpoint_id>",
"oci_apiformat": "GENERIC"
}');
END;
/Using OCI Generative AI with Chat Model OCID
This example demonstrates how to specify the OCI Generative AI Chat Model OCID as
the
model.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_CHAT_OCID',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADMIN"}],
"model": "<model_ocid>",
"oci_apiformat": "COHERE"
}');
END;
/Using OCI Generative AI with Generation Model
This example demonstrates how to specify the OCI Generative AI Generation Model
such as cohere.command as the
model.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_COHERE_COMMAND',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADMIN"}],
"model": "cohere.command"
}');
END;
/ Using OCI Generative AI with Generation Model Endpoint ID
This example demonstrates how to use the OCI Generative AI Generation
Model endpoint ID instead of the
model.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_GENTEXT_ENDPOINT',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADMIN"}],
"oci_endpoint_id": "<endpoint_id>"
"oci_runtimetype": "COHERE"
}');
END;
/Using OCI Generative AI with Generation Model OCID
This example demonstrates how to specify the OCI Generative AI
Generation Model OCID as the
model.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_GENTEXT_OCID',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADMIN"}],
"model": "<model_ocid>"
"oci_runtimetype": "LLAMA"
}');
END;
/Parent topic: Examples of Using Select AI
Example: Select AI with Google
This example shows how you can
use Google to generate, run, and explain SQL from natural language prompts or chat using
the Google Gemini LLM.
The following example demonstrates using Google as your AI provider. The example demonstrates using your Google API signing key to provide network access, creating an AI profile, using Select AI actions to generate SQL queries from natural language prompts and chat responses.
--Grants EXECUTE privilege to ADB_USER
--
SQL> grant EXECUTE on DBMS_CLOUD_AI to ADB_USER;
--
-- Create Credential for AI provider
--
SQL> BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'GOOGLE_CRED',
username => 'GOOGLE',
password => '<your_api_key>'
);
END;
/
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for Google endpoint
--
SQL>
SQL> BEGIN
DBMS_NETWORK_ACL_ADB_USER.APPEND_HOST_ACE(
host => 'generativelanguage.googleapis.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
SQL> BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GOOGLE',
attributes =>'{"provider": "google",
"credential_name": "GOOGLE_CRED",
"object_list": [{"owner": "ADB_USER", "name": "users"},
{"owner": "ADB_USER", "name": "movies"},
{"owner": "ADB_USER", "name": "genres"},
{"owner": "ADB_USER", "name": "watch_history"},
{"owner": "ADB_USER", "name": "movie_genres"}
]
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('GOOGLE');
PL/SQL procedure successfully completed.
--
-- Use AI
--
SQL> select ai list the year that most of the movies are released;
RELEASE_YEAR
------------
2020
1 row selected.
SQL> select ai showsql List movies watched by users but not in genre 'Comedy';
RESPONSE
--------------------------------------------------------------------------------
SELECT DISTINCT m.TITLE AS MOVIE_TITLE FROM "ADMIN"."MOVIES" m JOIN "ADMIN"."WAT
CH_HISTORY" wh ON m.MOVIE_ID = wh.MOVIE_ID JOIN "ADMIN"."MOVIE_GENRES" mg ON m.M
OVIE_ID = mg.MOVIE_ID JOIN "ADMIN"."GENRES" g ON mg.GENRE_ID = g.GENRE_ID WHERE
g.GENRE_NAME != 'Comedy'
1 row selected.
SQL> select ai showsql Show users who have watched at least one movie from each genre;
RESPONSE
--------------------------------------------------------------------------------
SELECT DISTINCT "ADMIN"."USERS"."USER_NAME" FROM "ADMIN"."USERS" JOIN "ADMIN"."W
ATCH_HISTORY" ON "ADMIN"."USERS"."USER_ID" = "ADMIN"."WATCH_HISTORY"."USER_ID" J
OIN "ADMIN"."MOVIES" ON "ADMIN"."WATCH_HISTORY"."MOVIE_ID" = "ADMIN"."MOVIES"."M
OVIE_ID" JOIN "ADMIN"."MOVIE_GENRES" ON "ADMIN"."MOVIES"."MOVIE_ID" = "ADMIN"."M
OVIE_GENRES"."MOVIE_ID" JOIN "ADMIN"."GENRES" ON "ADMIN"."MOVIE_GENRES"."GENRE_I
D" = "ADMIN"."GENRES"."GENRE_ID" GROUP BY "ADMIN"."USERS"."USER_NAME" HAVING COU
NT(DISTINCT "ADMIN"."GENRES"."GENRE_ID") = (SELECT COUNT(*) FROM "ADMIN"."GENRES
")
1 row selected.
SQL> select ai explainsql the top 3 most popular genres based on movie watch counts;
RESPONSE
--------------------------------------------------------------------------------
```sql
SELECT
g."GENRE_NAME" AS "Genre Name",
COUNT(DISTINCT wh."WATCH_ID") AS "Watch Count"
FROM
"ADMIN"."GENRES" g
JOIN
"ADMIN"."MOVIE_GENRES" mg ON g."GENRE_ID" = mg."GENRE_ID"
JOIN
"ADMIN"."MOVIES" m ON mg."MOVIE_ID" = m."MOVIE_ID"
JOIN
"ADMIN"."WATCH_HISTORY" wh ON m."MOVIE_ID" = wh."MOVIE_ID"
GROUP BY
g."GENRE_NAME"
ORDER BY
"Watch Count" DESC
FETCH FIRST 3 ROWS ONLY;
```
**Explanation:**
1. **FROM and JOIN Clauses:**
- We start by selecting data from the "GENRES" table (aliased as "g") to get
the genre names.
- We join it with "MOVIE_GENRES" (aliased as "mg") on the common column "GENR
E_ID" to link genres to movies.
- We join "MOVIE_GENRES" with "MOVIES" (aliased as "m") using "MOVIE_ID" to c
onnect movies to their genres.
- Finally, we join "MOVIES" with "WATCH_HISTORY" (aliased as "wh") using "MOV
IE_ID" to connect movies to watch history records.
2. **SELECT Clause:**
- `g."GENRE_NAME" AS "Genre Name"`: Selects the genre name from the "GENRES"
table and aliases it as "Genre Name".
- `COUNT(DISTINCT wh."WATCH_ID") AS "Watch Count"`: Counts the distinct watch
IDs to get the number of times movies in each genre have been watched. We use D
ISTINCT to avoid counting the same watch record multiple times if a movie belong
s to multiple genres.
3. **GROUP BY Clause:**
- We group the results by "Genre Name" to count watch counts for each distinc
t genre.
4. **ORDER BY Clause:**
- `ORDER BY "Watch Count" DESC`: Sorts the result set in descending order bas
ed on the "Watch Count" column to get the most popular genres first.
5. **FETCH FIRST 3 ROWS ONLY:**
- This clause limits the result set to the top 3 rows, effectively returning
only the top 3 most popular genres.
**Key Points:**
- **Case Sensitivity:** Oracle SQL is case-sensitive. We use double quotes to en
close table and column names to preserve their original casing.
- **Table and Column Aliases:** Using aliases makes the query more readable and
concise.
- **DISTINCT:** Ensures accurate watch count by eliminating duplicates from the
watch history records.
- **ORDER BY and FETCH FIRST:** These clauses effectively retrieve the top 3 mos
t popular genres.
**Key Points:**
- **Case Sensitivity:** Oracle SQL is case-sensitive. We use double quotes to en
close table and column names to preserve their original casing.
- **Table and Column Aliases:** Using aliases makes the query more readable and
concise.
- **DISTINCT:** Ensures accurate watch count by eliminating duplicates from the
watch history records.
- **ORDER BY and FETCH FIRST:** These clauses effectively retrieve the top 3 mos
t popular genres.
1 row selected.
SQL> select ai narrate Show the average and total watch counts per genre;
RESPONSE
--------------------------------------------------------------------------------
The answer shows the total and average number of times movies belonging to each
genre were watched.
For example:
- Action genre movies were watched 3 times in total and the average watch count
for Action genre movies is 3.
- Comedy genre movies were watched 3 times in total and the average watch count
for Comedy genre movies is 3.
- Drama genre movies were watched 2 times in total and the average watch count f
or Drama genre movies is 2.
1 row selected.
SQL> select ai chat how long the history of the movie industry is;
RESPONSE
--------------------------------------------------------------------------------
The history of the movie industry is long and complex, spanning over **130 years
**.
Here's a quick timeline to give you an idea:
* **1890s:** The first motion pictures were created, leading to the development
of early film cameras and projectors.
* **1900s:** The first movie theaters opened, and early film studios began to em
erge.
* **1910s:** The rise of Hollywood as a center for film production, and the deve
lopment of narrative storytelling in film.
* **1920s:** The introduction of sound to film, marking a significant shift in t
he industry.
* **1930s:** The Golden Age of Hollywood, with the rise of iconic stars and stud
ios.
* **1940s:** The industry grapples with World War II and the rise of television.
* **1950s:** The introduction of color film and wide-screen formats.
* **1960s:** The rise of independent cinema and the New Hollywood era.
* **1970s:** The rise of blockbuster movies and the influence of Hollywood on gl
obal culture.
* **1980s:** The rise of home video and the increasing influence of technology i
n filmmaking.
* **1990s:** The digital revolution in film and the emergence of new distributio
n platforms.
* **2000s:** The rise of streaming services and the continued impact of technolo
gy on the industry.
The movie industry has gone through many changes throughout its history, but its
impact on culture and entertainment remains undeniable.
1 row selected.Parent topic: Examples of Using Select AI
Example: Set Up and Use Select AI with RAG
This example guides you through setting up credentials, configuring network access, and creating a vector index for integrating OCI Generative AI vector store cloud services with OpenAI using Oracle Autonomous Database.
The setup concludes with creating an AI profile that uses the vector
index to enhance LLM responses. Finally, this example uses the Select AI
narrate action, which returns a response that has been enhanced
using information from the specified vector database.
The following example demonstrates building and querying vector index in Oracle 23ai.
SQL> -- Create the OpenAI credential
SQL> BEGIN
2 DBMS_CLOUD.CREATE_CREDENTIAL(
3 credential_name => 'OPENAI_CRED',
4 username => 'OPENAI_CRED',
5 password => '<your_api_key>'
6 );
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> -- Append the OpenAI endpoint
SQL> BEGIN
2 DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
3 host => 'api.openai.com',
4 ace => xs$ace_type(privilege_list => xs$name_list('http'),
5 principal_name => 'ADMIN',
6 principal_type => xs_acl.ptype_db)
7 );
8 END;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL> -- Create the object store credential
SQL> BEGIN
2 DBMS_CLOUD.CREATE_CREDENTIAL(
3 credential_name => 'OCI_CRED',
4 username => '<your_username>',
5 password => '<OCI_profile_password>'
6 );
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> -- create a vector index with the vector store name, object store location and
SQL> -- object store credential
SQL> BEGIN
DBMS_CLOUD_AI.CREATE_VECTOR_INDEX(
index_name => 'MY_INDEX',
attributes => '{"vector_db_provider": "oracle",
"location": "https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/my_namespace/my_bucket/my_data_folder",
"object_storage_credential_name": "OCI_CRED",
"profile_name": "OPENAI_ORACLE",
"vector_dimension": 1536,
"vector_distance_metric": "cosine",
"chunk_overlap":128,
"chunk_size":1024
}');
END;
/
PL/SQL procedure successfully completed.
SQL>
SQL> -- Create the profile with the vector index.
SQL> BEGIN
2 DBMS_CLOUD_AI.CREATE_PROFILE(
3 profile_name =>'OPENAI_ORACLE',
4 attributes =>'{"provider": "openai",
5 "credential_name": "OPENAI_CRED",
6 "vector_index_name": "MY_INDEX",
7 "temperature": 0.2,
8 "max_tokens": 4096,
9 "model": "gpt-3.5-turbo-1106"
10 }');
11 end;
12 /
PL/SQL procedure successfully completed.
SQL>
SQL> -- Set profile
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('OPENAI_ORACLE');
PL/SQL procedure successfully completed.
SQL> -- After the vector index is populated, we can now query the index.
SQL> conn admin/<password>@pdb_name;
Connected.
SQL>
SQL> -- Set profile
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('OPENAI_ORACLE');
PL/SQL procedure successfully completed.
SQL> -- Select AI answers the question with the knowledge available in the vector database.
SQL> set pages 1000
SQL> set linesize 150
SQL> select AI narrate how can I deploy an oracle machine learning model;
RESPONSE
To deploy an Oracle Machine Learning model, you would first build your model within the Oracle database. Once your in-database models are built, they become immediately available for use, for instance, through a SQL query using the prediction operators built into the SQL language.
The model scoring, like model building, occurs directly in the database, eliminating the need for a separate engine or environment within which the model and corresponding algorithm code operate. You can also use models from a different schema (user account) if the appropriate permissions are in place.
Sources:
- Manage-your-models-with-Oracle-Machine-Learning-on-Autonomous-Database.txt (https://objectstorage.../v1/my_namespace/my_bucket/my_data_folder)
- Develop-and-deploy-machine-learning-models-using-Oracle-Autonomous-Database-Machine-Learning-and-APEX.txt (https://objectstorage.../v1/my_namespace/my_bucket/my_data_folder)
Parent topic: Examples of Using Select AI
Example: Improve SQL Query Generation with Table and Column Comments
This example demonstrates how comments in database tables and columns can improve the generation of SQL queries from natural language prompts.
In this example, Azure OpenAI Service acts as the AI provider. The
"comments":"true"
parameter in DBMS_CLOUD_AI.CREATE_PROFILE function determines whether
comments are passed to the model for SQL generation.
-- Adding comments to table 1, table 2, and table 3. Table 1 has 3 columns, table 2 has 7 columns, table 3 has 2 columns.
-- TABLE1
COMMENT ON TABLE table1 IS 'Contains movies, movie titles and the year it was released';
COMMENT ON COLUMN table1.c1 IS 'movie ids. Use this column to join to other tables';
COMMENT ON COLUMN table1.c2 IS 'movie titles';
COMMENT ON COLUMN table1.c3 IS 'year the movie was released';
-- TABLE2
COMMENT ON TABLE table2 IS 'transactions for movie views - also known as streams';
COMMENT ON COLUMN table2.c1 IS 'day the movie was streamed';
COMMENT ON COLUMN table2.c2 IS 'genre ids. Use this column to join to other tables';
COMMENT ON COLUMN table2.c3 IS 'movie ids. Use this column to join to other tables';
COMMENT ON COLUMN table2.c4 IS 'customer ids. Use this column to join to other tables';
COMMENT ON COLUMN table2.c5 IS 'device used to stream, watch or view the movie';
COMMENT ON COLUMN table2.c6 IS 'sales from the movie';
COMMENT ON COLUMN table2.c7 IS 'number of views, watched, streamed';
-- TABLE3
COMMENT ON TABLE table3 IS 'Contains the genres';
COMMENT ON COLUMN table3.c1 IS 'genre id. use this column to join to other tables';
COMMENT ON COLUMN table3.c2 IS 'name of the genre';
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'myprofile',
attributes =>
'{"provider": "azure",
"azure_resource_name": "my_resource",
"azure_deployment_name": "my_deployment",
"credential_name": "my_credential",
"comments":"true",
"object_list": [
{"owner": "moviestream", "name": "table1"},
{"owner": "moviestream", "name": "table2"},
{"owner": " moviestream", "name": "table3"}
]
}'
);
DBMS_CLOUD_AI.SET_PROFILE(
profile_name => 'myprofile'
);
END;
/
--Prompts
select ai what are our total views;
RESPONSE
-------------------------------------------------
TOTAL_VIEWS
-----------
97890562
select ai showsql what are our total views;
RESPONSE
-------------------------------------------------------------------------
SELECT SUM(QUANTITY_SOLD) AS total_views
FROM "moviestream"."table"
select ai what are our total views broken out by device;
DEVICE TOTAL_VIEWS
-------------------------- -----------
mac 14719238
iphone 20793516
ipad 15890590
pc 14715169
galaxy 10587343
pixel 10593551
lenovo 5294239
fire 5296916
8 rows selected.
select ai showsql what are our total views broken out by device;
RESPONSE
---------------------------------------------------------------------------------------
SELECT DEVICE, COUNT(*) AS TOTAL_VIEWS
FROM "moviestream"."table"
GROUP BY DEVICEParent topic: Examples of Using Select AI