10.2 Use Select AI with AI Providers
Provides examples of how to configure and use Select AI with supported AI providers.
Topics:
- 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 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 Principal, create an AI profile, and generate SQL from natural language prompts. - 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: Select AI with Anthropic
This example shows how you can use Anthropic to generate, run, and explain SQL from natural language prompts or chat using the Anthropic Claude LLM. - Example: Select AI with Hugging Face
This example shows how you can use Hugging Face to generate, run, and explain SQL from natural language prompts or chat using the Hugging Face LLM. - Example: Select AI with AWS
This example shows how you can use AWS to generate, run, and explain SQL from natural language prompts or chat using the models available with AWS. - Example: Select AI with OpenAI-Compatible Providers
This example shows how you can use OpenAI-compatible providers to generate, run, and explain SQL from natural language prompts or chat using the models available with OpenAI-compatible providers.
Parent topic: Examples Using Select AI
10.2.1 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:
If you do not specify themodel_name parameter, OCI Generative AI uses the default model
as per the table in Select your AI Provider and LLMs. 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
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GENAI',
attributes =>'{"provider": "oci",
"credential_name": "GENAI_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"}]
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"GENAI"
--
-- Use AI
--
SQL> select ai how many customers exist;
Number of Customers
-------------------
55500
SQL> select ai how many customers in San Francisco are married;
COUNT(DISTINCTC."CUST_ID")
--------------------------
28
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(DISTINCT c."CUST_ID")
FROM "SH"."CUSTOMERS" c
JOIN "SH"."COUNTRIES" co ON c."COUNTRY_ID" = co."COUNTRY_ID"
WHERE c."CUST_CITY" = 'San Francisco' AND c."CUST_MARITAL_STATUS" = 'married'
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
Here is the Oracle SQL query to find the number of customers in San Francisco who are married:
```
SELECT COUNT(*)
FROM "SH"."CUSTOMERS" c
WHERE c."CUST_CITY" = 'San Francisco'
AND c."CUST_MARITAL_STATUS" = 'Married';
```
Explanation:
* We use the `COUNT(*)` aggregate function to count the number of rows that match the conditions.
* We specify the table alias `c` for the `"SH"."CUSTOMERS"` table to make the query more readable.
* We use the `WHERE` clause to filter the rows based on two conditions:
+ `c."CUST_CITY" = 'San Francisco'`: We filter the rows where the customer's city is San Francisco.
+ `c."CUST_MARITAL_STATUS" = 'Married'`: We filter the rows where the customer's marital status is Married.
* The double quotes around the table and column names are used to make the query case-sensitive, as required in Oracle SQL.
Note: The column names and table names are assumed to be case-sensitive, so we enclose them in double quotes. If the column names and table names are not case-sensitive, you can remove the double quotes.
Collapse
This snippet was truncated for display; see it in full
SQL> select ai narrate how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------
There are 28 customers in San Francisco who are married.
SQL> select ai chat what is Autonomous AI Database;
RESPONSE
An Autonomous AI Database is a type of database that uses artificial intelligence (AI) and machine learning (ML) to automate many of the administrative and maintenance tasks typically performed by a database administrator (DBA). This allows the database to manage itself, without human intervention, to a large extent.
Autonomous AI Databases are designed to be self-driving, self-securing, and self-repairing, which means they can:
1. **Automate administrative tasks**: Such as provisioning, patching, upgrading, and tuning, which frees up DBAs to focus on higher-level tasks.
2. **Optimize performance**: By automatically adjusting parameters, indexing, and caching to ensure optimal performance and efficiency.
3. **Detect and respond to security threats**: By using AI-powered security tools to identify and respond to potential security threats in real-time.
4. **Heal itself**: By automatically detecting and repairing errors, corruption, or other issues that may arise.
5. **Scale up or down**: To match changing workload demands, without the need for manual intervention.
The benefits of Autonomous AI Databases include:
1. **Increased efficiency**: By automating routine tasks, DBAs can focus on more strategic activities.
2. **Improved performance**: Autonomous AI Databases can optimize performance in real-time, leading to faster query response times and better overall system performance.
3. **Enhanced security**: AI-powered security tools can detect and respond to threats more quickly and effectively than human administrators.
4. **Reduced costs**: By minimizing the need for manual intervention, Autonomous AI Databases can help reduce labor costs and improve resource utilization.
5. **Improved reliability**: Autonomous AI Databases can detect and repair errors more quickly, reducing downtime and improving overall system reliability.
Oracle Autonomous AI Database is a popular example of an Autonomous AI Database, which was introduced in 2018. Other vendors, such as Amazon, Microsoft, and Google, also offer Autonomous AI Database services as part of their cloud offerings.
In summary, Autonomous AI Databases are designed to be self-managing, self-optimizing, and self-healing, which can lead to improved performance, security, and efficiency, while reducing costs and administrative burdens.
--
--Clear the profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('GENAI');
PL/SQL procedure successfully completed.
Example: 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 AI 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:
If you do not specify themodel_name parameter, OCI Generative AI uses the default model
as per the table in Select your AI Provider and LLMs. 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
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GENAI',
attributes =>'{"provider": "oci",
"credential_name": "OCI$RESOURCE_PRINCIPAL",
"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"}]
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"GENAI"
--
-- Use AI
--
SQL> select ai how many customers exist;
Number of Customers
-------------------
55500
SQL> select ai how many customers in San Francisco are married;
COUNT(DISTINCTC."CUST_ID")
--------------------------
28
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(DISTINCT c."CUST_ID")
FROM "SH"."CUSTOMERS" c
JOIN "SH"."COUNTRIES" co ON c."COUNTRY_ID" = co."COUNTRY_ID"
WHERE c."CUST_CITY" = 'San Francisco' AND c."CUST_MARITAL_STATUS" = 'married'
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
Here is the Oracle SQL query to find the number of customers in San Francisco who are married:
```
SELECT COUNT(*)
FROM "SH"."CUSTOMERS" c
WHERE c."CUST_CITY" = 'San Francisco'
AND c."CUST_MARITAL_STATUS" = 'Married';
```
Explanation:
* We use the `COUNT(*)` aggregate function to count the number of rows that match the conditions.
* We specify the table alias `c` for the `"SH"."CUSTOMERS"` table to make the query more readable.
* We use the `WHERE` clause to filter the rows based on two conditions:
+ `c."CUST_CITY" = 'San Francisco'`: We filter the rows where the customer's city is San Francisco.
+ `c."CUST_MARITAL_STATUS" = 'Married'`: We filter the rows where the customer's marital status is Married.
* The double quotes around the table and column names are used to make the query case-sensitive, as required in Oracle SQL.
Note: The column names and table names are assumed to be case-sensitive, so we enclose them in double quotes. If the column names and table names are not case-sensitive, you can remove the double quotes.
Collapse
This snippet was truncated for display; see it in full
SQL> select ai narrate how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------
There are 28 customers in San Francisco who are married.
SQL> select ai chat what is Autonomous AI Database;
RESPONSE
An Autonomous AI Database is a type of database that uses artificial intelligence (AI) and machine learning (ML) to automate many of the administrative and maintenance tasks typically performed by a database administrator (DBA). This allows the database to manage itself, without human intervention, to a large extent.
Autonomous AI Databases are designed to be self-driving, self-securing, and self-repairing, which means they can:
1. **Automate administrative tasks**: Such as provisioning, patching, upgrading, and tuning, which frees up DBAs to focus on higher-level tasks.
2. **Optimize performance**: By automatically adjusting parameters, indexing, and caching to ensure optimal performance and efficiency.
3. **Detect and respond to security threats**: By using AI-powered security tools to identify and respond to potential security threats in real-time.
4. **Heal itself**: By automatically detecting and repairing errors, corruption, or other issues that may arise.
5. **Scale up or down**: To match changing workload demands, without the need for manual intervention.
The benefits of Autonomous AI Databases include:
1. **Increased efficiency**: By automating routine tasks, DBAs can focus on more strategic activities.
2. **Improved performance**: Autonomous AI Databases can optimize performance in real-time, leading to faster query response times and better overall system performance.
3. **Enhanced security**: AI-powered security tools can detect and respond to threats more quickly and effectively than human administrators.
4. **Reduced costs**: By minimizing the need for manual intervention, Autonomous AI Databases can help reduce labor costs and improve resource utilization.
5. **Improved reliability**: Autonomous AI Databases can detect and repair errors more quickly, reducing downtime and improving overall system reliability.
Oracle Autonomous AI Database is a popular example of an Autonomous AI Database, which was introduced in 2018. Other vendors, such as Amazon, Microsoft, and Google, also offer Autonomous AI Database services as part of their cloud offerings.
In summary, Autonomous AI Databases are designed to be self-managing, self-optimizing, and self-healing, which can lead to improved performance, security, and efficiency, while reducing costs and administrative burdens.
--
--Clear profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('GENAI');
PL/SQL procedure successfully completed.Example: Specify a Different Region for OCI
Generative AI Profile
This example shows specifying an OCI
Generative AI supported region in your profile. See Regions with Generative
AI.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'GENAI',
attributes =>'{"provider": "oci",
"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"}
]
"region":"eu-frankfurt-1",
"model": "meta.llama-3.3-70b-instruct",
"credential_name": "GENAI_CRED",
"oci_compartment_id": "ocid1.compartment.oc1..."}');
END;
/Example:
Select AI with OCI Generative AI Using Google Model
Select AI supports Gemini
models from Google, now integrated with the OCI Generative AI service. You can
configure Gemini models as part of your AI profile using the provider:
oci setting.
For more details on Gemini models in OCI, see Supported Google Models.
This example shows how to use Google models with OCI Generative AI in Select AI.
--Create your AI Profile
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'gemini',
attributes => '{
"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "SH"}],
"oci_compartment_id": "ocid1.compartment.oc1..aaaaa...ocid",
"model": "google.gemini-2.5-flash-lite",
"conversation" : "true"
}'
);
END;
/
--Set the profile
BEGIN
DBMS_CLOUD_AI.SET_PROFILE('gemini');
END;
/
--Use Select AI
select ai what are the top 3 product categories by sales;
--Use SHOWSQL
select ai showsql top 3 product categories by sales;
--Use EXPLAINSQL
select ai explainsql top 3 product categories by sales;
--Use NARRATE
select ai narrate what are the top 3 customers in San Francisco;
--Use CHAT
select ai chat what is Autonomous AI Database;Example: Select AI with OCI
Generative AI Using Grok Model
This example demonstrates how you can use xAI's Grok models with OCI Generative AI support. Review Perform Prerequisites for Select AI.
--Create your AI Profile
BEGIN
DBMS_CLOUD_AI.create_profile(
profile_name =>'grok',
attributes =>'{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [ {"owner": "SH"}],
"oci_compartment_id": "ocid1.compartment.oc1..aaaaa...",
"model":"xai.grok-4.3"
}');
END;
/
PL/SQL procedure successfully completed.
--Set Profile
exec dbms_cloud_ai.set_profile('grok');
PL/SQL procedure successfully completed.
--Use Select AI
select ai how many customers exist;
TOTAL_CUSTOMERS
---------------
55500
select ai how many customers in San Francisco are married;
TOTAL_MARRIED_CUSTOMERS
-----------------------
46
select ai showsql how many customers in San Francisco are married;
RESPONSE
----------------------------------------------------------
SELECT COUNT(*) AS total_married_customers
FROM "SH"."CUSTOMERS" c
WHERE UPPER(c."CUST_CITY") = UPPER('San Francisco')
AND UPPER(c."CUST_MARITAL_STATUS") = UPPER('married')
select ai explainsql how many customers in San Francisco are married;
RESPONSE
-------------------
### Oracle SQL Query
```sql
SELECT COUNT(*) AS "Total_Married_Customers"
FROM "SH"."CUSTOMERS" "cust"
WHERE UPPER("cust"."CUST_CITY") = UPPER('San Francisco')
AND UPPER("cust"."CUST_MARITAL_STATUS") = UPPER('married')
```
### Detailed Explanation
1. **Table and Schema Naming**:
- The table `"CUSTOMERS"` is referenced with its schema name `"SH"` as `"SH"."CUSTOMERS"`. This ensures that the query explicitly points to the correct schema and table, avoiding ambiguity.
- A table alias `"cust"` is used for the `"CUSTOMERS"` table to make the query more readable and concise when referencing columns.
2. **Column Naming**:
- The result of the `COUNT(*)` function is aliased as `"Total_Married_Customers"` for clarity and readability. This descriptive name indicates exactly what the count represents.
- All column names (e.g., `"CUST_CITY"`, `"CUST_MARITAL_STATUS"`) are enclosed in double quotes to maintain case sensitivity as per Oracle's naming conventions when explicitly defined.
3. **String Comparison in WHERE Clause**:
- The strings 'San Francisco' and 'married' in the question are not enclosed in double quotes. As per the provided rules, case-insensitive comparison is required.
- Therefore, the `UPPER()` function is applied to both the column values (`"cust"."CUST_CITY"` and `"cust"."CUST_MARITAL_STATUS"`) and the literal strings ('San Francisco' and 'married') to ensure the comparison ignores case differences.
- This means that records with values like 'SAN FRANCISCO', 'san francisco', or any other case variation of 'San Francisco' will match, and similarly for 'married'.
4. **Purpose of the Query**:
- The query counts the total number of customers who are located in 'San Francisco' and have a marital status of 'married'.
- The `COUNT(*)` function is used to return the total number of rows that satisfy the conditions specified in the `WHERE` clause.
5. **Readability**:
- The query uses consistent formatting with indentation for the `WHERE` clause conditions to improve readability.
- The use of a meaningful alias (`"cust"`) and a descriptive result column name (`"Total_Married_Customers"`) makes the query easier to understand at a glance.
This query will return a single number representing the count of married customers in San Francisco, handling case variations in the data appropriately.
select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
--------------------------------------------------------------------------------
I'm showing you the top 3 customers from San Francisco, based on their unique id
entification numbers, sorted from lowest to highest. Here's who they are:
- Lyndon Baltzer
- Mike Crocker
- Milburn Klemm
select ai chat what is Autonomous AI Database;
RESPONSE
-------------------
An **Autonomous AI Database** is a cloud-based database solution that uses artificial intelligence (AI) and machine learning (ML) to automate many of the routine tasks associated with managing and maintaining a database. It is designed to operate with minimal human intervention, allowing organizations to focus on data-driven insights and application development rather than database administration. The concept is often associated with Oracle's Autonomous AI Database, which was one of the first widely recognized implementations, but other cloud providers like AWS, Microsoft Azure, and Google Cloud also offer similar self-managing database services.
### Key Features of an Autonomous AI Database:
1. **Self-Driving:** Automates tasks such as provisioning, patching, tuning, and upgrading without manual input. It uses AI to optimize performance and adapt to workload changes in real-time.
2. **Self-Securing:** Automatically applies security updates, detects vulnerabilities, and protects against threats using built-in mechanisms like encryption and threat detection, reducing the risk of human error.
3. **Self-Repairing:** Identifies and resolves issues such as system failures or performance bottlenecks autonomously, ensuring high availability and minimizing downtime.
4. **Scalability:** Dynamically scales resources (compute and storage) up or down based on demand, optimizing cost and performance.
5. **Data Management:** Supports various data types and workloads, including transactional (OLTP), analytical (OLAP), and mixed workloads, often in a single converged database environment.
### Benefits:
- **Reduced Costs:** Minimizes the need for dedicated database administrators, lowering operational expenses.
- **Improved Performance:** AI-driven optimization ensures efficient query execution and resource allocation.
- **Enhanced Security:** Automated security features reduce the likelihood of breaches due to misconfigurations or delayed updates.
- **Increased Productivity:** Frees up IT staff to focus on innovation rather than routine maintenance.
### Use Cases:
- Businesses requiring high availability and reliability for critical applications.
- Organizations looking to modernize IT infrastructure with cloud-native solutions.
- Data analytics and machine learning projects needing scalable, optimized data storage.
- Environments where security and compliance are paramount, such as finance or healthcare.
### Example:
Oracle Autonomous AI Database, available on Oracle Cloud, offers two primary services:
- **Autonomous Transaction Processing (ATP):** Optimized for transactional workloads with high performance and reliability.
- **Autonomous AI Lakehouse:** Designed for analytics and big data workloads with automated data integration and optimization.
In essence, an Autonomous AI Database represents a shift toward intelligent, self-managing data systems that leverage automation and AI to simplify database operations, enhance security, and improve efficiency in a cloud environment. If you have a specific provider or context in mind, let me know, and I can dive deeper!Example: Select AI with OCI
Generative AI Using LLAMA Model
This example showcases the
chat feature from OCI Generative AI.
It highlights the model's capabilities through two prompts: analyzing customer comments to gauze their sentiment and
generate an introductory paragraph on rock
climbing.
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",
"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"}
]
"model": "meta.llama-3.3-70b-instruct",
"oci_apiformat":"GENERIC",
"credential_name": "GENAI_CRED",
"oci_compartment_id": "ocid1.compartment.oc1..."}');
END;
/
PL/SQL procedure successfully completed.
--
--Set profile
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');
PL/SQL procedure successfully completed.
SQL> set linesize 150
SQL> SELECT AI chat what is the sentiment of this comment I am not going to waste my time filling up this three page form. Lousy idea;
SQL>
RESPONSE
------------------------------------------------------------------------------------------------------------------------------------------------------
The sentiment of this comment is strongly negative. The user is expressing frustration and annoyance with the idea of filling out a three-page form, an
d is explicitly stating that they consider it a "lousy idea". The use of the phrase "waste my time" also implies that they feel the task is unnecessary
and unproductive. The tone is dismissive and critical.
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,
and 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
flexibility, 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. If
you do not specify the model_name parameter, OCI Generative AI uses
the default model as per the table in Select your AI Provider and
LLMs.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_DEFAULT',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADB_USER"}]
}');
END;
/Using OCI Generative AI with Chat Model
The
following example uses cohere.command-a-03-2025 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": "ADB_USER"}],
"model": "cohere.command-a-03-2025"
}');
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 model. If you are using Meta
Llama Chat Model endpoint ID, then specify oci_apiformat as
GENERIC.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_CHAT_ENDPOINT',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADB_USER"}],
"oci_endpoint_id": "<endpoint_id>",
"oci_apiformat": "GENERIC"
}');
END;
/Using OCI Generative AI with Chat Model
This
example demonstrates how to specify the OCI Generative AI Cohere Chat Model endpoint
ID instead of model. If you are using Meta Llama Chat Model
endpoint ID, then specify oci_apiformat as
GENERIC.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_CHAT_OCID',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADB_USER"}],
"model": "cohere.command-a-03-2025",
"oci_apiformat": "COHERE"
}');
END;
/Parent topic: Use Select AI with AI Providers
10.2.2 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
--
EXEC
DBMS_CLOUD.CREATE_CREDENTIAL(
CREDENTIAL_NAME => 'OPENAI_CRED',
username => 'OPENAI',
password => '<your_api_token>');
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OPENAI',
attributes =>'{"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 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> 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 AI Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous AI 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 AI Database uses machine learning and automation to optimize performance, security, and availability, allowing
users to focus on their applications and data rather than database administration tasks. It offers both Autonomous Transaction Processing (ATP) for
transactional workloads and Autonomous AI Lakehouse for analytical workloads. Autonomous AI Database provides high performance, scalability, and
reliability, making it an ideal choice for modern cloud-based applications.
--
--Clear the profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('OPENAI');
PL/SQL procedure successfully completed.The following example shows specifying a different model in your AI profile:
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OPENAI',
attributes =>'{"provider": "openai",
"credential_name": "OPENAI_CRED",
"model": "gpt-5.4",
"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;
/ Parent topic: Use Select AI with AI Providers
10.2.3 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
--
EXEC
DBMS_CLOUD.CREATE_CREDENTIAL(
CREDENTIAL_NAME => 'COHERE_CRED',
username => 'COHERE',
password => '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
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'COHERE',
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"}]
}');
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
--
--Clear the profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('COHERE');
PL/SQL procedure successfully completed.The following example shows specifying a different model and custom attributes in your AI profile:
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'COHERE',
attributes =>
'{"provider": "cohere",
"credential_name": "COHERE_CRED",
"model": "cohere.command-a-03-2025",
"object_list": [{"owner": "ADB_USER"}],
"max_tokens":512,
"stop_tokens": [";"],
"temperature": 0.5,
"comments": true
}');
END;
/Parent topic: Use Select AI with AI Providers
10.2.4 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 Principal, create an AI profile, and generate SQL from natural language prompts.
-- Create Credential for AI integration
--
EXEC
DBMS_CLOUD.CREATE_CREDENTIAL(
CREDENTIAL_NAME => 'AZURE_CRED',
username => 'AZUREAI',
password => 'your_api_token');
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for OpenAI endpoint
--
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
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name=> 'AZUREAI',
attributes=> '{"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
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"AZUREAI"
--
-- Use AI
--
select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
select ai how many customers in San Francisco are married;
MARRIED_CUSTOMERS
-----------------
18
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'
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.
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
select ai chat what is Autonomous AI Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous AI 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 AI Database uses machine learning and automation to optimize performance, security, and availability, allowing
users to focus on their applications and data rather than database administration tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous AI Lakehouse for analytical workloads. Autonomous AI Database provides high performance, scalability, and reliability, making it
an ideal choice for modern cloud-based applications.
--
--Clear the profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.The following example shows specifying a different model in your AI profile:
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name=>'AZUREAI',
attributes=>'{"provider": "azure",
"credential_name": "AZURE$PA",
"model": "gpt-4o",
"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;
/Example: Select AI with Azure OpenAI Service Principal
Connect as a database administrator to provide access to Azure service
principal 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
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name=>'AZUREAI',
attributes=>'{"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
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"AZUREAI"
--
-- Use AI
--
select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
select ai how many customers in San Francisco are married;
MARRIED_CUSTOMERS
-----------------
18
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'
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.
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
select ai chat what is Autonomous AI Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous AI 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 AI 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 AI Lakehouse for analytical workloads. Autonomous AI Database provides high performance, scalability, and reliability, making it
an ideal choice for modern cloud-based applications.
EXEC DBMS_CLOUD_AI.DROP_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.Parent topic: Use Select AI with AI Providers
10.2.5 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_ADMIN.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": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}]
}');
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 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 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> 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 AI Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous AI 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 AI Database uses machine learning and automation to optimize performance, security, and availability, allowing
users to focus on their applications and data rather than database administration tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Lakehouse for analytical workloads. Autonomous AI Database provides high performance, scalability, and reliability, making it
an ideal choice for modern cloud-based applications.
--
--Clear the profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('GOOGLE');
PL/SQL procedure successfully completed. The following example shows specifying a different model in your AI profile:
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GOOGLE',
attributes =>'{"provider": "google",
"credential_name": "GOOGLE_CRED",
"model": "gemini-3-flash-preview",
"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"}]
}');
END;
/Parent topic: Use Select AI with AI Providers
10.2.6 Example: Select AI with Anthropic
This example shows how you can
use Anthropic to generate, run, and explain SQL from natural language prompts or chat
using the Anthropic Claude LLM.
The following example demonstrates using Anthropic as your AI provider. The example demonstrates using your Anthropic API signing key to provide network access, creating an AI profile, and using Select AI actions to generate SQL queries from natural language prompts and chat using the Anthropic Claude LLM.
See Profile Attributes to supply the profile attributes.
--Grant 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 => 'ANTHROPIC_CRED',
username => 'ANTHROPIC',
password => '<your api key>'
);
END;
/
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for Anthropic endpoint
--
SQL>BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api.anthropic.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 =>'ANTHROPIC',
attributes =>'{"provider": "anthropic",
"credential_name": "ANTHROPIC_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"}]
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL>EXEC DBMS_CLOUD_AI.SET_PROFILE('ANTHROPIC');
PL/SQL procedure successfully completed.
--
-- 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 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> 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 AI Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous AI 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 AI Database uses machine learning and automation to optimize performance, security, and availability, allowing
users to focus on their applications and data rather than database administration tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous AI Lakehouse for analytical workloads. Autonomous AI Database provides high performance, scalability, and reliability, making it
an ideal choice for modern cloud-based applications.
--
--Clear the profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('ANTHROPIC');
PL/SQL procedure successfully completed.
The following example shows specifying a different model in your AI profile:
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'ANTHROPIC',
attributes =>'{"provider": "anthropic",
"credential_name": "ANTHROPIC_CRED",
"model": "claude-opus-4-6",
"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"}]
}');
END;
/Parent topic: Use Select AI with AI Providers
10.2.7 Example: Select AI with Hugging Face
This example shows how you can
use Hugging Face to generate, run, and explain SQL from natural language prompts or chat
using the Hugging Face LLM.
The following example demonstrates using Hugging Face as your AI provider. The example demonstrates using your Hugging Face API signing key to provide network access, creating an AI profile, and using Select AI actions to generate SQL queries from natural language prompts and chat using the Hugging Face LLM.
--Grant 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 => 'HF_CRED',
username => 'HF',
password => '<your_api_key>'
);
END;
/
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for Hugging Face endpoint
--
SQL>BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api-inference.huggingface.co',
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 =>'HF',
attributes =>'{"provider": "huggingface",
"credential_name": "HF_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"}],
"model" : "Qwen/Qwen2.5-72B-Instruct"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL>EXEC DBMS_CLOUD_AI.SET_PROFILE('HF');
PL/SQL procedure successfully completed.
--
-- 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
-----------------
46
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
------------------------------------------------------------
SELECT COUNT("CUST_ID") AS "Married_Customers"
FROM "SH"."CUSTOMERS" "C"
WHERE "CUST_CITY" = 'San Francisco' AND "CUST_MARITAL_STATUS
" = 'Married'
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
------------------------------------------------------------
To answer the question "How many customers in San Francisco are married?", we need to query the "SH"."CUSTOMERS" table and filter the results based on the city and marital status.
We will use table aliases to make the query more readable and ensure that the string comparisons follow the specified rules.
Here is the Oracle SQL query:
```sql
SELECT COUNT("CUST_ID") AS "Married_Customers_in_San_Francisco"
FROM "SH"."CUSTOMERS" c
WHERE c."CUST_CITY" = 'San Francisco' -- Case insensitive comparison
AND UPPER(c."CUST_MARITAL_STATUS") = UPPER('Married'); --
Case insensitive comparison
```
### Explanation:
1. **Table Alias**:
RESPONSE
------------------------------------------------------------
- We use the alias `c` for the "SH"."CUSTOMERS" table to make the query more readable.
2. **Column Selection**:
- We select the count of "CUST_ID" to get the number of customers who meet the criteria. We alias this count as "Married_Customers_in_San_Francisco" for clarity.
3. **WHERE Clause**:
- **City Filter**: `c."CUST_CITY" = 'San Francisco'`
- Since 'San Francisco' is not in double quotes, we use a case-insensitive comparison.
- **Marital Status Filter**: `UPPER(c."CUST_MARITAL_STATUS") = UPPER('Married')`
- Since 'Married' is not in double quotes, we use a case-insensitive comparison by applying the `UPPER` function to both sides of the comparison.
This query will return the number of customers in San Francisco who are married.
SQL> select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
------------------------------------------------------------
The top 3 customers in San Francisco are:
- Hector Colven, with a customer ID of 8866.
- Milburn Klemm, with a customer ID of 1944.
- Gavin Xie, with a customer ID of 2579.
These customers have the highest total sales among all customers in San Francisco.
SQL> select ai chat what is Autonomous AI Database;
RESPONSE
------------------------------------------------------------
An Autonomous AI Database is a type of database management system that uses artificial intelligence (AI) and machine learning (ML) to automate many of the routine tasks typically performed by database administrators (DBAs). These tasks include provisioning, patching, backups, tuning, and security. The goal of an Autonomous AI Database is to reduce the need for human intervention, minimize errors, and optimize performance,thereby allowing organizations to focus on more strategic activities.
### Key Features of Autonomous AI Databases:
RESPONSE
------------------------------------------------------------
1. **Automated Provisioning**: The database can be set up and configured automatically, reducing the time and effort required to get it up and running.
2. **Self-Patching and Updates**: The database automatically applies security patches and updates without downtime, ensuring that the system remains secure and up-to-date.
3. **Self-Tuning**: The database continuously monitors its performance and adjusts settings to optimize query execution and resource utilization.
4. **Self-Backup and Recovery**: Automated backup and recovery processes ensure that data is protected and can be restored quickly in the event of a failure.
5. **Security**: Advanced security features, including threat detection and response, are built into the database to protect against cyber threats.
6. **Scalability**: The database can automatically scale resources up or down based on demand, ensuring optimal performance and cost efficiency.
7. **Monitoring and Diagnostics**: Real-time monitoring and diagnostics help identify and resolve issues before they impact performance.
RESPONSE
------------------------------------------------------------
### Benefits of Autonomous AI Databases:
- **Reduced Operational Costs**: By automating routine tasks, the need for dedicated DBAs is reduced, lowering operational costs.
- **Improved Reliability**: Automated processes reduce the risk of human error, leading to more reliable and consistent performance.
- **Enhanced Security**: Continuous monitoring and automated security measures help protect against threats.
- **Faster Time to Market**: Automated provisioning and tuning allow applications to be deployed more quickly.
RESPONSE
------------------------------------------------------------
- **Scalability and Flexibility**: The ability to scale resources automatically ensures that the database can handle varying workloads efficiently.
### Use Cases:
- **Cloud Applications**: Autonomous AI Databases are particularly useful in cloud environments where scalability and reliability are critical.
- **Data Warehousing**: They can handle large volumes of data and complex queries, making them ideal for data warehousing and analytics.
RESPONSE
------------------------------------------------------------
- **IoT and Real-Time Data Processing**: They can process and analyze real-time data from IoT devices efficiently.
- **E-commerce**: They can handle high transaction volumes and ensure fast response times for online shopping platforms.
### Examples of Autonomous AI Databases:
- **Oracle Autonomous AI Database**: One of the first and most well-known Autonomous AI Databases, offering both transactional
and data warehousing capabilities.
- **Amazon Aurora**: A managed relational database service that includes automated scaling, patching, and backups.
- **Microsoft Azure SQL Database Managed Instance**: Provides a high level of automation and management for SQL Server databases in the cloud.
- **Google Cloud Spanner**: A globally distributed, horizontally scalable relational database that is highly available and consistent.
Autonomous AI Databases represent a significant advancement in database technology, offering organizations a more efficient, secure, and cost-effective way to manage their data.
--
--Clear the profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('HF');
PL/SQL procedure successfully completed.Parent topic: Use Select AI with AI Providers
10.2.8 Example: Select AI with AWS
This example shows how you can use AWS to generate, run, and explain SQL
from natural language prompts or chat using the models available with AWS.
The following example shows how to use AWS as the AI provider with Amazon Bedrock and its foundation models. The example shows creating AWS credentials, provide network access, creating an AI profile, and using Select AI actions to generate SQL queries from natural language prompts and chat using the AWS foundation models.
To use AWS, obtain access key, secret keys, and model ID. See Use AWS. Use the model ID as the model attribute in the
DBMS_CLOUD_AI.CREATE_PROFILE procedure. You must
specify the model attribute explicitly, as no default model is
provided.
--Grant EXECUTE privilege to ADB_USER
GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;
--
-- Create Credential for AI provider
--
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'AWS_CRED',
username => '<your_AWS_access_key>',
password => '<your_AWS_secret_key>'
);
END;
/
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for AWS
--
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'bedrock-runtime.us-east-1.amazonaws.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
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'AWS',
attributes =>'{"provider": "aws",
"credential_name": "AWS_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"}],
"model" : "meta.llama3-70b-instruct-v1:0",
"conversation" : "true"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('AWS');
PL/SQL procedure successfully completed.
--
-- Use AI
--
SELECT AI how many customers exist;
"RESPONSE"
"COUNT(*)"
55500
SELECT AI how many customers in San Francisco are married;
"RESPONSE"
"COUNT(*)"
46
SELECT AI showsql how many customers in San Francisco are married;
"RESPONSE"
"SELECT COUNT(*) AS "Number of Married Customers in San Francisco"
FROM "SH"."CUSTOMERS" C
WHERE UPPER(C."CUST_CITY") = UPPER('San Francisco')
AND UPPER(C."CUST_MARITAL_STATUS") = UPPER('Married')"
SELECT AI explainsql how many customers in San Francisco are married;
"RESPONSE""SELECT
COUNT(*) AS "Number of Married Customers in San Francisco"
FROM "SH"."CUSTOMERS" C
WHERE C."CUST_CITY" = 'San Francisco'
AND C."CUST_MARITAL_STATUS" = 'Married'
Explanation:
- Used table alias C for CUSTOMERS table
- Used easy to read column names like CUST_CITY, CUST_MARITAL_STATUS
- Enclosed table name, schema name and column names in double quotes
- Compared string values in WHERE clause without UPPER() since the values are not in double quotes
- Counted number of rows satisfying the condition and aliased the count as "Number of Married Customers in San Francisco""
SELECT AI narrate what are the top 3 customers in San Francisco;
"RESPONSE"
The top 3 customers in San Francisco ordered by credit limit in descending order are:
1. Bert Katz
2. Madallyn Ladd
3. Henrietta Snodgrass
SELECT AI chat what is Autonomous AI Database;
"RESPONSE"
"An Autonomous AI Database is a cloud database service provided by Oracle Corporation. Some key features of Oracle Autonomous AI Database include:
- Fully automated and self-driving - The database automatically upgrades, patches, tunes, and backs itself up without any human intervention required.
- Self-securing - The database uses machine learning to detect threats and automatically apply security updates.
- Self-repairing - The database monitors itself and automatically recovers from failures and errors without downtime.
- Self-scaling - The database automatically scales compute and storage resources up and down as needed to meet workload demands.
- Serverless - The database is accessed as a cloud service without having to manually provision any servers or infrastructure.
- High performance - The database uses Oracle's advanced automation and machine learning to continuously tune itself for high performance.
- Multiple workload support - Supports transaction processing, analytics, graph processing, etc in a single converged database.
- Fully managed - Oracle handles all the management and administration of the database. Users just load and access their data.
- Compatible - Supports common SQL and Oracle PL/SQL for easy migration from on-prem Oracle databases.
So in summary, an Oracle Autonomous AI Database is a fully automated, self-driving, self-securing, and self-repairing database provided as a simple cloud service. The automation provides high performance, elasticity, and availability with minimal human labor required."
--
--Clear the profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('AWS');
PL/SQL procedure successfully completed.Parent topic: Use Select AI with AI Providers
10.2.9 Example: Select AI with OpenAI-Compatible Providers
This example shows how you can
use OpenAI-compatible providers to generate, run, and explain SQL from natural language
prompts or chat using the models available with OpenAI-compatible providers.
The following example shows how to use Fireworks AI as an OpenAI-compatible provider. It demonstrates how to create credentials using your Fireworks AI API signing key, configure network access, create an AI profile, and use Select AI actions to generate SQL queries from natural language prompts and chat using the Fireworks AI LLM model.
Before You Begin
To use Fireworks AI, specify provider_endpoint as an
attribute in the DBMS_CLOUD_AI.CREATE_PROFILE procedure instead of the
provider attribute. See Use OpenAI-Compatible Providers to obtain the attribute. You must specify the
model attribute explicitly, as no default model is provided.
--Grant EXECUTE privilege to ADB_USER
GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;
--
-- Create Credential for AI provider
--
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'FIREWORKS_CRED',
username => 'FIREWORKS',
password => '<your_fireworksaiapi_key>'
);
END;
/
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for Fireworks AI endpoint
--
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api.fireworks.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
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'FIREWORKS',
attributes =>'{
"credential_name": "FIREWORKS_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"}],
"model" : "accounts/fireworks/models/llama-v3p1-405b-instruct",
"provider_endpoint" : "api.fireworks.ai/inference",
"conversation" : "true"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('FIREWORKS');
PL/SQL procedure successfully completed.
--
-- Use AI
--
select ai how many customers exist;
"RESPONSE"
"COUNT(*)"
55500
select ai how many customers in San Francisco are married;
"RESPONSE"
"COUNT(*)"
46
select ai showsql how many customers in San Francisco are married;
"RESPONSE"
"SELECT COUNT(*)
FROM ""SH"".""CUSTOMERS"" ""c""
WHERE UPPER(""c"".""CUST_CITY"") = UPPER('San Francisco')
AND UPPER(""c"".""CUST_MARITAL_STATUS"") = UPPER('Married')"
select ai explainsql how many customers in San Francisco are married;
"RESPONSE"
"Here is the Oracle SQL query:
```sql
SELECT COUNT(*)
FROM ""SH"".""CUSTOMERS"" ""c""
WHERE UPPER(""c"".""CUST_CITY"") = UPPER('San Francisco')
AND UPPER(""c"".""CUST_MARITAL_STATUS"") = UPPER('Married')
```
Explanation:
* We use the table alias `""c""` to refer to the `""CUSTOMERS""` table.
* We enclose the schema name `""SH""`, table name `""CUSTOMERS""`, and column names `""CUST_CITY""` and `""CUST_MARITAL_STATUS""` in double quotes to ensure case sensitivity.
* In the `WHERE` clause, we use the `UPPER()` function to perform case-insensitive comparisons for the strings `'San Francisco'` and `'Married'`, since they are not enclosed in double quotes.
* The `COUNT(*)` function returns the number of rows in the result set, which represents the number of customers in San Francisco who are married."
select ai narrate what are the top 3 customers in San Francisco;
"RESPONSE"
The top 3 customers in San Francisco are:
1. Lyndon Baltzer
2. Mike Crocker
3. Milburn Klemm
These customers are identified by their unique customer IDs, which are used to track their information and interactions. The list shows the customer's first and last names.
select ai chat what is Autonomous AI Database;
"RESPONSE"
"An Autonomous AI Database is a type of database that uses artificial intelligence (AI) and machine learning (ML) to automate many of the tasks traditionally performed by database administrators (DBAs). The goal of an Autonomous AI Database is to provide a self-managing, self-securing, and self-repairing database that can operate with minimal human intervention.
Autonomous AI Databases use advanced algorithms and machine learning techniques to:
1. **Automate database administration**: Tasks such as patching, backups, and performance tuning are automated, freeing up DBAs to focus on higher-level tasks.
2. **Predict and prevent issues**: The database uses machine learning to predict and prevent issues such as downtime, data breaches, and performance problems.
3. **Optimize performance**: The database continuously monitors and optimizes its own performance, ensuring that it is running at peak efficiency.
4. **Enhance security**: Autonomous AI Databases use advanced security features, such as encryption and access controls, to protect data from unauthorized access.
5. **Improve data management**: Autonomous AI Databases can automatically manage data, including data ingestion, processing, and storage.
The benefits of Autonomous AI Databases include:
1. **Increased efficiency**: By automating routine tasks, Autonomous AI Databases can reduce the workload of DBAs and improve overall efficiency.
2. **Improved security**: Autonomous AI Databases can detect and respond to security threats in real-time, reducing the risk of data breaches.
3. **Enhanced performance**: Autonomous AI Databases can optimize their own performance, ensuring that applications run quickly and efficiently.
4. **Reduced costs**: By automating routine tasks and improving efficiency, Autonomous AI Databases can help reduce costs associated with database management.
Examples of Autonomous AI Databases include:
1. Oracle Autonomous AI Database
2. Microsoft Azure SQL Database
3. Amazon Aurora
4. Google Cloud SQL
Overall, Autonomous AI Databases represent a significant shift in the way databases are managed and maintained, using AI and ML to automate many of the tasks traditionally performed by DBAs."
--
--Clear the profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('FIREWORKS');
PL/SQL procedure successfully completed.Example: Use a Custom LLM Deployed in
OCI Data Science with Select AI
In this configuration, Select AI
uses an OCI user credential and wraps it with OpenAI-style bearer token
authentication. The AI profile uses provider: openai together with
a custom provider_endpoint that points to the OCI Data Science
model deployment endpoint.
Before You Begin
Review:
-
Create an OCI Data Science model deployment using AI Quick Actions (AQUA).
-
Note the model deployment endpoint URL.
For information about creating and deploying models using OCI Data Science AI Quick Actions (AQUA), see AI Quick Actions and Model Deployment in OCI Data Science documentation.
The following example shows how to create
credentials using an OCI user credential that wraps authentication in an
OpenAI-style Bearer token, configure network access, create an AI profile, and use
Select AI chat action using the OCI Data Science (AQUA) LLM
model.
To use OCI Data Science (AQUA), specify
provider_endpoint, provider: openai, and
model: odsc-llm as attributes in the
DBMS_CLOUD_AI.CREATE_PROFILE
procedure.
--Grant EXECUTE privilege to ADB_USER
GRANT EXECUTE ON DBMS_CLOUD_AI TO ADB_USER;
-- Create a credential for OCI request signing
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'OCI_GENAI_CRED',
user_ocid => '<user_ocid>',
tenancy_ocid => '<tenancy_ocid>',
private_key => '<private_key>',
fingerprint => '<fingerprint>'
);
END;
/
-- Grant network access to the OCI Data Science model deployment host
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => '*.oci.customer-oci.com',
ace => xs$ace_type(
privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db
)
);
END;
/
-- Drop the profile if it already exists
BEGIN
DBMS_CLOUD_AI.DROP_PROFILE(
profile_name => 'OCI_DS_LLAMA_MODEL_PROFILE',
force => TRUE
);
END;
/
-- Create the AI profile for the OCI Data Science model deployment
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_DS_LLAMA_MODEL_PROFILE',
attributes => '{
"credential_name": "OCI_GENAI_CRED",
"model": "odsc-llm",
"provider": "openai",
"provider_endpoint": "https://modeldeployment.<region>.oci.customer-oci.com/<deployment_ocid>/predict",
"conversation": "true",
"object_list": [
{"owner": "ADB_USER", "name": "CUSTOMERS"}
]
}'
);
END;
/
-- Set the AI profile for the current session
BEGIN
DBMS_CLOUD_AI.SET_PROFILE('OCI_DS_LLAMA_MODEL_PROFILE');
END;
/
-- Use Select AI test the profile with DBMS_CLOUD_AI.GENERATE
SELECT DBMS_CLOUD_AI.GENERATE(
prompt => 'what is oracle autonomous database',
profile_name => 'OCI_DS_LLAMA_MODEL_PROFILE',
action => 'chat'
)
FROM dual;
Select AI sends the prompt to the OCI Data Science model deployment and returns the generated response.
Example: Use an OpenAI-Compatible Provider
xAI with Select AI
The following example shows how to use xAI as an OpenAI-compatible provider. It demonstrates how to create credentials using your xAI API key, configure network access, create an AI profile, and use Select AI actions to generate SQL queries from natural language prompts and chat using an xAI model.
To use xAI, specify the provider_endpoint
attribute in the DBMS_CLOUD_AI.CREATE_PROFILE procedure. You must
also specify the model attribute explicitly, as no default model is
provided.
--Grant EXECUTE privilege to ADB_USER
GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;
--Create a Credential
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'XAI_CRED',
username => 'XAI',
password => '<your_xai_api_key>'
);
END;
/
--Grant Network Access
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api.x.ai',
ace => xs$ace_type(
privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db
)
);
END;
/
--Create an AI Profile
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'XAI_PROFILE',
attributes => '{
"credential_name": "XAI_CRED",
"provider_endpoint": "https://api.x.ai",
"model": "xai.grok-4.3",
"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;
/
--Set the profile
EXEC DBMS_CLOUD_AI.SET_PROFILE('XAI_PROFILE');
--Use Select AI
SELECT AI how many customers exist;Parent topic: Use Select AI with AI Providers