使用 Select AI 的範例
範例：選取 AI 動作
這些範例說明常見的 Select AI 動作。
下列範例說明可以使用 SELECT AI 執行的動作，例如 runsql (預設值)、showsql、narrate、chat、explainsql、feedback 和 summarize。這些範例使用 sh 綱要搭配 AI 提供者，以及 DBMS_CLOUD_AI.CREATE_PROFILE 函數中指定的設定檔屬性。在目前階段作業中使用 DBMS_CLOUD_AI.SET_PROFILE 程序設定 AI 設定檔之後，請使用「選取 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.範例：設定並使用含 RAG 的 Select AI
This example guides you through setting up credentials, configuring network access, and creating a vector index for integrating OCI Generative AI vector store cloud services with OpenAI using Oracle Autonomous AI Database.
這些設定最後會建立一個 AI 設定檔，該設定檔使用向量索引來增強 LLM 回應。最後，此範例使用 Select AI narrate 動作，此動作會傳回使用指定向量資料庫資訊增強的回應。
以下範例示範在 Oracle Database 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)
範例：使用資料庫內轉換器模型選取 AI
此範例示範如何將儲存在 Oracle 物件儲存中的預先訓練轉換器模型匯入 Oracle Database 23ai 執行處理，然後使用 Select AI 設定檔中的匯入資料庫內模型產生文件區塊和使用者提示的向量內嵌。
若要在 Select AI 設定檔中使用資料庫內變壓器模型，請確定您具有：
-
您在 Oracle Database 23ai 執行處理中匯入的預先訓練模型。
-
(選擇性) 存取 Oracle 物件儲存體。
從 Oracle Object Storage 將預先訓練的變壓器模型匯入您的 Oracle Database 23ai
請檢閱在資料庫內以 ONNX 格式匯入用於向量生成的預先訓練模型和部落格針對 Oracle AI Database 26ai 預先建置的內嵌生成模型中的步驟，以將預先訓練的變壓器模型匯入您的資料庫。
下列範例顯示如何將預先取得的轉換器模型從 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 設定檔中使用資料庫內變壓器模型。一個設定檔僅設定用於產生向量內嵌，而另一個設定檔則同時支援 Select AI 動作和向量索引建立。
請參閱設定 DBMS_CLOUD_AI 套裝程式的需求以完成先決條件。
以下為僅產生向量內嵌的範例：
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'EMBEDDING_PROFILE',
attributes => '{"provider" : "database",
"embedding_model": "MY_ONNX_MODEL"}'
);
END;
/以下為一般 Select AI 動作和向量索引產生範例，您可以在其中指定支援的 AI 提供程式。此範例使用 OCI Gen AI 設定檔和證明資料。如需支援提供者的清單，請參閱 。不過，如果您想要使用資料庫內變壓器模型來產生向量內嵌，請在 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 的端對端步驟。一個設定檔使用 database 作為專門用於產生內嵌向量的 provider，而另一個設定檔使用 oci 作為為 Select AI 動作和向量索引建立的 provider。
請參閱設定 DBMS_CLOUD_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)範例：產生合成資料
此範例探討如何產生合成資料，以模擬真實資料的特性和分佈。
附註：
Support for Synthetic Data Generation is available in Oracle Database 19c starting with version 19.29, and in Oracle Database 26ai starting with version 23.26.下列範例顯示如何在綱要中建立幾個表格、使用 OCI Generative AI 作為 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 引數，您可以將 5 個範例資料列從表格傳送給 AI 提供者。此範例會根據 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 時，提示會指示 AI 在 2009 年發行的影片上產生綜合資料。
-- Definition for the Movie table CREATE TABLE Movie
CREATE TABLE Movie (
movie_id INT PRIMARY KEY,
title VARCHAR(100),
release_date DATE,
genre VARCHAR(50),
director_id INT,
FOREIGN KEY (director_id) REFERENCES Director(director_id)
);
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_name => 'Movie',
owner_name => 'ADB_USER',
record_count => 10,
user_prompt => 'all movies are released in 2009',
params => '{"sample_rows":5}'
);
END;
/
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_list => '[{"owner": "ADB_USER", "name": "Director","record_count":5},
{"owner": "ADB_USER", "name": "Movie_Actor","record_count":5},
{"owner": "ADB_USER", "name": "Actor","record_count":10},
{"owner": "ADB_USER", "name": "Movie","record_count":5,"user_prompt":"all movies are released in 2009"}]'
);
END;
/範例：使用表格統計資料改善合成資料品質
如果表格有資料欄統計資料，或是從包含描述資料的資料庫複製，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) 會受到專用 Exadata 基礎架構上的自治式 AI 資料庫服務層級和 ECPU 或 OCPU 設定值的影響，決定每個區塊處理作業的記錄數目。平行執行工作通常會改善效能，尤其是在跨多個表格產生大量資料時。若要管理合成資料產生的平行處理，請將 priority 設為選擇性參數。請參閱可選參數。
範例：啟用或停用資料存取
本範例說明管理員如何控制資料存取，以及防止 Select AI 將實際結構表傳送至 LLM。
停用資料存取
若要限制對綱要表格的存取，請以管理員的身分登入，然後執行下列程序。
EXEC DBMS_CLOUD_AI.DISABLE_DATA_ACCESS;
PL/SQL procedure successfully completed.
停用資料存取限制 選取 AI 的 narrate 動作和合成資料產生。narrate 動作和合成資料產生會產生錯誤。
以資料庫使用者身分登入，建立並設定您的 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 設定檔。請參閱 以設定您的 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.For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.
取得 Oracle 支援
Oracle 客戶對於 Oracle 支援服務的取得及使用，應以其 Oracle 訂單中針對適用服務所指定的條款與條件為依據。