Examples of Using Select AI

Explore integrating Oracle's Select AI with various supported AI providers to generate, run, and explain SQL from natural language prompts or chat with the LLM.

Example: Select AI Actions

These examples illustrate common Select AI actions.

The following example illustrates actions such as runsql (the default), showsql, narrate, chat, explainsql, feedback, and summarize that you can perform with SELECT AI. These examples use the sh schema with AI provider and profile attributes specified in the DBMS_CLOUD_AI.CREATE_PROFILE function. Use Select AI actions after setting your AI profile by using the DBMS_CLOUD_AI.SET_PROFILE procedure in the current session.

To generate a summary of your text, use 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.

Example: Set Up and Use Select AI with RAG

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.

The setup concludes with creating an AI profile that uses the vector index to enhance LLM responses. Finally, this example uses the Select AI narrate action, which returns a response that has been enhanced using information from the specified vector database.

The following example demonstrates building and querying vector index in 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)

Example: Select AI with In-database Transformer Models

This example demonstrates how you can import a pretrained transformer model that is stored in Oracle object storage into your Oracle Database 23ai instance and then use the imported in-database model in Select AI profile to generate vector embeddings for document chunks and user prompts.

To use in-database transformer models in your Select AI profile, be sure you have:

  • your pretrained model imported in your Oracle Database 23ai instance.

  • optionally, access to Oracle object storage.

Import a Pretrained Transformer Model into your Oracle Database 23ai From Oracle Object Storage

Review the steps in Import Pretrained Models in ONNX Format for Vector Generation Within the Database and the blog Pre-built Embedding Generation model for Oracle AI Database 26ai to import a pretrained transformer model into your database.

The following example shows how to import a pretained transformer model from Oracle object storage into your database and then view the imported model.

- 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';
Use In-database Transformer Models in Select AI Profiles

These examples illustrate how to use in-database transformer models within a Select AI profile. One profile is configured only for generating vector embeddings, while the other supports both Select AI actions and vector index creation.

Review Requirements to Configure DBMS_CLOUD_AI Package to complete the prerequisites.

The following is an example for generating vector embeddings only:

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
     profile_name => 'EMBEDDING_PROFILE',
     attributes   => '{"provider" : "database",
                       "embedding_model": "MY_ONNX_MODEL"}'
  );
END;
/

The following is an example for general Select AI actions and vector index generation where you can specify a supported AI provider. This example uses OCI Gen AI profile and credentials. See for list of supported providers. However, if you want to use in-database transformer model for generating vector embeddings, then use "database: <MY_ONNX_MODEL>" in embedding_model attribute: 

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;
/
Use Select AI with an In-database Transformer Model from Another Schema

This example demonstrates how to use Select AI with an in-database transformer model if another schema owner owns the model. Specify schema_name.object_name as the fully qualified name of the model in embedding_model attribute. If the current user is the schema owner or owns the model, you can omit the schema name.

Be sure to have the following privileges if a different schema owner owns the model:
  • CREATE ANY MINING MODEL system privilege
  • SELECT ANY MINING MODEL system privilege
  • SELECT MINING MODEL object privilege on the specific model

To grant a system privilege, you must either have been granted the system privilege with the ADMIN OPTION or have been granted the GRANT ANY PRIVILEGE system privilege.

See System Privileges for Oracle Machine Learning for SQL to review the privileges.

The following statements allow ADB_USER1 to score data and view model details in any schema as long as SELECT access has been granted to the data. However, ADB_USER1 can only create models in the ADB_USER1 schema. 

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;
/

The following example shows how you can specify case sensitive model object name:

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
     profile_name => 'OCI_GENAI',
     attributes   => '{"provider": "oci",
                       "credential_name": "GENAI_CRED",
                       "model": "meta.llama-3.3-70b-instruct",
                       "vector_index_name": "MY_INDEX",
                       "embedding_model": "database: \"adb_user1\".\"my_model\""}'
  );
END;
/
End-to-end Examples with Different AI Providers

These examples demonstrate end-to-end steps for using in-database transformer model with Select AI RAG. One profile uses database as the provider exclusively created for generating embedding vectors while the other profile uses oci as the provider created for Select AI actions as well as vector index.

Review Requirements to Configure DBMS_CLOUD_AI Package to complete the prerequisites. 

