Exemples d'utilisation de Select AI
Découvrez comment intégrer l'IA Select d'Oracle à divers fournisseurs d'IA pris en charge pour générer, exécuter et expliquer le langage SQL à partir d'invites en langage naturel ou discuter avec le LLM.
- Exemple : Sélectionner des actions AI
Ces exemples illustrent les actions AI de sélection courantes. - Exemple : sélection de l'IA avec OCI Generative AI
Ces exemples montrent comment accéder à OCI Generative AI à l'aide de votre clé d'API OCI ou de votre principal de ressource, créer un profil d'IA, et générer, exécuter et expliquer le code SQL à partir d'invites en langage naturel ou de discussions à l'aide des LLM OCI Generative AI. - Exemple : sélection de l'intelligence artificielle avec OpenAI
Cet exemple montre comment utiliser OpenAI pour générer des instructions SQL à partir d'invites en langage naturel. - Exemple : sélection de l'IA avec Cohere
Cet exemple montre comment utiliser Cohere pour générer des instructions SQL à partir d'invites en langage naturel. - Exemple : sélection de l'IA avec le service OpenAI Azure
Les exemples suivants montrent comment activer l'accès au service Azure OpenAI à l'aide de votre clé d'API ou utiliser le principal de service Azure OpenAI, créer un profil d'IA et générer du code SQL à partir d'invites en langage naturel. - Exemple : sélection de l'IA avec Google
Cet exemple montre comment utiliser Google pour générer, exécuter et expliquer des instructions SQL à partir d'invites en langage naturel ou de discussions à l'aide du LLM Google Gemini. - Exemple : sélection de l'IA avec l'anthropique
Cet exemple montre comment utiliser l'anthropique pour générer, exécuter et expliquer des instructions SQL à partir d'invites en langage naturel ou de discussions à l'aide du LLM Claude anthropique. - Exemple : sélection de l'IA avec une interface graphique enfichable
Cet exemple montre comment utiliser une interface graphique enfichable pour générer, exécuter et expliquer des instructions SQL à partir d'invites en langage naturel ou de discussions à l'aide du LLM avec une interface graphique enfichable. - Exemple : sélection de l'IA avec AWS
Cet exemple montre comment utiliser AWS pour générer, exécuter et expliquer le langage SQL à partir d'invites en langage naturel ou discuter à l'aide des modèles disponibles avec AWS. - Exemple : sélection d'une IA avec des fournisseurs compatibles OpenAI
Cet exemple montre comment utiliser des fournisseurs compatibles OpenAI pour générer, exécuter et expliquer des instructions SQL à partir d'invites en langage naturel ou discuter à l'aide des modèles disponibles avec des fournisseurs compatibles OpenAI. - Exemple : Activer les conversations dans Select AI
Cet exemple illustre l'activation des conversations dans Select AI. - Exemple : configuration et utilisation de Select AI avec RAG
Cet exemple vous guide tout au long de la configuration des informations d'identification, de la configuration de l'accès réseau et de la création d'un index vectoriel pour l'intégration des services cloud de banque de vecteurs OCI Generative AI avec OpenAI à l'aide d'Oracle Autonomous Database. - Exemple : sélection de l'IA avec des modèles de transformateur dans la base de données
Cet exemple montre comment importer un modèle de transformateur préentraîné stocké dans le stockage d'objets Oracle dans votre instance Oracle Database 23ai, puis utiliser le modèle dans la base de données importé dans Sélectionner un profil AI pour générer des incorporations vectorielles pour les blocs de document et les invites utilisateur. - Exemple : amélioration de la génération de requêtes SQL
Ces exemples montrent comment les commentaires, annotations, clés étrangères et contraintes d'intégrité référentielle dans les tables et colonnes de base de données peuvent améliorer la génération de requêtes SQL à partir d'invites en langage naturel. - Exemple : génération de données synthétiques
Cet exemple explique comment générer des données synthétiques en imitant les caractéristiques et la distribution de données réelles. - Exemple : activation ou désactivation de l'accès aux données
Cet exemple montre comment les administrateurs peuvent contrôler l'accès aux données et empêcher Select AI d'envoyer des tables de schéma réelles au LLM. - Exemple : restriction de l'accès aux tables dans un profil AI
Cet exemple montre comment restreindre l'accès aux tables et indique au LLM d'utiliser uniquement les tables spécifiées dans le fichierobject_listdu profil AI. - Exemple : Spécification de la sensibilité à la casse pour les colonnes
Cet exemple montre comment définir la sensibilité à la casse pour les colonnes dans le profil AI.
Exemple : Sélectionner des actions AI
Ces exemples illustrent les actions communes de sélection d'IA.
L'exemple suivant illustre des actions telles que runsql (valeur par défaut), showsql, narrate, chat et explainsql que vous pouvez effectuer avec SELECT AI. Ces exemples utilisent le schéma sh avec le fournisseur AI et les attributs de profil définis dans la fonction DBMS_CLOUD_AI.CREATE_PROFILE.
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.Rubrique parent : Exemples d'utilisation de Select AI
Exemple : Sélectionner l'IA avec OCI Generative AI
Ces exemples montrent comment accéder à OCI Generative AI à l'aide de votre clé d'API OCI ou de votre principal de ressource, créer un profil d'IA, et générer, exécuter et expliquer le code SQL à partir d'invites en langage naturel ou de discussions à l'aide des LLM OCI Generative AI.
Remarque
Si vous n'indiquez pas le paramètre
Si vous n'indiquez pas le paramètre
model_name, OCI Generative AI utilise le modèle par défaut conformément à la table dans Sélection de votre fournisseur d'IA et de vos LLM. Pour en savoir plus sur les paramètres, reportez-vous à Attributs de profil.
-- 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 Database;
RESPONSE
An Autonomous 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 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 Databases include:
1. **Increased efficiency**: By automating routine tasks, DBAs can focus on more strategic activities.
2. **Improved performance**: Autonomous 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 Databases can help reduce labor costs and improve resource utilization.
5. **Improved reliability**: Autonomous Databases can detect and repair errors more quickly, reducing downtime and improving overall system reliability.
Oracle Autonomous Database is a popular example of an Autonomous Database, which was introduced in 2018. Other vendors, such as Amazon, Microsoft, and Google, also offer Autonomous Database services as part of their cloud offerings.
In summary, Autonomous 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.
Exemple : sélection de l'IA avec le principal de ressource OCI Generative AI
Pour utiliser le principal de ressource avec OCI Generative AI, l'administrateur de location Oracle Cloud Infrastructure doit accorder l'accès aux ressources Generative AI à un groupe dynamique. Reportez-vous à Exécution des prérequis pour utiliser le principal de ressource avec Autonomous Database afin de fournir l'accès à un groupe dynamique.
Définissez les stratégies requises pour obtenir l'accès à toutes les ressources d'IA générative. Reportez-vous à Obtention de l'accès à l'IA générative pour en savoir plus sur les stratégies d'IA générative.
-
Pour obtenir l'accès à toutes les ressources Generative AI de l'ensemble de la location, utilisez la stratégie suivante :
allow group <your-group-name> to manage generative-ai-family in tenancy -
Pour obtenir l'accès à toutes les ressources Generative AI de votre compartiment, utilisez la stratégie suivante :
allow group <your-group-name> to manage generative-ai-family in compartment <your-compartment-name>
Connectez-vous en tant qu'administrateur et activez le principal de ressource OCI. Pour configurer les paramètres, reportez-vous à Procédure ENABLE_PRINCIPAL_AUTH.
Remarque
OCI Generative AI utilise
OCI Generative AI utilise
meta.llama-3-70b-instruct comme modèle par défaut si vous n'indiquez pas model. Pour en savoir plus sur les paramètres, reportez-vous à Attributs de profil.
-- 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 Database;
RESPONSE
An Autonomous 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 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 Databases include:
1. **Increased efficiency**: By automating routine tasks, DBAs can focus on more strategic activities.
2. **Improved performance**: Autonomous 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 Databases can help reduce labor costs and improve resource utilization.
5. **Improved reliability**: Autonomous Databases can detect and repair errors more quickly, reducing downtime and improving overall system reliability.
Oracle Autonomous Database is a popular example of an Autonomous Database, which was introduced in 2018. Other vendors, such as Amazon, Microsoft, and Google, also offer Autonomous Database services as part of their cloud offerings.
In summary, Autonomous 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.Exemple : spécification d'une autre région pour le profil OCI Generative AI
Cet exemple montre comment indiquer une région prise en charge par OCI Generative AI dans votre profil. Reportez-vous à Régions avec 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;
/Exemple : sélectionner l'IA avec OCI Generative AI à l'aide du modèle Grok
Cet exemple montre comment utiliser les modèles Grok de xAI avec la prise en charge d'OCI Generative AI. Reportez-vous à Exécution des prérequis pour 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-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 Database;
RESPONSE
-------------------
An **Autonomous 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 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 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 Database, available on Oracle Cloud, offers two primary services:
- **Autonomous Transaction Processing (ATP):** Optimized for transactional workloads with high performance and reliability.
- **Autonomous Data Warehouse (ADW):** Designed for analytics and big data workloads with automated data integration and optimization.
In essence, an Autonomous 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!Exemple : sélection de l'IA avec OCI Generative AI à l'aide du modèle LLAMA
Cet exemple présente la fonctionnalité chat d'OCI Generative AI. Il met en évidence les capacités du modèle à travers deux invites : l'analyse des commentaires des clients pour évaluer leur sentiment et générer un paragraphe d'introduction sur l'escalade.
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!Utilisation d'OCI Generative AI avec le modèle par défaut
L'exemple suivant utilise le modèle de discussion OCI Generative AI par défaut. Si vous n'indiquez pas le paramètre model_name, OCI Generative AI utilise le modèle par défaut comme indiqué dans la table dans Sélection de votre fournisseur d'IA et de vos LLM.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_DEFAULT',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADB_USER"}]
}');
END;
/Utilisation d'OCI Generative AI avec le modèle de discussion
L'exemple suivant utilise cohere.command-r-plus-08-2024 comme modèle de discussion OCI Generative AI.
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-r-plus-08-2024"
}');
END;
/Utilisation d'OCI Generative AI avec l'ID d'adresse de modèle de discussion
L'exemple suivant montre comment indiquer l'ID d'adresse du modèle de discussion OCI Generative AI au lieu de model. Si vous utilisez l'ID d'adresse de modèle de discussion Meta Llama, indiquez oci_apiformat en tant que 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;
/Utilisation d'OCI Generative AI avec l'OCID de modèle de discussion
Cet exemple montre comment indiquer l'ID d'adresse du modèle de discussion Cohere OCI Generative AI au lieu de model. Si vous utilisez l'ID d'adresse de modèle de discussion Meta Llama, indiquez oci_apiformat en tant que 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": "<model_ocid>",
"oci_apiformat": "COHERE"
}');
END;
/Rubrique parent : Exemples d'utilisation de Select AI
Exemple : sélectionnez AI avec OpenAI
Cet exemple montre comment utiliser OpenAI pour générer des instructions SQL à partir d'invites en langage naturel.
Remarque
Seul un administrateur de base de données peut exécuter les privilèges
Seul un administrateur de base de données peut exécuter les privilèges
EXECUTE et la procédure de liste de contrôle d'accès réseau.
--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 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
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 Data Warehouse (ADW) for analytical workloads. Autonomous 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.L'exemple suivant montre comment spécifier un autre modèle dans votre profil AI :
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OPENAI',
attributes =>'{"provider": "openai",
"credential_name": "OPENAI_CRED",
"model": "gpt-3.5-turbo",
"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;
/ Rubrique parent : Exemples d'utilisation de Select AI
Exemple : Sélectionner l'IA avec Cohere
Cet exemple montre comment utiliser Cohere pour générer des instructions SQL à partir d'invites en langage naturel.
Remarque
Seul un administrateur de base de données peut exécuter les privilèges
Seul un administrateur de base de données peut exécuter les privilèges
EXECUTE et la procédure de liste de contrôle d'accès réseau.
--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.L'exemple suivant montre comment spécifier un modèle et des attributs personnalisés différents dans votre profil AI :
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;
/Rubrique parent : Exemples d'utilisation de Select AI
Exemple : sélection de l'IA avec le service OpenAI Azure
Les exemples suivants montrent comment activer l'accès au service OpenAI Azure à l'aide de votre clé d'API ou utiliser le principal de service OpenAI Azure, créer un profil d'IA et générer du code SQL à partir d'invites en langage naturel.
-- 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 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
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 Data Warehouse (ADW) for analytical workloads. Autonomous 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.L'exemple suivant montre comment spécifier un autre modèle dans votre profil AI :
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name=>'AZUREAI',
attributes=>'{"provider": "azure",
"credential_name": "AZURE$PA",
"model": "gpt-3.5-turbo",
"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;
/Exemple : sélection de l'IA avec le principal de service OpenAI Azure
Connectez-vous en tant qu'administrateur de base de données pour fournir l'accès à l'authentification de principal de service Azure, puis accordez les droits d'accès de liste de contrôle d'accès réseau à l'utilisateur (ADB_USER) qui souhaite utiliser Select AI. Pour fournir l'accès aux ressources Azure, reportez-vous à Utilisation du principal de service Azure pour accéder aux ressources Azure.
Remarque
Seul un utilisateur DBA peut exécuter les privilèges
Seul un utilisateur DBA peut exécuter les privilèges
EXECUTE et la procédure de liste de contrôle d'accès réseau.
-- 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 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. Autonomous 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.Rubrique parent : Exemples d'utilisation de Select AI
Exemple : Sélectionner l'IA avec Google
Cet exemple montre comment utiliser Google pour générer, exécuter et expliquer des instructions SQL à partir d'invites en langage naturel ou de discussions à l'aide du LLM Google Gemini.
L'exemple suivant illustre l'utilisation de Google comme fournisseur d'IA. L'exemple illustre l'utilisation de votre clé de signature d'API Google pour fournir un accès réseau, la création d'un profil AI, l'utilisation des actions Select AI pour générer des requêtes SQL à partir d'invites en langage naturel et de réponses de discussion.
--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": "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 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
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 Data Warehouse (ADW) for analytical workloads. Autonomous 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. L'exemple suivant montre comment spécifier un autre modèle dans votre profil AI :
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GOOGLE',
attributes =>'{"provider": "google",
"credential_name": "GOOGLE_CRED",
"model": "gemini-1.5-pro",
"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;
/Rubrique parent : Exemples d'utilisation de Select AI
Exemple : Sélectionner AI avec Anthropic
Cet exemple montre comment utiliser Anthropic pour générer, exécuter et expliquer des instructions SQL à partir d'invites en langage naturel ou de discussions à l'aide du LLM Claude Anthropic.
L'exemple suivant illustre l'utilisation d'Anthropic en tant que fournisseur d'IA. L'exemple montre comment utiliser votre clé de signature d'API anthropique pour fournir un accès réseau, créer un profil d'IA et utiliser les actions Sélectionner l'IA pour générer des requêtes SQL à partir d'invites en langage naturel et discuter à l'aide du LLM Claude anthropique.
Voir Attributs de profil pour fournir les attributs de profil.
--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_ADB_USER.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 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
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 Data Warehouse (ADW) for analytical workloads. Autonomous 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.
L'exemple suivant montre comment spécifier un autre modèle dans votre profil AI :
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'ANTHROPIC',
attributes =>'{"provider": "anthropic",
"credential_name": "ANTHROPIC_CRED",
"model": "claude-3-opus-20240229",
"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;
/Rubrique parent : Exemples d'utilisation de Select AI
Exemple : Sélectionnez AI avec Hugging Face
Cet exemple montre comment utiliser Hugging Face pour générer, exécuter et expliquer des instructions SQL à partir d'invites en langage naturel ou de discussions à l'aide du LLM Hugging Face.
L'exemple suivant illustre l'utilisation de Hugging Face en tant que fournisseur d'IA. L'exemple montre comment utiliser votre clé de signature d'API Hugging Face pour fournir un accès réseau, créer un profil AI et utiliser les actions Select AI pour générer des requêtes SQL à partir d'invites en langage naturel et discuter à l'aide du LLM Hugging Face.
--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 Database;
RESPONSE
------------------------------------------------------------
An Autonomous 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 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 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 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 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 Databases:
- **Oracle Autonomous Database**: One of the first and most well-known autonomous 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 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.Rubrique parent : Exemples d'utilisation de Select AI
Exemple : Sélectionner une IA avec AWS
Cet exemple montre comment utiliser AWS pour générer, exécuter et expliquer le langage SQL à partir d'invites en langage naturel ou discuter à l'aide des modèles disponibles avec AWS.
L'exemple suivant montre comment utiliser AWS en tant que fournisseur d'IA avec Amazon Bedrock et ses modèles de base. L'exemple présente la création d'informations d'identification AWS, la fourniture d'un accès réseau, la création d'un profil AI et l'utilisation d'actions Select AI pour générer des requêtes SQL à partir d'invites en langage naturel et la discussion à l'aide des modèles de base AWS.
Pour utiliser AWS, obtenez une clé d'accès, des clés secrètes et un ID de modèle. Reportez-vous à la section Use AWS. Utilisez l'ID de modèle comme attribut model dans la procédure DBMS_CLOUD_AI.CREATE_PROFILE. Vous devez indiquer l'attribut model explicitement, car aucun modèle par défaut n'est fourni.
--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" : "anthropic.claude-v2",
"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 Database;
"RESPONSE"
"An Autonomous Database is a cloud database service provided by Oracle Corporation. Some key features of Oracle Autonomous 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 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.Rubrique parent : Exemples d'utilisation de Select AI
Exemple : sélectionnez AI avec des fournisseurs compatibles OpenAI
Cet exemple montre comment utiliser des fournisseurs compatibles avec OpenAI pour générer, exécuter et expliquer du code SQL à partir d'invites en langage naturel ou discuter à l'aide des modèles disponibles avec des fournisseurs compatibles avec OpenAI.
L'exemple suivant montre comment utiliser Fireworks AI en tant que fournisseur compatible OpenAI. Il montre comment créer des informations d'identification à l'aide de votre clé de signature d'API d'IA Fireworks, configurer l'accès réseau, créer un profil d'IA et utiliser les actions Select AI pour générer des requêtes SQL à partir d'invites de langage naturel et discuter à l'aide du modèle LLM d'IA Fireworks.
Pour utiliser Fireworks AI, indiquez provider_endpoint en tant qu'attribut dans la procédure DBMS_CLOUD_AI.CREATE_PROFILE au lieu de l'attribut provider. Pour obtenir l'attribut, reportez-vous à Utilisation des fournisseurs compatibles OpenAI. Vous devez indiquer l'attribut model explicitement, car aucun modèle par défaut n'est fourni.
--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 Database;
"RESPONSE"
"An Autonomous 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 Database is to provide a self-managing, self-securing, and self-repairing database that can operate with minimal human intervention.
Autonomous 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 Databases use advanced security features, such as encryption and access controls, to protect data from unauthorized access.
5. **Improve data management**: Autonomous Databases can automatically manage data, including data ingestion, processing, and storage.
The benefits of Autonomous Databases include:
1. **Increased efficiency**: By automating routine tasks, Autonomous Databases can reduce the workload of DBAs and improve overall efficiency.
2. **Improved security**: Autonomous Databases can detect and respond to security threats in real-time, reducing the risk of data breaches.
3. **Enhanced performance**: Autonomous Databases can optimize their own performance, ensuring that applications run quickly and efficiently.
4. **Reduced costs**: By automating routine tasks and improving efficiency, Autonomous Databases can help reduce costs associated with database management.
Examples of Autonomous Databases include:
1. Oracle Autonomous Database
2. Microsoft Azure SQL Database
3. Amazon Aurora
4. Google Cloud SQL
Overall, Autonomous 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.Rubrique parent : Exemples d'utilisation de Select AI
Exemple : activation des conversations dans Select AI
Cet exemple illustre l'activation des conversations dans Select AI.
Remarque
Seul un administrateur de base de données peut exécuter les privilèges
Seul un administrateur de base de données peut exécuter les privilèges
EXECUTE et la procédure de liste de contrôle d'accès réseau.
Créez votre profil AI. Définissez l'attribut conversation sur true dans le profil. Cette action inclut le contenu des interactions ou invites précédentes, y compris éventuellement les métadonnées de schéma, et définissez votre profil. Une fois le profil activé, vous pouvez commencer à avoir des conversations avec vos données. Utilisez le langage naturel pour poser des questions et effectuer un suivi si nécessaire.
--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
--
what are the total number of customers;
CUSTOMER_COUNT
--------------
55500
break out count of customers by country;
RESPONSE
-----------------
COUNTRY_NAME CUSTOMER_COUNT
Italy 7780
Brazil 832
Japan 624
United Kingdom 7557
Germany 8173
United States of America 18520
France 3833
Canada 2010
Spain 2039
China 712
Singapore 597
New Zealand 244
Poland 708
Australia 831
Argentina 403
Denmark 383
South Africa 88
Saudi Arabia 75
Turkey 91
what age group is most common;
RESPONSE
--------------------------------------------------------------------------------
AGE_GROUP CUSTOMER_COUNT
65+ 28226
select ai keep the top 5 customers and their country by their purchases and include a rank in the result;
RESPONSE
--------------------------------------------------------------------------------
RANK CUSTOMER_NAME COUNTRY PURCHASES
1 Abigail Ruddy Japan 276
2 Abigail Ruddy Italy 168
3 Abigail Ruddy Japan 74
3 Abner Robbinette Germany 74
5 Abner Everett France 68
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('OPENAI');
PL/SQL procedure successfully completed.Rubrique parent : Exemples d'utilisation de Select AI
Exemple : Configurer et utiliser Select AI avec RAG
Cet exemple vous guide tout au long de la configuration des informations d'identification, de la configuration de l'accès réseau et de la création d'un index vectoriel pour l'intégration des services cloud de banque de vecteurs OCI Generative AI avec OpenAI à l'aide d'Oracle Autonomous Database.
La configuration se termine par la création d'un profil AI qui utilise l'index vectoriel pour améliorer les réponses LLM. Enfin, cet exemple utilise l'action Sélectionner AI narrate, qui renvoie une réponse qui a été améliorée à l'aide des informations de la base de données vectorielle spécifiée.
L'exemple suivant illustre la création et l'interrogation d'un index vectoriel dans Oracle 23ai.
--Grants EXECUTE privilege to ADB_USER
GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;
--Grants EXECUTE privilege DBMS_CLOUD_PIPELINE to ADB_USER
GRANT EXECUTE on DBMS_CLOUD_PIPELINE to ADB_USER;
-- Create the OpenAI credential
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'OPENAI_CRED',
username => 'OPENAI_CRED',
password => '<your_api_key>'
);
END;
/
PL/SQL procedure successfully completed.
-- Append the OpenAI endpoint
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 the object store credential
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'OCI_CRED',
username => '<your_username>',
password => '<OCI_profile_password>'
);
END;
/
PL/SQL procedure successfully completed.
-- Create the profile with the vector index.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'OPENAI_ORACLE',
attributes =>'{"provider": "openai",
"credential_name": "OPENAI_CRED",
"vector_index_name": "MY_INDEX",
"temperature": 0.2,
"max_tokens": 4096,
"model": "gpt-3.5-turbo-1106"
}');
END;
/
PL/SQL procedure successfully completed.
-- Set profile
EXEC DBMS_CLOUD_AI.SET_PROFILE('OPENAI_ORACLE');
PL/SQL procedure successfully completed.
-- create a vector index with the vector store name, object store location and
-- object store credential
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.
-- After the vector index is populated, we can now query the index.
-- Set profile
EXEC DBMS_CLOUD_AI.SET_PROFILE('OPENAI_ORACLE');
PL/SQL procedure successfully completed.
-- Select AI answers the question with the knowledge available in the vector database.
set pages 1000
set linesize 150
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/Manage-your-models-with-Oracle-Machine-Learning-on-Autonomous-Database.txt)
- 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/Develop-and-deploy-machine-learning-models-using-Oracle-Autonomous-Database-Machine-Learning-and-APEX.txt)
Rubrique parent : Exemples d'utilisation de Select AI
Exemple : Sélectionner l'IA avec des modèles de transformateur dans la base de données
Cet exemple montre comment importer un modèle de transformateur préentraîné stocké dans le stockage d'objets Oracle dans votre instance Oracle Database 23ai, puis utiliser le modèle de base de données importé dans le profil Select AI pour générer des incorporations vectorielles pour les blocs de document et les invites utilisateur.
Pour utiliser des modèles de transformateur dans la base de données dans votre profil Select AI, assurez-vous de disposer des éléments suivants :
-
votre modèle préentraîné importé dans votre instance Oracle Database 23ai.
-
en option, accès au stockage d'objets Oracle.
Import d'un modèle de transformateur préentraîné dans Oracle Database 23ai à partir d'Oracle Object Storage
Passez en revue les étapes décrites dans Importer des modèles préentraînés au format ONNX pour la génération de vecteurs dans la base de données et le blog Modèle de génération d'intégration prédéfini pour Oracle Database 23ai pour importer un modèle de transformateur préentraîné dans votre base de données.
L'exemple suivant montre comment importer un modèle de transformateur obtenu à partir du stockage d'objets Oracle dans votre base de données, puis visualiser le modèle importé.
- Create a Directory object, or use an existing directory object
CREATE OR REPLACE DIRECTORY ONNX_DIR AS 'onnx_model';
-- Object storage bucket
VAR location_uri VARCHAR2(4000);
EXEC :location_uri := 'https://adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com/p/eLddQappgBJ7jNi6Guz9m9LOtYe2u8LWY19GfgU8flFK4N9YgP4kTlrE9Px3pE12/n/adwc4pm/b/OML-Resources/o/';
-- Model file name
VAR file_name VARCHAR2(512);
EXEC :file_name := 'all_MiniLM_L12_v2.onnx';
-- Download ONNX model from object storage into the directory object
BEGIN
DBMS_CLOUD.GET_OBJECT(
credential_name => NULL,
directory_name => 'ONNX_DIR',
object_uri => :location_uri || :file_name);
END;
/
-- Load the ONNX model into the database
BEGIN
DBMS_VECTOR.LOAD_ONNX_MODEL(
directory => 'ONNX_DIR',
file_name => :file_name,
model_name => 'MY_ONNX_MODEL');
END;
/
-- Verify
SELECT model_name, algorithm, mining_function
FROM user_mining_models
WHERE model_name='MY_ONNX_MODEL';Utiliser des modèles de transformateur dans la base de données dans la sélection de profils AI
Ces exemples illustrent l'utilisation des modèles de transformateur dans la base de données dans un profil Select AI. Un profil est configuré uniquement pour générer des incorporations vectorielles, tandis que l'autre prend en charge à la fois les actions Select AI et la création d'index vectoriels.
Reportez-vous à Exécution des prérequis pour Select AI pour terminer les prérequis.
Voici un exemple pour générer des incorporations vectorielles uniquement :
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'EMBEDDING_PROFILE',
attributes => '{"provider" : "database",
"embedding_model": "MY_ONNX_MODEL"}'
);
END;
/Voici un exemple d'actions générales Select AI et de génération d'index vectoriel où vous pouvez spécifier un fournisseur d'IA pris en charge. Cet exemple utilise le profil et les informations d'identification OCI Gen AI. Reportez-vous à Sélectionner votre fournisseur d'IA et vos LLM pour obtenir la liste des fournisseurs pris en charge. Toutefois, si vous voulez utiliser un modèle de transformateur dans la base de données pour générer des incorporations vectorielles, utilisez "database: <MY_ONNX_MODEL>" dans l'attribut embedding_model :
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;
/
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_GENAI',
attributes => '{"provider": "oci",
"model": "meta.llama-3.3-70b-instruct",
"credential_name": "GENAI_CRED",
"vector_index_name": "MY_INDEX",
"embedding_model": "database: MY_ONNX_MODEL"}'
);
END;
/Utiliser Select AI avec un modèle de transformateur dans la base de données à partir d'un autre schéma
Cet exemple montre comment utiliser Select AI avec un modèle de transformateur dans la base de données si un autre propriétaire de schéma possède le modèle. Indiquez schema_name.object_name en tant que nom qualifié complet du modèle dans l'attribut embedding_model. Si l'utilisateur en cours est le propriétaire du schéma ou le propriétaire du modèle, vous pouvez omettre le nom du schéma.
Assurez-vous de disposer des privilèges suivants si un autre propriétaire de schéma est propriétaire du modèle :
- Privilège système
CREATE ANY MINING MODEL - Privilège système
SELECT ANY MINING MODEL - Privilège objet
SELECT MINING MODELsur le modèle spécifique
Pour accorder un privilège système, vous devez avoir obtenu le privilège système ADMIN OPTION ou le privilège système GRANT ANY PRIVILEGE.
Pour consulter les privilèges, reportez-vous à Privilèges système pour Oracle Machine Learning for SQL.
Les instructions suivantes permettent à ADB_USER1 de noter les données et de visualiser les détails de modèle dans n'importe quel schéma tant que l'accès SELECT a été accordé aux données. Cependant, ADB_USER1 peut uniquement créer des modèles dans le schéma ADB_USER1.
GRANT CREATE MINING MODEL TO ADB_USER1;
GRANT SELECT ANY MINING MODEL TO ADB_USER1;
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_GENAI',
attributes => '{"provider": "oci",
"credential_name": "GENAI_CRED",
"vector_index_name": "MY_INDEX",
"embedding_model": "database: ADB_USER1.MY_ONNX_MODEL"}'
);
END;
/L'exemple suivant montre comment indiquer un nom d'objet de modèle sensible à la casse :
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_GENAI',
attributes => '{"provider": "oci",
"credential_name": "GENAI_CRED",
"model": "meta.llama-3.3-70b-instruct",
"vector_index_name": "MY_INDEX",
"embedding_model": "database: \"adb_user1\".\"my_model\""}'
);
END;
/Exemples de bout en bout avec différents fournisseurs d'IA
Ces exemples illustrent les étapes de bout en bout de l'utilisation du modèle de transformateur dans la base de données avec Select AI RAG. Un profil utilise database en tant que provider créé exclusivement pour générer des vecteurs d'intégration, tandis que l'autre utilise oci en tant que provider créé pour les actions Select AI et l'index vectoriel.
Reportez-vous à Exécution des prérequis pour Select AI pour fournir les privilèges nécessaires.
--Grant create any directory privilege to the user
GRANT CREATE ANY DIRECTORY to ADB_USER;
- Create a Directory object, or use an existing directory object
CREATE OR REPLACE DIRECTORY ONNX_DIR AS 'onnx_model';
-- Object storage bucket
VAR location_uri VARCHAR2(4000);
EXEC :location_uri := 'https://adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com/p/eLddQappgBJ7jNi6Guz9m9LOtYe2u8LWY19GfgU8flFK4N9YgP4kTlrE9Px3pE12/n/adwc4pm/b/OML-Resources/o/';
-- Model file name
VAR file_name VARCHAR2(512);
EXEC :file_name := 'all_MiniLM_L12_v2.onnx';
-- Download ONNX model from object storage into the directory object
BEGIN
DBMS_CLOUD.GET_OBJECT(
credential_name => NULL,
directory_name => 'ONNX_DIR',
object_uri => :location_uri || :file_name);
END;
/
-- Load the ONNX model into the database
BEGIN
DBMS_VECTOR.LOAD_ONNX_MODEL(
directory => 'ONNX_DIR',
file_name => :file_name,
model_name => 'MY_ONNX_MODEL');
END;
/
-- Verify
SELECT model_name, algorithm, mining_function
FROM user_mining_models
WHERE model_name='MY_ONNX_MODEL';
--Administrator grants EXECUTE privilege to ADB_USER
GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;
--Administrator grants EXECUTE privilege DBMS_CLOUD_PIPELINE to ADB_USER
GRANT EXECUTE on DBMS_CLOUD_PIPELINE to ADB_USER;
-- Create the object store credential
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'OCI_CRED',
username => '<your_username>',
password => '<OCI_profile_password>'
);
END;
/
PL/SQL procedure successfully completed.
-- Create the profile with Oracle Database.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'EMBEDDING_PROFILE',
attributes =>'{"provider": "database",
"embedding_model": "MY_ONNX_MODEL"
}');
END;
/
PL/SQL procedure successfully completed.
-- Set profile
EXEC DBMS_CLOUD_AI.SET_PROFILE('EMBEDDING_PROFILE');
PL/SQL procedure successfully completed.
Cet exemple utilise oci en tant que provider.
--Grant create any directory privilege to the user
GRANT CREATE ANY DIRECTORY to ADB_USER;
- Create a Directory object, or use an existing directory object
CREATE OR REPLACE DIRECTORY ONNX_DIR AS 'onnx_model';
-- Object storage bucket
VAR location_uri VARCHAR2(4000);
EXEC :location_uri := 'https://adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com/p/eLddQappgBJ7jNi6Guz9m9LOtYe2u8LWY19GfgU8flFK4N9YgP4kTlrE9Px3pE12/n/adwc4pm/b/OML-Resources/o/';
-- Model file name
VAR file_name VARCHAR2(512);
EXEC :file_name := 'all_MiniLM_L12_v2.onnx';
-- Download ONNX model from object storage into the directory object
BEGIN
DBMS_CLOUD.GET_OBJECT(
credential_name => NULL,
directory_name => 'ONNX_DIR',
object_uri => :location_uri || :file_name);
END;
/
-- Load the ONNX model into the database
BEGIN
DBMS_VECTOR.LOAD_ONNX_MODEL(
directory => 'ONNX_DIR',
file_name => :file_name,
model_name => 'MY_ONNX_MODEL');
END;
/
-- Verify
SELECT model_name, algorithm, mining_function
FROM user_mining_models
WHERE model_name='MY_ONNX_MODEL';
–-Administrator Grants EXECUTE privilege to ADB_USER
GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;
--Administrator Grants EXECUTE privilege DBMS_CLOUD_PIPELINE to ADB_USER
GRANT EXECUTE on DBMS_CLOUD_PIPELINE to ADB_USER;
-- Create the object store credential
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'OCI_CRED',
username => '<your_username>',
password => '<OCI_profile_password>'
);
END;
/
--Create GenAI credentials
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 OCI AI profile
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_GENAI',
attributes => '{"provider": "oci",
"model": "meta.llama-3.3-70b-instruct",
"credential_name": "GENAI_CRED",
"vector_index_name": "MY_INDEX",
"embedding_model": "database: MY_ONNX_MODEL"}'
);
END;
/
-- Set profile
EXEC DBMS_CLOUD_AI.SET_PROFILE('OCI_GENAI');
PL/SQL procedure successfully completed.
-- create a vector index with the vector store name, object store location and
-- object store credential
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": "OCI_GENAI",
"vector_dimension": 384,
"vector_distance_metric": "cosine",
"chunk_overlap":128,
"chunk_size":1024
}');
END;
/
PL/SQL procedure successfully completed.
-- Set profile
EXEC DBMS_CLOUD_AI.SET_PROFILE('OCI_GENAI');
PL/SQL procedure successfully completed.
-- Select AI answers the question with the knowledge available in the vector database.
set pages 1000
set linesize 150
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/Manage-your-models-with-Oracle-Machine-Learning-on-Autonomous-Database.txt)
- 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/Develop-and-deploy-machine-learning-models-using-Oracle-Autonomous-
Database-Machine-Learning-and-APEX.txt)Rubrique parent : Exemples d'utilisation de Select AI
Exemple : améliorer la génération de requêtes SQL
Ces exemples montrent comment les commentaires, les annotations, la clé étrangère et les contraintes d'intégrité référentielle dans les tables et colonnes de base de données peuvent améliorer la génération de requêtes SQL à partir d'invites en langage naturel.
Exemple : Améliorer la génération SQL avec des commentaires de table et de colonne
Si vos tables de base de données contiennent des commentaires de table et de colonne, activez le paramètre "comments":"true" dans la fonction DBMS_CLOUD_AI.CREATE_PROFILE pour extraire les commentaires au niveau de la table et de la colonne. Les commentaires sont ajoutés aux métadonnées du LLM pour une meilleure génération SQL.
-- 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 DEVICEExemple : Améliorer la génération de requêtes SQL avec des annotations de table et de colonne
Cet exemple illustre l'intégration des annotations dans Select AI, applicable dans Oracle Database 23ai. Les annotations sont ajoutées aux métadonnées envoyées au LLM.
Si votre schéma contient une table avec des annotations, activez "annotations":"true" dans la fonction DBMS_CLOUD_AI.CREATE_PROFILE pour demander à Select AI d'ajouter des annotations aux métadonnées.
--
-- Annotations
--
CREATE TABLE emp2 (
empno NUMBER,
ename VARCHAR2(50) ANNOTATIONS (display 'lastname'),
salary NUMBER ANNOTATIONS ("person_salary", "column_hidden"),
deptno NUMBER ANNOTATIONS (display 'department')
)ANNOTATIONS (requires_audit 'yes', version '1.0', ówner 'HR Organization');
Table created.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'GOOGLE_ANNOTATIONS',
attributes => '{"provider": "google",
"credential_name": "GOOGLE_CRED",
"object_list": [{"owner": "ADB_USER", "name": "emp2"}],
"annotations" : "true"
}');
END;
/
PL/SQL procedure successfully completed.
EXEC DBMS_CLOUD_AI.SET_PROFILE('GOOGLE_ANNOTATIONS');
PL/SQL procedure successfully completed.Exemple : amélioration de la génération de requêtes SQL avec des contraintes de clé étrangère et de clé référentielle
Cet exemple montre comment le LLM peut générer des conditions JOIN précises en extrayant les contraintes de clé étrangère et de clé référentielle dans les métadonnées du LLM. Les contraintes de clé étrangère et de clé référentielle fournissent des données de relation structurées entre les tables et le LLM.
Activez "constraints":"true" dans la fonction DBMS_CLOUD_AI.CREATE_PROFILE pour que Select AI récupère la clé étrangère et la clé référentielle.
--
-- Referential Constraints
--
CREATE TABLE dept_test (
deptno NUMBER PRIMARY KEY,
dname VARCHAR2(50)
);
Table created.
CREATE TABLE emp3 (
empno NUMBER PRIMARY KEY,
ename VARCHAR2(50),
salary NUMBER,
deptno NUMBER,
CONSTRAINT emp_dept_fk FOREIGN KEY (deptno) REFERENCES dept_test(deptno)
);
Table created.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name=>'GOOGLE_CONSTRAINTS',
attribues =>'{"provider": "google",
"credential_name": "GOOGLE_CRED",
"object_list": [{"owner": "ADB_USER", "name": "dept_test"},
{"owner": "ADB_USER", "name": "emp3"}],
"constraints" : "true"
}');
END;
/
PL/SQL procedure successfully completed.
EXEC DBMS_CLOUD_AI.SET_PROFILE('GOOGLE_CONSTRAINTS');
PL/SQL procedure successfully completed.Exemple : détection automatique des métadonnées de table pertinentes
Ces exemples montrent comment Select AI détecte automatiquement les tables pertinentes et envoie des métadonnées uniquement pour les tables spécifiques pertinentes pour la requête dans Oracle Database 23ai. Pour activer cette fonctionnalité, définissez object_list_mode sur automated. Cela crée automatiquement un index vectoriel nommé <profile_name>_OBJECT_LIST_VECINDEX. L'index vectoriel est initialisé avec des attributs et des valeurs par défaut tels que refresh_rate, similarity_threshold et match_limit. Vous pouvez modifier certains des attributs via DBMS_CLOUD_AI.UPDATE_VECTOR_INDEX. Pour plus d'informations, reportez-vous à Procédure UPDATE_VECTOR_INDEX.
Un profil est configuré pour utiliser object_list afin d'indiquer le schéma ou les objets du schéma, tandis que l'autre n'indique pas object_list. Cependant, la même construction SQL est attendue.
Reportez-vous à Exécution des prérequis pour la sélection d'IA afin de fournir l'accès au package DBMS_CLOUD_AI et l'accès réseau au fournisseur d'IA.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name=>'OCI_AUTO',
attributes=>'{"provider": "oci",
"credential_name": "GENAI_CRED",
"object_list": [{"owner": "SH"}],
"oci_compartment_id": "ocid1.compartment.oc1..aaaa...",
"model" : "meta.llama-3.3-70b-instruct"
}');
END;
/
PL/SQL procedure successfully completed.
EXEC DBMS_CLOUD_AI.SET_PROFILE('OCI_AUTO');
PL/SQL procedure successfully completed.
select ai showsql how many customers in San Francisco are married;
RESPONSE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(DISTINCT c."CUST_ID") AS "NUMBER_OF_CUSTOMERS"
FROM "SH"."CUSTOMERS" c
WHERE UPPER(c."CUST_CITY") = UPPER('San Francisco')
AND UPPER(c."CUST_MARITAL_STATUS") = UPPER('married')L'exemple suivant compare le même scénario sans utiliser object_list. Lorsque vous n'indiquez pas object_list, Select AI choisit automatiquement tous les objets disponibles pour le schéma en cours.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name=>'OCI_AUTO1',
attributes=>'{"provider": "oci",
"credential_name": "GENAI_CRED",
"oci_compartment_id": "ocid1.compartment.oc1..aaaa...",
"object_list_mode": "automated",
"model" : "meta.llama-3.3-70b-instruct"
}');
END;
/
PL/SQL procedure successfully completed.
EXEC DBMS_CLOUD_AI.SET_PROFILE('OCI_AUTO1');
PL/SQL procedure successfully completed.
select ai showsql how many customers in San Francisco are married?;
RESPONSE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(c."CUST_ID") AS "Number_of_Customers"
FROM "SH"."CUSTOMERS" c
WHERE UPPER(c."CUST_CITY") = UPPER('San Francisco')
AND UPPER(c."CUST_MARITAL_STATUS") = UPPER('Married')Rubrique parent : Exemples d'utilisation de Select AI
Exemple : génération de données synthétiques
Cet exemple explique comment générer des données synthétiques en imitant les caractéristiques et la distribution de données réelles.
L'exemple suivant montre comment créer quelques tables dans votre schéma, utiliser OCI Generative AI en tant que fournisseur d'IA pour créer un profil d'IA, synthétiser des données dans ces tables à l'aide de la fonction DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA, et interroger ou générer des réponses aux invites en langage naturel avec Select AI.
--Create tables or use cloned tables
CREATE TABLE ADB_USER.Director (
director_id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE ADB_USER.Movie (
movie_id INT PRIMARY KEY,
title VARCHAR(100),
release_date DATE,
genre VARCHAR(50),
director_id INT,
FOREIGN KEY (director_id) REFERENCES ADB_USER.Director(director_id)
);
CREATE TABLE ADB_USER.Actor (
actor_id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE ADB_USER.Movie_Actor (
movie_id INT,
actor_id INT,
PRIMARY KEY (movie_id, actor_id),
FOREIGN KEY (movie_id) REFERENCES ADB_USER.Movie(movie_id),
FOREIGN KEY (actor_id) REFERENCES ADB_USER.Actor(actor_id)
);
-- Create the GenAI credential
BEGIN
DBMS_CLOUD.create_credential(
credential_name => 'GENAI_CRED',
user_ocid => 'ocid1.user.oc1....',
tenancy_ocid => 'ocid1.tenancy.oc1....',
private_key => 'vZ6cO...',
fingerprint => '86:7d:...'
);
END;
/
-- Create a profile
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GENAI',
attributes =>'{"provider": "oci",
"credential_name": "GENAI_CRED",
"object_list": [{"owner": "ADB_USER",
"oci_compartment_id": "ocid1.compartment.oc1...."}]
}');
END;
/
EXEC DBMS_CLOUD_AI.set_profile('GENAI');
-- Run the API for single table
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_name => 'Director',
owner_name => 'ADB_USER',
record_count => 5
);
END;
/
PL/SQL procedure successfully completed.
-- Query the table to see results
SQL> SELECT * FROM ADB_USER.Director;
DIRECTOR_ID NAME
----------- ----------------------------------------------------------------------------------------------------
1 John Smith
2 Emily Chen
3 Michael Brown
4 Sarah Taylor
5 David Lee
-- Or ask select ai to show the results
SQL> select ai how many directors are there;
NUMBER_OF_DIRECTORS
-------------------
5
Exemple : génération de données synthétiques pour plusieurs tables
Après avoir créé et défini le profil de fournisseur d'IA, utilisez DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA afin de générer des données pour plusieurs tables. Vous pouvez interroger ou utiliser Select AI pour répondre aux invites en langage naturel.
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_list => '[{"owner": "ADB_USER", "name": "Director","record_count":5},
{"owner": "ADB_USER", "name": "Movie_Actor","record_count":5},
{"owner": "ADB_USER", "name": "Actor","record_count":10},
{"owner": "ADB_USER", "name": "Movie","record_count":5,"user_prompt":"all movies released in 2009"}]'
);
END;
/
PL/SQL procedure successfully completed.
-- Query the table to see results
SQL> select * from ADB_USER.Movie;
MOVIE_ID TITLE RELEASE_D GENRE DIRECTOR_ID
---------- -------------------------------------------------------- --------- --------------------------------------------------------------- -----------
1 The Dark Knight 15-JUL-09 Action 8
2 Inglourious Basterds 21-AUG-09 War 3
3 Up in the Air 04-SEP-09 Drama 6
4 The Hangover 05-JUN-09 Comedy 1
5 District 9 14-AUG-09 Science Fiction 10
-- Or ask select ai to show the results
SQL> select ai how many actors are there;
Number of Actors
----------------
10Exemple : guidez la génération de données synthétiques avec des exemples de lignes
Pour guider le service AI dans la génération de données synthétiques, vous pouvez sélectionner de manière aléatoire des enregistrements existants dans une table. Par exemple, en ajoutant {"sample_rows": 5} à l'argument params, vous pouvez envoyer 5 exemples de lignes d'une table au fournisseur AI. Cet exemple génère 10 lignes supplémentaires en fonction des exemples de lignes de la table Transactions.
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_name => 'Transactions',
owner_name => 'ADB_USER',
record_count => 10,
params => '{"sample_rows":5}'
);
END;
/Exemple : personnalisation de la génération de données synthétiques avec des invites utilisateur
L'argument user_prompt vous permet de spécifier des règles ou des exigences supplémentaires pour la génération de données. Cela peut être appliqué à une seule table ou dans le cadre de l'argument object_list pour plusieurs tables. Par exemple, dans les appels suivants vers DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA, l'invite indique à l'IA de générer des données synthétiques sur les films publiés en 2009.
-- Definition for the Movie table CREATE TABLE Movie
CREATE TABLE Movie (
movie_id INT PRIMARY KEY,
title VARCHAR(100),
release_date DATE,
genre VARCHAR(50),
director_id INT,
FOREIGN KEY (director_id) REFERENCES Director(director_id)
);
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_name => 'Movie',
owner_name => 'ADB_USER',
record_count => 10,
user_prompt => 'all movies are released in 2009',
params => '{"sample_rows":5}'
);
END;
/
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_list => '[{"owner": "ADB_USER", "name": "Director","record_count":5},
{"owner": "ADB_USER", "name": "Movie_Actor","record_count":5},
{"owner": "ADB_USER", "name": "Actor","record_count":10},
{"owner": "ADB_USER", "name": "Movie","record_count":5,"user_prompt":"all movies are released in 2009"}]'
);
END;
/Exemple : Améliorer la qualité des données synthétiques à l'aide des statistiques de table
Si une table contient des statistiques de colonne ou est clonée à partir d'une base de données qui inclut des métadonnées, Select AI peut utiliser ces statistiques pour générer des données qui ressemblent étroitement aux données d'origine ou qui sont cohérentes avec celles-ci.
Pour les colonnes NUMBER, les valeurs haute et basse des statistiques guident la plage de valeurs. Par exemple, si la colonne SALARY de la table EMPLOYEES d'origine est comprise entre 1000 et 10000, les données synthétiques de cette colonne seront également comprises dans cette plage.
Pour les colonnes avec des valeurs distinctes, telles qu'une colonne STATE avec les valeurs CA, WA et TX, les données synthétiques utiliseront ces valeurs spécifiques. Vous pouvez gérer cette fonctionnalité à l'aide du paramètre {"table_statistics": true/false}. Par défaut, les statistiques de table sont activées.
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_name => 'Movie',
owner_name => 'ADB_USER',
record_count => 10,
user_prompt => 'all movies released in 2009',
params => '{"sample_rows":5,"table_statistics":true}'
);
END;
/Exemple : utilisation de commentaires de colonne pour guider la génération de données
S'il existe des commentaires de colonne, l'option Sélectionner IA les inclut automatiquement afin de fournir des informations supplémentaires pour le LLM lors de la génération des données. Par exemple, un commentaire sur la colonne Status d'une table Transaction peut répertorier les valeurs autorisées telles que successful, failed, pending, canceled et need manual check. Vous pouvez également ajouter des commentaires pour expliquer davantage la colonne, en donnant aux services d'IA des instructions ou des conseils plus précis pour générer des données précises. Par défaut, les commentaires sont désactivés. Pour plus d'informations, reportez-vous à Paramètres facultatifs.
-- Use comment on column
COMMENT ON COLUMN Transaction.status IS 'the value for state should either be ''successful'', ''failed'', ''pending'' or ''canceled''';
/
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_name => 'employees',
owner_name => 'ADB_USER',
record_count => 10
params => '{"comments":true}'
);
END;
/Exemple : définition de valeurs uniques dans la génération de données synthétiques
Lors de la génération de grandes quantités de données synthétiques avec des LLM, des valeurs en double sont susceptibles de se produire. Pour éviter cela, configurez une contrainte unique sur la colonne appropriée. Cela garantit que Select AI ignore les lignes avec des valeurs en double dans la réponse LLM. En outre, afin de limiter les valeurs de certaines colonnes, vous pouvez utiliser user_prompt ou ajouter des commentaires pour indiquer les valeurs autorisées, par exemple en limitant une colonne STATE à CA, WA et TX.
-- Use 'user_prompt'
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_name => 'employees',
owner_name => 'ADB_USER',
user_prompt => 'the value for state should either be CA, WA, or TX',
record_count => 10
);
END;
/
-- Use comment on column
COMMENT ON COLUMN EMPLOYEES.state IS 'the value for state should either be CA, WA, or TX'
/Exemple : amélioration de la génération de données synthétiques par traitement parallèle
Pour réduire l'exécution, Select AI divise les tâches de génération de données synthétiques en blocs plus petits pour les tables sans clés primaires ou avec des clés primaires numériques. Ces tâches s'exécutent en parallèle et interagissent avec le fournisseur d'IA pour générer des données plus efficacement. Le degré de parallélisme (DOP) de votre base de données, influencé par votre niveau de service Autonomous Database et les paramètres d'ECPU ou d'OCPU, détermine le nombre d'enregistrements que chaque bloc traite. L'exécution de tâches en parallèle améliore généralement les performances, en particulier lors de la génération de grandes quantités de données dans de nombreuses tables. Pour gérer le traitement parallèle de la génération de données synthétiques, définissez priority en tant que paramètre facultatif. Reportez-vous à Paramètres facultatifs.
Rubrique parent : Exemples d'utilisation de Select AI
Exemple : activation ou désactivation de l'accès aux données
Cet exemple montre comment les administrateurs peuvent contrôler l'accès aux données et empêcher Select AI d'envoyer des tables de schéma réelles au LLM.
Désactivation de l'accès aux données
Pour restreindre l'accès aux tables de schéma, connectez-vous en tant qu'administrateur et exécutez la procédure suivante.
EXEC DBMS_CLOUD_AI.DISABLE_DATA_ACCESS;
PL/SQL procedure successfully completed.
Désactivation des limites d'accès aux données Sélectionnez l'action narrate de l'IA et la génération de données synthétiques. L'action narrate et la génération de données synthétiques génèrent une erreur.
Connectez-vous en tant qu'utilisateur de base de données, créez et configurez votre profil AI. Reportez-vous à Exécution des prérequis pour la sélection d'IA pour configurer votre profil AI.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'DATA_ACCESS',
attributes =>'{"provider": "openai",
"credential_name": "OPENAI_CRED",
"object_list": [{"owner":"SH"}]
}');
END;
/
EXEC DBMS_CLOUD_AI.SET_PROFILE('DATA_ACCESS');
select ai how many customers;
NUM_CUSTOMERS
55500
select ai narrate what are the top 3 customers in San Francisco;
ORA-20000: Data access is disabled for SELECT AI.
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 2228
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD_AI", line 13157
ORA-06512: at line 1 https://docs.oracle.com/error-help/db/ora-20000/
The stored procedure 'raise_application_error' was called which causes this error to be generated
Error at Line: 1 Column: 6L'exemple suivant illustre les erreurs déclenchées lorsque vous tentez de générer des données synthétiques.
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'DATA_ACCESS_SDG',
object_name => 'CUSTOMERS_NEW',
owner_name => 'ADB_USER,
record_count => 5
);
END;
/
ERROR at line 1:
ORA-20000: Data access is disabled for SELECT AI.
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 2228
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD_AI", line 13401
ORA-06512: at line 2Activer l'accès aux données
L'exemple suivant illustre l'activation de l'accès aux données. Connectez-vous en tant qu'administrateur et exécutez la procédure suivante :
EXEC DBMS_CLOUD_AI.ENABLE_DATA_ACCESS;
PL/SQL procedure successfully completed.Connectez-vous en tant qu'utilisateur de base de données, créez et configurez votre profil AI. Reportez-vous à Exécution des prérequis pour la sélection d'IA pour configurer votre profil AI. Exécutez l'action narrate et générez séparément des données synthétiques.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'DATA_ACCESS_NEW',
attributes =>'{"provider": "openai",
"credential_name": "OPENAI_CRED",
"object_list": [{"owner":"SH"}]
}');
END;
/
PL/SQL procedure successfully completed.
EXEC DBMS_CLOUD_AI.SET_PROFILE('DATA_ACCESS_NEW');
PL/SQL procedure successfully completed.
select ai how many customers;
NUM_CUSTOMERS
55500
select ai narrate what are the top 3 customers in San Francisco;
"RESPONSE"
"The top 3 customers in San Francisco are Cody Seto, Lauren Yaskovich, and Ian Mc"
L'exemple suivant illustre la génération réussie de données synthétiques après l'activation de l'accès aux données.
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'DATA_ACCESS_SDG',
object_name => 'CUSTOMERS_NEW',
owner_name => 'ADB_USER',
record_count => 5
);
END;
/
PL/SQL procedure successfully completed.Rubrique parent : Exemples d'utilisation de Select AI
Exemple : restriction de l'accès aux tables dans le profil AI
Cet exemple montre comment restreindre l'accès aux tables et indiquer au LLM d'utiliser uniquement les tables spécifiées dans le fichier object_list du profil AI.
Définissez enforce_object_list sur true pour restreindre l'accès aux tables au LLM.
En tant qu'utilisateur de base de données, créez et configurez votre profil AI. Reportez-vous à Exécution des prérequis pour la sélection d'IA pour configurer votre profil AI.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GOOGLE_ENFORCED',
attributes =>'{"provider": "google",
"credential_name": "GOOGLE_CRED",
"object_list": [{"owner": "ADB_USER", "name": "GENRE"},
{"owner": "ADB_USER", "name": "CUSTOMER"},
{"owner": "ADB_USER", "name": "PIZZA_SHOP"},
{"owner": "ADB_USER", "name": "STREAMS"},
{"owner": "ADB_USER", "name": "MOVIES"},
{"owner": "ADB_USER", "name": "ACTIONS"}],
"enforce_object_list" : "true"
}');
END;
/
PL/SQL procedure successfully completed.
EXEC DBMS_CLOUD_AI.set_profile('GOOGLE_ENFORCED');
PL/SQL procedure successfully completed.
select ai showsql please list the user tables;
RESPONSE
--------------------------------------------------------------------------------------------
SELECT 'ADB_USER.GENRE' AS TABLE_NAME FROM DUAL UNION ALL SELECT 'ADB_USER.CUSTOMER' AS
TABLE_NAME FROM DUAL UNION ALL SELECT 'ADB_USER.PIZZA_SHOP' AS TABLE_NAME FROM DUAL UNION
ALL SELECT 'ADB_USER.STREAMS' AS TABLE_NAME FROM DUAL UNION ALL SELECT 'ADB_USER.MOVIES'
AS TABLE_NAME FROM DUAL
--La définition de enforce_object_list sur false indique au LLM d'utiliser d'autres tables et vues en fonction de ses connaissances antérieures.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GOOGLE_ENFORCED1',
attributes =>'{"provider": "google",
"credential_name": "GOOGLE_CRED",
"object_list": [{"owner": "ADB_USER", "name": "GENRE"},
{"owner": "ADB_USER", "name": "CUSTOMER"},
{"owner": "ADB_USER", "name": "PIZZA_SHOP"},
{"owner": "ADB_USER", "name": "STREAMS"},
{"owner": "ADB_USER", "name": "MOVIES"},
{"owner": "ADB_USER", "name": "ACTIONS"}],
"enforce_object_list" : "false"
}');
END;
/
PL/SQL procedure successfully completed.
EXEC DBMS_CLOUD_AI.set_profile('GOOGLE_ENFORCED1');
PL/SQL procedure successfully completed.
select ai showsql please list the user tables;
RESPONSE
----------------------------------
SELECT TABLE_NAME FROM USER_TABLES
Rubrique parent : Exemples d'utilisation de Select AI
Exemple : indiquer la sensibilité à la casse des colonnes
Cet exemple montre comment définir la sensibilité à la casse des colonnes dans le profil AI.
Définissez case_sensitive_values sur false pour extraire les requêtes qui ne sont pas sensibles à la casse.
En tant qu'utilisateur de base de données, créez et configurez votre profil AI. Reportez-vous à Exécution des prérequis pour la sélection d'IA pour configurer votre profil AI.
BEGIN
DBMS_CLOUD_AI.create_profile(
profile_name =>'GOOGLE',
attributes =>'{"provider": "google",
"credential_name": "GOOGLE_CRED",
"object_list": [{"owner": "ADB_USER", "name": "GENRE"},
{"owner": "ADB_USER", "name": "CUSTOMER"},
{"owner": "ADB_USER", "name": "PIZZA_SHOP"},
{"owner": "ADB_USER", "name": "STREAMS"},
{"owner": "ADB_USER", "name": "MOVIES"},
{"owner": "ADB_USER", "name": "ACTIONS"}],
"case_sensitive_values" : "false"
}');
END;
/
PL/SQL procedure successfully completed.
-- With "case_sensitive_values" set to "false", LLM will give back case insensitive query.
select ai showsql how many people watch Inception;
RESPONSE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(DISTINCT c.CUSTOMER_ID) AS "COUNT"
FROM "ADB_USER"."CUSTOMER" c
JOIN "ADB_USER"."STREAMS" s ON c.CUSTOMER_ID = s.CUSTOMER_ID
JOIN "ADB_USER"."MOVIES" m ON s.MOVIE_ID = m.MOVIE_ID
WHERE UPPER(m.TITLE) = UPPER('Inception')Vous pouvez indiquer une requête sensible à la casse à l'aide de guillemets, même si case_sensitive_values est défini sur false.
select ai showsql how many people watch "Inception";
RESPONSE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(DISTINCT c.CUSTOMER_ID) AS "COUNT"
FROM "ADB_USER"."CUSTOMER" c JOIN "ADB_USER"."STREAMS" s ON
c.CUSTOMER_ID = s.CUSTOMER_ID JOIN "ADB_USER"."MOVIES" m ON
s.MOVIE_ID = m.MOVIE_ID WHERE m.TITLE = 'Inception'
Rubrique parent : Exemples d'utilisation de Select AI