Beispiele für die Verwendung von Select AI
Erfahren Sie, wie Sie die Select AI von Oracle mit verschiedenen unterstützten KI-Providern integrieren, um SQL aus Eingabeaufforderungen in natürlicher Sprache zu generieren, auszuführen und zu erklären oder mit dem LLM zu chatten.
- Beispiel: AI-Aktionen auswählen
Diese Beispiele veranschaulichen allgemeine Select AI-Aktionen. - Beispiel: KI mit OCI Generative AI auswählen
Diese Beispiele zeigen, wie Sie mit Ihrem OCI-API-Schlüssel oder Resource Principal auf OCI Generative AI zugreifen, ein KI-Profil erstellen und SQL aus Eingabeaufforderungen in natürlicher Sprache oder Chat mit den OCI Generative AI-LLMs generieren, ausführen und erklären können. - Beispiel: AI mit OpenAI auswählen
In diesem Beispiel wird gezeigt, wie Sie mit OpenAI SQL-Anweisungen aus Eingabeaufforderungen in natürlicher Sprache generieren können. - Beispiel: AI mit Cohere auswählen
Dieses Beispiel zeigt, wie Sie mit Cohere SQL-Anweisungen aus Eingabeaufforderungen in natürlicher Sprache generieren können. - Beispiel: AI mit Azure OpenAI Service auswählen
Die folgenden Beispiele zeigen, wie Sie den Zugriff auf Azure OpenAI Service mit Ihrem API-Schlüssel aktivieren oder den Azure OpenAI Service Principal verwenden, ein AI-Profil erstellen und SQL aus Eingabeaufforderungen in natürlicher Sprache generieren können. - Beispiel: KI mit Google auswählen
Dieses Beispiel zeigt, wie Sie mit Google SQL aus Eingabeaufforderungen in natürlicher Sprache generieren, ausführen und erklären oder mit dem Google Gemini LLM chatten können. - Beispiel: KI mit Anthropic auswählen
In diesem Beispiel wird gezeigt, wie Sie mit Anthropic SQL aus Eingabeaufforderungen in natürlicher Sprache generieren, ausführen und erklären oder mit dem Anthropic Claude LLM chatten können. - Beispiel: KI mit Hugging Face auswählen
Dieses Beispiel zeigt, wie Sie mit Hugging Face SQL aus Eingabeaufforderungen in natürlicher Sprache generieren, ausführen und erklären oder mit dem Hugging Face LLM chatten können. - Beispiel: KI mit AWS auswählen
Dieses Beispiel zeigt, wie Sie mit AWS SQL aus Eingabeaufforderungen in natürlicher Sprache generieren, ausführen und erklären oder mit den mit AWS verfügbaren Modellen chatten können. - Beispiel: AI mit OpenAI-kompatiblen Providern auswählen
Dieses Beispiel zeigt, wie Sie mit OpenAI-kompatiblen Providern SQL aus Eingabeaufforderungen in natürlicher Sprache generieren, ausführen und erklären oder mit den Modellen chatten können, die mit OpenAI-kompatiblen Providern verfügbar sind. - Beispiel: Unterhaltungen in ausgewählter KI aktivieren
Diese Beispiele veranschaulichen das Aktivieren von Unterhaltungen in ausgewählter KI. - Beispiel: Select AI mit RAG einrichten und verwenden
In diesem Beispiel werden Sie durch das Einrichten von Zugangsdaten, das Konfigurieren des Netzwerkzugriffs und das Erstellen eines Vektorindex für die Integration von OCI Generative AI Vector Store Cloud-Services mit OpenAI mit Oracle Autonomous AI Database geführt. - Beispiel: KI mit datenbankinternen Transformatormodellen auswählen
Dieses Beispiel zeigt, wie Sie ein vortrainiertes Transformatormodell, das im Oracle-Objektspeicher gespeichert ist, in Ihre Oracle AI Database 26ai-Instanz importieren und dann das importierte datenbankinterne Modell im AI-Profil "Select" verwenden können, um Vektoreinbettungen für Dokument-Chunks und Benutzer-Prompts zu generieren. - Beispiel: SQL-Abfragegenerierung verbessern
Diese Beispiele zeigen, wie Kommentare, Anmerkungen, Fremdschlüssel und referenzielle Integritäts-Constraints in Datenbanktabellen und -spalten die Generierung von SQL-Abfragen aus Eingabeaufforderungen in natürlicher Sprache verbessern können. - Beispiel: Synthetische Daten generieren
In diesem Beispiel wird erläutert, wie Sie synthetische Daten generieren können, die Eigenschaften und die Verteilung realer Daten imitieren. - Beispiel: Datenzugriff aktivieren oder deaktivieren
Dieses Beispiel veranschaulicht, wie Administratoren den Datenzugriff kontrollieren und verhindern können, dass Select AI tatsächliche Schematabellen an das LLM sendet. - Beispiel: KI-Feedback auswählen
Diese Beispiele zeigen, wie Sie die ProzedurDBMS_CLOUD_AI.FEEDBACKund die verschiedenen Szenarios für die Verwendung der Aktionfeedbackverwenden können, um Feedback zu geben und die nachfolgende Generierung von SQL-Abfragen zu verbessern. - Beispiel: AI Summarize auswählen
Diese Beispiele zeigen, wie die Aktionsummarizeund die FunktionDBMS_CLOUD_AI.SUMMARIZEverwendet werden. Passen Sie außerdem die Summengenerierung für Ihren Inhalt mit der Funktion an. - Beispiel: AI Translate auswählen
Diese Beispiele zeigen, wie Sie die Funktiontranslateverwenden können. - Beispiel: Tabellenzugriff im AI-Profil einschränken
Dieses Beispiel zeigt, wie Sie den Tabellenzugriff einschränken und das LLM anweisen, nur die Tabellen zu verwenden, die in derobject_listdes AI-Profils angegeben sind. - Beispiel: Groß-/Kleinschreibung für Spalten angeben
Dieses Beispiel zeigt, wie Sie die Groß-/Kleinschreibung für Spalten im AI-Profil festlegen können.
Übergeordnetes Thema: Select AI für Natural Language Interaction mit Ihrer Datenbank verwenden
Beispiel: AI-Aktionen auswählen
Diese Beispiele veranschaulichen gängige Select AI-Aktionen.
Das folgende Beispiel zeigt Aktionen wie runsql (Standard), showsql, narrate, chat, explainsql, feedback und summarize, die Sie mit SELECT AI ausführen können. In diesen Beispielen wird das Schema sh mit AI-Provider- und Profilattributen verwendet, die in der Funktion DBMS_CLOUD_AI.CREATE_PROFILE angegeben sind. Verwenden Sie "AI-Aktionen auswählen", nachdem Sie Ihr AI-Profil mit der Prozedur DBMS_CLOUD_AI.SET_PROFILE in der aktuellen Session festgelegt haben.
Um eine Übersicht über den Text zu generieren, verwenden Sie SELECT AI SUMMARIZE <TEXT>.
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.
-- Feedback on SQL Text
-- Negative feedback example:
SQL > select ai feedback for query "select ai showsql how many watch histories in total", please use sum instead of count;
-- Positive feedback example:
SQL > select ai feedback for query "select ai showsql how many watch histories in total", the sql query generated is correct;
-- Feedback on SQL ID
-- Negative feedback example:
SQL > select ai feedback please use sum instead of count for sql_id 1v1z68ra6r9zf;
-- Positive feedback example:
SQL > select ai feedback sql query result is correct for sql_id 1v1z68ra6r9zf;
-- If not specified, use default LASTAI SQL
-- To use default LASTAI sql, make sure that set server output off;
-- Negative feedback example:
SQL > select ai feedback please use ascending sorting for ranking;
-- Positive feedback example:
SQL > select ai feedback the result is correct;
SQL> SELECT AI SUMMARIZE
Like countless other people around the globe, I stream music, and like more
than six hundred million of them I mainly use Spotify. Streaming currently
accounts for about eighty per cent of the American recording industry’s
revenue, and in recent years Spotify’s health is often consulted as a measure
for the health of the music business over all. Last spring, the International
Federation of the Phonographic Industry reported global revenues of $28.6
billion, making for the ninth straight year of growth. All of this was
unimaginable in the two-thousands, when the major record labels appeared
poorly equipped to deal with piracy and the so-called death of physical media.
On the consumer side, the story looks even rosier. Adjusted for inflation, a
... (skipped 1000 rows in the middle)
Pelly writes of some artists, in search of viral fame, who surreptitiously use
social media to effectively beta test melodies and motifs, basically putting
together songs via crowdsourcing. Artists have always fretted about the
pressure to conform, but the data-driven, music-as-content era feels
different. “You are a Spotify employee at that point,” Daniel Lopatin, who
makes abstract electronic music as Oneohtrix Point Never, told Pelly. “If your
art practice is so ingrained in the brutal reality that Spotify has outlined
for all of us, then what is the music that you’re not making? What does the
music you’re not making sound like?” Listeners might wonder something similar.
What does the music we’re not hearing sound like?;
RESPONSE
------------------------------------------------------------------------------
The music streaming industry, led by Spotify, has revolutionized the way
people consume music, with streaming accounting for 80% of the American
recording industry's revenue. However, this shift has also complicated the
lives of artists, who struggle to survive in a hyper-abundant present where
music is often valued for its convenience rather than its artistic merit.
Spotify's algorithms prioritize popularity and profitability over artistic
diversity, leading to a homogenization of music and a devaluation of the
labor that goes into creating it. Meanwhile, the company's executives reap
enormous profits, with CEO Daniel Ek's net worth rivaling that of the
wealthiest musicians. As music critic Liz Pelly argues, the streaming economy
raises important questions about autonomy, creativity, and the value of art
in a world where everything is readily available and easily accessible.Übergeordnetes Thema: Beispiele für die Verwendung von Select AI
Beispiel: AI mit OCI Generative AI auswählen
Diese Beispiele zeigen, wie Sie mit Ihrem OCI-API-Schlüssel oder Resource Principal auf OCI Generative AI zugreifen, ein KI-Profil erstellen und SQL aus Eingabeaufforderungen in natürlicher Sprache generieren, ausführen und erklären oder mit den OCI Generative AI-LLMs chatten können.
Hinweis
Wenn Sie den Parameter
Wenn Sie den Parameter
model_name nicht angeben, verwendet OCI Generative AI das Standardmodell gemäß der Tabelle unter KI-Provider und LLMs auswählen. Weitere Informationen zu den Parametern finden Sie unter Profilattribute.
-- Create Credential with OCI API key
--
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'GENAI_CRED',
user_ocid => 'ocid1.user.oc1..aaaa...',
tenancy_ocid => 'ocid1.tenancy.oc1..aaaa...',
private_key => '<your_api_key>',
fingerprint => '<your_fingerprint>'
);
END;
/
--
-- Create AI profile
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GENAI',
attributes =>'{"provider": "oci",
"credential_name": "GENAI_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}]
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"GENAI"
--
-- Use AI
--
SQL> select ai how many customers exist;
Number of Customers
-------------------
55500
SQL> select ai how many customers in San Francisco are married;
COUNT(DISTINCTC."CUST_ID")
--------------------------
28
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(DISTINCT c."CUST_ID")
FROM "SH"."CUSTOMERS" c
JOIN "SH"."COUNTRIES" co ON c."COUNTRY_ID" = co."COUNTRY_ID"
WHERE c."CUST_CITY" = 'San Francisco' AND c."CUST_MARITAL_STATUS" = 'married'
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
Here is the Oracle SQL query to find the number of customers in San Francisco who are married:
```
SELECT COUNT(*)
FROM "SH"."CUSTOMERS" c
WHERE c."CUST_CITY" = 'San Francisco'
AND c."CUST_MARITAL_STATUS" = 'Married';
```
Explanation:
* We use the `COUNT(*)` aggregate function to count the number of rows that match the conditions.
* We specify the table alias `c` for the `"SH"."CUSTOMERS"` table to make the query more readable.
* We use the `WHERE` clause to filter the rows based on two conditions:
+ `c."CUST_CITY" = 'San Francisco'`: We filter the rows where the customer's city is San Francisco.
+ `c."CUST_MARITAL_STATUS" = 'Married'`: We filter the rows where the customer's marital status is Married.
* The double quotes around the table and column names are used to make the query case-sensitive, as required in Oracle SQL.
Note: The column names and table names are assumed to be case-sensitive, so we enclose them in double quotes. If the column names and table names are not case-sensitive, you can remove the double quotes.
Collapse
This snippet was truncated for display; see it in full
SQL> select ai narrate how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------
There are 28 customers in San Francisco who are married.
SQL> select ai chat what is Autonomous AI Database;
RESPONSE
An Autonomous AI Database is a type of database that uses artificial intelligence (AI) and machine learning (ML) to automate many of the administrative and maintenance tasks typically performed by a database administrator (DBA). This allows the database to manage itself, without human intervention, to a large extent.
Autonomous AI Databases are designed to be self-driving, self-securing, and self-repairing, which means they can:
1. **Automate administrative tasks**: Such as provisioning, patching, upgrading, and tuning, which frees up DBAs to focus on higher-level tasks.
2. **Optimize performance**: By automatically adjusting parameters, indexing, and caching to ensure optimal performance and efficiency.
3. **Detect and respond to security threats**: By using AI-powered security tools to identify and respond to potential security threats in real-time.
4. **Heal itself**: By automatically detecting and repairing errors, corruption, or other issues that may arise.
5. **Scale up or down**: To match changing workload demands, without the need for manual intervention.
The benefits of Autonomous AI Databases include:
1. **Increased efficiency**: By automating routine tasks, DBAs can focus on more strategic activities.
2. **Improved performance**: Autonomous AI Databases can optimize performance in real-time, leading to faster query response times and better overall system performance.
3. **Enhanced security**: AI-powered security tools can detect and respond to threats more quickly and effectively than human administrators.
4. **Reduced costs**: By minimizing the need for manual intervention, Autonomous AI Databases can help reduce labor costs and improve resource utilization.
5. **Improved reliability**: Autonomous AI Databases can detect and repair errors more quickly, reducing downtime and improving overall system reliability.
Oracle Autonomous AI Database is a popular example of an Autonomous AI Database, which was introduced in 2018. Other vendors, such as Amazon, Microsoft, and Google, also offer Autonomous AI Database services as part of their cloud offerings.
In summary, Autonomous AI Databases are designed to be self-managing, self-optimizing, and self-healing, which can lead to improved performance, security, and efficiency, while reducing costs and administrative burdens.
--
--Clear the profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('GENAI');
PL/SQL procedure successfully completed.
Beispiel: AI mit OCI Generative AI Resource Principal auswählen
Um den Resource Principal mit OCI Generative AI zu verwenden, muss der Oracle Cloud Infrastructure-Mandantenadministrator einer dynamischen Gruppe Zugriff auf generative KI-Ressourcen erteilen. Siehe Voraussetzungen für die Verwendung des Resource Principals mit einer autonomen KI-Datenbank ausführen, um Zugriff auf eine dynamische Gruppe bereitzustellen.
Legen Sie die erforderlichen Policys fest, um Zugriff auf alle generativen KI-Ressourcen zu erhalten. Weitere Informationen zu generativen KI-Policys finden Sie unter Zugriff auf generative KI erhalten.
-
Um Zugriff auf alle generativen KI-Ressourcen im gesamten Mandanten zu erhalten, verwenden Sie die folgende Policy:
allow group <your-group-name> to manage generative-ai-family in tenancy -
Um Zugriff auf alle generativen KI-Ressourcen in Ihrem Compartment zu erhalten, nutzen Sie die folgende Policy:
allow group <your-group-name> to manage generative-ai-family in compartment <your-compartment-name>
Melden Sie sich als Administrator an, und aktivieren Sie den OCI-Resource Principal. Informationen zur Konfiguration der Parameter finden Sie unter Prozedur ENABLE_PRINCIPAL_AUTH.
Hinweis
Wenn Sie den Parameter
Wenn Sie den Parameter
model_name nicht angeben, verwendet OCI Generative AI das Standardmodell gemäß der Tabelle unter KI-Provider und LLMs auswählen. Weitere Informationen zu den Parametern finden Sie unter Profilattribute.
-- Connect as Administrator user and enable OCI resource principal.
BEGIN
DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(provider => 'OCI');
END;
/
--
-- Create AI profile
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GENAI',
attributes =>'{"provider": "oci",
"credential_name": "OCI$RESOURCE_PRINCIPAL",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}]
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"GENAI"
--
-- Use AI
--
SQL> select ai how many customers exist;
Number of Customers
-------------------
55500
SQL> select ai how many customers in San Francisco are married;
COUNT(DISTINCTC."CUST_ID")
--------------------------
28
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(DISTINCT c."CUST_ID")
FROM "SH"."CUSTOMERS" c
JOIN "SH"."COUNTRIES" co ON c."COUNTRY_ID" = co."COUNTRY_ID"
WHERE c."CUST_CITY" = 'San Francisco' AND c."CUST_MARITAL_STATUS" = 'married'
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
Here is the Oracle SQL query to find the number of customers in San Francisco who are married:
```
SELECT COUNT(*)
FROM "SH"."CUSTOMERS" c
WHERE c."CUST_CITY" = 'San Francisco'
AND c."CUST_MARITAL_STATUS" = 'Married';
```
Explanation:
* We use the `COUNT(*)` aggregate function to count the number of rows that match the conditions.
* We specify the table alias `c` for the `"SH"."CUSTOMERS"` table to make the query more readable.
* We use the `WHERE` clause to filter the rows based on two conditions:
+ `c."CUST_CITY" = 'San Francisco'`: We filter the rows where the customer's city is San Francisco.
+ `c."CUST_MARITAL_STATUS" = 'Married'`: We filter the rows where the customer's marital status is Married.
* The double quotes around the table and column names are used to make the query case-sensitive, as required in Oracle SQL.
Note: The column names and table names are assumed to be case-sensitive, so we enclose them in double quotes. If the column names and table names are not case-sensitive, you can remove the double quotes.
Collapse
This snippet was truncated for display; see it in full
SQL> select ai narrate how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------
There are 28 customers in San Francisco who are married.
SQL> select ai chat what is Autonomous AI Database;
RESPONSE
An Autonomous AI Database is a type of database that uses artificial intelligence (AI) and machine learning (ML) to automate many of the administrative and maintenance tasks typically performed by a database administrator (DBA). This allows the database to manage itself, without human intervention, to a large extent.
Autonomous AI Databases are designed to be self-driving, self-securing, and self-repairing, which means they can:
1. **Automate administrative tasks**: Such as provisioning, patching, upgrading, and tuning, which frees up DBAs to focus on higher-level tasks.
2. **Optimize performance**: By automatically adjusting parameters, indexing, and caching to ensure optimal performance and efficiency.
3. **Detect and respond to security threats**: By using AI-powered security tools to identify and respond to potential security threats in real-time.
4. **Heal itself**: By automatically detecting and repairing errors, corruption, or other issues that may arise.
5. **Scale up or down**: To match changing workload demands, without the need for manual intervention.
The benefits of Autonomous AI Databases include:
1. **Increased efficiency**: By automating routine tasks, DBAs can focus on more strategic activities.
2. **Improved performance**: Autonomous AI Databases can optimize performance in real-time, leading to faster query response times and better overall system performance.
3. **Enhanced security**: AI-powered security tools can detect and respond to threats more quickly and effectively than human administrators.
4. **Reduced costs**: By minimizing the need for manual intervention, Autonomous AI Databases can help reduce labor costs and improve resource utilization.
5. **Improved reliability**: Autonomous AI Databases can detect and repair errors more quickly, reducing downtime and improving overall system reliability.
Oracle Autonomous AI Database is a popular example of an Autonomous AI Database, which was introduced in 2018. Other vendors, such as Amazon, Microsoft, and Google, also offer Autonomous AI Database services as part of their cloud offerings.
In summary, Autonomous AI Databases are designed to be self-managing, self-optimizing, and self-healing, which can lead to improved performance, security, and efficiency, while reducing costs and administrative burdens.
--
--Clear profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('GENAI');
PL/SQL procedure successfully completed.Beispiel: Andere Region für OCI Generative AI-Profil angeben
In diesem Beispiel wird eine von OCI Generative AI unterstützte Region in Ihrem Profil angegeben. Siehe Regionen mit generativer KI.
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;
/Beispiel: AI mit OCI Generative AI mit Grok-Modell auswählen
Dieses Beispiel zeigt, wie Sie die Grok-Modelle von xAI mit OCI Generative AI-Unterstützung verwenden können. Prüfen Sie Voraussetzungen für ausgewählte KI ausführen.
--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 AI Database;
RESPONSE
-------------------
An **Autonomous AI Database** is a cloud-based database solution that uses artificial intelligence (AI) and machine learning (ML) to automate many of the routine tasks associated with managing and maintaining a database. It is designed to operate with minimal human intervention, allowing organizations to focus on data-driven insights and application development rather than database administration. The concept is often associated with Oracle's Autonomous AI Database, which was one of the first widely recognized implementations, but other cloud providers like AWS, Microsoft Azure, and Google Cloud also offer similar self-managing database services.
### Key Features of an Autonomous AI Database:
1. **Self-Driving:** Automates tasks such as provisioning, patching, tuning, and upgrading without manual input. It uses AI to optimize performance and adapt to workload changes in real-time.
2. **Self-Securing:** Automatically applies security updates, detects vulnerabilities, and protects against threats using built-in mechanisms like encryption and threat detection, reducing the risk of human error.
3. **Self-Repairing:** Identifies and resolves issues such as system failures or performance bottlenecks autonomously, ensuring high availability and minimizing downtime.
4. **Scalability:** Dynamically scales resources (compute and storage) up or down based on demand, optimizing cost and performance.
5. **Data Management:** Supports various data types and workloads, including transactional (OLTP), analytical (OLAP), and mixed workloads, often in a single converged database environment.
### Benefits:
- **Reduced Costs:** Minimizes the need for dedicated database administrators, lowering operational expenses.
- **Improved Performance:** AI-driven optimization ensures efficient query execution and resource allocation.
- **Enhanced Security:** Automated security features reduce the likelihood of breaches due to misconfigurations or delayed updates.
- **Increased Productivity:** Frees up IT staff to focus on innovation rather than routine maintenance.
### Use Cases:
- Businesses requiring high availability and reliability for critical applications.
- Organizations looking to modernize IT infrastructure with cloud-native solutions.
- Data analytics and machine learning projects needing scalable, optimized data storage.
- Environments where security and compliance are paramount, such as finance or healthcare.
### Example:
Oracle Autonomous AI Database, available on Oracle Cloud, offers two primary services:
- **Autonomous Transaction Processing (ATP):** Optimized for transactional workloads with high performance and reliability.
- **Autonomous AI Lakehouse:** Designed for analytics and big data workloads with automated data integration and optimization.
In essence, an Autonomous AI Database represents a shift toward intelligent, self-managing data systems that leverage automation and AI to simplify database operations, enhance security, and improve efficiency in a cloud environment. If you have a specific provider or context in mind, let me know, and I can dive deeper!Beispiel: AI mit OCI Generative AI mit LLAMA-Modell auswählen
In diesem Beispiel wird das Feature chat von OCI Generative AI dargestellt. Es hebt die Fähigkeiten des Modells durch zwei Prompts hervor: die Analyse von Kundenkommentaren, um ihre Stimmung zu erfassen und einen einleitenden Absatz zum Klettern zu generieren.
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!OCI Generative AI mit dem Standardmodell verwenden
Im folgenden Beispiel wird das standardmäßige OCI Generative AI-Chatmodell verwendet. Wenn Sie den Parameter model_name nicht angeben, verwendet OCI Generative AI das Standardmodell gemäß der Tabelle unter KI-Provider und LLMs auswählen.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_DEFAULT',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADB_USER"}]
}');
END;
/OCI Generative AI mit Chatmodell verwenden
Im folgenden Beispiel wird cohere.command-r-plus-08-2024 als OCI Generative AI-Chatmodell verwendet.
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;
/OCI Generative AI mit Chatmodellendpunkt-ID verwenden
Das folgende Beispiel zeigt, wie Sie die Endpunkt-ID des OCI Generative AI-Chatmodells anstelle von model angeben. Wenn Sie die Endpunkt-ID des Meta-Llama-Chatmodells verwenden, geben Sie oci_apiformat als GENERIC an.
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;
/OCI Generative AI mit Chatmodell-OCID verwenden
Dieses Beispiel zeigt, wie Sie die Endpunkt-ID des Cohere-Chatmodells von OCI Generative AI anstelle von model angeben. Wenn Sie die Endpunkt-ID des Meta-Llama-Chatmodells verwenden, geben Sie oci_apiformat als GENERIC an.
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;
/Übergeordnetes Thema: Beispiele für die Verwendung von Select AI
Beispiel: AI mit OpenAI auswählen
Dieses Beispiel zeigt, wie Sie mit OpenAI SQL-Anweisungen aus Eingabeaufforderungen in natürlicher Sprache generieren können.
Hinweis
Nur ein DBA kann
Nur ein DBA kann
EXECUTE-Berechtigungen und eine Netzwerk-ACL-Prozedur ausführen.
--Grants EXECUTE privilege to ADB_USER
--
SQL> grant execute on DBMS_CLOUD_AI to ADB_USER;
-- Grant Network ACL for OpenAI endpoint
--
SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api.openai.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create Credential for AI provider
--
EXEC
DBMS_CLOUD.CREATE_CREDENTIAL(
CREDENTIAL_NAME => 'OPENAI_CRED',
username => 'OPENAI',
password => '<your_api_token>');
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OPENAI',
attributes =>'{"provider": "openai",
"credential_name": "OPENAI_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"conversation": "true"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('OPENAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"OPENAI"
--
-- Use AI
--
SQL> select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
SQL> select ai how many customers in San Francisco are married;
MARRIED_CUSTOMERS
-----------------
18
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS married_customers_count
FROM SH.CUSTOMERS c
WHERE c.CUST_CITY = 'San Francisco'
AND c.CUST_MARITAL_STATUS = 'Married'
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
- 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
- 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
Remember to adjust the table and column names based on your actual schema if they differ from the example.
Feel free to ask if you have more questions related to SQL or database in general.
SQL> select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
--------------------------------------------------------------------------------
The top 3 customers in San Francisco are:
1. Hector Colven - Total amount sold: $52,025.99
2. Milburn Klemm - Total amount sold: $50,842.28
3. Gavin Xie - Total amount sold: $48,677.18
SQL> select ai chat what is Autonomous AI Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous AI Database is a cloud-based database service provided by Oracle. It is designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous AI Database uses machine learning and automation to optimize performance, security, and availability, allowing
users to focus on their applications and data rather than database administration tasks. It offers both Autonomous Transaction Processing (ATP) for
transactional workloads and Autonomous AI Lakehouse for analytical workloads. Autonomous AI Database provides high performance, scalability, and
reliability, making it an ideal choice for modern cloud-based applications.
--
--Clear the profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('OPENAI');
PL/SQL procedure successfully completed.Das folgende Beispiel zeigt, wie Sie ein anderes Modell in Ihrem AI-Profil angeben:
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;
/ Übergeordnetes Thema: Beispiele für die Verwendung von Select AI
Beispiel: AI mit Cohere auswählen
Dieses Beispiel zeigt, wie Sie mit Cohere SQL-Anweisungen aus Eingabeaufforderungen in natürlicher Sprache generieren können.
Hinweis
Nur ein DBA kann
Nur ein DBA kann
EXECUTE-Berechtigungen und eine Netzwerk-ACL-Prozedur ausführen.
--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.Das folgende Beispiel zeigt, wie Sie ein anderes Modell und benutzerdefinierte Attribute in Ihrem AI-Profil angeben:
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;
/Übergeordnetes Thema: Beispiele für die Verwendung von Select AI
Beispiel: AI mit Azure OpenAI Service auswählen
Die folgenden Beispiele zeigen, wie Sie den Zugriff auf Azure OpenAI Service mit Ihrem API-Schlüssel aktivieren oder den Azure OpenAI Service Principal verwenden, ein AI-Profil erstellen und SQL aus Eingabeaufforderungen in natürlicher Sprache generieren können.
-- Create Credential for AI integration
--
EXEC
DBMS_CLOUD.CREATE_CREDENTIAL(
CREDENTIAL_NAME => 'AZURE_CRED',
username => 'AZUREAI',
password => 'your_api_token');
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for OpenAI endpoint
--
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => '<azure_resource_name>.openai.azure.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name=> 'AZUREAI',
attributes=> '{"provider": "azure",
"azure_resource_name": "<azure_resource_name>",
"azure_deployment_name": "<azure_deployment_name>"
"credential_name": "AZURE_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"conversation": "true"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"AZUREAI"
--
-- Use AI
--
select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
select ai how many customers in San Francisco are married;
MARRIED_CUSTOMERS
-----------------
18
select ai showsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS married_customers_count
FROM SH.CUSTOMERS c
WHERE c.CUST_CITY = 'San Francisco'
AND c.CUST_MARITAL_STATUS = 'Married'
select ai explainsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
- 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
- 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
Remember to adjust the table and column names based on your actual schema if they differ from the example.
Feel free to ask if you have more questions related to SQL or database in general.
select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
--------------------------------------------------------------------------------
The top 3 customers in San Francisco are:
1. Hector Colven - Total amount sold: $52,025.99
2. Milburn Klemm - Total amount sold: $50,842.28
3. Gavin Xie - Total amount sold: $48,677.18
select ai chat what is Autonomous AI Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous AI Database is a cloud-based database service provided by Oracle. It is designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous AI Database uses machine learning and automation to optimize performance, security, and availability, allowing
users to focus on their applications and data rather than database administration tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous AI Lakehouse for analytical workloads. Autonomous AI Database provides high performance, scalability, and reliability, making it
an ideal choice for modern cloud-based applications.
--
--Clear the profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.Das folgende Beispiel zeigt, wie Sie ein anderes Modell in Ihrem AI-Profil angeben:
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;
/Beispiel: AI mit Azure OpenAI Service Principal auswählen
Melden Sie sich als Datenbankadministrator an, um Zugriff auf die Principal-Authentifizierung des Azure-Service zu erteilen. Erteilen Sie dann dem Benutzer (ADB_USER), der Select AI verwenden möchte, die Netzwerk-ACL-Berechtigungen. Informationen zum Zugriff auf Azure-Ressourcen finden Sie unter Azure Service-Principal für den Zugriff auf Azure-Ressourcen nutzen.
Hinweis
Nur ein DBA-Benutzer kann
Nur ein DBA-Benutzer kann
EXECUTE-Berechtigungen und eine Netzwerk-ACL-Prozedur ausführen.
-- Connect as ADMIN user and enable Azure service principal authentication.
BEGIN
DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(provider => 'AZURE',
params => JSON_OBJECT('azure_tenantid' value 'azure_tenantid'));
END;
/
-- Copy the consent url from cloud_integrations view and consents the ADB-S application.
SQL> select param_value from CLOUD_INTEGRATIONS where param_name = 'azure_consent_url';
PARAM_VALUE
--------------------------------------------------------------------------------
https://login.microsoftonline.com/<tenant_id>/oauth2/v2.0/authorize?client_id=<client_id>&response_type=code&scope=User.read
-- On the Azure OpenAI IAM console, search for the Azure application name and assign the permission to the application.
-- You can get the application name in the cloud_integrations view.
SQL> select param_value from CLOUD_INTEGRATIONS where param_name = 'azure_app_name';
PARAM_VALUE
--------------------------------------------------------------------------------
ADBS_APP_DATABASE_OCID
--
-- Grant Network ACL for Azure OpenAI endpoint
--SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'azure_resource_name.openai.azure.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name=>'AZUREAI',
attributes=>'{"provider": "azure",
"credential_name": "AZURE$PA",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"azure_resource_name": "<azure_resource_name>",
"azure_deployment_name": "<azure_deployment_name>"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"AZUREAI"
--
-- Use AI
--
select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
select ai how many customers in San Francisco are married;
MARRIED_CUSTOMERS
-----------------
18
select ai showsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS married_customers_count
FROM SH.CUSTOMERS c
WHERE c.CUST_CITY = 'San Francisco'
AND c.CUST_MARITAL_STATUS = 'Married'
select ai explainsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
- 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
- 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
Remember to adjust the table and column names based on your actual schema if they differ from the example.
Feel free to ask if you have more questions related to SQL or database in general.
select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
--------------------------------------------------------------------------------
The top 3 customers in San Francisco are:
1. Hector Colven - Total amount sold: $52,025.99
2. Milburn Klemm - Total amount sold: $50,842.28
3. Gavin Xie - Total amount sold: $48,677.18
select ai chat what is Autonomous AI Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous AI Database is a cloud-based database service provided by Oracle. It is designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous AI Database uses machine learning and automation to optimize performance, security, and availability, allowing us
ers to focus on their applications and data rather than database administration tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous AI Lakehouse for analytical workloads. Autonomous AI Database provides high performance, scalability, and reliability, making it
an ideal choice for modern cloud-based applications.
EXEC DBMS_CLOUD_AI.DROP_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.Übergeordnetes Thema: Beispiele für die Verwendung von Select AI
Beispiel: AI mit Google auswählen
Dieses Beispiel zeigt, wie Sie mit Google SQL aus Eingabeaufforderungen in natürlicher Sprache generieren, ausführen und erklären oder mit dem Google Gemini LLM chatten können.
Das folgende Beispiel zeigt, wie Sie Google als KI-Anbieter verwenden. Das Beispiel zeigt, wie Sie Ihren Google-API-Signaturschlüssel verwenden, um Netzwerkzugriff bereitzustellen, ein KI-Profil zu erstellen und mit Select AI-Aktionen SQL-Abfragen aus Eingabeaufforderungen in natürlicher Sprache und Chatantworten zu generieren.
--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 AI Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous AI Database is a cloud-based database service provided by Oracle. It is designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous AI Database uses machine learning and automation to optimize performance, security, and availability, allowing
users to focus on their applications and data rather than database administration tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Lakehouse for analytical workloads. Autonomous AI Database provides high performance, scalability, and reliability, making it
an ideal choice for modern cloud-based applications.
--
--Clear the profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('GOOGLE');
PL/SQL procedure successfully completed. Das folgende Beispiel zeigt, wie Sie ein anderes Modell in Ihrem AI-Profil angeben:
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;
/Übergeordnetes Thema: Beispiele für die Verwendung von Select AI
Beispiel: AI mit Anthropic auswählen
In diesem Beispiel wird gezeigt, wie Sie mit Anthropic SQL aus Eingabeaufforderungen in natürlicher Sprache oder Chat mit dem Anthropic Claude LLM generieren, ausführen und erklären können.
Das folgende Beispiel zeigt, wie Sie Anthropic als KI-Anbieter verwenden. Das Beispiel zeigt, wie Sie Ihren Anthropic API-Signaturschlüssel verwenden, um Netzwerkzugriff bereitzustellen, ein KI-Profil zu erstellen und mit Select AI-Aktionen SQL-Abfragen aus Eingabeaufforderungen in natürlicher Sprache zu generieren und mit dem Anthropic Claude LLM zu chatten.
Informationen zu den Profilattributen finden Sie unter Profilattribute.
--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 AI Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous AI Database is a cloud-based database service provided by Oracle. It is designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous AI Database uses machine learning and automation to optimize performance, security, and availability, allowing
users to focus on their applications and data rather than database administration tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous AI Lakehouse for analytical workloads. Autonomous AI Database provides high performance, scalability, and reliability, making it
an ideal choice for modern cloud-based applications.
--
--Clear the profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('ANTHROPIC');
PL/SQL procedure successfully completed.
Das folgende Beispiel zeigt, wie Sie ein anderes Modell in Ihrem AI-Profil angeben:
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;
/Übergeordnetes Thema: Beispiele für die Verwendung von Select AI
Beispiel: AI mit Hugging Face auswählen
Dieses Beispiel zeigt, wie Sie mit Hugging Face SQL aus Eingabeaufforderungen in natürlicher Sprache oder Chat mit dem Hugging Face LLM generieren, ausführen und erklären können.
Das folgende Beispiel zeigt, wie Sie Hugging Face als KI-Anbieter verwenden. Das Beispiel zeigt, wie Sie Ihren Hugging Face API-Signaturschlüssel verwenden, um Netzwerkzugriff bereitzustellen, ein KI-Profil zu erstellen und mit Select AI-Aktionen SQL-Abfragen aus Eingabeaufforderungen in natürlicher Sprache zu generieren und mit dem Hugging Face LLM zu chatten.
--Grant EXECUTE privilege to ADB_USER
SQL>GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;
--
-- Create Credential for AI provider
--
SQL>BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'HF_CRED',
username => 'HF',
password => '<your_api_key>'
);
END;
/
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for Hugging Face endpoint
--
SQL>BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api-inference.huggingface.co',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
SQL>BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'HF',
attributes =>'{"provider": "huggingface",
"credential_name": "HF_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"model" : "Qwen/Qwen2.5-72B-Instruct"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL>EXEC DBMS_CLOUD_AI.SET_PROFILE('HF');
PL/SQL procedure successfully completed.
--
-- Use AI
--SQL> select ai how many customers exist;
Customer_Count
--------------
55500
SQL> select ai how many customers in San Francisco are married;
Married_Customers
-----------------
46
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
------------------------------------------------------------
SELECT COUNT("CUST_ID") AS "Married_Customers"
FROM "SH"."CUSTOMERS" "C"
WHERE "CUST_CITY" = 'San Francisco' AND "CUST_MARITAL_STATUS
" = 'Married'
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
------------------------------------------------------------
To answer the question "How many customers in San Francisco are married?", we need to query the "SH"."CUSTOMERS" table and filter the results based on the city and marital status.
We will use table aliases to make the query more readable and ensure that the string comparisons follow the specified rules.
Here is the Oracle SQL query:
```sql
SELECT COUNT("CUST_ID") AS "Married_Customers_in_San_Francisco"
FROM "SH"."CUSTOMERS" c
WHERE c."CUST_CITY" = 'San Francisco' -- Case insensitive comparison
AND UPPER(c."CUST_MARITAL_STATUS") = UPPER('Married'); --
Case insensitive comparison
```
### Explanation:
1. **Table Alias**:
RESPONSE
------------------------------------------------------------
- We use the alias `c` for the "SH"."CUSTOMERS" table to make the query more readable.
2. **Column Selection**:
- We select the count of "CUST_ID" to get the number of customers who meet the criteria. We alias this count as "Married_Customers_in_San_Francisco" for clarity.
3. **WHERE Clause**:
- **City Filter**: `c."CUST_CITY" = 'San Francisco'`
- Since 'San Francisco' is not in double quotes, we use a case-insensitive comparison.
- **Marital Status Filter**: `UPPER(c."CUST_MARITAL_STATUS") = UPPER('Married')`
- Since 'Married' is not in double quotes, we use a case-insensitive comparison by applying the `UPPER` function to both sides of the comparison.
This query will return the number of customers in San Francisco who are married.
SQL> select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
------------------------------------------------------------
The top 3 customers in San Francisco are:
- Hector Colven, with a customer ID of 8866.
- Milburn Klemm, with a customer ID of 1944.
- Gavin Xie, with a customer ID of 2579.
These customers have the highest total sales among all customers in San Francisco.
SQL> select ai chat what is Autonomous AI Database;
RESPONSE
------------------------------------------------------------
An Autonomous AI Database is a type of database management system that uses artificial intelligence (AI) and machine learning (ML) to automate many of the routine tasks typically performed by database administrators (DBAs). These tasks include provisioning, patching, backups, tuning, and security. The goal of an Autonomous AI Database is to reduce the need for human intervention, minimize errors, and optimize performance,thereby allowing organizations to focus on more strategic activities.
### Key Features of Autonomous AI Databases:
RESPONSE
------------------------------------------------------------
1. **Automated Provisioning**: The database can be set up and configured automatically, reducing the time and effort required to get it up and running.
2. **Self-Patching and Updates**: The database automatically applies security patches and updates without downtime, ensuring that the system remains secure and up-to-date.
3. **Self-Tuning**: The database continuously monitors its performance and adjusts settings to optimize query execution and resource utilization.
4. **Self-Backup and Recovery**: Automated backup and recovery processes ensure that data is protected and can be restored quickly in the event of a failure.
5. **Security**: Advanced security features, including threat detection and response, are built into the database to protect against cyber threats.
6. **Scalability**: The database can automatically scale resources up or down based on demand, ensuring optimal performance and cost efficiency.
7. **Monitoring and Diagnostics**: Real-time monitoring and diagnostics help identify and resolve issues before they impact performance.
RESPONSE
------------------------------------------------------------
### Benefits of Autonomous AI Databases:
- **Reduced Operational Costs**: By automating routine tasks, the need for dedicated DBAs is reduced, lowering operational costs.
- **Improved Reliability**: Automated processes reduce the risk of human error, leading to more reliable and consistent performance.
- **Enhanced Security**: Continuous monitoring and automated security measures help protect against threats.
- **Faster Time to Market**: Automated provisioning and tuning allow applications to be deployed more quickly.
RESPONSE
------------------------------------------------------------
- **Scalability and Flexibility**: The ability to scale resources automatically ensures that the database can handle varying workloads efficiently.
### Use Cases:
- **Cloud Applications**: Autonomous AI Databases are particularly useful in cloud environments where scalability and reliability are critical.
- **Data Warehousing**: They can handle large volumes of data and complex queries, making them ideal for data warehousing and analytics.
RESPONSE
------------------------------------------------------------
- **IoT and Real-Time Data Processing**: They can process and analyze real-time data from IoT devices efficiently.
- **E-commerce**: They can handle high transaction volumes and ensure fast response times for online shopping platforms.
### Examples of Autonomous AI Databases:
- **Oracle Autonomous AI Database**: One of the first and most well-known Autonomous AI Databases, offering both transactional
and data warehousing capabilities.
- **Amazon Aurora**: A managed relational database service that includes automated scaling, patching, and backups.
- **Microsoft Azure SQL Database Managed Instance**: Provides a high level of automation and management for SQL Server databases in the cloud.
- **Google Cloud Spanner**: A globally distributed, horizontally scalable relational database that is highly available and consistent.
Autonomous AI Databases represent a significant advancement in database technology, offering organizations a more efficient, secure, and cost-effective way to manage their data.
--
--Clear the profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('HF');
PL/SQL procedure successfully completed.Übergeordnetes Thema: Beispiele für die Verwendung von Select AI
Beispiel: AI mit AWS auswählen
Dieses Beispiel zeigt, wie Sie mit AWS SQL aus Eingabeaufforderungen in natürlicher Sprache generieren, ausführen und erklären oder mit den mit AWS verfügbaren Modellen chatten können.
Das folgende Beispiel zeigt, wie AWS als KI-Anbieter mit Amazon Bedrock und seinen Basismodellen verwendet wird. Das Beispiel zeigt das Erstellen von AWS-Zugangsdaten, das Bereitstellen von Netzwerkzugriff, das Erstellen eines KI-Profils und die Verwendung von Select AI-Aktionen, um SQL-Abfragen aus Eingabeaufforderungen in natürlicher Sprache zu generieren und mit den AWS-Grundlagenmodellen zu chatten.
Um AWS zu verwenden, rufen Sie den Zugriffsschlüssel, die Secret Keys und die Modell-ID ab. Siehe AWS verwenden. Verwenden Sie die Modell-ID als Attribut model in der Prozedur DBMS_CLOUD_AI.CREATE_PROFILE. Sie müssen das Attribut model explizit angeben, da kein Standardmodell angegeben ist.
--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 AI Database;
"RESPONSE"
"An Autonomous AI Database is a cloud database service provided by Oracle Corporation. Some key features of Oracle Autonomous AI Database include:
- Fully automated and self-driving - The database automatically upgrades, patches, tunes, and backs itself up without any human intervention required.
- Self-securing - The database uses machine learning to detect threats and automatically apply security updates.
- Self-repairing - The database monitors itself and automatically recovers from failures and errors without downtime.
- Self-scaling - The database automatically scales compute and storage resources up and down as needed to meet workload demands.
- Serverless - The database is accessed as a cloud service without having to manually provision any servers or infrastructure.
- High performance - The database uses Oracle's advanced automation and machine learning to continuously tune itself for high performance.
- Multiple workload support - Supports transaction processing, analytics, graph processing, etc in a single converged database.
- Fully managed - Oracle handles all the management and administration of the database. Users just load and access their data.
- Compatible - Supports common SQL and Oracle PL/SQL for easy migration from on-prem Oracle databases.
So in summary, an Oracle Autonomous AI Database is a fully automated, self-driving, self-securing, and self-repairing database provided as a simple cloud service. The automation provides high performance, elasticity, and availability with minimal human labor required."
--
--Clear the profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('AWS');
PL/SQL procedure successfully completed.Übergeordnetes Thema: Beispiele für die Verwendung von Select AI
Beispiel: AI mit OpenAI-kompatiblen Providern auswählen
Dieses Beispiel zeigt, wie Sie mit OpenAI-kompatiblen Providern SQL aus Eingabeaufforderungen in natürlicher Sprache oder Chat mit den Modellen generieren, ausführen und erklären können, die mit OpenAI-kompatiblen Providern verfügbar sind.
Das folgende Beispiel zeigt, wie Fireworks AI als OpenAI-kompatibler Provider verwendet wird. Es zeigt, wie Sie Zugangsdaten mit Ihrem Fireworks AI API-Signaturschlüssel erstellen, den Netzwerkzugriff konfigurieren, ein KI-Profil erstellen und Select AI-Aktionen verwenden, um SQL-Abfragen aus Eingabeaufforderungen in natürlicher Sprache zu generieren und mit dem Fireworks AI LLM-Modell zu chatten.
Um Fireworks AI zu verwenden, geben Sie provider_endpoint in der Prozedur DBMS_CLOUD_AI.CREATE_PROFILE anstelle des Attributs provider als Attribut an. Informationen zum Abrufen des Attributs finden Sie unter OpenAI-kompatible Provider verwenden. Sie müssen das Attribut model explizit angeben, da kein Standardmodell angegeben ist.
--Grant EXECUTE privilege to ADB_USER
GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;
--
-- Create Credential for AI provider
--
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'FIREWORKS_CRED',
username => 'FIREWORKS',
password => '<your_fireworksaiapi_key>'
);
END;
/
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for Fireworks AI endpoint
--
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api.fireworks.ai',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'FIREWORKS',
attributes =>'{
"credential_name": "FIREWORKS_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"model" : "accounts/fireworks/models/llama-v3p1-405b-instruct",
"provider_endpoint" : "api.fireworks.ai/inference",
"conversation" : "true"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('FIREWORKS');
PL/SQL procedure successfully completed.
--
-- Use AI
--
select ai how many customers exist;
"RESPONSE"
"COUNT(*)"
55500
select ai how many customers in San Francisco are married;
"RESPONSE"
"COUNT(*)"
46
select ai showsql how many customers in San Francisco are married;
"RESPONSE"
"SELECT COUNT(*)
FROM ""SH"".""CUSTOMERS"" ""c""
WHERE UPPER(""c"".""CUST_CITY"") = UPPER('San Francisco')
AND UPPER(""c"".""CUST_MARITAL_STATUS"") = UPPER('Married')"
select ai explainsql how many customers in San Francisco are married;
"RESPONSE"
"Here is the Oracle SQL query:
```sql
SELECT COUNT(*)
FROM ""SH"".""CUSTOMERS"" ""c""
WHERE UPPER(""c"".""CUST_CITY"") = UPPER('San Francisco')
AND UPPER(""c"".""CUST_MARITAL_STATUS"") = UPPER('Married')
```
Explanation:
* We use the table alias `""c""` to refer to the `""CUSTOMERS""` table.
* We enclose the schema name `""SH""`, table name `""CUSTOMERS""`, and column names `""CUST_CITY""` and `""CUST_MARITAL_STATUS""` in double quotes to ensure case sensitivity.
* In the `WHERE` clause, we use the `UPPER()` function to perform case-insensitive comparisons for the strings `'San Francisco'` and `'Married'`, since they are not enclosed in double quotes.
* The `COUNT(*)` function returns the number of rows in the result set, which represents the number of customers in San Francisco who are married."
select ai narrate what are the top 3 customers in San Francisco;
"RESPONSE"
The top 3 customers in San Francisco are:
1. Lyndon Baltzer
2. Mike Crocker
3. Milburn Klemm
These customers are identified by their unique customer IDs, which are used to track their information and interactions. The list shows the customer's first and last names.
select ai chat what is Autonomous AI Database;
"RESPONSE"
"An Autonomous AI Database is a type of database that uses artificial intelligence (AI) and machine learning (ML) to automate many of the tasks traditionally performed by database administrators (DBAs). The goal of an Autonomous AI Database is to provide a self-managing, self-securing, and self-repairing database that can operate with minimal human intervention.
Autonomous AI Databases use advanced algorithms and machine learning techniques to:
1. **Automate database administration**: Tasks such as patching, backups, and performance tuning are automated, freeing up DBAs to focus on higher-level tasks.
2. **Predict and prevent issues**: The database uses machine learning to predict and prevent issues such as downtime, data breaches, and performance problems.
3. **Optimize performance**: The database continuously monitors and optimizes its own performance, ensuring that it is running at peak efficiency.
4. **Enhance security**: Autonomous AI Databases use advanced security features, such as encryption and access controls, to protect data from unauthorized access.
5. **Improve data management**: Autonomous AI Databases can automatically manage data, including data ingestion, processing, and storage.
The benefits of Autonomous AI Databases include:
1. **Increased efficiency**: By automating routine tasks, Autonomous AI Databases can reduce the workload of DBAs and improve overall efficiency.
2. **Improved security**: Autonomous AI Databases can detect and respond to security threats in real-time, reducing the risk of data breaches.
3. **Enhanced performance**: Autonomous AI Databases can optimize their own performance, ensuring that applications run quickly and efficiently.
4. **Reduced costs**: By automating routine tasks and improving efficiency, Autonomous AI Databases can help reduce costs associated with database management.
Examples of Autonomous AI Databases include:
1. Oracle Autonomous AI Database
2. Microsoft Azure SQL Database
3. Amazon Aurora
4. Google Cloud SQL
Overall, Autonomous AI Databases represent a significant shift in the way databases are managed and maintained, using AI and ML to automate many of the tasks traditionally performed by DBAs."
--
--Clear the profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('FIREWORKS');
PL/SQL procedure successfully completed.Übergeordnetes Thema: Beispiele für die Verwendung von Select AI
Beispiel: Unterhaltungen in ausgewählter KI aktivieren
Diese Beispiele veranschaulichen das Aktivieren von Unterhaltungen in Select AI.
Bevor Sie beginnen
Prüfen Sie Voraussetzungen für ausgewählte KI ausführen.
Hinweis
Ein Benutzer mit Administratorberechtigungen (ADMIN) muss EXECUTE erteilen und die Network Access Control List (ACL) aktivieren.
Sessionbasierte Unterhaltungen
Erstellen Sie Ihr KI-Profil. Setzen Sie das Attribut conversation im Profil auf true. Diese Aktion enthält Inhalt aus vorherigen Interaktionen oder Prompts, möglicherweise einschließlich Schemametadaten, und legen Sie Ihr Profil fest. Sobald das Profil aktiviert ist, können Sie mit Unterhaltungen mit Ihren Daten beginnen. Verwenden Sie natürliche Sprache, um Fragen zu stellen und nach Bedarf zu folgen.
--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.Anpassbare Unterhaltungen
Die folgenden Beispiele zeigen die Verwendung der Unterhaltungsmanagement-API, die anpassbare Unterhaltungen unterstützt. So verwenden Sie Select AI für mehrere Unterhaltungen:
Sie können die Unterhaltung wie folgt erstellen und festlegen:
- Konversation erstellen
- Konversation in der aktuellen Benutzersession festlegen
- Verwenden Sie
Select AI <action> <prompt>
- Verwenden Sie die Funktion
DBMS_CLOUD_AI.CREATE_COVERSATION, und legen Sie die Unterhaltung dann mitDBMS_CLOUD_AI.SET_CONVERSATION_IDfest. - Rufen Sie die Prozedur
DBMS_CLOUD_AI.CREATE_CONVERSATIONdirekt auf, um die Unterhaltung in einem Schritt zu erstellen und festzulegen.
Das folgende Beispiel zeigt, wie Sie eine Unterhaltung mit der Funktion DBMS_CLOUD_AI.CREATE_COVERSATION erstellen und mit der Prozedur DBMS_CLOUD_AI.SET_CONVERSATION_ID festlegen.
SELECT DBMS_CLOUD_AI.CREATE_CONVERSATION; -- in 19c, run SELECT DBMS_CLOUD_AI.create_conversation FROM dual;
CREATE_CONVERSATION
------------------------------------
30C9DB6E-EA4D-AFBA-E063-9C6D46644B92
EXEC DBMS_CLOUD_AI.SET_CONVERSATION_ID('30C9DB6E-EA4D-AFBA-E063-9C6D46644B92');
PL/SQL procedure successfully completedDas folgende Beispiel zeigt, wie Sie die Prozedur DBMS_CLOUD_AI.CREATE_COVERSATION ausführen, um die conversation_id direkt zu erstellen und festzulegen.
EXEC DBMS_CLOUD_AI.create_conversation;
PL/SQL procedure successfully completed.Sie können auch die Unterhaltungsattribute wie die Attribute title, description, retention_days und conversation_length anpassen.
SELECT DBMS_CLOUD_AI.CREATE_CONVERSATION(
attributes => '{"title":"My first conversation",
"description":"this is my first conversation",
"retention_days":5,
"conversation_length":5}');
CREATE_CONVERSATION
------------------------------------
38F8B874-7687-2A3F-E063-9C6D4664EC3ASie können anzeigen, ob eine bestimmte Unterhaltung vorhanden ist, indem Sie die DBA/USER_CLOUD_AI_CONVERSATIONS-Ansicht abfragen.
-- Verify the setup
SELECT conversation_id, conversation_title, description, retention_days,
conversation_length FROM DBA_CLOUD_AI_CONVERSATIONS WHERE
conversation_id = '38F8B874-7687-2A3F-E063-9C6D4664EC3A';
CONVERSATION_ID CONVERSATION_TITLE DESCRIPTION RETENTION_DAYS CONVERSATION_LENGTH
------------------------------------ ----------------------------------------------- ---------------------------------- ------------------------------ -------------------
38F8B874-7687-2A3F-E063-9C6D4664EC3A My first conversation this is my first conversation +00005 00:00:00.000000 5
Sie können auch prüfen, ob eine Unterhaltung festgelegt ist, indem Sie die Funktion DBMS_CLOUD_AI.GET_CONVERSATION_ID aufrufen.
SELECT DBMS_CLOUD_AI.GET_CONVERSATION_ID;
--------------------------------------------------------------------------------
30C9DB6E-EA4F-AFBA-E063-9C6D46644B92Beispiel: Anpassbare Unterhaltungen mit Select AI verwenden
Nachdem Sie die Unterhaltung erstellt und festgelegt und Ihr KI-Profil aktiviert haben, können Sie mit Ihren Daten interagieren. Verwenden Sie natürliche Sprache, um Fragen zu stellen und nach Bedarf zu folgen.
Verwenden Sie SELECT AI <ACTION> <PROMPT>.
SELECT AI CHAT What is the difference in weather between Seattle and San Francisco?;
RESPONSE
--------------------------------------------------------------------------------
Seattle and San Francisco are both located on the West Coast of the United State
s, but they have distinct weather patterns due to their unique geography and cli
mate conditions. Here are the main differences:
1. **Rainfall**: Seattle is known for its rainy reputation, with an average annu
al rainfall of around 37 inches (94 cm). San Francisco, on the other hand, recei
ves significantly less rainfall, with an average of around 20 inches (51 cm) per
year.
2. **Cloud Cover**: Seattle is often cloudy, with an average of 226 cloudy days
per year. San Francisco is also cloudy, but to a lesser extent, with an average
of 165 cloudy days per year.
......
SELECT AI CHAT Explain the difference again in one paragraph only.;
RESPONSE
--------------------------------------------------------------------------------
Seattle and San Francisco have different weather patterns despite both experienc
ing a mild oceanic climate. San Francisco tends to be slightly warmer, with aver
age temperatures ranging from 45?F to 67?F, and receives less rainfall, around 2
0 inches per year, mostly during winter. In contrast, Seattle is cooler, with te
mperatures ranging from 38?F to 64?F, and rainier, with around 37 inches of rain
fall per year, distributed throughout the year. San Francisco is also known for
its fog, particularly during summer, and receives more sunshine, around 160 sunn
y days per year, although it's often filtered through the fog. Overall, San Fran
cisco's weather is warmer and sunnier, with more pronounced seasonal variations,
while Seattle's is cooler and rainier, with more consistent temperatures throug
hout the year.Beispiel: Mit der Funktion GENERATE zwei Unterhaltungen vergleichen
Das folgende Beispiel zeigt, wie zwei Unterhaltungen austauschbar verwendet werden, um Fragen zu stellen und genaue Antworten zu prüfen. Jede Unterhaltung beginnt mit einer anderen Frage, die sich auf den Vergleich konzentriert. Wenn Sie später in beiden Unterhaltungen dieselbe Nachfassfrage stellen, gibt jeder eine andere Antwort basierend auf seinem vorherigen Kontext zurück.
-- First conversation
SELECT DBMS_CLOUD_AI.CREATE_CONVERSATION;
CREATE_CONVERSATION
------------------------------------
30C9DB6E-EA4D-AFBA-E063-9C6D46644B92
-- Second conversation
SELECT DBMS_CLOUD_AI.CREATE_CONVERSATION;
CREATE_CONVERSATION
------------------------------------
30C9DB6E-EA4E-AFBA-E063-9C6D46644B92
-- Call generate using the first conversation.
SELECT DBMS_CLOUD_AI.GENERATE(
prompt => 'What is the difference in weather between Seattle and San Francisco?',
profile_name => 'GENAI',
action => 'CHAT',
params => '{"conversation_id":"30C9DB6E-EA4D-AFBA-E063-9C6D46644B92"}') AS RESPONSE;
RESPONSE
--------------------------------------------------------------------------------
Seattle and San Francisco, both located in the Pacific Northwest and Northern Ca
lifornia respectively, experience a mild oceanic climate. However, there are som
e notable differences in their weather patterns:
1. **Temperature**: San Francisco tends to be slightly warmer than Seattle, espe
cially during the summer months. San Francisco's average temperature ranges from
45?F (7?C) in winter to 67?F (19?C) in summer, while Seattle's average temperat
ure ranges from 38?F (3?C) in winter to 64?F (18?C) in summer.
2. **Rainfall**: Seattle is known for its rainy reputation, with an average annu
al rainfall of around 37 inches (94 cm). San Francisco receives less rainfall, w
ith an average of around 20 inches (51 cm) per year. However, San Francisco's ra
infall is more concentrated during the winter months, while Seattle's rainfall i
s more evenly distributed throughout the year.
......
-- Call generate using the second conversation.
SELECT DBMS_CLOUD_AI.GENERATE(
prompt => 'How does the cost of living compare between New York and Los Angeles?',
profile_name => 'GENAI',
action => 'CHAT',
params => '{"conversation_id":"30C9DB6E-EA4E-AFBA-E063-9C6D46644B92"}') AS RESPONSE;
RESPONSE
--------------------------------------------------------------------------------
The cost of living in New York and Los Angeles is relatively high compared to ot
her cities in the United States. However, there are some differences in the cost
of living between the two cities. Here's a comparison of the cost of living in
New York and Los Angeles:
1. Housing: The cost of housing is significantly higher in New York than in Los
Angeles. The median home price in New York is around $999,000, while in Los Ange
les it's around $849,000. Rent is also higher in New York, with the average rent
for a one-bedroom apartment being around $3,000 per month, compared to around $
2,400 per month in Los Angeles.
2. Food: The cost of food is relatively similar in both cities, with some variat
ion in the cost of certain types of cuisine. However, eating out in New York can
be more expensive, with the average cost of a meal at a mid-range restaurant be
ing around $15-20 per person, compared to around $12-18 per person in Los Angele
s.
......
-- Call generate using the first conversation.
SELECT DBMS_CLOUD_AI.GENERATE(
prompt => 'Explain the difference again in one paragraph only.',
profile_name => 'GENAI',
action => 'CHAT',
params => '{"conversation_id":"30C9DB6E-EA4D-AFBA-E063-9C6D46644B92"}') AS RESPONSE;
RESPONSE
--------------------------------------------------------------------------------
Seattle and San Francisco have different weather patterns despite both experienc
ing a mild oceanic climate. San Francisco tends to be slightly warmer, with aver
age temperatures ranging from 45?F to 67?F, and receives less rainfall, around 2
0 inches per year, mostly during winter. In contrast, Seattle is cooler, with te
mperatures ranging from 38?F to 64?F, and rainier, with around 37 inches of rain
fall per year, distributed throughout the year. San Francisco is also known for
its fog, particularly during summer, and receives more sunshine, around 160 sunn
y days per year, although it's often filtered through the fog. Overall, San Fran
cisco's weather is warmer and sunnier, with more pronounced seasonal variations,
while Seattle's is cooler and rainier, with more consistent temperatures throug
hout the year.
-- Call generate using the second conversation.
SELECT DBMS_CLOUD_AI.GENERATE(
prompt => 'Explain the difference again in one paragraph only.',
profile_name => 'GENAI',
action => 'CHAT',
params => '{"conversation_id":"30C9DB6E-EA4E-AFBA-E063-9C6D46644B92"}') AS RESPONSE;
RESPONSE
--------------------------------------------------------------------------------
The cost of living in New York is approximately 20-30% higher than in Los Angele
s, mainly due to the higher cost of housing and transportation. New York has a m
edian home price of around $999,000 and average rent of $3,000 per month for a o
ne-bedroom apartment, compared to Los Angeles' median home price of $849,000 and
average rent of $2,400 per month. While the cost of food and utilities is relat
ively similar in both cities, the cost of transportation is higher in Los Angele
s due to its car-centric culture, but the cost of public transportation is highe
r in New York. Overall, the total monthly expenses for a single person in New Yo
rk can range from $4,600, compared to around $4,050 in Los Angeles, making New Y
ork the more expensive city to live in.
Sie können die Funktion DBMS_CLOUD_AI.GENERATE aufrufen, ohne eine Unterhaltung anzugeben. In solchen Fällen sollte jedoch keine sinnvolle Antwort erwartet werden.
-- Ask SELECT AI using the second conversation.
SELECT DBMS_CLOUD_AI.GENERATE(
prompt => 'Explain the difference again in one paragraph only.',
profile_name => 'GENAI',
action => 'CHAT') AS RESPONSE;
RESPONSE
--------------------------------------------------------------------------------
There is no previous explanation to draw from, as this is the beginning of our c
onversation. If you would like to ask a question or provide a topic, I would be
happy to explain the differences related to it in one paragraph.
Beispiel: Unterhaltungen über DBMS_CLOUD_AI-Ansichten prüfen
Sie können die DBMS_CLOUD_AI-Unterhaltungsansichten abfragen, um Konversations- und Prompt-Details zu prüfen. Weitere Informationen finden Sie unter DBMS_CLOUD_AI Views.
Hinweis
Die
Die
Ansichten mit dem Präfix DBA_ sind nur für Benutzer mit Administratorberechtigungen (ADMIN) verfügbar.
SELECT conversation_id, conversation_title, description FROM dba_cloud_ai_conversations;
CONVERSATION_ID
------------------------------------
CONVERSATION_TITLE
----------------------------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
30C9DB6E-EA4D-AFBA-E063-9C6D46644B92
Seattle vs San Francisco Weather
The conversation discusses the comparison of weather patterns between Seattle an
d San Francisco, focusing on the differences in temperature, rainfall, fog, suns
hine, and seasonal variation between the two cities.
30C9DB6E-EA4E-AFBA-E063-9C6D46644B92
NY vs LA Cost Comparison
The conversation discusses and compares the cost of living in New York and Los A
ngeles, covering housing, food, transportation, utilities, and taxes to provide
an overall view of the expenses in both cities.
SELECT conversation_id, count(*) FROM dba_cloud_ai_conversation_prompts
GROUP BY conversation_id;
CONVERSATION_ID COUNT(*)
------------------------------------ ----------
30C9DB6E-EA4D-AFBA-E063-9C6D46644B92 2
30C9DB6E-EA4E-AFBA-E063-9C6D46644B92 2Beispiel: Unterhaltungsdetails aktualisieren
Sie können die title, description und retention_days einer Unterhaltung mit der Prozedur DBMS_CLOUD_AI.UPDATE_CONVERSATION aktualisieren. Sie können die Aktualisierung prüfen, indem Sie die Unterhaltungsansicht DBMS_CLOUD_AI abfragen.
-- Update the second conversation's title, description and retention_days
SQL> EXEC DBMS_CLOUD_AI.update_conversation(conversation_id => '30C9DB6E-EA4E-AFBA-E063-9C6D46644B92',
attributes => '{"retention_days":20,
"description":"This a description",
"title":"a title",
"conversation_length":20}');
PL/SQL procedure successfully completed.
-- Verify the information for the second conversation
SQL> SELECT conversation_title, description, retention_days
FROM dba_cloud_ai_conversations
WHERE conversation_id = '30C9DB6E-EA4E-AFBA-E063-9C6D46644B92';
CONVERSATION_TITLE DESCRIPTION RETENTION_DAYS LENGTH
-------------------------- ------------------------------------ -------------- --------------
a title This a description 20 20Beispiel: Prompt löschen
Sie können eine einzelne Eingabeaufforderung aus Ihren Unterhaltungen löschen und die Änderung prüfen, indem Sie die Unterhaltungsansicht DBMS_CLOUD_AI abfragen.
-- Find the latest prompt for first conversation
SELECT conversation_prompt_id FROM dba_cloud_ai_conversation_prompts
WHERE conversation_id = '30C9DB6E-EA4D-AFBA-E063-9C6D46644B92'
ORDER BY created DESC
FETCH FIRST ROW ONLY;
CONVERSATION_PROMPT_ID
------------------------------------
30C9DB6E-EA61-AFBA-E063-9C6D46644B92
-- Delete the prompt
EXEC DBMS_CLOUD_AI.DELETE_CONVERSATION_PROMPT('30C9DB6E-EA61-AFBA-E063-9C6D46644B92');
PL/SQL procedure successfully completed.
-- Verify if the prompt is deleted
SELECT conversation_prompt_id FROM dba_cloud_ai_conversation_prompts
WHERE conversation_id = '30C9DB6E-EA4D-AFBA-E063-9C6D46644B92';
-- Only one prompt now
CONVERSATION_PROMPT_ID
------------------------------------
30C9DB6E-EA5A-AFBA-E063-9C6D46644B92Beispiel: Unterhaltung löschen
Sie können die gesamte Konversation löschen. Dadurch werden auch alle zugehörigen Prompts entfernt.
-- Delete the first conversation
EXEC DBMS_CLOUD_AI.DROP_CONVERSATION('30C9DB6E-EA4D-AFBA-E063-9C6D46644B92');
PL/SQL procedure successfully completed.
-- Verify if the conversation and its prompts are removed
SELECT conversation_id FROM dba_cloud_ai_conversations;
-- We only have the second conversation now
CONVERSATION_ID
------------------------------------
30C9DB6E-EA4E-AFBA-E063-9C6D46644B92
SELECT conversation_id, count(*) FROM dba_cloud_ai_conversation_prompts GROUP BY conversation_id;
-- We only have prompts in the second conversation
CONVERSATION_ID COUNT(*)
------------------------------------ ----------
30C9DB6E-EA4E-AFBA-E063-9C6D46644B92 2Übergeordnetes Thema: Beispiele für die Verwendung von Select AI
Beispiel: AI mit RAG einrichten und verwenden
Dieses Beispiel führt Sie durch das Einrichten von Zugangsdaten, das Konfigurieren des Netzwerkzugriffs und das Erstellen eines Vektorindex für die Integration von OCI Generative AI Vektorspeicher-Cloud-Services mit OpenAI mit Oracle Autonomous AI Database.
Das Setup endet mit der Erstellung eines KI-Profils, das den Vektorindex verwendet, um LLM-Antworten zu verbessern. In diesem Beispiel wird schließlich die Aktion "AI auswählen narrate" verwendet, die eine Antwort zurückgibt, die mit Informationen aus der angegebenen Vektordatenbank verbessert wurde.
Das folgende Beispiel zeigt das Erstellen und Abfragen eines Vektorindex in Oracle AI Database 26ai.
--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)
Übergeordnetes Thema: Beispiele für die Verwendung von Select AI
Beispiel: AI mit datenbankinternen Transformer-Modellen auswählen
Dieses Beispiel zeigt, wie Sie ein vortrainiertes Transformatormodell, das im Oracle-Objektspeicher gespeichert ist, in Ihre Oracle AI Database 26ai-Instanz importieren und dann das importierte datenbankinterne Modell im AI-Profil "Select" verwenden können, um Vektoreinbettungen für Dokument-Chunks und Benutzer-Prompts zu generieren.
Um datenbankinterne Transformer-Modelle in Ihrem Select AI-Profil zu verwenden, stellen Sie sicher, dass Folgendes vorhanden ist:
-
Ihr vortrainiertes Modell, das in Ihre Oracle AI Database 26ai-Instanz importiert wurde.
-
optional Zugriff auf Oracle Object Storage.
Vorgeschultes Transformatormodell aus Oracle Object Storage in Oracle AI Database 26ai importieren
Lesen Sie die Schritte unter Vorgeschulte Modelle im ONNX-Format für die Vektorgenerierung in der Datenbank importieren und im Blog Vorgefertigtes Einbettungsgenerierungsmodell für Oracle AI Database 26ai, um ein vorgeschultes Transformatormodell in Ihre Datenbank zu importieren.
Das folgende Beispiel zeigt, wie Sie ein vordefiniertes Transformatormodell aus dem Oracle-Objektspeicher in die Datenbank importieren und dann das importierte Modell anzeigen.
- 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';Datenbankinterne Transformer-Modelle in ausgewählten KI-Profilen verwenden
Diese Beispiele veranschaulichen, wie datenbankinterne Transformatormodelle in einem Select AI-Profil verwendet werden. Ein Profil ist nur für die Generierung von Vektoreinbettungen konfiguriert, während das andere sowohl Select AI-Aktionen als auch die Erstellung von Vektorindizes unterstützt.
Prüfen Sie Voraussetzungen für ausgewählte KI ausführen, um die Voraussetzungen zu erfüllen.
Im Folgenden finden Sie ein Beispiel für die Generierung von Vektoreinbettungen:
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'EMBEDDING_PROFILE',
attributes => '{"provider" : "database",
"embedding_model": "MY_ONNX_MODEL"}'
);
END;
/Im Folgenden finden Sie ein Beispiel für allgemeine Select AI-Aktionen und Vektorindexgenerierung, bei denen Sie einen unterstützten AI-Provider angeben können. In diesem Beispiel werden das KI-Profil und die Zugangsdaten der OCI-Generation verwendet. Eine Liste der unterstützten Provider finden Sie unter KI-Provider und LLMs auswählen. Wenn Sie jedoch das datenbankinterne Transformatormodell zum Generieren von Vektoreinbettungen verwenden möchten, verwenden Sie "database: <MY_ONNX_MODEL>" im 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;
/Select AI mit einem datenbankinternen Transformatormodell aus einem anderen Schema verwenden
In diesem Beispiel wird gezeigt, wie Sie Select AI mit einem datenbankinternen Transformatormodell verwenden, wenn ein anderer Schemaeigentümer Eigentümer des Modells ist. Geben Sie schema_name.object_name als vollqualifizierten Namen des Modells im Attribut embedding_model an. Wenn der aktuelle Benutzer der Schemaeigentümer oder Eigentümer des Modells ist, können Sie den Schemanamen weglassen.
Stellen Sie sicher, dass Sie über die folgenden Berechtigungen verfügen, wenn ein anderer Schemaeigentümer Eigentümer des Modells ist:
CREATE ANY MINING MODEL-SystemberechtigungSELECT ANY MINING MODEL-SystemberechtigungSELECT MINING MODEL-Objektberechtigung für das spezifische Modell
Um eine Systemberechtigung zu erteilen, benötigen Sie entweder die Systemberechtigung mit der ADMIN OPTION oder die Systemberechtigung GRANT ANY PRIVILEGE.
Informationen zu den Berechtigungen finden Sie unter Systemberechtigungen für Oracle Machine Learning for SQL.
Mit den folgenden Anweisungen kann ADB_USER1 Daten bewerten und Modelldetails in jedem Schema anzeigen, solange SELECT-Zugriff auf die Daten erteilt wurde. ADB_USER1 kann jedoch nur Modelle im Schema ADB_USER1 erstellen.
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;
/Das folgende Beispiel zeigt, wie Sie den Modellobjektnamen für die Groß-/Kleinschreibung angeben können:
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;
/End-to-End-Beispiele mit verschiedenen KI-Providern
Diese Beispiele zeigen End-to-End-Schritte für die Verwendung des datenbankinternen Transformatormodells mit Select AI RAG. Ein Profil verwendet database als die provider, die ausschließlich zum Generieren von Einbettungsvektoren erstellt wurde, während das andere Profil oci als die provider verwendet, die für Select AI-Aktionen sowie für Vektorindizes erstellt wurden.
Prüfen Sie Voraussetzungen für ausgewählte KI ausführen, um die erforderlichen Berechtigungen bereitzustellen.
--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.
In diesem Beispiel wird oci als provider verwendet.
--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)Übergeordnetes Thema: Beispiele für die Verwendung von Select AI
Beispiel: SQL-Abfragegenerierung verbessern
Diese Beispiele zeigen, wie Kommentare, Annotationen, Fremdschlüssel und referenzielle Integritäts-Constraints in Datenbanktabellen und -spalten die Generierung von SQL-Abfragen aus Eingabeaufforderungen in natürlicher Sprache verbessern können.
Beispiel: SQL-Generierung mit Tabellen- und Spaltenkommentaren verbessern
Wenn Tabellen- und Spaltenkommentare in den Datenbanktabellen vorhanden sind, aktivieren Sie den Parameter "comments":"true" in der Funktion DBMS_CLOUD_AI.CREATE_PROFILE, um Kommentare auf Tabellen- und Spaltenebene abzurufen. Die Kommentare werden den Metadaten des LLM hinzugefügt, um eine bessere SQL-Generierung zu ermöglichen.
-- 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 DEVICEBeispiel: SQL-Abfragegenerierung mit Tabellen- und Spaltenanmerkungen verbessern
Dieses Beispiel zeigt die Integration von Anmerkungen in Select AI, die in Oracle AI Database 26ai anwendbar sind. Die Annotationen werden den Metadaten hinzugefügt, die an das LLM gesendet werden.
Wenn in Ihrem Schema eine Tabelle mit Annotationen vorhanden ist, aktivieren Sie "annotations":"true" in der Funktion DBMS_CLOUD_AI.CREATE_PROFILE, um Select AI anzuweisen, den Metadaten Anmerkungen hinzuzufügen.
--
-- 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.Beispiel: SQL-Abfragegenerierung mit Fremdschlüssel- und referenziellen Schlüssel-Constraints verbessern
Dieses Beispiel zeigt die Fähigkeit des LLM, genaue JOIN-Bedingungen zu generieren, indem der Fremdschlüssel und die referentiellen Schlüssel-Constraints in die Metadaten des LLM abgerufen werden. Die Constraints für Fremdschlüssel und referenzielle Schlüssel stellen strukturierte Beziehungsdaten zwischen den Tabellen für das LLM bereit.
Aktivieren Sie "constraints":"true" in der Funktion DBMS_CLOUD_AI.CREATE_PROFILE, damit Select AI Fremdschlüssel und Referenzschlüssel abrufen kann.
--
-- 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.Beispiel: Relevante Tabellenmetadaten automatisch ermitteln
In diesen Beispielen wird gezeigt, wie Select AI relevante Tabellen automatisch erkennt und Metadaten nur für die spezifischen Tabellen sendet, die für die Abfrage in Oracle AI Database 26ai relevant sind. Um dieses Feature zu aktivieren, setzen Sie object_list_mode auf automated. Dadurch wird automatisch ein Vektorindex mit dem Namen <profile_name>_OBJECT_LIST_VECINDEX erstellt. Der Vektorindex wird mit Standardattributen und -werten initialisiert, wie refresh_rate, similarity_threshold und match_limit. Sie können einige der Attribute über DBMS_CLOUD_AI.UPDATE_VECTOR_INDEX ändern. Weitere Informationen finden Sie unter Prozedur UPDATE_VECTOR_INDEX.
Ein Profil ist so konfiguriert, dass es object_list verwendet, um das Schema oder die Objekte im Schema anzugeben, während das andere nicht object_list angibt. Es wird jedoch dasselbe SQL-Konstrukt erwartet.
Prüfen Sie Voraussetzungen für ausgewählte KI ausführen, um Zugriff auf das Package DBMS_CLOUD_AI bereitzustellen und Netzwerkzugriff für den AI-Provider bereitzustellen.
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')Im folgenden Beispiel wird dasselbe Szenario ohne Verwendung von object_list verglichen. Wenn Sie object_list nicht angeben, wählt Select AI automatisch alle Objekte aus, die für das aktuelle Schema verfügbar sind.
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')Übergeordnetes Thema: Beispiele für die Verwendung von Select AI
Beispiel: Synthetische Daten generieren
In diesem Beispiel wird erläutert, wie Sie synthetische Daten generieren können, die Eigenschaften und die Verteilung realer Daten nachahmen.
Das folgende Beispiel zeigt, wie Sie einige Tabellen in Ihrem Schema erstellen, OCI Generative AI als AI-Provider verwenden, um ein AI-Profil zu erstellen, Daten mit der Funktion DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA in diesen Tabellen zu synthetisieren und Antworten auf Eingabeaufforderungen in natürlicher Sprache mit Select AI abzufragen oder zu generieren.
--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
Beispiel: Synthetische Daten für mehrere Tabellen generieren
Nachdem Sie Ihr AI-Providerprofil erstellt und festgelegt haben, verwenden Sie DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA, um Daten für mehrere Tabellen zu generieren. Sie können Select AI abfragen oder verwenden, um auf die Eingabeaufforderungen in natürlicher Sprache zu reagieren.
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
----------------
10Beispiel: Anleitung zur Generierung synthetischer Daten mit Beispielzeilen
Um den AI-Service beim Generieren synthetischer Daten zu unterstützen, können Sie nach dem Zufallsprinzip vorhandene Datensätze aus einer Tabelle auswählen. Beispiel: Wenn Sie dem Argument params {"sample_rows": 5} hinzufügen, können Sie 5 Beispielzeilen aus einer Tabelle an den AI-Provider senden. In diesem Beispiel werden 10 zusätzliche Zeilen basierend auf den Beispielzeilen aus der Tabelle Transactions generiert.
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;
/Beispiel: Synthetische Datengenerierung mit Benutzer-Prompts anpassen
Mit dem Argument user_prompt können Sie zusätzliche Regeln oder Anforderungen für die Datengenerierung angeben. Dies kann auf eine einzelne Tabelle oder als Teil des Arguments object_list für mehrere Tabellen angewendet werden. Beispiel: Bei den folgenden Aufrufen von DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA weist der Prompt die KI an, synthetische Daten in Filmen zu generieren, die 2009 veröffentlicht wurden.
-- 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;
/Beispiel: Synthetische Datenqualität mit Tabellenstatistiken verbessern
Wenn eine Tabelle Spaltenstatistiken enthält oder aus einer Datenbank geklont wird, die Metadaten enthält, kann Select AI diese Statistiken verwenden, um Daten zu generieren, die den ursprünglichen Daten ähnlich oder konsistent sind.
Bei NUMBER-Spalten leiten die Werte für "Hoch" und "Niedrig" aus den Statistiken den Wertebereich. Beispiel: Wenn die Spalte SALARY in der ursprünglichen Tabelle EMPLOYEES zwischen 1000 und 10000 liegt, fallen auch die synthetischen Daten für diese Spalte in diesen Bereich.
Für Spalten mit eindeutigen Werten, wie z.B. eine Spalte STATE mit den Werten CA, WA und TX, verwenden die synthetischen Daten diese spezifischen Werte. Sie können dieses Feature mit dem Parameter {"table_statistics": true/false} verwalten. Standardmäßig sind die Tabellenstatistiken aktiviert.
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;
/Beispiel: Mit Spaltenkommentaren die Datengenerierung steuern
Wenn Spaltenkommentare vorhanden sind, schließt Select AI diese automatisch ein, um während der Datengenerierung zusätzliche Informationen für das LLM bereitzustellen. Beispiel: Ein Kommentar in der Spalte Status einer Transaktionstabelle kann zulässige Werte auflisten, wie successful, failed, pending, canceled und need manual check. Sie können auch Kommentare hinzufügen, um die Spalte weiter zu erläutern und KI-Services präzisere Anweisungen oder Hinweise zur Generierung genauer Daten zu geben. Standardmäßig sind Kommentare deaktiviert. Weitere Informationen finden Sie unter Optionale Parameter.
-- 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;
/Beispiel: Eindeutige Werte in der Generierung synthetischer Daten festlegen
Wenn große Mengen synthetischer Daten mit LLMs generiert werden, treten wahrscheinlich doppelte Werte auf. Um dies zu verhindern, richten Sie einen eindeutigen Constraint für die relevante Spalte ein. Dadurch wird sichergestellt, dass Select AI Zeilen mit doppelten Werten in der LLM-Antwort ignoriert. Um außerdem Werte für bestimmte Spalten einzuschränken, können Sie die user_prompt verwenden oder Kommentare hinzufügen, um die zulässigen Werte anzugeben, wie z.B. die Begrenzung einer STATE-Spalte auf CA, WA und 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'
/Beispiel: Synthetische Datengenerierung durch parallele Verarbeitung verbessern
Um die Laufzeit zu reduzieren, teilt Select AI Aufgaben zur synthetischen Datengenerierung in kleinere Chunks für Tabellen ohne Primärschlüssel oder mit numerischen Primärschlüsseln auf. Diese Aufgaben werden parallel ausgeführt und interagieren mit dem KI-Provider, um Daten effizienter zu generieren. Der Parallelitätsgrad (DOP) in Ihrer Datenbank, der von Ihrem Servicegrad der autonomen KI-Datenbank und den ECPU- oder OCPU-Einstellungen beeinflusst wird, bestimmt die Anzahl der Datensätze, die jeder Chunk-Prozess verarbeitet. Die parallele Ausführung von Aufgaben verbessert im Allgemeinen die Performance, insbesondere wenn große Datenmengen über viele Tabellen hinweg generiert werden. Um die parallele Verarbeitung der synthetischen Datengenerierung zu verwalten, legen Sie priority als optionalen Parameter fest. Siehe Optionale Parameter.
Übergeordnetes Thema: Beispiele für die Verwendung von Select AI
Beispiel: Datenzugriff aktivieren oder deaktivieren
Dieses Beispiel veranschaulicht, wie Administratoren den Datenzugriff kontrollieren und verhindern können, dass Select AI tatsächliche Schematabellen an das LLM sendet.
Datenzugriff deaktivieren
Um den Zugriff auf Schematabellen einzuschränken, melden Sie sich als Administrator an, und führen Sie das folgende Verfahren aus.
EXEC DBMS_CLOUD_AI.DISABLE_DATA_ACCESS;
PL/SQL procedure successfully completed.
Deaktivieren von Datenzugriffsbeschränkungen Wählen Sie die narrate-Aktion von AI und die Generierung synthetischer Daten aus. Die Aktion narrate und die Generierung synthetischer Daten lösen einen Fehler aus.
Melden Sie sich als Datenbankbenutzer an, und erstellen und konfigurieren Sie Ihr AI-Profil. Prüfen Sie Voraussetzungen für ausgewählte KI ausführen, um Ihr AI-Profil zu konfigurieren.
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: 6Das folgende Beispiel zeigt die Fehler, die ausgelöst werden, wenn Sie versuchen, synthetische Daten zu generieren.
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 2Datenzugriff aktivieren
Das folgende Beispiel zeigt, wie Sie den Datenzugriff aktivieren. Melden Sie sich als Administrator an, und führen Sie das folgende Verfahren aus:
EXEC DBMS_CLOUD_AI.ENABLE_DATA_ACCESS;
PL/SQL procedure successfully completed.Melden Sie sich als Datenbankbenutzer an, und erstellen und konfigurieren Sie Ihr AI-Profil. Prüfen Sie Voraussetzungen für ausgewählte KI ausführen, um Ihr AI-Profil zu konfigurieren. Führen Sie die Aktion narrate aus, und generieren Sie synthetische Daten separat.
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"
Das folgende Beispiel zeigt eine erfolgreiche Generierung synthetischer Daten nach dem Aktivieren des Datenzugriffs.
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.Übergeordnetes Thema: Beispiele für die Verwendung von Select AI
Beispiel: KI-Feedback auswählen
Diese Beispiele zeigen, wie Sie die Prozedur DBMS_CLOUD_AI.FEEDBACK und die verschiedenen Szenarios der Aktion feedback verwenden können, um Feedback zu geben und die nachfolgende Generierung von SQL-Abfragen zu verbessern.
Bevor Sie beginnen
Prüfen Sie Voraussetzungen für ausgewählte KI ausführen.
Beispiel: Negatives Feedback geben
Das folgende Beispiel zeigt, wie Sie Korrekturen an der generierten SQL als Feedback (negatives Feedback) angeben, indem Sie feedback_type als negative verwenden und Ihre SQL-Abfrage bereitstellen.
Sie fügen Ihr Feedback zum KI-Profil OCI_FEEDBACK1 hinzu, indem Sie die Prozedur DBMS_CLOUD_AI.FEEDBACK mit dem Parameter sql_text aufrufen, der den Prompt enthält. Weitere Informationen zu den Attributen finden Sie unter FEEDBACK-Prozedur. Anschließend rufen Sie die Spalten content und attributes aus der Tabelle <profile_name>_FEEDBACK_VECINDEX$VECTAB ab, die mit dieser spezifischen SQL-Abfrage verknüpft ist. Select AI erstellt diese Vektortabelle automatisch, wenn Sie die Feedbackfunktion zum ersten Mal verwenden. Weitere Informationen finden Sie unter Vector-Index für FEEDBACK.
SQL> select ai showsql how many movies;
RESPONSE
------------------------------------------------------------------------
SELECT COUNT(m."MOVIE_ID") AS "Number of Movies" FROM "ADB_USER"."MOVIES" m
SQL> exec DBMS_CLOUD_AI.FEEDBACK(profile_name=>'OCI_FEEDBACK1', sql_text=> 'select ai showsql how many movies', feedback_type=> 'negative', response=>'SELECT SUM(1) FROM "ADB_USER"."MOVIES"');
PL/SQL procedure successfully completed.
SQL> select CONTENT, ATTRIBUTES from OCI_FEEDBACK1_FEEDBACK_VECINDEX$VECTAB where JSON_VALUE(attributes, '$.sql_text') = 'select ai showsql how many movies';
CONTENT
----------------------------------------------------------------------------------------------------
how many movies
ATTRIBUTES
----------------------------------------------------------------------------------------------------
{"response":"SELECT SUM(1) FROM \"ADB_USER\".\"MOVIES\"","feedback_type":"negative","sql_id":null,"sql_text":"select ai showsql how many movies","feedback_content":null}Beispiel: Positives Feedback geben
Das folgende Beispiel zeigt, dass Sie zustimmen und das generierte SQL (positives Feedback) bestätigen, indem Sie feedback_type als positive verwenden.
In diesem Beispiel ruft die Abfrage die sql_id aus der v$mapped_sql-Ansicht für den angegebenen Prompt ab. Weitere Informationen finden Sie unter V_MAPPED_SQL.
Sie fügen Ihr Feedback zum KI-Profil OCI_FEEDBACK1 hinzu, indem Sie die Prozedur DBMS_CLOUD_AI.FEEDBACK mit dem Parameter sql_id aufrufen. Anschließend rufen Sie die Spalten content und attributes aus der Tabelle <profile_name>_FEEDBACK_VECINDEX$VECTAB ab, die mit dieser spezifischen SQL-Abfrage verknüpft ist. Select AI erstellt diese Vektortabelle automatisch, wenn Sie die Feedbackfunktion zum ersten Mal verwenden. Weitere Informationen finden Sie unter Vector-Index für FEEDBACK.
SQL> select ai showsql how many distinct movie genres?;
RESPONSE
-----------------------------------------------------------------------------------------
SELECT COUNT(DISTINCT g."GENRE_NAME") AS "Number of Movie Genres" FROM "ADB_USER"."GENRES" g
SQL> SELECT sql_id FROM v$mapped_sql WHERE sql_text = 'select ai showsql how many distinct movie genres?';
SQL_ID
-------------
852w8u83gktc1
SQL> exec DBMS_CLOUD_AI.FEEDBACK(profile_name=>'OCI_FEEDBACK1', sql_id=> '852w8u83gktc1', feedback_type=>'positive', operation=>'add');
PL/SQL procedure successfully completed.
SQL> SELECT content, attributes FROM OCI_FEEDBACK1_FEEDBACK_VECINDEX$VECTAB WHERE JSON_VALUE(attributes, '$.sql_id') ='852w8u83gktc1';
CONTENT
----------------------------------------------------------------------------------------------------
how many distinct movie genres?
ATTRIBUTES
----------------------------------------------------------------------------------------------------
{"response":"SELECT COUNT(DISTINCT g.\"GENRE_NAME\") AS \"Number of Movie Genres\" FROM \"ADB_USER\".\"GENRES\" g","feedback_type":"positive","sql_id":"852w8u83gktc1","sql_text":"select ai showsql how many distinct movie genres?","feedback_content":null}Beispiel: Feedback für das generierte SQL hinzufügen oder löschen
Das folgende Beispiel zeigt, wie Sie Ihr Feedback für die generierte SQL hinzufügen oder löschen, indem Sie die Parameter der Prozedur
DBMS_CLOUD_AI.FEEDBACK angeben. In diesem Beispiel wird gezeigt, dass sql_id und sql_text zusammen mit anderen Parametern verwendet werden.
Hinweis
Bei der Auswahl von AI ist nur ein Feedbackeintrag für jede
Bei der Auswahl von AI ist nur ein Feedbackeintrag für jede
sql_id zulässig. Wenn Sie für dieselbe sql_id zusätzliches Feedback geben, ersetzt Select AI den vorherigen Eintrag durch den neuen.
Weitere Informationen zu den Parametern finden Sie unter FEEDBACK-Prozedur.
EXEC DBMS_CLOUD_AI.FEEDBACK(profile_name=>'OCI_FEEDBACK1',
sql_id=> '852w8u83gktc1',
feedback_type=>'positive',
operation=>'add');
EXEC DBMS_CLOUD_AI.FEEDBACK(profile_name=>'OCI_FEEDBACK1',
sql_text=> 'select ai showsql how many movies',
feedback_type=> 'negative',
response=>'SELECT SUM(1) FROM "ADB_USER"."MOVIES"',
feedback_content=>'Use SUM instead of COUNT');
EXEC DBMS_CLOUD_AI.FEEDBACK(profile_name=>'OCI_FEEDBACK1',
sql_id=> '852w8u83gktc1',
operation=>'delete');Beispiel: Feedbackaktion mit der letzten KI-SQL verwenden, um negatives Feedback zu geben
In diesem Beispiel wird gezeigt, wie Sie mit der Aktion feedback die generierte SQL verbessern, indem Sie die Änderungen in natürlicher Sprache vorschlagen.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name=>'OCI_FEEDBACK1',
attributes=>'{"provider": "oci",
"credential_name": "GENAI_CRED",
"oci_compartment_id": "ocid1.compartment.oc1..aaaa...",
"object_list": [{"owner": "ADB_USER", "name": "users"},
{"owner": "ADB_USER", "name": "movies"},
{"owner": "ADB_USER", "name": "genres"},
{"owner": "ADB_USER", "name": "watch_history"},
{"owner": "ADB_USER", "name": "movie_genres"},
{"owner": "ADB_USER", "name": "employees1"},
{"owner": "ADB_USER", "name": "employees2"}
]
}');
END;
/
EXEC DBMS_CLOUD_AI.SET_PROFILE('OCI_FEEDBACK1');
PL/SQL procedure successfully completed.
select ai showsql rank movie duration;
RESPONSE
-------------------------------------------------------------------------------
SELECT "DURATION" AS "Movie Duration" FROM "ADB_USER"."MOVIES" ORDER BY "DURATION"
select ai feedback use ascending sorting;
RESPONSE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Based on your feedback, the SQL query for prompt "rank movie duration" is successfully refined. The refined SQL query as following:
SELECT m."DURATION" AS "Movie Duration" FROM "ADB_USER."MOVIES" m ORDER BY m."DURATION" ASC
select ai showsql rank the movie duration;
RESPONSE
-----------------------------------------------------------------------------------------
SELECT m."DURATION" AS "Movie Duration" FROM "ADB_USER."MOVIES" m ORDER BY m."DURATION" ASCBeispiel: Feedbackaktion mit der letzten KI-SQL verwenden, um positives Feedback zu geben
Dieses Beispiel zeigt, wie Sie mit der Aktion feedback die generierte SQL in natürlicher Sprache akzeptieren.
--Positive feedback
select ai showsql which movies are comedy?;
RESPONSE
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT DISTINCT m."TITLE" AS "Movie Title" FROM "ADB_USER"."MOVIES" m INNER JOIN "ADB_USER"."MOVIE_GENRES" mg ON m."MOVIE_ID" = mg."MOVIE_ID" INNER JOIN "ADB_USER"."GENRES" g ON mg."GENRE_ID" = g."GENRE_ID" WHERE g."GENRE_NAME" = 'comedy'
select ai feedback this is correct;
RESPONSE
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Thank you for your positive feedback. The SQL query for prompt "which movies are comedy?" is correctly implemented and delivering the expected results. It will be referenced for future optimizations and improvements.
Select AI Feedback Action Referring SQL_IDBeispiel: Feedbackaktion mit SQL_ID verwenden, um Feedback zu geben
Dieses Beispiel zeigt, wie Sie SQL_ID mit der Aktion feedback verwenden, um Feedback für eine bestimmte generierte SQL-Abfrage zu geben. Sie können die SQL_ID abrufen, indem Sie die Tabelle v$MAPPED_SQL abfragen.
-- Query mentioned with SQL_ID
select ai showsql how many movies are in each genre;
RESPONSE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT g."GENRE_NAME" AS "Genre Name", COUNT(m."MOVIE_ID") AS "Number of Movies" FROM "ADB_USER"."MOVIES" m INNER JOIN "ADB_USER"."MOVIE_GENRES" mg ON m."MOVIE_ID" = mg."MOVIE_ID" INNER JOIN "ADB_USER"."GENRES" g ON mg."GENRE_ID" = g."GENRE_ID" GROUP BY g."GENRE_NAME"
select sql_id from v$cloud_ai_sql where sql_text = 'select ai showsql how many movies are in each genre';
SQL_ID
-------------
8azkwc0hr87ga
select ai feedback for query with sql_id = '8azkwc0hr87ga', rank in descending sorting;
RESPONSE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Based on your feedback, the SQL query for prompt "how many movies are in each genre" is successfully refined. The refined SQL query as following:
SELECT g."GENRE_NAME" AS "Genre Name", COUNT(m."MOVIE_ID") AS "Number of Movies"
FROM "ADB_USER"."MOVIES" m
INNER JOIN "ADB_USER"."MOVIE_GENRES" mg ON m."MOVIE_ID" = mg."MOVIE_ID"
INNER JOIN "ADB_USER"."GENRES" g ON mg."GENRE_ID" = g."GENRE_ID"
GROUP BY g."GENRE_NAME"
ORDER BY COUNT(m."MOVIE_ID") DESC
Beispiel: Feedbackaktion mit Abfragetext verwenden
Dieses Beispiel zeigt die Aktion feedback für eine bestimmte Select AI-Abfrage, indem der Select AI-Prompt in Anführungszeichen gefolgt von Ihrem Feedback eingefügt wird.
-Query mentioned with SQL_TEXT
select ai showsql how many watch history in total;
RESPONSE
----------------------------------------------------------------------------------
SELECT COUNT(w."WATCH_ID") AS "Total Watch History" FROM "ADB_USER"."WATCH_HISTORY" w
select ai feedback for query "select ai showsql how many watch history in total", name the column as total_watch;
RESPONSE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Based on your feedback, the SQL query for prompt "how many watch history in total" is successfully refined. The refined SQL query as following:
SELECT COUNT(w."WATCH_ID") AS "total_watch" FROM "ADB_USER"."WATCH_HISTORY" wÜbergeordnetes Thema: Beispiele für die Verwendung von Select AI
Beispiel: AI-Zusammenfassung auswählen
Diese Beispiele zeigen, wie die Aktion summarize und die Funktion DBMS_CLOUD_AI.SUMMARIZE verwendet werden. Passen Sie außerdem die Summengenerierung für Ihren Inhalt mit der Funktion an.
Bevor Sie beginnen
Prüfen Sie Voraussetzungen für ausgewählte KI ausführen.
Beispiel: Aktion "Zusammenfassen" in SQL-Befehlszeile verwenden
Im folgenden Beispiel wird
SUMMARIZE als Aktion "AI auswählen" verwendet. Verwenden Sie SELECT AI SUMMARIZE <TEXT> in der SQL-Befehlszeile, um eine Übersicht über den Eingabetext zu generieren.
SELECT AI SUMMARIZE
Like countless other people around the globe, I stream music, and like more
than six hundred million of them I mainly use Spotify. Streaming currently
accounts for about eighty per cent of the American recording industry’s
revenue, and in recent years Spotify’s health is often consulted as a measure
for the health of the music business over all. Last spring, the International
Federation of the Phonographic Industry reported global revenues of $28.6
billion, making for the ninth straight year of growth. All of this was
unimaginable in the two-thousands, when the major record labels appeared
poorly equipped to deal with piracy and the so-called death of physical media.
On the consumer side, the story looks even rosier. Adjusted for inflation, a
... (skipped 1000 rows in the middle)
Pelly writes of some artists, in search of viral fame, who surreptitiously use
social media to effectively beta test melodies and motifs, basically putting
together songs via crowdsourcing. Artists have always fretted about the
pressure to conform, but the data-driven, music-as-content era feels
different. “You are a Spotify employee at that point,” Daniel Lopatin, who
makes abstract electronic music as Oneohtrix Point Never, told Pelly. “If your
art practice is so ingrained in the brutal reality that Spotify has outlined
for all of us, then what is the music that you’re not making? What does the
music you’re not making sound like?” Listeners might wonder something similar.
What does the music we’re not hearing sound like?;
RESPONSE
------------------------------------------------------------------------------
The music streaming industry, led by Spotify, has revolutionized the way
people consume music, with streaming accounting for 80% of the American
recording industry's revenue. However, this shift has also complicated the
lives of artists, who struggle to survive in a hyper-abundant present where
music is often valued for its convenience rather than its artistic merit.
Spotify's algorithms prioritize popularity and profitability over artistic
diversity, leading to a homogenization of music and a devaluation of the
labor that goes into creating it. Meanwhile, the company's executives reap
enormous profits, with CEO Daniel Ek's net worth rivaling that of the
wealthiest musicians. As music critic Liz Pelly argues, the streaming economy
raises important questions about autonomy, creativity, and the value of art
in a world where everything is readily available and easily accessible.Tipp:
In SQL*Plus wird ein einfaches Anführungszeichen (
') als Zeichenfolgenbegrenzungszeichen behandelt. Wenn der Text einfache Anführungszeichen enthält, entziehen Sie das Anführungszeichen, indem Sie es verdoppeln (' bis ''), oder schließen Sie den Text mit dem Anführungszeichenmechanismus q'[]' ein. Wenn der Text leere Anführungszeichen ("") enthält, schließen Sie den Text mit dem q'[]'-Verfahren ein. Beispiel:SELECT AI SUMMARIZE q'[this's a text]';Beispiel: Mit der Prozedur DBMS_CLOUD_AI.SUMMARIZE eine Übersicht generieren
Diese Beispiele veranschaulichen das Generieren einer Übersicht mit verschiedenen Parametern aus der Prozedur DBMS_CLOUD_AI.SUMMARIZE.
Sie können eine Zusammenfassung aus mehr als 3000 Wörtern generieren, die in einem OCI-Objektspeicher gespeichert sind, indem Sie den Objektspeicherlink als Parameter
location_uri und Ihre Cloud-Accountzugangsdaten mit DBMS_CLOUD_AI.SUMMARIZE als credential_name angeben.SELECT DBMS_CLOUD_AI.SUMMARIZE(
location_uri => 'https://objectstorage.ca-toronto-1.oraclecloud.com/n/' ||
'namespace-string/b/bucketname/o/data_folder/' ||
'summary/test_4000_words.txt',
credential_name => 'STORE_CRED',
profile_name => 'GENAI')
from DUAL;Eine weitere Möglichkeit, eine Zusammenfassung aus einem Text zu generieren, der in einem OCI-Objektspeicher gespeichert ist, besteht darin, die Prozedur
DBMS_CLOUD.GET_OBJECT mit dem Parameter content aufzurufen.
SELECT DBMS_CLOUD_AI.SUMMARIZE(
content => TO_CLOB(
DBMS_CLOUD.GET_OBJECT(
credential_name => 'STORE_CRED',
location_uri => 'https://objectstorage.ca-toronto-1.oraclecloud.com/n/' ||
'namespace-string/b/bucketname/o/data_folder/' ||
'summary/test_4000_words.txt')),
profile_name => 'GENAI'>)
from DUAL;Beispiel: Zusammenfassung durch Angabe von Benutzereingabeaufforderungen, Mindestwörtern und maximalen Wörtern generieren
Das folgende Beispiel zeigt, wie Sie eine Zusammenfassung eines mehr als 3000 Wörter umfassenden Textes erstellen, indem Sie die folgenden Parameter angeben:
user_prompt: Die Übersicht muss mit "Zusammenfassung des Artikels" beginnen: "min_words: 50max_words: 100
SELECT DBMS_CLOUD_AI.SUMMARIZE(
content => TO_CLOB(
DBMS_CLOUD.GET_OBJECT(
credential_name =>'STORE_CRED',
location_uri =>'https://objectstorage.ca-toronto-1.oraclecloud.com/n/' ||
'namespace-string/b/bucketname/o/data_folder/' ||
'summary/test_4000_words.txt')),
profile_name => 'GENAI',
user_prompt => 'The summary should start with ''The summary of ' ||
'the article is: ''',
params => '{"min_words":50,"max_words":100}')
As response FROM dual;
RESPONSE
--------------------------------------------------------------------------------
The summary of the article is: The music streaming industry, led by Spotify, has
revolutionized the way people consume music, with streaming accounting for abou
t eighty per cent of the American recording industry's revenue. However, this sh
ift has also raised concerns about the impact on artists, with many struggling t
o make a living due to low royalty rates and the dominance of playlists. The art
icle explores the history of music streaming, from the early days of Napster to
the current landscape, and how it has changed the way people listen to music. It
also delves into the issues of autonomy and creativity in the music industry, w
ith some artists feeling pressured to conform to certain styles or formulas to s
ucceed on platforms like Spotify. The article cites examples of artists who have
spoken out against the streaming economy, including Taylor Swift and Neil Young
, and discusses the rise of alternative platforms like Bandcamp and Nina. Ultima
tely, the article suggests that the streaming economy has created a perverse vis
ion for art, where music is valued for its ability to be ignored rather than app
reciated, and that this has significant implications for the future of music and
creativity. With the rise of AI-generated music and the increasing importance o
f data-driven decision making in the music industry, the article asks what the m
usic we're not hearing sounds like, and what the consequences of this shift will
be for artists and listeners alike. The article concludes by highlighting the n
eed for a more nuanced understanding of the music industry and the impact of str
eaming on artists and listeners, and for alternative models that prioritize crea
tivity and autonomy over profit and convenience.Beispiel: Zusammenfassung durch Angabe von Benutzereingabeaufforderungen, maximalen Wörtern und Sammelart generieren
Das folgende Beispiel veranschaulicht das Generieren einer Zusammenfassung eines Textes mit mehr als 12000 Wörtern, indem die folgenden Parameter angegeben werden:
user_prompt: Die Übersicht muss mit "Zusammenfassung des Artikels" beginnen: "max_words: 100summary_style: list
SELECT DBMS_CLOUD_AI.SUMMARIZE(
location_uri => 'https://objectstorage.ca-toronto-1.' ||
'oraclecloud.com/n/namespace-string/b/' ||
'/bucketname/o/data_folder/' ||
'summary/dreams.txt',
credential_name => 'STORE_CRED',
profile_name => 'GENAI',
user_prompt => 'The summary should start with ''The summary of ' ||
'the article is: ''',
params => '{"max_words":100, "summary_style":"list"}')
As response FROM dual;
RESPONSE
--------------------------------------------------------------------------------
The summary of the article is:
- The book "Dreams" by Henri Bergson explores the concept of dreams and their si
gnificance in understanding human consciousness.
- Bergson argues that dreams are not just random thoughts, but rather a way for
our unconscious mind to process and consolidate memories.
- He suggests that dreams are a result of the relaxation of our mental faculties
, which allows our unconscious mind to freely associate and create new connectio
ns between memories.
- The book also discusses the role of sensations, such as visual and auditory im
pressions, in shaping our dreams.
- Bergson's theory of dreams is compared to other theories, including those of F
reud and Jung, and is seen as a unique and insightful contribution to the field
of psychology.
- The book concludes by highlighting the importance of studying dreams in order
to gain a deeper understanding of human consciousness and the workings of the mi
nd.Beispiel: Übersicht über ein Buch generieren
In diesem Beispiel wird die Übergabe einer 35.66 MiB-Datei als Eingabe zum Generieren einer Übersicht veranschaulicht. Die Funktion DBMS_CLOUD_AI.SUMMARIZE verwendet eine iterative Verfeinerungsmethode zur Verarbeitung der Chunks. Weitere Informationen finden Sie unter Iterative Verfeinerung.
SELECT DBMS_CLOUD_AI.SUMMARIZE(
location_uri => 'https://objectstorage.ca-toronto-1.oraclecloud.com/n/namespace-string/b/' ||
'bucketname/o/data_folder/summary/Descartes_An_Intellectual_Biography.pdf',
credential_name => 'STORE_CRED',
profile_name => 'GENAI',
params => '{"chunk_processing_method":"iterative_refinement"}')
AS response FROM dual;
RESPONSE
--------------------------------------------------------------------------------
Stephen Gaukroger's intellectual biography of Rene Descartes provides a detailed
examination of the philosopher's crucial role in shaping modern thought, placin
g him within the cultural, religious, and scientific context of the early sevent
eenth century. It traces Descartes' intellectual journey from his education at L
a Fleche, where he rejected Aristotelian logic, to his influential interactions
with figures like Isaac Beeckman, which shaped his mechanistic worldview evident
in works like his hydrostatics manuscript and *Compendium Musicae*. The biograp
hy underscores Descartes' dual commitment to philosophy and science, highlightin
g his social status among the gentry, mathematical innovations such as solving t
he Pappus problem through algebraic geometry, and his epistemology based on clea
r and distinct ideas. It explores his mechanistic explanations of bodily functio
ns, challenging traditional soul-body distinctions, and his extensive natural ph
ilosophy in texts like *Le Monde* and *L'Homme*. Gaukroger also delves into Desc
artes' cosmological theories, including the vortex theory and laws of motion lin
ked to divine immutability, as well as his nuanced perspectives on animal cognit
ion versus human consciousness. Central to the narrative is Descartes' use of hy
perbolic doubt to combat skepticism and establish metaphysical foundations throu
gh the *cogito*, alongside his classification of ideas and theological proofs of
God's existence. The complex relationship between his natural philosophy and me
taphysics, especially in defining motion as a mode, and his innovative approach
to the passions in *Passions of the Soul*, rejecting Stoic views for a mind-body
union, are key themes. This portrayal captures Descartes' struggle with traditi
onal paradigms during a transformative era, emphasizing his enduring impact on p
hilosophy and science.Übergeordnetes Thema: Beispiele für die Verwendung von Select AI
Beispiel: AI Translate auswählen
Diese Beispiele zeigen, wie Sie die Funktion translate verwenden können.
Bevor Sie beginnen
Beurteilung:
Beispiel: Translate-Aktion in der SQL-Befehlszeile verwenden
Das folgende Beispiel zeigt, wie Sie die Aktion translate in der SQL-Befehlszeile verwenden.
Hinweis
Ihr KI-Profil muss die Zielsprache angeben. Dieses Feature wird nur für den Provider OCI unterstützt.
Ihr KI-Profil muss die Zielsprache angeben. Dieses Feature wird nur für den Provider OCI unterstützt.
--Create an AI profile with language parameters
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GENAI_NEW',
attributes =>'{"provider": "oci",
"credential_name": "GENAI_CRED",
"target_language": "french",
"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.
SQL> exec DBMS_CLOUD_AI.SET_PROFILE('GENAI_NEW');
PL/SQL procedure successfully completed.
SQL> select ai translate I need to translate this;
RESPONSE
---------------------
Je dois traduire ceciBeispiel: Funktion "In DBMS_CLOUD_AI.GENERATE übersetzen" verwenden
Die folgenden Beispiele zeigen die Verwendung von translate als Select AI-Aktion in der Funktion DBMS_CLOUD_AI.GENERATE. Weitere Informationen finden Sie unter GENERATE-Funktion.
Hinweis
Das AI-Profil kann die Angabe des Zielsprachenparameters überspringen, wenn er als Attribut in
Das AI-Profil kann die Angabe des Zielsprachenparameters überspringen, wenn er als Attribut in
DBMS_CLOUD_AI.GENERATE übergeben wird.
Die Aktion translate wird in der Funktion DBMS_CLOUD_AI.GENERATE zusammen mit target_language und source_language bereitgestellt. In diesem Beispiel wird generative KI-Übersetzung verwendet. Der Eingabetext this is a document in Englisch (source_language: "en") wird ins Französische (target_language: "fr") übersetzt.
SELECT DBMS_CLOUD_AI.GENERATE('select ai translate text to be translated')
FROM dual;
DECLARE
l_attributes clob := '{"target_language": "fr", "source_language": "en"}';
output clob;
BEGIN
output := DBMS_CLOUD_AI.GENERATE(
prompt => 'this is a document',
profile_name => 'oci_translate',
action => 'translate',
attributes => l_attributes
);
Beispiel: Funktion DBMS_CLOUD_AI.TRANSLATE für die Übersetzung verwenden
In diesem Beispiel wird die Funktion DBMS_CLOUD_AI.TRANSLATE aufgerufen, um die generative KI-Übersetzung zu verwenden und den Eingabetext mit dem angegebenen KI-Profil aus dem Englischen (source_language) in Französisch (target_language) zu konvertieren.
Weitere Informationen finden Sie unter TRANSLATE-Funktion.
BEGIN
output_text := DBMS_CLOUD_AI.TRANSLATE(
profile_name => 'GENAI_NEW'
text => 'text to be translated',
source_language => 'English',
target_language => 'French');
END;
/Beispiel: Unterstützte Sprachen für einen Provider anzeigen
Fragen Sie die Ansicht AI_TRANSLATION_LANGUAGES ab, um eine Liste der Sprachen anzuzeigen, die Ihr KI-Provider unterstützt. Weitere Informationen finden Sie in der Ansicht AI_TRANSLATION_LANGUAGES.
SELECT* FROM AI_TRANSLATION_LANGUAGES;
LANGUAGE_NAME LANGUAGE_CODE PROVIDER
-------------------- -------------------- ---------------
ARABIC ar OCI
CROATIAN hr OCI
CZECH cs OCI
DANISH da OCI
GERMAN de OCI
GREEK el OCI
ENGLISH en OCI
SPANISH es OCI
FINNISH fi OCI
FRENCH fr OCI
FRENCH CANADA fr-CA OCI
HEBREW he OCI
HUNGARIAN hu OCI
ITALIAN it OCIBeispiel: Verwenden eines anderen Profils für die SQL-Generierung in natürlicher Sprache
In diesem Beispiel wird gezeigt, wie Sie ein anderes Profil namens
openai mit OpenAI als Provider einrichten. Sie können dieses Profil verwenden, um SQL unter anderen Select AI-Features und -Aktionen aus Ihren Prompts zu generieren.
Hinweis
Das Profil in diesem Beispiel unterstützt die Funktion
Das Profil in diesem Beispiel unterstützt die Funktion
translate nicht.
-- OpenAI profile used for sql generation
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name = > 'openai',
attributes => '{"provider": "openai",
"credential_name": "OPENAI_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"}]
}');
END;
/
EXEC DBMS_CLOUD_AI.SET_PROFILE('openai');
SELECT AI SHOWSQL Give me the total number of customers;
RESPONSE
------------
SELECT COUNT("Customer_ID") AS "Total_Customers" FROM "Customers"Übergeordnetes Thema: Beispiele für die Verwendung von Select AI
Beispiel: Tabellenzugriff im AI-Profil einschränken
Dieses Beispiel zeigt, wie Sie den Tabellenzugriff einschränken und das LLM anweisen, nur die Tabellen zu verwenden, die in der object_list des AI-Profils angegeben sind.
Setzen Sie enforce_object_list auf true, um den Tabellenzugriff auf das LLM einzuschränken.
Erstellen und konfigurieren Sie als Datenbankbenutzer Ihr KI-Profil. Prüfen Sie Voraussetzungen für ausgewählte KI ausführen, um Ihr AI-Profil zu konfigurieren.
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
--Wenn Sie enforce_object_list auf false setzen, wird das LLM angewiesen, andere Tabellen und Ansichten basierend auf seinen Vorkenntnissen zu verwenden.
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
Übergeordnetes Thema: Beispiele für die Verwendung von Select AI
Beispiel: Groß-/Kleinschreibung für Spalten angeben
In diesem Beispiel wird gezeigt, wie Sie die Groß-/Kleinschreibung für Spalten im AI-Profil festlegen können.
Setzen Sie case_sensitive_values auf false, um Abfragen abzurufen, bei denen die Groß-/Kleinschreibung nicht beachtet wird.
Erstellen und konfigurieren Sie als Datenbankbenutzer Ihr KI-Profil. Prüfen Sie Voraussetzungen für ausgewählte KI ausführen, um Ihr AI-Profil zu konfigurieren.
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')Sie können Abfragen mit doppelten Anführungszeichen angeben, obwohl case_sensitive_values auf false gesetzt ist.
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'
Übergeordnetes Thema: Beispiele für die Verwendung von Select AI