--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.

This example uses oci as the 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)

Example: Generate Synthetic Data

This example explores how you can generate synthetic data mimicking the characteristics and distribution of real data.

Note:

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.

The following example shows how to create a few tables in your schema, use OCI Generative AI as your AI provider to create an AI profile, synthesize data into those tables using the DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA function, and query or generate responses to natural language prompts with 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
Example: Generate Synthetic Data for Multiple Tables

After you create and set your AI provider profile, use the DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA to generate data for multiple tables. You can query or use Select AI to respond to the natural language prompts. 

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
Example: Guide Synthetic Data Generation with Sample Rows

To guide AI service in generating synthetic data, you can randomly select existing records from a table. For instance, by adding {"sample_rows": 5} to the params argument, you can send 5 sample rows from a table to the AI provider. This example generates 10 additional rows based on the sample rows from the Transactions table. 

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;
/
Example: Customize Synthetic Data Generation with User Prompts

The user_prompt argument enables you to specify additional rules or requirements for data generation. This can be applied to a single table or as part of the object_list argument for multiple tables. For example, in the following calls to DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA, the prompt instructs the AI to generate synthetic data on movies released in 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;
/
Example: Improve Synthetic Data Quality by Using Table Statistics

If a table has column statistics or is cloned from a database that includes metadata, Select AI can use these statistics to generate data that closely resembles or is consistent with the original data.

For NUMBER columns, the high and low values from the statistics guide the value range. For instance, if the SALARY column in the original EMPLOYEES table ranges from 1000 to 10000, the synthetic data for this column will also fall within this range.

For columns with distinct values, such as a STATE column with values CA, WA, and TX, the synthetic data will use these specific values. You can manage this feature using the {"table_statistics": true/false} parameter. By default, the table statistics are enabled. 

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;
/
Example: Use Column Comments to Guide Data Generation

If column comments exist, Select AI automatically includes them to provide additional information for the LLM during data generation. For example, a comment on the Status column in a Transaction table might list allowed values such as successful, failed, pending, canceled, and need manual check. You can also add comments to further explain the column, giving AI services more precise instructions or hints for generating accurate data. By default, comments are disabled. See Optional Parameters for more details. 

-- 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;
/
Example: Set Unique Values in Synthetic Data Generation

When generating large amounts of synthetic data with LLMs, duplicate values are likely to occur. To prevent this, set up a unique constraint on the relevant column. This ensures that Select AI ignores rows with duplicate values in the LLM response. Additionally, to restrict values for certain columns, you can use the user_prompt or add comments to specify the allowed values, such as limiting a STATE column to CA, WA, and 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'
/
Example: Enhance Synthetic Data Generation by Parallel Processing

To reduce runtime, Select AI splits synthetic data generation tasks into smaller chunks for tables without primary keys or with numeric primary keys. These tasks run in parallel, interacting with the AI provider to generate data more efficiently. The Degree of Parallelism (DOP) in your database, influenced by your Autonomous AI Database on Dedicated Exadata Infrastructure service level and ECPU or OCPU settings, determines the number of records each chunk processes. Running tasks in parallel generally improves performance, especially when generating large amounts of data across many tables. To manage the parallel processing of synthetic data generation, set priority as an optional parameter. See Optional Parameters.

Example: Enable or Disable Data Access

This example illustrates how administrators can control data access and prevent Select AI from sending actual schema tables to the LLM.

Disabling Data Access

To restrict access to schema tables, log in as an administrator and run the following procedure.

EXEC DBMS_CLOUD_AI.DISABLE_DATA_ACCESS;
 
PL/SQL procedure successfully completed.

Disabling data access limits Select AI's narrate action and Synthetic Data Generation. The narrate action and synthetic data generation raise an error.

Log in as database user, create and configure your AI profile. Review to configure your AI profile. 

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

The following example shows the errors that are triggered when you try to generate synthetic data.

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
Enabling Data Access

The following example shows enabling data access. Log in as an administrator and run the following procedure:

EXEC DBMS_CLOUD_AI.ENABLE_DATA_ACCESS;
 
PL/SQL procedure successfully completed.

Log in as database user, create and configure your AI profile. Review to configure your AI profile. Run narrate action and separately generate synthetic data. 

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"

The following example shows successful synthetic data generation after enabling data access.

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.