Select AIの使用例
OracleのSelect AIを、OpenAI、Cohere、Azure Open AI、OCI Generative AIなどの様々なAIプロバイダーと統合して、自然言語から直接SQLクエリを生成する方法をご紹介します。
これらの例では、一般的なSelect AIアクションを紹介し、それらのアクションを活用するために様々なAIプロバイダでプロファイルを設定する手順を示します。
例: AIアクションの選択
次の例は、SELECT AIで実行できるrunsql、showsql、narrate、chat、explainsqlなどのアクションを示しています。これらの例では、DBMS_CLOUD_AI.CREATE_PROFILEファンクションで設定されたAIプロバイダおよびプロファイル属性でshスキーマを使用します。
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.- 例: OpenAIを使用したAIの選択
この例では、OpenAIを使用して自然言語プロンプトからSQL文を生成する方法を示します。 - 例: Cohereを使用したAIの選択
この例は、Cohereを使用して自然言語プロンプトからSQL文を生成する方法を示しています。 - 例: Azure OpenAIサービスでのAIの選択
次の例は、APIキーを使用してAzure OpenAI Serviceへのアクセスを有効にする方法、またはAzure OpenAIサービス原則を使用してAIプロファイルを作成し、自然言語プロンプトからSQLを生成する方法を示しています。 - 例: OCI生成AIを使用したAIの選択
これらの例は、OCI APIキーまたはリソース・プリンシパルを使用してOCI生成AIにアクセスし、AIプロファイルを作成し、自然言語プロンプトからSQLを生成する方法を示しています。 - 例: 表および列コメントを使用したSQL問合せ生成の改善
この例では、データベース表および列のコメントによって、自然言語プロンプトからのSQL問合せの生成がどのように改善されるかを示します。
親トピック: Select AIを使用したデータベースとの自然言語対話
例: OpenAIを使用した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
--
SQL> EXEC DBMS_CLOUD.CREATE_CREDENTIAL('OPENAI_CRED', 'OPENAI', '<your api token>');
PL/SQL procedure successfully completed.
--
-- Create AI profile
--SQL> BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
'OPENAI',
'{"provider": "openai",
"credential_name": "OPENAI_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"conversation": "true"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('OPENAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"OPENAI"
--
-- Use AI
--
SQL> select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
SQL> select ai how many customers in San Francisco are married;
MARRIED_CUSTOMERS
-----------------
18
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS married_customers_count
FROM SH.CUSTOMERS c
WHERE c.CUST_CITY = 'San Francisco'
AND c.CUST_MARITAL_STATUS = 'Married'
SQL> select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
--------------------------------------------------------------------------------
The top 3 customers in San Francisco are:
1. Hector Colven - Total amount sold: $52,025.99
2. Milburn Klemm - Total amount sold: $50,842.28
3. Gavin Xie - Total amount sold: $48,677.18
SQL> select ai chat what is Autonomous Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous Database is a cloud-based database service provided by Oracle. It is
designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous Database uses machine learning
and automation to optimize performance, security, and availability, allowing us
ers to focus on their applications and data rather than database administration
tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomo
us Database provides high performance, scalability, and reliability, making it a
n ideal choice for modern cloud-based applications.
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
- 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
- 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
Remember to adjust the table and column names based on your actual schema if they differ from the example.
Feel free to ask if you have more questions related to SQL or database in general.
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('OPENAI');
PL/SQL procedure successfully completed.親トピック: Select AIの使用例
例: Cohereで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
--
SQL> EXEC DBMS_CLOUD.CREATE_CREDENTIAL('COHERE_CRED', 'COHERE', '<your api token>');
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for Cohere endpoint
--
SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api.cohere.ai',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--SQL> BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
'COHERE',
'{"provider": "cohere",
"credential_name": "COHERE_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "sales"},
{"owner": "SH", "name": "products"},
{"owner": "SH", "name": "countries"}]
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('COHERE');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"COHERE"
--
-- Use AI
--
SQL> select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('COHERE');
PL/SQL procedure successfully completed.親トピック: Select AIの使用例
例: Azure OpenAI Serviceを使用したAIの選択
次の例は、APIキーを使用してAzure OpenAI Serviceへのアクセスを有効にする方法、またはAzure OpenAIサービス原則を使用する方法、AIプロファイルを作成する方法、および自然言語プロンプトからSQLを生成する方法を示しています。
-- Create Credential for AI integration
--
SQL> EXEC DBMS_CLOUD.CREATE_CREDENTIAL('AZURE_CRED', 'AZUREAI', '<your api token>');
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for OpenAI endpoint
--SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => '<azure_resource_name>.openai.azure.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADMIN',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
SQL> BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
'AZUREAI',
'{"provider": "azure",
"azure_resource_name": "<azure_resource_name>",
"azure_deployment_name": "<azure_deployment_name>"
"credential_name": "AZURE_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"conversation": "true"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"AZUREAI"
--
-- Use AI
--
SQL> select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
SQL> select ai how many customers in San Francisco are married;
MARRIED_CUSTOMERS
-----------------
18
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS married_customers_count
FROM SH.CUSTOMERS c
WHERE c.CUST_CITY = 'San Francisco'
AND c.CUST_MARITAL_STATUS = 'Married'
SQL> select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
--------------------------------------------------------------------------------
The top 3 customers in San Francisco are:
1. Hector Colven - Total amount sold: $52,025.99
2. Milburn Klemm - Total amount sold: $50,842.28
3. Gavin Xie - Total amount sold: $48,677.18
SQL> select ai chat what is Autonomous Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous Database is a cloud-based database service provided by Oracle. It is
designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous Database uses machine learning
and automation to optimize performance, security, and availability, allowing us
ers to focus on their applications and data rather than database administration
tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomo
us Database provides high performance, scalability, and reliability, making it a
n ideal choice for modern cloud-based applications.
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
- 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
- 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
Remember to adjust the table and column names based on your actual schema if they differ from the example.
Feel free to ask if you have more questions related to SQL or database in general.
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.例: Azure OpenAI Serviceの原則を使用した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
--SQL> BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
'AZUREAI',
'{"provider": "azure",
"credential_name": "AZURE$PA",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"azure_resource_name": "<azure_resource_name>",
"azure_deployment_name": "<azure_deployment_name>"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"AZUREAI"
--
-- Use AI
--
SQL> select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
SQL> select ai how many customers in San Francisco are married;
MARRIED_CUSTOMERS
-----------------
18
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS married_customers_count
FROM SH.CUSTOMERS c
WHERE c.CUST_CITY = 'San Francisco'
AND c.CUST_MARITAL_STATUS = 'Married'
SQL> select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
--------------------------------------------------------------------------------
The top 3 customers in San Francisco are:
1. Hector Colven - Total amount sold: $52,025.99
2. Milburn Klemm - Total amount sold: $50,842.28
3. Gavin Xie - Total amount sold: $48,677.18
SQL> select ai chat what is Autonomous Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous Database is a cloud-based database service provided by Oracle. It is
designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous Database uses machine learning
and automation to optimize performance, security, and availability, allowing us
ers to focus on their applications and data rather than database administration
tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomo
us Database provides high performance, scalability, and reliability, making it a
n ideal choice for modern cloud-based applications.
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
- 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
- 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
Remember to adjust the table and column names based on your actual schema if they differ from the example.
Feel free to ask if you have more questions related to SQL or database in general.
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.親トピック: Select AIの使用例
例: OCI生成AIを使用したAIの選択
これらの例は、OCI APIキーまたはリソース・プリンシパルを使用してOCI生成AIにアクセスし、AIプロファイルを作成し、自然言語プロンプトからSQLを生成する方法を示しています。
ノート
model_nameを指定しない場合、OCI生成AIではmeta.llama-3-70b-instructがデフォルト・モデルとして使用されます。パラメータの詳細は、「プロファイル属性」を参照してください。
-- Create Credential with OCI API key
--
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'GENAI_CRED',
user_ocid => 'ocid1.user.oc1..aaaa...',
tenancy_ocid => 'ocid1.tenancy.oc1..aaaa...',
private_key => '<your_api_key>',
fingerprint => '<your_fingerprint>'
);
END;
/
--
-- Create AI profile
--
SQL> BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
'GENAI',
'{"provider": "oci",
"credential_name": "GENAI_CRED"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"GENAI"
--
-- Use AI
--
SQL> select ai chat what is Autonomous Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous Database is a cloud-based database service provided by Oracle. It is
designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous Database uses machine learning
and automation to optimize performance, security, and availability, allowing us
ers to focus on their applications and data rather than database administration
tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomo
us Database provides high performance, scalability, and reliability, making it a
n ideal choice for modern cloud-based applications.
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('GENAI');
PL/SQL procedure successfully completed.例5-1 OCI生成AIリソース・プリンシパルを使用した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を指定しない場合、OCI生成AIではmeta.llama-3-70b-instructがデフォルト・モデルとして使用されます。パラメータの詳細は、「プロファイル属性」を参照してください。
-- Connect as Administrator user and enable OCI resource principal.
BEGIN
DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(provider => 'OCI');
END;
/
--
-- Create AI profile
--SQL>BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
'GENAI',
'{"provider": "oci",
"credential_name": "OCI$RESOURCE_PRINCIPAL"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL>EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"GENAI"
-- Use AI
SQL> select ai chat what is Autonomous Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous Database is a cloud-based database service provided by Oracle. It is
designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous Database uses machine learning
and automation to optimize performance, security, and availability, allowing us
ers to focus on their applications and data rather than database administration
tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomo
us Database provides high performance, scalability, and reliability, making it a
n ideal choice for modern cloud-based applications.
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('GENAI');
PL/SQL procedure successfully completed.例: LLAMAモデルを使用したOCI生成AIを使用したAIの選択
この例では、OCI生成AIからのLLAMAモデルのchat機能を示します。これは、2つのプロンプトによってモデルの機能を強調します。つまり、映画の推奨事項を顧客にわかりやすい電子メールを生成し、ロッククライミングの概要段落を生成します。
SQL> BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'GENAI_CRED',
user_ocid => 'ocid1.user.oc1..aaa',
tenancy_ocid => 'ocid1.tenancy.oc1..aaa',
private_key => '<your_api_key>',
fingerprint => '<your_fingerprint>'
);
END;
/
PL/SQL procedure successfully completed.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
'GENAI',
'{"provider": "oci",
"model": "meta.llama-2-70b-chat",
"oci_runtimetype":"LLAMA"}');
END;
/
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_CLOUD_AI.SET_ATTRIBUTE(
'GENAI', 'credential_name', 'GENAI_CRED');
END;
/
PL/SQL procedure successfully completed.
SQL > BEGIN
DBMS_CLOUD_AI.SET_ATTRIBUTE(
'GENAI', 'oci_compartment_id', 'ocid1.compartment.oc1...');
END;
/
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');
PL/SQL procedure successfully completed.
SQL> set linesize 150
SQL> SELECT AI chat Generate a friendly email to customer Gilbert Lee with two action-thriller movie recommendations available through our MovieStream service;
SQL>
RESPONSE
------------------------------------------------------------------------------------------------------------------------------------------------------
.
Subject: Action-packed movie recommendations for you!
Dear Gilbert,
I hope this email finds you well! I wanted to reach out to you today to recommend two action-thriller movies that are currently available on our Movie
Stream service. I think you'll really enjoy them!
The first movie I recommend is "John Wick" starring Keanu Reeves. This movie follows the story of a retired hitman who seeks vengeance against a power
ful crime lord and his army of assassins. The action scenes are intense and non-stop, and Keanu Reeves delivers an outstanding performance.
RESPONSE
------------------------------------------------------------------------------------------------------------------------------------------------------
The second movie I recommend is "Mission: Impossible - Fallout" starring Tom Cruise. This movie follows Ethan Hunt and his team as they try to prevent
a global catastrophe. The action scenes are heart-stopping and the stunts are truly impressive. Tom Cruise once again proves why he's one of the grea
test action stars of all time.
Both of these movies are sure to keep you on the edge of your seat and provide plenty of thrills and excitement. They're available to stream now on Mo
vieStream, so be sure to check them out!
If you have any questions or need assistance with MovieStream, please don't hesitate to reach out to me. I'm always here to help.
Thank you for being a valued customer, and I hope you enjoy the movies!
RESPONSE
------------------------------------------------------------------------------------------------------------------------------------------------------
Best regards,
[Your Name]
MovieStream Customer Service
SQL> SELECT AI chat Write an enthusiastic introductory paragraph on how to get started with rock climbing with Athletes as the target audience;
RESPONSE
------------------------------------------------------------------------------------------------------------------------------------------------------
Rock climbing is an exhilarating and challenging sport that's perfect for athletes looking to push their limits and test their strength, endurance, an
d mental toughness. Whether you're a seasoned athlete or just starting out, rock climbing offers a unique and rewarding experience that will have you
hooked from the very first climb. With its combination of physical and mental challenges, rock climbing is a great way to build strength, improve flex
ibility, and develop problem-solving skills. Plus, with the supportive community of climbers and the breathtaking views from the top of the climb, you
'll be hooked from the very first climb. So, if you're ready to take on a new challenge and experience the thrill of adventure, then it's time to get
started with rock climbing!デフォルト・モデルでのOCI生成AIの使用
次の例では、デフォルトのOCI生成AIチャット・モデルmeta.llama-3-70b-instructを使用します。modelを指定しない場合、OCI生成AIはmeta.llama-3-70b-instructをデフォルト・モデルとして使用します。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_DEFAULT',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADMIN"}]
}');
END;
/チャット・モデルでのOCI生成AIの使用
次の例では、OCI生成AIチャット・モデルとしてcohere.command-r-plusを使用します。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_COHERE_COMMAND_R_PLUS',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADMIN"}],
"model": "cohere.command-r-plus"
}');
END;
/チャット・モデル・エンドポイントIDでのOCI生成AIの使用
次の例では、modelのかわりにOCI生成AIチャット・モデルのエンドポイントIDを指定する方法を示します。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_CHAT_ENDPOINT',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADMIN"}],
"oci_endpoint_id": "<endpoint_id>",
"oci_apiformat": "GENERIC"
}');
END;
/チャット・モデルOCIDでのOCI生成AIの使用
この例では、OCI生成AIチャット・モデルOCIDをmodelとして指定する方法を示します。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_CHAT_OCID',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADMIN"}],
"model": "<model_ocid>",
"oci_apiformat": "COHERE"
}');
END;
/生成モデルでのOCI生成AIの使用
この例では、cohere.commandなどのOCI生成AI生成モデルをmodelとして指定する方法を示します。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_COHERE_COMMAND',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADMIN"}],
"model": "cohere.command"
}');
END;
/ 生成モデル・エンドポイントIDでのOCI生成AIの使用
この例では、modelのかわりにOCI生成AI生成モデルのエンドポイントIDを使用する方法を示します。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_GENTEXT_ENDPOINT',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADMIN"}],
"oci_endpoint_id": "<endpoint_id>"
"oci_runtimetype": "COHERE"
}');
END;
/生成モデルOCIDでのOCI生成AIの使用
この例では、OCI生成AI生成モデルOCIDをmodelとして指定する方法を示します。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_GENTEXT_OCID',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADMIN"}],
"model": "<model_ocid>"
"oci_runtimetype": "LLAMA"
}');
END;
/親トピック: Select AIの使用例
例: 表および列コメントを使用したSQL問合せ生成の改善
この例では、データベース表および列のコメントによって、自然言語プロンプトからのSQL問合せの生成がどのように改善されるかを示します。
この例では、Azure OpenAI ServiceがAIプロバイダとして機能します。
DBMS_CLOUD_AI.CREATE_PROFILEファンクションの"comments":"true"パラメータは、コメントが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親トピック: Select AIの使用例