Select AIの使用例
OracleのSelect AIとサポートされている様々なAIプロバイダーを統合して、自然言語プロンプトからSQLを生成、実行、説明したり、LLMとチャットしたりする方法をご覧ください。
- 例: Select AIアクション
これらの例は、一般的なSelect AIアクションを示しています。 - 例: OCI生成AIを使用したSelect AI
これらの例は、OCI APIキーまたはリソース・プリンシパルを使用してOCI生成AIにアクセスし、AIプロファイルを作成し、OCI生成AI LLMを使用して自然言語プロンプトまたはチャットからSQLを生成、実行および説明する方法を示しています。 - 例: OpenAIを使用したSelect AI
この例は、OpenAIを使用して自然言語プロンプトからSQL文を生成する方法を示しています。 - 例: Cohereを使用したSelect AI
この例は、Cohereを使用して自然言語プロンプトからSQL文を生成する方法を示しています。 - 例: Azure OpenAIサービスを使用したSelect AI
次の例は、APIキーを使用してAzure OpenAIサービスへのアクセスを有効にする方法、またはAzure OpenAIサービス・プリンシパルを使用してAIプロファイルを作成し、自然言語プロンプトからSQLを生成する方法を示しています。 - 例: Googleを使用したSelect AI
この例は、Googleを使用して、Google Gemini LLMを使用して自然言語プロンプトまたはチャットからSQLを生成、実行および説明する方法を示しています。 - 例: Anthropicを使用したSelect AI
この例は、Anthropicを使用して、Anthropic Claude LLMを使用して自然言語プロンプトまたはチャットからSQLを生成、実行および説明する方法を示しています。 - 例: Hugging Faceを使用したSelect AI
この例は、Hugging Faceを使用して、Hugging Face LLMを使用して自然言語プロンプトまたはチャットからSQLを生成、実行および説明する方法を示しています。 - 例: AWSを使用したSelect AI
この例は、AWSを使用して、AWSで使用可能なモデルを使用して、自然言語プロンプトまたはチャットからSQLを生成、実行および説明する方法を示しています。 - 例: OpenAI互換プロバイダを使用したSelect AI
この例は、OpenAI互換プロバイダを使用して、OpenAI互換プロバイダで使用可能なモデルを使用して、自然言語プロンプトまたはチャットからSQLを生成、実行および説明する方法を示しています。 - 例: Select AIでの会話の有効化
これらの例は、Select AIでの会話の有効化を示しています。 - 例: RAGでのSelect AIの設定および使用
この例では、Oracle Autonomous Databaseを使用して、資格証明の設定、ネットワーク・アクセスの構成、およびOCI生成AIベクトル・ストア・クラウド・サービスをOpenAIと統合するためのベクトル索引の作成について説明します。 - 例: インデータベース・トランスフォーマ・モデルを使用したSelect AI
この例では、Oracleオブジェクト・ストレージに格納されている事前トレーニング済トランスフォーマ・モデルをOracle Database 23aiインスタンスにインポートし、Select AIプロファイルのインポート済インデータベース・モデルを使用して、ドキュメント・チャンクおよびユーザー・プロンプトのベクトル埋込みを生成する方法を示します。 - 例: SQL問合せ生成の改善
これらの例は、データベース表および列のコメント、注釈、外部キーおよび参照整合性制約によって、自然言語プロンプトからのSQL問合せの生成を改善する方法を示しています。 - 例: 合成データの生成
この例では、実データの特性と分布を模倣した合成データの生成方法を説明します。 - 例: データ・アクセスの有効化または無効化
この例は、管理者がデータ・アクセスを制御し、Select AIがLLMに実際のスキーマ表を送信できないようにする方法を示しています。 - 例: Select AIフィードバック
これらの例では、DBMS_CLOUD_AI.FEEDBACKプロシージャの使用方法と、feedbackアクションに関連する様々なシナリオを使用してフィードバックを提供し、後続のSQL問合せの生成を改善する方法を示します。 - 例: Select AIサマリー
これらの例は、summarizeアクションおよびDBMS_CLOUD_AI.SUMMARIZE関数の使用方法を示しています。また、この関数を使用してコンテンツのサマリー生成をカスタマイズします。 - 例: AIプロファイルでの表アクセスの制限
この例は、表アクセスを制限し、AIプロファイルのobject_listで指定された表のみを使用するようにLLMに指示する方法を示しています。 - 例: 列の大/小文字の区別を指定
この例は、AIプロファイルの列に大/小文字の区別を設定する方法を示しています。
例: Select AI Actions
これらの例は、一般的な Select AIアクションを示しています。
次の例では、SELECT AIで実行できるrunsql (デフォルト)、showsql、narrate、chat、explainsql、feedback、summarizeなどのアクションを示します。この例では、shスキーマとAIプロバイダ、およびDBMS_CLOUD_AI.CREATE_PROFILE関数で指定されたプロファイル属性を使用します。現在のセッションでDBMS_CLOUD_AI.SET_PROFILEプロシージャを使用して、AIプロファイルの設定後にSelect AIアクションを使用します。
テキストのサマリーを生成するには、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.親トピック: Select AIの使用例
例: OCI Generative AIを使用したSelect AI
これらの例は、OCI APIキーまたはリソース・プリンシパルを使用してOCI生成AIにアクセスし、AIプロファイルを作成し、OCI生成AI LLMを使用して自然言語プロンプトまたはチャットからSQLを生成、実行および説明する方法を示しています。
ノート
model_nameパラメータを指定しない場合、OCI生成AIでは、「AIプロバイダおよびLLMの選択」の表に従ってデフォルト・モデルが使用されます。パラメータについてさらに学習するには、プロファイル属性を参照してください。
-- Create Credential with OCI API key
--
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'GENAI_CRED',
user_ocid => 'ocid1.user.oc1..aaaa...',
tenancy_ocid => 'ocid1.tenancy.oc1..aaaa...',
private_key => '<your_api_key>',
fingerprint => '<your_fingerprint>'
);
END;
/
--
-- Create AI profile
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GENAI',
attributes =>'{"provider": "oci",
"credential_name": "GENAI_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}]
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"GENAI"
--
-- Use AI
--
SQL> select ai how many customers exist;
Number of Customers
-------------------
55500
SQL> select ai how many customers in San Francisco are married;
COUNT(DISTINCTC."CUST_ID")
--------------------------
28
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(DISTINCT c."CUST_ID")
FROM "SH"."CUSTOMERS" c
JOIN "SH"."COUNTRIES" co ON c."COUNTRY_ID" = co."COUNTRY_ID"
WHERE c."CUST_CITY" = 'San Francisco' AND c."CUST_MARITAL_STATUS" = 'married'
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
Here is the Oracle SQL query to find the number of customers in San Francisco who are married:
```
SELECT COUNT(*)
FROM "SH"."CUSTOMERS" c
WHERE c."CUST_CITY" = 'San Francisco'
AND c."CUST_MARITAL_STATUS" = 'Married';
```
Explanation:
* We use the `COUNT(*)` aggregate function to count the number of rows that match the conditions.
* We specify the table alias `c` for the `"SH"."CUSTOMERS"` table to make the query more readable.
* We use the `WHERE` clause to filter the rows based on two conditions:
+ `c."CUST_CITY" = 'San Francisco'`: We filter the rows where the customer's city is San Francisco.
+ `c."CUST_MARITAL_STATUS" = 'Married'`: We filter the rows where the customer's marital status is Married.
* The double quotes around the table and column names are used to make the query case-sensitive, as required in Oracle SQL.
Note: The column names and table names are assumed to be case-sensitive, so we enclose them in double quotes. If the column names and table names are not case-sensitive, you can remove the double quotes.
Collapse
This snippet was truncated for display; see it in full
SQL> select ai narrate how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------
There are 28 customers in San Francisco who are married.
SQL> select ai chat what is Autonomous Database;
RESPONSE
An Autonomous Database is a type of database that uses artificial intelligence (AI) and machine learning (ML) to automate many of the administrative and maintenance tasks typically performed by a database administrator (DBA). This allows the database to manage itself, without human intervention, to a large extent.
Autonomous databases are designed to be self-driving, self-securing, and self-repairing, which means they can:
1. **Automate administrative tasks**: Such as provisioning, patching, upgrading, and tuning, which frees up DBAs to focus on higher-level tasks.
2. **Optimize performance**: By automatically adjusting parameters, indexing, and caching to ensure optimal performance and efficiency.
3. **Detect and respond to security threats**: By using AI-powered security tools to identify and respond to potential security threats in real-time.
4. **Heal itself**: By automatically detecting and repairing errors, corruption, or other issues that may arise.
5. **Scale up or down**: To match changing workload demands, without the need for manual intervention.
The benefits of Autonomous Databases include:
1. **Increased efficiency**: By automating routine tasks, DBAs can focus on more strategic activities.
2. **Improved performance**: Autonomous databases can optimize performance in real-time, leading to faster query response times and better overall system performance.
3. **Enhanced security**: AI-powered security tools can detect and respond to threats more quickly and effectively than human administrators.
4. **Reduced costs**: By minimizing the need for manual intervention, Autonomous Databases can help reduce labor costs and improve resource utilization.
5. **Improved reliability**: Autonomous Databases can detect and repair errors more quickly, reducing downtime and improving overall system reliability.
Oracle Autonomous Database is a popular example of an Autonomous Database, which was introduced in 2018. Other vendors, such as Amazon, Microsoft, and Google, also offer Autonomous Database services as part of their cloud offerings.
In summary, Autonomous Databases are designed to be self-managing, self-optimizing, and self-healing, which can lead to improved performance, security, and efficiency, while reducing costs and administrative burdens.
--
--Clear the profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('GENAI');
PL/SQL procedure successfully completed.
例: OCI Generative AI Resource Principalを使用したSelect AI
OCI生成AIでリソース・プリンシパルを使用するには、Oracle Cloud Infrastructureテナンシ管理者が生成AIリソースへのアクセス権を動的グループに付与する必要があります。動的グループへのアクセスを提供するには、Autonomous Databaseでリソース・プリンシパルを使用するための前提条件の実行を参照してください。
すべての生成AIリソースへのアクセスを取得するために必要なポリシーを設定します。生成AIポリシーの詳細は、生成AIへのアクセスの取得を参照してください。
-
テナンシ全体のすべての生成AIリソースにアクセスするには、次のポリシーを使用します:
allow group <your-group-name> to manage generative-ai-family in tenancy -
コンパートメント内のすべての生成AIリソースにアクセスするには、次のポリシーを使用します:
allow group <your-group-name> to manage generative-ai-family in compartment <your-compartment-name>
管理者として接続し、OCIリソース・プリンシパルを有効にします。パラメータを構成するには、ENABLE_PRINCIPAL_AUTHプロシージャを参照してください。
ノート
model_nameパラメータを指定しない場合、OCI生成AIでは、「AIプロバイダおよびLLMの選択」の表に従ってデフォルト・モデルが使用されます。パラメータについてさらに学習するには、プロファイル属性を参照してください。
-- Connect as Administrator user and enable OCI resource principal.
BEGIN
DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(provider => 'OCI');
END;
/
--
-- Create AI profile
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GENAI',
attributes =>'{"provider": "oci",
"credential_name": "OCI$RESOURCE_PRINCIPAL",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}]
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"GENAI"
--
-- Use AI
--
SQL> select ai how many customers exist;
Number of Customers
-------------------
55500
SQL> select ai how many customers in San Francisco are married;
COUNT(DISTINCTC."CUST_ID")
--------------------------
28
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(DISTINCT c."CUST_ID")
FROM "SH"."CUSTOMERS" c
JOIN "SH"."COUNTRIES" co ON c."COUNTRY_ID" = co."COUNTRY_ID"
WHERE c."CUST_CITY" = 'San Francisco' AND c."CUST_MARITAL_STATUS" = 'married'
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
Here is the Oracle SQL query to find the number of customers in San Francisco who are married:
```
SELECT COUNT(*)
FROM "SH"."CUSTOMERS" c
WHERE c."CUST_CITY" = 'San Francisco'
AND c."CUST_MARITAL_STATUS" = 'Married';
```
Explanation:
* We use the `COUNT(*)` aggregate function to count the number of rows that match the conditions.
* We specify the table alias `c` for the `"SH"."CUSTOMERS"` table to make the query more readable.
* We use the `WHERE` clause to filter the rows based on two conditions:
+ `c."CUST_CITY" = 'San Francisco'`: We filter the rows where the customer's city is San Francisco.
+ `c."CUST_MARITAL_STATUS" = 'Married'`: We filter the rows where the customer's marital status is Married.
* The double quotes around the table and column names are used to make the query case-sensitive, as required in Oracle SQL.
Note: The column names and table names are assumed to be case-sensitive, so we enclose them in double quotes. If the column names and table names are not case-sensitive, you can remove the double quotes.
Collapse
This snippet was truncated for display; see it in full
SQL> select ai narrate how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------
There are 28 customers in San Francisco who are married.
SQL> select ai chat what is Autonomous Database;
RESPONSE
An Autonomous Database is a type of database that uses artificial intelligence (AI) and machine learning (ML) to automate many of the administrative and maintenance tasks typically performed by a database administrator (DBA). This allows the database to manage itself, without human intervention, to a large extent.
Autonomous databases are designed to be self-driving, self-securing, and self-repairing, which means they can:
1. **Automate administrative tasks**: Such as provisioning, patching, upgrading, and tuning, which frees up DBAs to focus on higher-level tasks.
2. **Optimize performance**: By automatically adjusting parameters, indexing, and caching to ensure optimal performance and efficiency.
3. **Detect and respond to security threats**: By using AI-powered security tools to identify and respond to potential security threats in real-time.
4. **Heal itself**: By automatically detecting and repairing errors, corruption, or other issues that may arise.
5. **Scale up or down**: To match changing workload demands, without the need for manual intervention.
The benefits of Autonomous Databases include:
1. **Increased efficiency**: By automating routine tasks, DBAs can focus on more strategic activities.
2. **Improved performance**: Autonomous databases can optimize performance in real-time, leading to faster query response times and better overall system performance.
3. **Enhanced security**: AI-powered security tools can detect and respond to threats more quickly and effectively than human administrators.
4. **Reduced costs**: By minimizing the need for manual intervention, Autonomous Databases can help reduce labor costs and improve resource utilization.
5. **Improved reliability**: Autonomous Databases can detect and repair errors more quickly, reducing downtime and improving overall system reliability.
Oracle Autonomous Database is a popular example of an Autonomous Database, which was introduced in 2018. Other vendors, such as Amazon, Microsoft, and Google, also offer Autonomous Database services as part of their cloud offerings.
In summary, Autonomous Databases are designed to be self-managing, self-optimizing, and self-healing, which can lead to improved performance, security, and efficiency, while reducing costs and administrative burdens.
--
--Clear profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('GENAI');
PL/SQL procedure successfully completed.例: OCI生成AIプロファイルの別のリージョンの指定
この例では、プロファイルでOCI生成AIでサポートされているリージョンを指定する方法を示します。生成AIを使用するリージョンを参照してください。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'GENAI',
attributes =>'{"provider": "oci",
"object_list": [
{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}
]
"region":"eu-frankfurt-1",
"model": "meta.llama-3.3-70b-instruct",
"credential_name": "GENAI_CRED",
"oci_compartment_id": "ocid1.compartment.oc1..."}');
END;
/例: Grokモデルを使用したOCI生成AIによるSelect AI
この例では、OCI生成AIサポートでxAIのGrokモデルを使用する方法を示します。「Select AIの前提条件の実行」を確認します。
--Create your AI Profile
BEGIN
DBMS_CLOUD_AI.create_profile(
profile_name =>'grok',
attributes =>'{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [ {"owner": "SH"}],
"oci_compartment_id": "ocid1.compartment.oc1..aaaaa...",
"model":"xai.grok-3"
}');
END;
/
PL/SQL procedure successfully completed.
--Set Profile
exec dbms_cloud_ai.set_profile('grok');
PL/SQL procedure successfully completed.
--Use Select AI
select ai how many customers exist;
TOTAL_CUSTOMERS
---------------
55500
select ai how many customers in San Francisco are married;
TOTAL_MARRIED_CUSTOMERS
-----------------------
46
select ai showsql how many customers in San Francisco are married;
RESPONSE
----------------------------------------------------------
SELECT COUNT(*) AS total_married_customers
FROM "SH"."CUSTOMERS" c
WHERE UPPER(c."CUST_CITY") = UPPER('San Francisco')
AND UPPER(c."CUST_MARITAL_STATUS") = UPPER('married')
select ai explainsql how many customers in San Francisco are married;
RESPONSE
-------------------
### Oracle SQL Query
```sql
SELECT COUNT(*) AS "Total_Married_Customers"
FROM "SH"."CUSTOMERS" "cust"
WHERE UPPER("cust"."CUST_CITY") = UPPER('San Francisco')
AND UPPER("cust"."CUST_MARITAL_STATUS") = UPPER('married')
```
### Detailed Explanation
1. **Table and Schema Naming**:
- The table `"CUSTOMERS"` is referenced with its schema name `"SH"` as `"SH"."CUSTOMERS"`. This ensures that the query explicitly points to the correct schema and table, avoiding ambiguity.
- A table alias `"cust"` is used for the `"CUSTOMERS"` table to make the query more readable and concise when referencing columns.
2. **Column Naming**:
- The result of the `COUNT(*)` function is aliased as `"Total_Married_Customers"` for clarity and readability. This descriptive name indicates exactly what the count represents.
- All column names (e.g., `"CUST_CITY"`, `"CUST_MARITAL_STATUS"`) are enclosed in double quotes to maintain case sensitivity as per Oracle's naming conventions when explicitly defined.
3. **String Comparison in WHERE Clause**:
- The strings 'San Francisco' and 'married' in the question are not enclosed in double quotes. As per the provided rules, case-insensitive comparison is required.
- Therefore, the `UPPER()` function is applied to both the column values (`"cust"."CUST_CITY"` and `"cust"."CUST_MARITAL_STATUS"`) and the literal strings ('San Francisco' and 'married') to ensure the comparison ignores case differences.
- This means that records with values like 'SAN FRANCISCO', 'san francisco', or any other case variation of 'San Francisco' will match, and similarly for 'married'.
4. **Purpose of the Query**:
- The query counts the total number of customers who are located in 'San Francisco' and have a marital status of 'married'.
- The `COUNT(*)` function is used to return the total number of rows that satisfy the conditions specified in the `WHERE` clause.
5. **Readability**:
- The query uses consistent formatting with indentation for the `WHERE` clause conditions to improve readability.
- The use of a meaningful alias (`"cust"`) and a descriptive result column name (`"Total_Married_Customers"`) makes the query easier to understand at a glance.
This query will return a single number representing the count of married customers in San Francisco, handling case variations in the data appropriately.
select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
--------------------------------------------------------------------------------
I'm showing you the top 3 customers from San Francisco, based on their unique id
entification numbers, sorted from lowest to highest. Here's who they are:
- Lyndon Baltzer
- Mike Crocker
- Milburn Klemm
select ai chat what is Autonomous Database;
RESPONSE
-------------------
An **Autonomous Database** is a cloud-based database solution that uses artificial intelligence (AI) and machine learning (ML) to automate many of the routine tasks associated with managing and maintaining a database. It is designed to operate with minimal human intervention, allowing organizations to focus on data-driven insights and application development rather than database administration. The concept is often associated with Oracle's Autonomous Database, which was one of the first widely recognized implementations, but other cloud providers like AWS, Microsoft Azure, and Google Cloud also offer similar self-managing database services.
### Key Features of an Autonomous Database:
1. **Self-Driving:** Automates tasks such as provisioning, patching, tuning, and upgrading without manual input. It uses AI to optimize performance and adapt to workload changes in real-time.
2. **Self-Securing:** Automatically applies security updates, detects vulnerabilities, and protects against threats using built-in mechanisms like encryption and threat detection, reducing the risk of human error.
3. **Self-Repairing:** Identifies and resolves issues such as system failures or performance bottlenecks autonomously, ensuring high availability and minimizing downtime.
4. **Scalability:** Dynamically scales resources (compute and storage) up or down based on demand, optimizing cost and performance.
5. **Data Management:** Supports various data types and workloads, including transactional (OLTP), analytical (OLAP), and mixed workloads, often in a single converged database environment.
### Benefits:
- **Reduced Costs:** Minimizes the need for dedicated database administrators, lowering operational expenses.
- **Improved Performance:** AI-driven optimization ensures efficient query execution and resource allocation.
- **Enhanced Security:** Automated security features reduce the likelihood of breaches due to misconfigurations or delayed updates.
- **Increased Productivity:** Frees up IT staff to focus on innovation rather than routine maintenance.
### Use Cases:
- Businesses requiring high availability and reliability for critical applications.
- Organizations looking to modernize IT infrastructure with cloud-native solutions.
- Data analytics and machine learning projects needing scalable, optimized data storage.
- Environments where security and compliance are paramount, such as finance or healthcare.
### Example:
Oracle Autonomous Database, available on Oracle Cloud, offers two primary services:
- **Autonomous Transaction Processing (ATP):** Optimized for transactional workloads with high performance and reliability.
- **Autonomous Data Warehouse (ADW):** Designed for analytics and big data workloads with automated data integration and optimization.
In essence, an Autonomous Database represents a shift toward intelligent, self-managing data systems that leverage automation and AI to simplify database operations, enhance security, and improve efficiency in a cloud environment. If you have a specific provider or context in mind, let me know, and I can dive deeper!例: LLAMAモデルを使用したOCI生成AIによるSelect AI
この例では、OCI生成AIのchat機能を示します。2つのプロンプトでモデルの機能を強調します。顧客のコメントを分析して感情を把握し、ロッククライミングに関する導入パラグラフを生成します。
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生成AIの使用
次の例では、デフォルトのOCI生成AIチャット・モデルを使用します。model_nameパラメータを指定しない場合、OCI生成AIでは、「AIプロバイダおよびLLMの選択」の表に従ってデフォルト・モデルが使用されます。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_DEFAULT',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADB_USER"}]
}');
END;
/チャット・モデルでのOCI生成AIの使用
次の例では、OCI生成AIチャット・モデルとしてcohere.command-r-plus-08-2024を使用します。
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;
/チャット・モデル・エンドポイントIDでのOCI生成AIの使用
次の例は、modelのかわりにOCI生成AIチャット・モデルのエンドポイントIDを指定する方法を示しています。Meta Llamaチャット・モデルのエンドポイントIDを使用している場合は、oci_apiformatをGENERICとして指定します。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_CHAT_ENDPOINT',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADB_USER"}],
"oci_endpoint_id": "<endpoint_id>",
"oci_apiformat": "GENERIC"
}');
END;
/チャット・モデルOCIDでのOCI生成AIの使用
この例では、modelのかわりにOCI生成AIコヒーレ・チャット・モデルのエンドポイントIDを指定する方法を示します。Meta Llamaチャット・モデルのエンドポイントIDを使用している場合は、oci_apiformatをGENERICとして指定します。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_CHAT_OCID',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADB_USER"}],
"model": "<model_ocid>",
"oci_apiformat": "COHERE"
}');
END;
/親トピック: Select AIの使用例
例: OpenAIを使用したSelect AI
この例では、OpenAIを使用して自然言語プロンプトからSQL文を生成する方法を示します。
ノート
EXECUTE権限およびネットワークACLプロシージャを実行できるのはDBAのみです。
--Grants EXECUTE privilege to ADB_USER
--
SQL> grant execute on DBMS_CLOUD_AI to ADB_USER;
-- Grant Network ACL for OpenAI endpoint
--
SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api.openai.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create Credential for AI provider
--
EXEC
DBMS_CLOUD.CREATE_CREDENTIAL(
CREDENTIAL_NAME => 'OPENAI_CRED',
username => 'OPENAI',
password => '<your_api_token>');
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OPENAI',
attributes =>'{"provider": "openai",
"credential_name": "OPENAI_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"conversation": "true"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('OPENAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"OPENAI"
--
-- Use AI
--
SQL> select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
SQL> select ai how many customers in San Francisco are married;
MARRIED_CUSTOMERS
-----------------
18
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS married_customers_count
FROM SH.CUSTOMERS c
WHERE c.CUST_CITY = 'San Francisco'
AND c.CUST_MARITAL_STATUS = 'Married'
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
- 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
- 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
Remember to adjust the table and column names based on your actual schema if they differ from the example.
Feel free to ask if you have more questions related to SQL or database in general.
SQL> select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
--------------------------------------------------------------------------------
The top 3 customers in San Francisco are:
1. Hector Colven - Total amount sold: $52,025.99
2. Milburn Klemm - Total amount sold: $50,842.28
3. Gavin Xie - Total amount sold: $48,677.18
SQL> select ai chat what is Autonomous Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous Database is a cloud-based database service provided by Oracle. It is designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous Database uses machine learning and automation to optimize performance, security, and availability, allowing
users to focus on their applications and data rather than database administration tasks. It offers both Autonomous Transaction Processing (ATP) for
transactional workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomous Database provides high performance, scalability, and
reliability, making it an ideal choice for modern cloud-based applications.
--
--Clear the profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('OPENAI');
PL/SQL procedure successfully completed.次の例は、AIプロファイルで別のモデルを指定する方法を示しています。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OPENAI',
attributes =>'{"provider": "openai",
"credential_name": "OPENAI_CRED",
"model": "gpt-3.5-turbo",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"conversation": "true"
}');
END;
/ 親トピック: Select AIの使用例
例: Cohereを使用したSelect AI
この例では、Cohereを使用して自然言語プロンプトからSQL文を生成する方法を示します。
ノート
EXECUTE権限およびネットワークACLプロシージャを実行できるのはDBAのみです。
--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.次の例は、AIプロファイルで異なるモデル属性とカスタム属性を指定する方法を示しています。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'COHERE',
attributes =>
'{"provider": "cohere",
"credential_name": "COHERE_CRED",
"model": "cohere.command-a-03-2025",
"object_list": [{"owner": "ADB_USER"}],
"max_tokens":512,
"stop_tokens": [";"],
"temperature": 0.5,
"comments": true
}');
END;
/親トピック: Select AIの使用例
例: Azure OpenAIサービスを使用したSelect AI
次の例は、APIキーを使用したAzure OpenAIサービスへのアクセスの有効化、またはAzure OpenAIサービス・プリンシパルの使用、AIプロファイルの作成、自然言語プロンプトからのSQLの生成を行う方法を示しています。
-- Create Credential for AI integration
--
EXEC
DBMS_CLOUD.CREATE_CREDENTIAL(
CREDENTIAL_NAME => 'AZURE_CRED',
username => 'AZUREAI',
password => 'your_api_token');
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for OpenAI endpoint
--
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => '<azure_resource_name>.openai.azure.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name=> 'AZUREAI',
attributes=> '{"provider": "azure",
"azure_resource_name": "<azure_resource_name>",
"azure_deployment_name": "<azure_deployment_name>"
"credential_name": "AZURE_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"conversation": "true"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"AZUREAI"
--
-- Use AI
--
select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
select ai how many customers in San Francisco are married;
MARRIED_CUSTOMERS
-----------------
18
select ai showsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS married_customers_count
FROM SH.CUSTOMERS c
WHERE c.CUST_CITY = 'San Francisco'
AND c.CUST_MARITAL_STATUS = 'Married'
select ai explainsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
- 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
- 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
Remember to adjust the table and column names based on your actual schema if they differ from the example.
Feel free to ask if you have more questions related to SQL or database in general.
select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
--------------------------------------------------------------------------------
The top 3 customers in San Francisco are:
1. Hector Colven - Total amount sold: $52,025.99
2. Milburn Klemm - Total amount sold: $50,842.28
3. Gavin Xie - Total amount sold: $48,677.18
select ai chat what is Autonomous Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous Database is a cloud-based database service provided by Oracle. It is designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous Database uses machine learning and automation to optimize performance, security, and availability, allowing
users to focus on their applications and data rather than database administration tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomous Database provides high performance, scalability, and reliability, making it
an ideal choice for modern cloud-based applications.
--
--Clear the profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.次の例は、AIプロファイルで別のモデルを指定する方法を示しています。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name=>'AZUREAI',
attributes=>'{"provider": "azure",
"credential_name": "AZURE$PA",
"model": "gpt-3.5-turbo",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"azure_resource_name": "<azure_resource_name>",
"azure_deployment_name": "<azure_deployment_name>"
}');
END;
/例: Azure OpenAI Service Principalを使用したSelect AI
データベース管理者として接続し、Azureサービス・プリンシパル認証へのアクセスを提供し、Select AIを使用するユーザー(ADB_USER)にネットワークACL権限を付与します。Azureリソースへのアクセス権を提供するには、Azureサービス・プリンシパルを使用してAzureリソースにアクセスを参照してください。
ノート
EXECUTE権限およびネットワークACLプロシージャを実行できるのは、DBAユーザーのみです。
-- Connect as ADMIN user and enable Azure service principal authentication.
BEGIN
DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(provider => 'AZURE',
params => JSON_OBJECT('azure_tenantid' value 'azure_tenantid'));
END;
/
-- Copy the consent url from cloud_integrations view and consents the ADB-S application.
SQL> select param_value from CLOUD_INTEGRATIONS where param_name = 'azure_consent_url';
PARAM_VALUE
--------------------------------------------------------------------------------
https://login.microsoftonline.com/<tenant_id>/oauth2/v2.0/authorize?client_id=<client_id>&response_type=code&scope=User.read
-- On the Azure OpenAI IAM console, search for the Azure application name and assign the permission to the application.
-- You can get the application name in the cloud_integrations view.
SQL> select param_value from CLOUD_INTEGRATIONS where param_name = 'azure_app_name';
PARAM_VALUE
--------------------------------------------------------------------------------
ADBS_APP_DATABASE_OCID
--
-- Grant Network ACL for Azure OpenAI endpoint
--SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'azure_resource_name.openai.azure.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name=>'AZUREAI',
attributes=>'{"provider": "azure",
"credential_name": "AZURE$PA",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"azure_resource_name": "<azure_resource_name>",
"azure_deployment_name": "<azure_deployment_name>"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"AZUREAI"
--
-- Use AI
--
select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
select ai how many customers in San Francisco are married;
MARRIED_CUSTOMERS
-----------------
18
select ai showsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS married_customers_count
FROM SH.CUSTOMERS c
WHERE c.CUST_CITY = 'San Francisco'
AND c.CUST_MARITAL_STATUS = 'Married'
select ai explainsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
- 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
- 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
Remember to adjust the table and column names based on your actual schema if they differ from the example.
Feel free to ask if you have more questions related to SQL or database in general.
select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
--------------------------------------------------------------------------------
The top 3 customers in San Francisco are:
1. Hector Colven - Total amount sold: $52,025.99
2. Milburn Klemm - Total amount sold: $50,842.28
3. Gavin Xie - Total amount sold: $48,677.18
select ai chat what is Autonomous Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous Database is a cloud-based database service provided by Oracle. It is designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous Database uses machine learning and automation to optimize performance, security, and availability, allowing us
ers to focus on their applications and data rather than database administration tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomous Database provides high performance, scalability, and reliability, making it
an ideal choice for modern cloud-based applications.
EXEC DBMS_CLOUD_AI.DROP_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.親トピック: Select AIの使用例
例: Googleを使用したSelect AI
この例では、Googleを使用して、Google Gemini LLMを使用して自然言語プロンプトまたはチャットからSQLを生成、実行および説明する方法を示します。
次の例は、GoogleをAIプロバイダとして使用する方法を示しています。この例では、Google API署名キーを使用してネットワーク・アクセスを提供し、AIプロファイルを作成し、Select AIアクションを使用して自然言語プロンプトとチャット・レスポンスからSQL問合せを生成する方法を示します。
--Grants EXECUTE privilege to ADB_USER
--
SQL> grant EXECUTE on DBMS_CLOUD_AI to ADB_USER;
--
-- Create Credential for AI provider
--
SQL> BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'GOOGLE_CRED',
username => 'GOOGLE',
password => '<your_api_key>'
);
END;
/
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for Google endpoint
--
SQL>
SQL> BEGIN
DBMS_NETWORK_ACL_ADB_USER.APPEND_HOST_ACE(
host => 'generativelanguage.googleapis.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
SQL> BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GOOGLE',
attributes =>'{"provider": "google",
"credential_name": "GOOGLE_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}]
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('GOOGLE');
PL/SQL procedure successfully completed.
--
-- Use AI
--
SQL> select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
SQL> select ai how many customers in San Francisco are married;
MARRIED_CUSTOMERS
-----------------
18
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS married_customers_count
FROM SH.CUSTOMERS c
WHERE c.CUST_CITY = 'San Francisco'
AND c.CUST_MARITAL_STATUS = 'Married'
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
- 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
- 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
Remember to adjust the table and column names based on your actual schema if they differ from the example.
Feel free to ask if you have more questions related to SQL or database in general.
SQL> select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
--------------------------------------------------------------------------------
The top 3 customers in San Francisco are:
1. Hector Colven - Total amount sold: $52,025.99
2. Milburn Klemm - Total amount sold: $50,842.28
3. Gavin Xie - Total amount sold: $48,677.18
SQL> select ai chat what is Autonomous Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous Database is a cloud-based database service provided by Oracle. It is designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous Database uses machine learning and automation to optimize performance, security, and availability, allowing
users to focus on their applications and data rather than database administration tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomous Database provides high performance, scalability, and reliability, making it
an ideal choice for modern cloud-based applications.
--
--Clear the profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('GOOGLE');
PL/SQL procedure successfully completed. 次の例は、AIプロファイルで別のモデルを指定する方法を示しています。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GOOGLE',
attributes =>'{"provider": "google",
"credential_name": "GOOGLE_CRED",
"model": "gemini-1.5-pro",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}]
}');
END;
/親トピック: Select AIの使用例
例: Anthropicを使用したSelect AI
この例では、Anthropicを使用して、Anthropic Claude LLMを使用して自然言語プロンプトまたはチャットからSQLを生成、実行および説明する方法を示します。
次の例は、AnthropicをAIプロバイダとして使用する方法を示しています。この例では、Anthropic API署名キーを使用してネットワーク・アクセスを提供し、AIプロファイルを作成し、Select AIアクションを使用して自然言語プロンプトからSQL問合せを生成し、Anthropic Claude LLMを使用してチャットする方法を示します。
プロファイル属性を指定するには、「プロファイル属性」を参照してください。
--Grant EXECUTE privilege to ADB_USER
SQL>GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;
--
-- Create Credential for AI provider
--
SQL>BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'ANTHROPIC_CRED',
username => 'ANTHROPIC',
password => '<your api key>'
);
END;
/
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for Anthropic endpoint
--
SQL>BEGIN
DBMS_NETWORK_ACL_ADB_USER.APPEND_HOST_ACE(
host => 'api.anthropic.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
SQL>BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'ANTHROPIC',
attributes =>'{"provider": "anthropic",
"credential_name": "ANTHROPIC_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}]
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL>EXEC DBMS_CLOUD_AI.SET_PROFILE('ANTHROPIC');
PL/SQL procedure successfully completed.
--
-- Use AI
--
SQL> select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
SQL> select ai how many customers in San Francisco are married;
MARRIED_CUSTOMERS
-----------------
18
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS married_customers_count
FROM SH.CUSTOMERS c
WHERE c.CUST_CITY = 'San Francisco'
AND c.CUST_MARITAL_STATUS = 'Married'
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
- 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
- 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
Remember to adjust the table and column names based on your actual schema if they differ from the example.
Feel free to ask if you have more questions related to SQL or database in general.
SQL> select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
--------------------------------------------------------------------------------
The top 3 customers in San Francisco are:
1. Hector Colven - Total amount sold: $52,025.99
2. Milburn Klemm - Total amount sold: $50,842.28
3. Gavin Xie - Total amount sold: $48,677.18
SQL> select ai chat what is Autonomous Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous Database is a cloud-based database service provided by Oracle. It is designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous Database uses machine learning and automation to optimize performance, security, and availability, allowing
users to focus on their applications and data rather than database administration tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomous Database provides high performance, scalability, and reliability, making it
an ideal choice for modern cloud-based applications.
--
--Clear the profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('ANTHROPIC');
PL/SQL procedure successfully completed.
次の例は、AIプロファイルで別のモデルを指定する方法を示しています。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'ANTHROPIC',
attributes =>'{"provider": "anthropic",
"credential_name": "ANTHROPIC_CRED",
"model": "claude-3-opus-20240229",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}]
}');
END;
/親トピック: Select AIの使用例
例: Hugging Faceを使用したSelect AI
この例は、Hugging Faceを使用して、自然言語プロンプトからSQLを生成、実行および説明したり、Hugging Face LLMを使用してチャットする方法を示しています。
次の例は、Hugging FaceをAIプロバイダとして使用する方法を示しています。この例では、Hugging Face API署名キーを使用してネットワーク・アクセスを提供し、AIプロファイルを作成し、Select AIアクションを使用して自然言語プロンプトからSQL問合せを生成し、Hugging Face LLMを使用してチャットする方法を示します。
--Grant EXECUTE privilege to ADB_USER
SQL>GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;
--
-- Create Credential for AI provider
--
SQL>BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'HF_CRED',
username => 'HF',
password => '<your_api_key>'
);
END;
/
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for Hugging Face endpoint
--
SQL>BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api-inference.huggingface.co',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
SQL>BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'HF',
attributes =>'{"provider": "huggingface",
"credential_name": "HF_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"model" : "Qwen/Qwen2.5-72B-Instruct"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL>EXEC DBMS_CLOUD_AI.SET_PROFILE('HF');
PL/SQL procedure successfully completed.
--
-- Use AI
--SQL> select ai how many customers exist;
Customer_Count
--------------
55500
SQL> select ai how many customers in San Francisco are married;
Married_Customers
-----------------
46
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
------------------------------------------------------------
SELECT COUNT("CUST_ID") AS "Married_Customers"
FROM "SH"."CUSTOMERS" "C"
WHERE "CUST_CITY" = 'San Francisco' AND "CUST_MARITAL_STATUS
" = 'Married'
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
------------------------------------------------------------
To answer the question "How many customers in San Francisco are married?", we need to query the "SH"."CUSTOMERS" table and filter the results based on the city and marital status.
We will use table aliases to make the query more readable and ensure that the string comparisons follow the specified rules.
Here is the Oracle SQL query:
```sql
SELECT COUNT("CUST_ID") AS "Married_Customers_in_San_Francisco"
FROM "SH"."CUSTOMERS" c
WHERE c."CUST_CITY" = 'San Francisco' -- Case insensitive comparison
AND UPPER(c."CUST_MARITAL_STATUS") = UPPER('Married'); --
Case insensitive comparison
```
### Explanation:
1. **Table Alias**:
RESPONSE
------------------------------------------------------------
- We use the alias `c` for the "SH"."CUSTOMERS" table to make the query more readable.
2. **Column Selection**:
- We select the count of "CUST_ID" to get the number of customers who meet the criteria. We alias this count as "Married_Customers_in_San_Francisco" for clarity.
3. **WHERE Clause**:
- **City Filter**: `c."CUST_CITY" = 'San Francisco'`
- Since 'San Francisco' is not in double quotes, we use a case-insensitive comparison.
- **Marital Status Filter**: `UPPER(c."CUST_MARITAL_STATUS") = UPPER('Married')`
- Since 'Married' is not in double quotes, we use a case-insensitive comparison by applying the `UPPER` function to both sides of the comparison.
This query will return the number of customers in San Francisco who are married.
SQL> select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
------------------------------------------------------------
The top 3 customers in San Francisco are:
- Hector Colven, with a customer ID of 8866.
- Milburn Klemm, with a customer ID of 1944.
- Gavin Xie, with a customer ID of 2579.
These customers have the highest total sales among all customers in San Francisco.
SQL> select ai chat what is Autonomous Database;
RESPONSE
------------------------------------------------------------
An Autonomous Database is a type of database management system that uses artificial intelligence (AI) and machine learning (ML) to automate many of the routine tasks typically performed by database administrators (DBAs). These tasks include provisioning, patching, backups, tuning, and security. The goal of an autonomous database is to reduce the need for human intervention, minimize errors, and optimize performance,thereby allowing organizations to focus on more strategic activities.
### Key Features of Autonomous Databases:
RESPONSE
------------------------------------------------------------
1. **Automated Provisioning**: The database can be set up and configured automatically, reducing the time and effort required to get it up and running.
2. **Self-Patching and Updates**: The database automatically applies security patches and updates without downtime, ensuring that the system remains secure and up-to-date.
3. **Self-Tuning**: The database continuously monitors its performance and adjusts settings to optimize query execution and resource utilization.
4. **Self-Backup and Recovery**: Automated backup and recovery processes ensure that data is protected and can be restored quickly in the event of a failure.
5. **Security**: Advanced security features, including threat detection and response, are built into the database to protect against cyber threats.
6. **Scalability**: The database can automatically scale resources up or down based on demand, ensuring optimal performance and cost efficiency.
7. **Monitoring and Diagnostics**: Real-time monitoring and diagnostics help identify and resolve issues before they impact performance.
RESPONSE
------------------------------------------------------------
### Benefits of Autonomous Databases:
- **Reduced Operational Costs**: By automating routine tasks, the need for dedicated DBAs is reduced, lowering operational costs.
- **Improved Reliability**: Automated processes reduce the risk of human error, leading to more reliable and consistent performance.
- **Enhanced Security**: Continuous monitoring and automated security measures help protect against threats.
- **Faster Time to Market**: Automated provisioning and tuning allow applications to be deployed more quickly.
RESPONSE
------------------------------------------------------------
- **Scalability and Flexibility**: The ability to scale resources automatically ensures that the database can handle varying workloads efficiently.
### Use Cases:
- **Cloud Applications**: Autonomous databases are particularly useful in cloud environments where scalability and reliability are critical.
- **Data Warehousing**: They can handle large volumes of data and complex queries, making them ideal for data warehousing and analytics.
RESPONSE
------------------------------------------------------------
- **IoT and Real-Time Data Processing**: They can process and analyze real-time data from IoT devices efficiently.
- **E-commerce**: They can handle high transaction volumes and ensure fast response times for online shopping platforms.
### Examples of Autonomous Databases:
- **Oracle Autonomous Database**: One of the first and most well-known autonomous databases, offering both transactional
and data warehousing capabilities.
- **Amazon Aurora**: A managed relational database service that includes automated scaling, patching, and backups.
- **Microsoft Azure SQL Database Managed Instance**: Provides a high level of automation and management for SQL Server databases in the cloud.
- **Google Cloud Spanner**: A globally distributed, horizontally scalable relational database that is highly available and consistent.
Autonomous databases represent a significant advancement in database technology, offering organizations a more efficient, secure, and cost-effective way to manage their data.
--
--Clear the profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('HF');
PL/SQL procedure successfully completed.親トピック: Select AIの使用例
例: AWSを使用したSelect AI
この例では、AWSを使用して、AWSで使用可能なモデルを使用して、自然言語プロンプトまたはチャットからSQLを生成、実行および説明する方法を示します。
次の例は、Amazon Bedrockとその基盤モデルでAWSをAIプロバイダとして使用する方法を示しています。この例では、AWS資格証明の作成、ネットワーク・アクセスの提供、AIプロファイルの作成、およびSelect AIアクションを使用した自然言語プロンプトからのSQL問合せの生成、およびAWS基盤モデルを使用したチャットを示しています。
AWSを使用するには、アクセス・キー、秘密キーおよびモデルIDを取得します。「AWSの使用」を参照してください。DBMS_CLOUD_AI.CREATE_PROFILEプロシージャのmodel属性としてモデルIDを使用します。デフォルト・モデルが指定されていないため、model属性を明示的に指定する必要があります。
--Grant EXECUTE privilege to ADB_USER
GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;
--
-- Create Credential for AI provider
--
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'AWS_CRED',
username => '<your_AWS_access_key>',
password => '<your_AWS_secret_key>'
);
END;
/
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for AWS
--
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'bedrock-runtime.us-east-1.amazonaws.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'AWS',
attributes =>'{"provider": "aws",
"credential_name": "AWS_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"model" : "anthropic.claude-v2",
"conversation" : "true"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('AWS');
PL/SQL procedure successfully completed.
--
-- Use AI
--
SELECT AI how many customers exist;
"RESPONSE"
"COUNT(*)"
55500
SELECT AI how many customers in San Francisco are married;
"RESPONSE"
"COUNT(*)"
46
SELECT AI showsql how many customers in San Francisco are married;
"RESPONSE"
"SELECT COUNT(*) AS "Number of Married Customers in San Francisco"
FROM "SH"."CUSTOMERS" C
WHERE UPPER(C."CUST_CITY") = UPPER('San Francisco')
AND UPPER(C."CUST_MARITAL_STATUS") = UPPER('Married')"
SELECT AI explainsql how many customers in San Francisco are married;
"RESPONSE""SELECT
COUNT(*) AS "Number of Married Customers in San Francisco"
FROM "SH"."CUSTOMERS" C
WHERE C."CUST_CITY" = 'San Francisco'
AND C."CUST_MARITAL_STATUS" = 'Married'
Explanation:
- Used table alias C for CUSTOMERS table
- Used easy to read column names like CUST_CITY, CUST_MARITAL_STATUS
- Enclosed table name, schema name and column names in double quotes
- Compared string values in WHERE clause without UPPER() since the values are not in double quotes
- Counted number of rows satisfying the condition and aliased the count as "Number of Married Customers in San Francisco""
SELECT AI narrate what are the top 3 customers in San Francisco;
"RESPONSE"
The top 3 customers in San Francisco ordered by credit limit in descending order are:
1. Bert Katz
2. Madallyn Ladd
3. Henrietta Snodgrass
SELECT AI chat what is Autonomous Database;
"RESPONSE"
"An Autonomous Database is a cloud database service provided by Oracle Corporation. Some key features of Oracle Autonomous Database include:
- Fully automated and self-driving - The database automatically upgrades, patches, tunes, and backs itself up without any human intervention required.
- Self-securing - The database uses machine learning to detect threats and automatically apply security updates.
- Self-repairing - The database monitors itself and automatically recovers from failures and errors without downtime.
- Self-scaling - The database automatically scales compute and storage resources up and down as needed to meet workload demands.
- Serverless - The database is accessed as a cloud service without having to manually provision any servers or infrastructure.
- High performance - The database uses Oracle's advanced automation and machine learning to continuously tune itself for high performance.
- Multiple workload support - Supports transaction processing, analytics, graph processing, etc in a single converged database.
- Fully managed - Oracle handles all the management and administration of the database. Users just load and access their data.
- Compatible - Supports common SQL and Oracle PL/SQL for easy migration from on-prem Oracle databases.
So in summary, an Oracle Autonomous Database is a fully automated, self-driving, self-securing, and self-repairing database provided as a simple cloud service. The automation provides high performance, elasticity, and availability with minimal human labor required."
--
--Clear the profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('AWS');
PL/SQL procedure successfully completed.親トピック: Select AIの使用例
例: OpenAI互換プロバイダを使用したSelect AI
この例では、OpenAI互換プロバイダを使用して、OpenAI互換プロバイダで使用可能なモデルを使用して、自然言語プロンプトまたはチャットからSQLを生成、実行および説明する方法を示します。
次の例は、Fireworks AIをOpenAI互換プロバイダとして使用する方法を示しています。Fireworks AI API署名キーを使用した資格証明の作成、ネットワーク・アクセスの構成、AIプロファイルの作成、Select AIアクションを使用した自然言語プロンプトからのSQL問合せの生成およびFireworks AI LLMモデルを使用したチャット方法を示します。
Fireworks AIを使用するには、provider属性のかわりに、DBMS_CLOUD_AI.CREATE_PROFILEプロシージャの属性としてprovider_endpointを指定します。属性を取得するには、「OpenAI互換プロバイダの使用」を参照してください。デフォルト・モデルが指定されていないため、model属性を明示的に指定する必要があります。
--Grant EXECUTE privilege to ADB_USER
GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;
--
-- Create Credential for AI provider
--
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'FIREWORKS_CRED',
username => 'FIREWORKS',
password => '<your_fireworksaiapi_key>'
);
END;
/
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for Fireworks AI endpoint
--
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api.fireworks.ai',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'FIREWORKS',
attributes =>'{
"credential_name": "FIREWORKS_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"model" : "accounts/fireworks/models/llama-v3p1-405b-instruct",
"provider_endpoint" : "api.fireworks.ai/inference",
"conversation" : "true"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('FIREWORKS');
PL/SQL procedure successfully completed.
--
-- Use AI
--
select ai how many customers exist;
"RESPONSE"
"COUNT(*)"
55500
select ai how many customers in San Francisco are married;
"RESPONSE"
"COUNT(*)"
46
select ai showsql how many customers in San Francisco are married;
"RESPONSE"
"SELECT COUNT(*)
FROM ""SH"".""CUSTOMERS"" ""c""
WHERE UPPER(""c"".""CUST_CITY"") = UPPER('San Francisco')
AND UPPER(""c"".""CUST_MARITAL_STATUS"") = UPPER('Married')"
select ai explainsql how many customers in San Francisco are married;
"RESPONSE"
"Here is the Oracle SQL query:
```sql
SELECT COUNT(*)
FROM ""SH"".""CUSTOMERS"" ""c""
WHERE UPPER(""c"".""CUST_CITY"") = UPPER('San Francisco')
AND UPPER(""c"".""CUST_MARITAL_STATUS"") = UPPER('Married')
```
Explanation:
* We use the table alias `""c""` to refer to the `""CUSTOMERS""` table.
* We enclose the schema name `""SH""`, table name `""CUSTOMERS""`, and column names `""CUST_CITY""` and `""CUST_MARITAL_STATUS""` in double quotes to ensure case sensitivity.
* In the `WHERE` clause, we use the `UPPER()` function to perform case-insensitive comparisons for the strings `'San Francisco'` and `'Married'`, since they are not enclosed in double quotes.
* The `COUNT(*)` function returns the number of rows in the result set, which represents the number of customers in San Francisco who are married."
select ai narrate what are the top 3 customers in San Francisco;
"RESPONSE"
The top 3 customers in San Francisco are:
1. Lyndon Baltzer
2. Mike Crocker
3. Milburn Klemm
These customers are identified by their unique customer IDs, which are used to track their information and interactions. The list shows the customer's first and last names.
select ai chat what is Autonomous Database;
"RESPONSE"
"An Autonomous Database is a type of database that uses artificial intelligence (AI) and machine learning (ML) to automate many of the tasks traditionally performed by database administrators (DBAs). The goal of an Autonomous Database is to provide a self-managing, self-securing, and self-repairing database that can operate with minimal human intervention.
Autonomous Databases use advanced algorithms and machine learning techniques to:
1. **Automate database administration**: Tasks such as patching, backups, and performance tuning are automated, freeing up DBAs to focus on higher-level tasks.
2. **Predict and prevent issues**: The database uses machine learning to predict and prevent issues such as downtime, data breaches, and performance problems.
3. **Optimize performance**: The database continuously monitors and optimizes its own performance, ensuring that it is running at peak efficiency.
4. **Enhance security**: Autonomous Databases use advanced security features, such as encryption and access controls, to protect data from unauthorized access.
5. **Improve data management**: Autonomous Databases can automatically manage data, including data ingestion, processing, and storage.
The benefits of Autonomous Databases include:
1. **Increased efficiency**: By automating routine tasks, Autonomous Databases can reduce the workload of DBAs and improve overall efficiency.
2. **Improved security**: Autonomous Databases can detect and respond to security threats in real-time, reducing the risk of data breaches.
3. **Enhanced performance**: Autonomous Databases can optimize their own performance, ensuring that applications run quickly and efficiently.
4. **Reduced costs**: By automating routine tasks and improving efficiency, Autonomous Databases can help reduce costs associated with database management.
Examples of Autonomous Databases include:
1. Oracle Autonomous Database
2. Microsoft Azure SQL Database
3. Amazon Aurora
4. Google Cloud SQL
Overall, Autonomous Databases represent a significant shift in the way databases are managed and maintained, using AI and ML to automate many of the tasks traditionally performed by DBAs."
--
--Clear the profile
--
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('FIREWORKS');
PL/SQL procedure successfully completed.親トピック: Select AIの使用例
例: Select AIでの会話の有効化
これらの例は、Select AIでの会話の有効化を示しています。
開始する前に
「Select AIの前提条件の実行」を確認します。
ノート
管理者権限(ADMIN)を持つユーザーは、EXECUTEを付与し、ネットワーク・アクセス制御リスト(ACL)を有効にする必要があります。
セッション・ベースの会話
AIプロファイルを作成します。プロファイルでconversation属性をtrueに設定します。このアクションには、スキーマ・メタデータを含む可能性のある以前の相互作用またはプロンプトからのコンテンツが含まれ、プロファイルが設定されます。プロファイルを有効にすると、データとの会話を開始できます。自然言語を使用して質問し、必要に応じてフォローアップします。
--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.カスタマイズ可能な会話
次の例は、カスタマイズ可能な会話をサポートする会話管理APIの使用方法を示しています。複数の会話にSelect AIを使用するには:
次の方法で会話を作成および設定できます。
- 会話の作成
- 現在のユーザー・セッションでの会話の設定
Select AI <action> <prompt>の使用
DBMS_CLOUD_AI.CREATE_COVERSATIONファンクションを使用し、DBMS_CLOUD_AI.SET_CONVERSATION_IDを使用して会話を設定します。DBMS_CLOUD_AI.CREATE_CONVERSATIONプロシージャを直接コールして、1ステップで会話を作成および設定します。
次の例は、DBMS_CLOUD_AI.CREATE_COVERSATIONファンクションを使用して会話を作成し、DBMS_CLOUD_AI.SET_CONVERSATION_IDプロシージャを使用して設定する方法を示しています。
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 completed次の例は、DBMS_CLOUD_AI.CREATE_COVERSATIONプロシージャを実行してconversation_idを直接作成および設定する方法を示しています。
EXEC DBMS_CLOUD_AI.create_conversation;
PL/SQL procedure successfully completed.title、description、retention_days、conversation_length属性などの会話属性をカスタマイズすることもできます。
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-9C6D4664EC3A特定の会話が存在するかどうかは、DBA/USER_CLOUD_AI_CONVERSATIONSビューを問い合せることで確認できます。
-- 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
DBMS_CLOUD_AI.GET_CONVERSATION_ID関数をコールして、会話が設定されているかどうかを確認することもできます。
SELECT DBMS_CLOUD_AI.GET_CONVERSATION_ID;
--------------------------------------------------------------------------------
30C9DB6E-EA4F-AFBA-E063-9C6D46644B92例: Select AIでのカスタマイズ可能な会話の使用
会話を作成して設定し、AIプロファイルを有効にすると、データとの対話を開始できます。自然言語を使用して質問し、必要に応じてフォローアップします。
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.例: GENERATEファンクションを使用した2つの会話の比較
次の例は、2つの会話を同じ意味で使用して質問をしたり、正確な応答を確認する方法を示しています。各会話は、比較に焦点を当てた別の質問から始まります。後で、両方の会話で同じフォローアップ質問をすると、それぞれが前のコンテキストに基づいて異なる回答を返します。
-- 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.
会話を指定せずにDBMS_CLOUD_AI.GENERATE関数をコールできますが、このような場合、意味のあるレスポンスは想定されません。
-- 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.
例: DBMS_CLOUD_AIビューを使用した会話の検証
DBMS_CLOUD_AI会話ビューを問い合せて、会話およびプロンプトの詳細を確認できます。詳細は、DBMS_CLOUD_AIビューを参照してください。
ノート
DBA_接頭辞を持つビューは、管理者権限(ADMIN)を持つユーザーのみが使用できます。
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 2例: 会話詳細の更新
DBMS_CLOUD_AI.UPDATE_CONVERSATIONプロシージャを使用して、会話のtitle、descriptionおよびretention_daysを更新できます。DBMS_CLOUD_AI対話ビューを問い合せて、更新を確認できます。
-- 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 20例: プロンプトの削除
会話から個々のプロンプトを削除し、DBMS_CLOUD_AI会話ビューを問い合せて変更を確認できます。
-- 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-9C6D46644B92例: 会話の削除
会話全体を削除すると、それに関連付けられているすべてのプロンプトも削除されます。
-- 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親トピック: Select AIの使用例
例: RAGを使用したSelect AIの設定および使用
この例では、Oracle Autonomous Databaseを使用して、OCI生成AIベクトル・ストア・クラウド・サービスとOpenAIを統合するための資格証明の設定、ネットワーク・アクセスの構成およびベクトル索引の作成をガイドします。
この設定は、ベクトル索引を使用してLLMレスポンスを強化するAIプロファイルを作成することで完了します。最後に、この例では、Select AI narrateアクションを使用します。このアクションは、指定されたベクトル・データベースの情報を使用して拡張されたレスポンスを返します。
次の例は、Oracle 23aiでのベクトル索引の構築および問合せを示しています。
--Grants EXECUTE privilege to ADB_USER
GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;
--Grants EXECUTE privilege DBMS_CLOUD_PIPELINE to ADB_USER
GRANT EXECUTE on DBMS_CLOUD_PIPELINE to ADB_USER;
-- Create the OpenAI credential
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'OPENAI_CRED',
username => 'OPENAI_CRED',
password => '<your_api_key>'
);
END;
/
PL/SQL procedure successfully completed.
-- Append the OpenAI endpoint
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api.openai.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
-- Create the object store credential
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'OCI_CRED',
username => '<your_username>',
password => '<OCI_profile_password>'
);
END;
/
PL/SQL procedure successfully completed.
-- Create the profile with the vector index.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'OPENAI_ORACLE',
attributes =>'{"provider": "openai",
"credential_name": "OPENAI_CRED",
"vector_index_name": "MY_INDEX",
"temperature": 0.2,
"max_tokens": 4096,
"model": "gpt-3.5-turbo-1106"
}');
END;
/
PL/SQL procedure successfully completed.
-- Set profile
EXEC DBMS_CLOUD_AI.SET_PROFILE('OPENAI_ORACLE');
PL/SQL procedure successfully completed.
-- create a vector index with the vector store name, object store location and
-- object store credential
BEGIN
DBMS_CLOUD_AI.CREATE_VECTOR_INDEX(
index_name => 'MY_INDEX',
attributes => '{"vector_db_provider": "oracle",
"location": "https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/my_namespace/my_bucket/my_data_folder",
"object_storage_credential_name": "OCI_CRED",
"profile_name": "OPENAI_ORACLE",
"vector_dimension": 1536,
"vector_distance_metric": "cosine",
"chunk_overlap":128,
"chunk_size":1024
}');
END;
/
PL/SQL procedure successfully completed.
-- After the vector index is populated, we can now query the index.
-- Set profile
EXEC DBMS_CLOUD_AI.SET_PROFILE('OPENAI_ORACLE');
PL/SQL procedure successfully completed.
-- Select AI answers the question with the knowledge available in the vector database.
set pages 1000
set linesize 150
SELECT AI narrate how can I deploy an oracle machine learning model;
RESPONSE
To deploy an Oracle Machine Learning model, you would first build your model within the Oracle database. Once your in-database models are built, they become immediately available for use, for instance, through a SQL query using the prediction operators built into the SQL language.
The model scoring, like model building, occurs directly in the database, eliminating the need for a separate engine or environment within which the model and corresponding algorithm code operate. You can also use models from a different schema (user account) if the appropriate permissions are in place.
Sources:
- Manage-your-models-with-Oracle-Machine-Learning-on-Autonomous-Database.txt (https://objectstorage.../v1/my_namespace/my_bucket/my_data_folder/Manage-your-models-with-Oracle-Machine-Learning-on-Autonomous-Database.txt)
- Develop-and-deploy-machine-learning-models-using-Oracle-Autonomous-Database-Machine-Learning-and-APEX.txt (https://objectstorage.../v1/my_namespace/my_bucket/my_data_folder/Develop-and-deploy-machine-learning-models-using-Oracle-Autonomous-Database-Machine-Learning-and-APEX.txt)
親トピック: Select AIの使用例
例: データベース内トランスフォーマ・モデルを使用したSelect AI
この例では、Oracleオブジェクト・ストレージに格納されている事前トレーニング済トランスフォーマ・モデルをOracle Database 23aiインスタンスにインポートし、Select AIプロファイルでインポート済データベース内モデルを使用して、ドキュメント・チャンクおよびユーザー・プロンプトのベクトル埋込みを生成する方法を示します。
Select AIプロファイルでデータベース内トランスフォーマ・モデルを使用するには、次のものがあることを確認してください:
-
Oracle Database 23aiインスタンスにインポートされた事前トレーニング済モデル。
-
オプションで、Oracleオブジェクト・ストレージにアクセスします。
Oracle Object StorageからのOracle Database 23aiへの事前トレーニング済トランスフォーマ・モデルのインポート
「データベース内のベクトル生成のためのONNX形式の事前トレーニング済モデルのインポート」およびブログ「Oracle Database 23aiの事前ビルド済埋込み生成モデル」のステップを確認し、事前トレーニング済トランスフォーマ・モデルをデータベースにインポートします。
次の例は、Oracleオブジェクト・ストレージからデータベースに事前設定されたトランスフォーマ・モデルをインポートし、インポートされたモデルを表示する方法を示しています。
- 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';Select AIプロファイルでのデータベース内トランスフォーマ・モデルの使用
これらの例は、Select AIプロファイル内でデータベース内トランスフォーマ・モデルを使用する方法を示しています。1つのプロファイルはベクトル埋込みを生成するためにのみ構成され、もう1つはSelect AIアクションとベクトル索引作成の両方をサポートします。
前提条件を完了するには、「Select AIの前提条件の実行」を確認します。
ベクトル埋込みのみを生成する例を次に示します。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'EMBEDDING_PROFILE',
attributes => '{"provider" : "database",
"embedding_model": "MY_ONNX_MODEL"}'
);
END;
/次に、サポートされているAIプロバイダを指定できる一般的なSelect AIアクションおよびベクトル索引生成の例を示します。この例では、OCI Gen AIプロファイルおよび資格証明を使用します。サポートされているプロバイダのリストは、AIプロバイダおよびLLMの選択を参照してください。ただし、ベクトル埋込みの生成にデータベース内トランスフォーマ・モデルを使用する場合は、embedding_model属性で"database: <MY_ONNX_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の使用
この例では、別のスキーマ所有者がモデルを所有している場合、データベース内トランスフォーマ・モデルでSelect AIを使用する方法を示します。embedding_model属性で、モデルの完全修飾名としてschema_name.object_nameを指定します。現在のユーザーがスキーマ所有者であるか、モデルを所有している場合は、スキーマ名を省略できます。
別のスキーマ所有者がモデルを所有している場合は、次の権限を持っていることを確認してください。
CREATE ANY MINING MODELシステム権限SELECT ANY MINING MODELシステム権限- 特定のモデルに対する
SELECT MINING MODELオブジェクト権限
システム権限を付与するには、ADMIN OPTIONを指定したシステム権限またはGRANT ANY PRIVILEGEシステム権限が付与されている必要があります。
権限を確認するには、Oracle Machine Learning for SQLのためのシステム権限を参照してください。
次の文により、SELECTアクセス権がデータに付与されているかぎり、ADB_USER1は任意のスキーマ内のデータのスコア付けおよびモデルの詳細の表示を許可します。ただし、ADB_USER1は、ADB_USER1スキーマでのみモデルを作成できます。
GRANT CREATE MINING MODEL TO ADB_USER1;
GRANT SELECT ANY MINING MODEL TO ADB_USER1;
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_GENAI',
attributes => '{"provider": "oci",
"credential_name": "GENAI_CRED",
"vector_index_name": "MY_INDEX",
"embedding_model": "database: ADB_USER1.MY_ONNX_MODEL"}'
);
END;
/次の例は、大/小文字を区別するモデル・オブジェクト名を指定する方法を示しています。
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;
/様々なAIプロバイダーによるエンドツーエンドの例
これらの例は、Select AI RAGでデータベース内トランスフォーマ・モデルを使用するためのエンドツーエンドのステップを示しています。1つのプロファイルでは、埋込みベクトルの生成のために排他的に作成されたproviderとしてdatabaseを使用し、もう1つのプロファイルでは、Select AIアクションおよびベクトル索引用に作成されたproviderとしてociを使用します。
必要な権限を指定するには、「Select AIの前提条件の実行」を確認します。
--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.
この例では、ociをproviderとして使用します。
--Grant create any directory privilege to the user
GRANT CREATE ANY DIRECTORY to ADB_USER;
- Create a Directory object, or use an existing directory object
CREATE OR REPLACE DIRECTORY ONNX_DIR AS 'onnx_model';
-- Object storage bucket
VAR location_uri VARCHAR2(4000);
EXEC :location_uri := 'https://adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com/p/eLddQappgBJ7jNi6Guz9m9LOtYe2u8LWY19GfgU8flFK4N9YgP4kTlrE9Px3pE12/n/adwc4pm/b/OML-Resources/o/';
-- Model file name
VAR file_name VARCHAR2(512);
EXEC :file_name := 'all_MiniLM_L12_v2.onnx';
-- Download ONNX model from object storage into the directory object
BEGIN
DBMS_CLOUD.GET_OBJECT(
credential_name => NULL,
directory_name => 'ONNX_DIR',
object_uri => :location_uri || :file_name);
END;
/
-- Load the ONNX model into the database
BEGIN
DBMS_VECTOR.LOAD_ONNX_MODEL(
directory => 'ONNX_DIR',
file_name => :file_name,
model_name => 'MY_ONNX_MODEL');
END;
/
-- Verify
SELECT model_name, algorithm, mining_function
FROM user_mining_models
WHERE model_name='MY_ONNX_MODEL';
–-Administrator Grants EXECUTE privilege to ADB_USER
GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;
--Administrator Grants EXECUTE privilege DBMS_CLOUD_PIPELINE to ADB_USER
GRANT EXECUTE on DBMS_CLOUD_PIPELINE to ADB_USER;
-- Create the object store credential
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'OCI_CRED',
username => '<your_username>',
password => '<OCI_profile_password>'
);
END;
/
--Create GenAI credentials
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'GENAI_CRED',
user_ocid => 'ocid1.user.oc1..aaaa...',
tenancy_ocid => 'ocid1.tenancy.oc1..aaaa...',
private_key => '<your_api_key>',
fingerprint => '<your_fingerprint>'
);
END;
/
--Create OCI AI profile
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_GENAI',
attributes => '{"provider": "oci",
"model": "meta.llama-3.3-70b-instruct",
"credential_name": "GENAI_CRED",
"vector_index_name": "MY_INDEX",
"embedding_model": "database: MY_ONNX_MODEL"}'
);
END;
/
-- Set profile
EXEC DBMS_CLOUD_AI.SET_PROFILE('OCI_GENAI');
PL/SQL procedure successfully completed.
-- create a vector index with the vector store name, object store location and
-- object store credential
BEGIN
DBMS_CLOUD_AI.CREATE_VECTOR_INDEX(
index_name => 'MY_INDEX',
attributes => '{"vector_db_provider": "oracle",
"location": "https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/my_namespace/my_bucket/my_data_folder",
"object_storage_credential_name": "OCI_CRED",
"profile_name": "OCI_GENAI",
"vector_dimension": 384,
"vector_distance_metric": "cosine",
"chunk_overlap":128,
"chunk_size":1024
}');
END;
/
PL/SQL procedure successfully completed.
-- Set profile
EXEC DBMS_CLOUD_AI.SET_PROFILE('OCI_GENAI');
PL/SQL procedure successfully completed.
-- Select AI answers the question with the knowledge available in the vector database.
set pages 1000
set linesize 150
SELECT AI narrate how can I deploy an oracle machine learning model;
RESPONSE
To deploy an Oracle Machine Learning model, you would first build your model within the Oracle database. Once your in-database models are
built, they become immediately available for use, for instance, through a SQL query using the prediction operators built into the SQL
language.
The model scoring, like model building, occurs directly in the database, eliminating the need for a separate engine or environment within
which the model and corresponding algorithm code operate. You can also use models from a different schema (user account) if the appropriate
permissions are in place.
Sources:
- Manage-your-models-with-Oracle-Machine-Learning-on-Autonomous-Database.txt (https://objectstorage.../v1/my_namespace/my_bucket/
my_data_folder/Manage-your-models-with-Oracle-Machine-Learning-on-Autonomous-Database.txt)
- Develop-and-deploy-machine-learning-models-using-Oracle-Autonomous-Database-Machine-Learning-and-APEX.txt
(https://objectstorage.../v1/my_namespace/my_bucket/my_data_folder/Develop-and-deploy-machine-learning-models-using-Oracle-Autonomous-
Database-Machine-Learning-and-APEX.txt)親トピック: Select AIの使用例
例: SQL問合せ生成の改善
これらの例は、データベース表および列のコメント、注釈、外部キーおよび参照整合性制約によって、自然言語プロンプトからのSQL問合せの生成がどのように改善されるかを示しています。
例: 表および列のコメントによるSQL生成の改善
データベース表に表および列のコメントがある場合は、DBMS_CLOUD_AI.CREATE_PROFILEファンクションの"comments":"true"パラメータを有効にして、表レベルおよび列レベルのコメントを取得します。コメントはLLMのメタデータに追加され、SQLの生成が改善されます。
-- Adding comments to table 1, table 2, and table 3. Table 1 has 3 columns, table 2 has 7 columns, table 3 has 2 columns.
-- TABLE1
COMMENT ON TABLE table1 IS 'Contains movies, movie titles and the year it was released';
COMMENT ON COLUMN table1.c1 IS 'movie ids. Use this column to join to other tables';
COMMENT ON COLUMN table1.c2 IS 'movie titles';
COMMENT ON COLUMN table1.c3 IS 'year the movie was released';
-- TABLE2
COMMENT ON TABLE table2 IS 'transactions for movie views - also known as streams';
COMMENT ON COLUMN table2.c1 IS 'day the movie was streamed';
COMMENT ON COLUMN table2.c2 IS 'genre ids. Use this column to join to other tables';
COMMENT ON COLUMN table2.c3 IS 'movie ids. Use this column to join to other tables';
COMMENT ON COLUMN table2.c4 IS 'customer ids. Use this column to join to other tables';
COMMENT ON COLUMN table2.c5 IS 'device used to stream, watch or view the movie';
COMMENT ON COLUMN table2.c6 IS 'sales from the movie';
COMMENT ON COLUMN table2.c7 IS 'number of views, watched, streamed';
-- TABLE3
COMMENT ON TABLE table3 IS 'Contains the genres';
COMMENT ON COLUMN table3.c1 IS 'genre id. use this column to join to other tables';
COMMENT ON COLUMN table3.c2 IS 'name of the genre';
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'myprofile',
attributes =>
'{"provider": "azure",
"azure_resource_name": "my_resource",
"azure_deployment_name": "my_deployment",
"credential_name": "my_credential",
"comments":"true",
"object_list": [
{"owner": "moviestream", "name": "table1"},
{"owner": "moviestream", "name": "table2"},
{"owner": " moviestream", "name": "table3"}
]
}'
);
DBMS_CLOUD_AI.SET_PROFILE(
profile_name => 'myprofile'
);
END;
/
--Prompts
select ai what are our total views;
RESPONSE
-------------------------------------------------
TOTAL_VIEWS
-----------
97890562
select ai showsql what are our total views;
RESPONSE
-------------------------------------------------------------------------
SELECT SUM(QUANTITY_SOLD) AS total_views
FROM "moviestream"."table"
select ai what are our total views broken out by device;
DEVICE TOTAL_VIEWS
-------------------------- -----------
mac 14719238
iphone 20793516
ipad 15890590
pc 14715169
galaxy 10587343
pixel 10593551
lenovo 5294239
fire 5296916
8 rows selected.
select ai showsql what are our total views broken out by device;
RESPONSE
---------------------------------------------------------------------------------------
SELECT DEVICE, COUNT(*) AS TOTAL_VIEWS
FROM "moviestream"."table"
GROUP BY DEVICE例: 表および列注釈を使用したSQL問合せ生成の改善
この例では、Oracle Database 23aiで適用可能なSelect AIでの注釈の統合を示します。注釈は、LLMに送信されるメタデータに追加されます。
スキーマに注釈を含む表がある場合は、DBMS_CLOUD_AI.CREATE_PROFILE関数で"annotations":"true"を有効にして、メタデータに注釈を追加するようにSelect AIに指示します。
--
-- 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.例: 外部キーおよび参照キー制約を使用したSQL問合せ生成の改善
この例では、LLMのメタデータに外部キーおよび参照キー制約を取得することで、LLMが正確なJOIN条件を生成する機能を示します。外部キー制約と参照キー制約は、LLMに対する表間の構造化関係データを提供します。
Select AIのDBMS_CLOUD_AI.CREATE_PROFILE関数で"constraints":"true"を有効にして、外部キーおよび参照キーを取得します。
--
-- 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.例: 関連する表メタデータの自動検出
これらの例は、Select AIが関連する表を自動的に検出し、Oracle Database 23aiの問合せに関連する特定の表に対してのみメタデータを送信する方法を示しています。この機能を有効にするには、object_list_modeをautomatedに設定します。これにより、<profile_name>_OBJECT_LIST_VECINDEXという名前のベクトル索引が自動的に作成されます。ベクトル索引は、デフォルトの属性およびrefresh_rate、similarity_threshold、match_limitなどの値で初期化されます。DBMS_CLOUD_AI.UPDATE_VECTOR_INDEXを使用して、一部の属性を変更できます。詳細は、UPDATE_VECTOR_INDEXプロシージャを参照してください。
一方のプロファイルは、object_listを使用してスキーマまたはスキーマ内のオブジェクトを指定するように構成され、もう一方のプロファイルはobject_listを指定しません。ただし、同じSQL構文が想定されます。
「Select AIの前提条件の実行」を確認して、DBMS_CLOUD_AIパッケージへのアクセスを提供し、AIプロバイダへのネットワーク・アクセスを提供します。
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')次の例では、object_listを使用せずに同じシナリオを比較します。object_listを指定しない場合、Select AIは現在のスキーマで使用可能なすべてのオブジェクトを自動的に選択します。
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')親トピック: Select AIの使用例
例: 合成データの生成
この例では、実際のデータの特性と分布を模倣した合成データを生成する方法について説明します。
次の例は、スキーマにいくつかの表を作成し、AIプロバイダとしてOCI生成AIを使用してAIプロファイルを作成し、DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA関数を使用してそれらの表にデータを合成し、Select AIを使用して自然言語プロンプトへの応答を問合せまたは生成する方法を示しています。
--Create tables or use cloned tables
CREATE TABLE ADB_USER.Director (
director_id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE ADB_USER.Movie (
movie_id INT PRIMARY KEY,
title VARCHAR(100),
release_date DATE,
genre VARCHAR(50),
director_id INT,
FOREIGN KEY (director_id) REFERENCES ADB_USER.Director(director_id)
);
CREATE TABLE ADB_USER.Actor (
actor_id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE ADB_USER.Movie_Actor (
movie_id INT,
actor_id INT,
PRIMARY KEY (movie_id, actor_id),
FOREIGN KEY (movie_id) REFERENCES ADB_USER.Movie(movie_id),
FOREIGN KEY (actor_id) REFERENCES ADB_USER.Actor(actor_id)
);
-- Create the GenAI credential
BEGIN
DBMS_CLOUD.create_credential(
credential_name => 'GENAI_CRED',
user_ocid => 'ocid1.user.oc1....',
tenancy_ocid => 'ocid1.tenancy.oc1....',
private_key => 'vZ6cO...',
fingerprint => '86:7d:...'
);
END;
/
-- Create a profile
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GENAI',
attributes =>'{"provider": "oci",
"credential_name": "GENAI_CRED",
"object_list": [{"owner": "ADB_USER",
"oci_compartment_id": "ocid1.compartment.oc1...."}]
}');
END;
/
EXEC DBMS_CLOUD_AI.set_profile('GENAI');
-- Run the API for single table
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_name => 'Director',
owner_name => 'ADB_USER',
record_count => 5
);
END;
/
PL/SQL procedure successfully completed.
-- Query the table to see results
SQL> SELECT * FROM ADB_USER.Director;
DIRECTOR_ID NAME
----------- ----------------------------------------------------------------------------------------------------
1 John Smith
2 Emily Chen
3 Michael Brown
4 Sarah Taylor
5 David Lee
-- Or ask select ai to show the results
SQL> select ai how many directors are there;
NUMBER_OF_DIRECTORS
-------------------
5
例: 複数の表に対する合成データの生成
AIプロバイダ・プロファイルを作成して設定した後、DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATAを使用して複数の表のデータを生成します。Select AIを問い合せるか使用して、自然言語プロンプトに応答できます。
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
----------------
10例: サンプル行を使用した合成データ生成のガイド
AIサービスによる合成データの生成をガイドするために、表から既存のレコードをランダムに選択できます。たとえば、{"sample_rows": 5}をparams引数に追加すると、表からAIプロバイダに5つのサンプル行を送信できます。この例では、Transactions表のサンプル行に基づいて、10行の追加を生成します。
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;
/例: ユーザー・プロンプトを使用した合成データ生成のカスタマイズ
user_prompt引数を使用すると、データ生成に関する追加のルールまたは要件を指定できます。これは、単一の表に適用することも、複数の表のobject_list引数の一部として適用することもできます。たとえば、DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATAへの次のコールでは、2009年にリリースされたムービーの合成データを生成するようにAIに指示します。
-- 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;
/例: 表統計を使用した合成データ品質の向上
表に列統計がある場合、またはメタデータを含むデータベースからクローニングされる場合、Select AIはこれらの統計を使用して、元のデータとよく似ているデータまたは一貫性のあるデータを生成できます。
NUMBER列の場合、統計の上限値と下限値は値の範囲をガイドします。たとえば、元のEMPLOYEES表のSALARY列が1000から10000の範囲にある場合、この列の合成データもこの範囲内に収まります。
値がCA、WAおよびTXのSTATE列など、個別値を持つ列の場合、合成データではこれらの固有の値が使用されます。この機能は、{"table_statistics": true/false}パラメータを使用して管理できます。デフォルトでは、表の統計は有効です。
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;
/例: 列コメントを使用したデータ生成のガイド
列コメントが存在する場合、Select AIでは、データ生成時にLLMの追加情報を提供するために列コメントが自動的に含まれます。たとえば、トランザクション表のStatus列のコメントに、successful、failed、pending、canceled、need manual checkなどの許容値がリストされることがあります。また、コメントを追加して列をさらに説明し、AIサービスが正確なデータを生成するためのより正確な指示またはヒントを提供することもできます。デフォルトでは、コメントは無効です。詳細は、オプション・パラメータを参照してください。
-- 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;
/例: 合成データ生成での一意の値の設定
大量のLLMを使用して合成データを生成すると、重複値が発生する可能性があります。これを回避するには、関連する列に一意制約を設定します。これにより、Select AIはLLM応答で値が重複する行を無視します。また、特定の列の値を制限するには、user_promptを使用するか、コメントを追加して、STATE列をCA、WAおよび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'
/例: パラレル処理による合成データ生成の拡張
実行時間を短縮するために、Select AIは、主キーがない表または数値主キーを持つ表の合成データ生成タスクを小さいチャンクに分割します。これらのタスクは並行して実行され、AIプロバイダーとやり取りしてデータをより効率的に生成します。データベースの並列度(DOP)は、Autonomous Databaseのサービス・レベルおよびECPUまたはOCPUの設定の影響を受け、各チャンク・プロセスにおけるレコード数を決定します。タスクをパラレルに実行すると、特に多数の表にわたって大量のデータを生成する場合に、パフォーマンスが一般的に向上します。合成データ生成のパラレル処理を管理するには、オプション・パラメータとしてpriorityを設定します。オプション・パラメータを参照してください。
親トピック: Select AIの使用例
例: データ・アクセスの有効化または無効化
この例では、管理者がデータ・アクセスを制御し、Select AIが実際のスキーマ表をLLMに送信できないようにする方法を示します。
データ・アクセスの無効化
スキーマ表へのアクセスを制限するには、管理者としてログインし、次の手順を実行します。
EXEC DBMS_CLOUD_AI.DISABLE_DATA_ACCESS;
PL/SQL procedure successfully completed.
データ・アクセスを無効にすると、Select AIのnarrateアクションおよび合成データ生成が制限されます。narrateアクションおよび合成データ生成でエラーが発生します。
データベースユーザーとしてログインし、AIプロファイルを作成および構成します。Select AIの前提条件の実行を確認して、AIプロファイルを構成します。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'DATA_ACCESS',
attributes =>'{"provider": "openai",
"credential_name": "OPENAI_CRED",
"object_list": [{"owner":"SH"}]
}');
END;
/
EXEC DBMS_CLOUD_AI.SET_PROFILE('DATA_ACCESS');
select ai how many customers;
NUM_CUSTOMERS
55500
select ai narrate what are the top 3 customers in San Francisco;
ORA-20000: Data access is disabled for SELECT AI.
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 2228
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD_AI", line 13157
ORA-06512: at line 1 https://docs.oracle.com/error-help/db/ora-20000/
The stored procedure 'raise_application_error' was called which causes this error to be generated
Error at Line: 1 Column: 6次の例は、合成データの生成を試行したときにトリガーされるエラーを示しています。
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 2データ・アクセスの使用可能化
次の例は、データ・アクセスの有効化を示しています。管理者としてログインし、次の手順を実行します。
EXEC DBMS_CLOUD_AI.ENABLE_DATA_ACCESS;
PL/SQL procedure successfully completed.データベースユーザーとしてログインし、AIプロファイルを作成および構成します。Select AIの前提条件の実行を確認して、AIプロファイルを構成します。narrateアクションを実行し、合成データを個別に生成します。
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"
次の例は、データ・アクセスを有効にした後の合成データ生成の成功を示しています。
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.親トピック: Select AIの使用例
例: Select AIフィードバック
これらの例では、DBMS_CLOUD_AI.FEEDBACKプロシージャの使用方法と、feedbackアクションに関連する様々なシナリオを使用してフィードバックを提供し、後続のSQL問合せの生成を改善する方法を示します。
開始する前に
「Select AIの前提条件の実行」を確認します。
例: 否定的なフィードバックの提供
次の例では、feedback_typeをnegativeとして使用し、SQL問合せを提供して、生成されたSQLをフィードバック(否定的なフィードバック)として修正する方法を示します。
OCI_FEEDBACK1という名前のAIプロファイルにフィードバックを追加するには、プロンプトを含むsql_textパラメータを指定してDBMS_CLOUD_AI.FEEDBACKプロシージャをコールします。属性について学習するには、FEEDBACKプロシージャを参照してください。次に、<profile_name>_FEEDBACK_VECINDEX$VECTAB表からcontent列およびattributes列を取得します。この表は、その特定のSQL問合せにリンクされています。フィードバック機能を初めて使用すると、Select AIによってこのベクトル表が自動的に作成されます。詳細は、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}例: 肯定的なフィードバックの提供
次の例では、feedback_typeをpositiveとして使用して、生成されたSQL (肯定的なフィードバック)に同意して確認する承認を示します。
この例では、問合せによって、指定されたプロンプトのv$mapped_sqlビューからsql_idが取得されます。詳細は、V_MAPPED_SQLを参照してください。
OCI_FEEDBACK1という名前のAIプロファイルにフィードバックを追加するには、sql_idパラメータを指定してDBMS_CLOUD_AI.FEEDBACKプロシージャをコールします。次に、<profile_name>_FEEDBACK_VECINDEX$VECTAB表からcontent列およびattributes列を取得します。この表は、その特定のSQL問合せにリンクされています。フィードバック機能を初めて使用すると、Select AIによってこのベクトル表が自動的に作成されます。詳細は、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}例: 生成されたSQLのフィードバックの追加または削除
次の例では、
DBMS_CLOUD_AI.FEEDBACKプロシージャのパラメータを指定して、生成されたSQLのフィードバックを追加または削除する方法を示します。この例では、sql_idおよびsql_textを他のパラメータとともに使用する方法を示します。
ノート
Select AIでは、
Select AIでは、
sql_idごとに1つのフィードバック・エントリのみ許可されます。同じsql_idに対して追加のフィードバックを提供すると、Select AIによって前のエントリが新しいエントリに置き換えられます。
パラメータの詳細は、FEEDBACKプロシージャを参照してください。
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');例: 最後のAI SQLでフィードバック処理を使用して否定的なフィードバックを提供
この例では、feedbackアクションを使用して、自然言語を使用して変更を提案することで、生成されたSQLを改善する方法を示します。
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" ASC例: 最後のAI SQLでフィードバック処理を使用して肯定的なフィードバックを提供
この例では、feedbackアクションを使用して、自然言語を使用して生成されたSQLを受け入れます。
--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_ID例: フィードバック・アクションをSQL_IDとともに使用してフィードバックを提供
この例では、SQL_IDをfeedbackアクションとともに使用して、生成された特定のSQL問合せに対するフィードバックを提供する方法を示します。SQL_IDを取得するには、v$MAPPED_SQL表を問い合せます。
-- 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
例: 問合せテキストでのフィードバック処理の使用
この例は、Select AIプロンプトを引用符で囲み、その後にフィードバックを含めることで、特定のSelect AI問合せのfeedbackアクションを示しています。
-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親トピック: Select AIの使用例
例: Select AIサマリー
これらの例は、summarizeアクションおよびDBMS_CLOUD_AI.SUMMARIZEファンクションの使用方法を示しています。また、この関数を使用してコンテンツのサマリー生成をカスタマイズします。
開始する前に
「Select AIの前提条件の実行」を確認します。
例: SQLコマンドラインでの要約処理の使用
次の例では、
SUMMARIZEをSelect AIアクションとして使用します。SQLコマンドラインでSELECT AI SUMMARIZE <TEXT>を使用して、入力テキストのサマリーを生成します。
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.ヒント:
SQL*Plusでは、一重引用符(
')は文字列デリミタとして扱われます。テキストに一重引用符が含まれている場合は、二重引用符('から'')で引用符をエスケープするか、q'[]'引用符メカニズムを使用してテキストを囲みます。テキストに空の二重引用符("")が含まれている場合は、q'[]'メカニズムを使用してテキストを囲みます。たとえば:SELECT AI SUMMARIZE q'[this's a text]';例: DBMS_CLOUD_AI.SUMMARIZEプロシージャを使用したサマリーの生成
これらの例は、DBMS_CLOUD_AI.SUMMARIZEプロシージャの様々なパラメータを使用したサマリーの生成を示しています。
location_uriパラメータとしてオブジェクト・ストレージ・リンクを指定し、DBMS_CLOUD_AI.SUMMARIZEを使用してクラウド・アカウント資格証明をcredential_nameとして指定することで、OCIオブジェクト・ストレージに格納されている3000以上のワード・テキストからサマリーを生成できます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;OCIオブジェクト・ストレージに格納されているテキストからサマリーを生成するもう1つの方法は、
contentパラメータを使用してDBMS_CLOUD.GET_OBJECTプロシージャをコールすることです。
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;例: ユーザー・プロンプト、最小単語数および最大単語数の指定による要約の生成
次の例では、次のパラメータを指定して、3000以上のワード・テキストのサマリーを生成する方法を示します。
user_prompt: サマリーは'記事のサマリー: 'で始める必要があります。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.例: ユーザー・プロンプト、最大単語数およびサマリー・スタイルの指定によるサマリーの生成
次の例では、次のパラメータを指定して、12000以上のワード・テキストのサマリーを生成する方法を示します。
user_prompt: サマリーは'記事のサマリー: 'で始める必要があります。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.例: ブックの要約の生成
この例では、35.66 MiBファイルを入力として渡してサマリーを生成する方法を示します。DBMS_CLOUD_AI.SUMMARIZE関数は、反復絞込みメソッドを使用してチャンクを処理します。詳細は、反復絞込みを参照してください。
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.親トピック: Select AIの使用例
例: AIプロファイルの表アクセスの制限
この例では、表アクセスを制限し、AIプロファイルのobject_listで指定された表のみを使用するようにLLMに指示する方法を示します。
enforce_object_listをtrueに設定して、LLMへの表アクセスを制限します。
データベースユーザーとして、AIプロファイルを作成および構成します。Select AIの前提条件の実行を確認して、AIプロファイルを構成します。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GOOGLE_ENFORCED',
attributes =>'{"provider": "google",
"credential_name": "GOOGLE_CRED",
"object_list": [{"owner": "ADB_USER", "name": "GENRE"},
{"owner": "ADB_USER", "name": "CUSTOMER"},
{"owner": "ADB_USER", "name": "PIZZA_SHOP"},
{"owner": "ADB_USER", "name": "STREAMS"},
{"owner": "ADB_USER", "name": "MOVIES"},
{"owner": "ADB_USER", "name": "ACTIONS"}],
"enforce_object_list" : "true"
}');
END;
/
PL/SQL procedure successfully completed.
EXEC DBMS_CLOUD_AI.set_profile('GOOGLE_ENFORCED');
PL/SQL procedure successfully completed.
select ai showsql please list the user tables;
RESPONSE
--------------------------------------------------------------------------------------------
SELECT 'ADB_USER.GENRE' AS TABLE_NAME FROM DUAL UNION ALL SELECT 'ADB_USER.CUSTOMER' AS
TABLE_NAME FROM DUAL UNION ALL SELECT 'ADB_USER.PIZZA_SHOP' AS TABLE_NAME FROM DUAL UNION
ALL SELECT 'ADB_USER.STREAMS' AS TABLE_NAME FROM DUAL UNION ALL SELECT 'ADB_USER.MOVIES'
AS TABLE_NAME FROM DUAL
--enforce_object_listをfalseに設定すると、LLMは、以前の知識に基づいて他の表およびビューを使用するように指示します。
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
親トピック: Select AIの使用例
例: 列に大/小文字の区別を指定
この例では、AIプロファイルのカラムに大文字と小文字の区別を設定する方法を示します。
大/小文字を区別しない問合せを取得するには、case_sensitive_valuesをfalseに設定します。
データベースユーザーとして、AIプロファイルを作成および構成します。Select AIの前提条件の実行を確認して、AIプロファイルを構成します。
BEGIN
DBMS_CLOUD_AI.create_profile(
profile_name =>'GOOGLE',
attributes =>'{"provider": "google",
"credential_name": "GOOGLE_CRED",
"object_list": [{"owner": "ADB_USER", "name": "GENRE"},
{"owner": "ADB_USER", "name": "CUSTOMER"},
{"owner": "ADB_USER", "name": "PIZZA_SHOP"},
{"owner": "ADB_USER", "name": "STREAMS"},
{"owner": "ADB_USER", "name": "MOVIES"},
{"owner": "ADB_USER", "name": "ACTIONS"}],
"case_sensitive_values" : "false"
}');
END;
/
PL/SQL procedure successfully completed.
-- With "case_sensitive_values" set to "false", LLM will give back case insensitive query.
select ai showsql how many people watch Inception;
RESPONSE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(DISTINCT c.CUSTOMER_ID) AS "COUNT"
FROM "ADB_USER"."CUSTOMER" c
JOIN "ADB_USER"."STREAMS" s ON c.CUSTOMER_ID = s.CUSTOMER_ID
JOIN "ADB_USER"."MOVIES" m ON s.MOVIE_ID = m.MOVIE_ID
WHERE UPPER(m.TITLE) = UPPER('Inception')case_sensitive_valuesがfalseに設定されていても、大/小文字を区別する問合せは二重引用符を使用して指定できます。
select ai showsql how many people watch "Inception";
RESPONSE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(DISTINCT c.CUSTOMER_ID) AS "COUNT"
FROM "ADB_USER"."CUSTOMER" c JOIN "ADB_USER"."STREAMS" s ON
c.CUSTOMER_ID = s.CUSTOMER_ID JOIN "ADB_USER"."MOVIES" m ON
s.MOVIE_ID = m.MOVIE_ID WHERE m.TITLE = 'Inception'
親トピック: Select AIの使用例