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: Select AI with OCI Generative AI

These examples show how you can access OCI Generative AI using your OCI API key or Resource Principal, create an AI profile, and generate, run, and explain SQL from natural language prompts or chat using the OCI Generative AI LLMs.

Note: If you do not specify the model_name parameter, OCI Generative AI uses the default model as per the table in Select your AI Provider and LLMs. To learn more about the parameters, see Profile Attributes.

-- Create Credential with OCI API key
--
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'GENAI_CRED',
    user_ocid       => 'ocid
1.user.oc1..aaaa...',
    tenancy_ocid    => 'ocid1.tenancy.oc1..aaaa...',
    private_key     => '`<your_api_key>`',
    fingerprint     => '`<your_fingerprint>`'
  );
END;
/

--
-- Create AI profile
--
BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
      profile_name =>'GENAI',
      attributes   =>'{"provider": "oci",
        "credential_name": "GENAI_CRED",
		"object_list": [{"owner": "SH", "name": "customers"},
                            {"owner": "SH", "name": "countries"},
                            {"owner": "SH", "name": "supplementary_demographics"},
                           {"owner": "SH", "name": "profits"},
                           {"owner": "SH", "name": "promotions"},
                           {"owner": "SH", "name": "products"}]
       }');
END;
/

PL/SQL procedure successfully completed.

--
-- Enable AI profile in current session
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');

PL/SQL procedure successfully completed.

--
-- Get Profile in current session
--
SELECT DBMS_CLOUD_AI.get_profile() from dual;

DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"GENAI"

--
-- Use AI
--
SQL> select ai how many customers exist;
Number of Customers
-------------------
              55500

SQL> select ai how many customers in San Francisco are married;
COUNT(DISTINCTC."CUST_ID")
--------------------------
                        28

SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(DISTINCT c."CUST_ID")
FROM "SH"."CUSTOMERS" c
JOIN "SH"."COUNTRIES" co ON c."COUNTRY_ID" = co."COUNTRY_ID"
WHERE c."CUST_CITY" = 'San Francisco' AND c."CUST_MARITAL_STATUS" = 'married'

SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
Here is the Oracle SQL query to find the number of customers in San Francisco who are married:


    SELECT COUNT(*)
    FROM "SH"."CUSTOMERS" c
    WHERE c."CUST_CITY" = 'San Francisco'
    AND c."CUST_MARITAL_STATUS" = 'Married';


Explanation:
* We use the `COUNT(*)` aggregate function to count the number of rows that match the conditions.
* We specify the table alias `c` for the `"SH"."CUSTOMERS"` table to make the query more readable.
* We use the `WHERE` clause to filter the rows based on two conditions:
    + `c."CUST_CITY" = 'San Francisco'`: We filter the rows where the customer's city is San Francisco.
    + `c."CUST_MARITAL_STATUS" = 'Married'`: We filter the rows where the customer's marital status is Married.
* The double quotes around the table and column names are used to make the query case-sensitive, as required in Oracle SQL.
Note: The column names and table names are assumed to be case-sensitive, so we enclose them in double quotes. If the column names and table names are not case-sensitive, you can remove the double quotes.
Collapse
 This snippet was truncated for display; see it in full

SQL> select ai narrate how many customers in San Francisco are married;

RESPONSE
--------------------------------------------------------
There are 28 customers in San Francisco who are married.


SQL> select ai chat what is Autonomous AI Database;

RESPONSE
An Autonomous AI Database is a type of database that uses artificial intelligence (AI) and machine learning (ML) to automate many of the administrative and maintenance tasks typically performed by a database administrator (DBA). This allows the database to manage itself, without human intervention, to a large extent.
Autonomous AI Databases are designed to be self-driving, self-securing, and self-repairing, which means they can:
1. Automate administrative tasks: Such as provisioning, patching, upgrading, and tuning, which frees up DBAs to focus on higher-level tasks.
2. Optimize performance: By automatically adjusting parameters, indexing, and caching to ensure optimal performance and efficiency.
3. Detect and respond to security threats: By using AI-powered security tools to identify and respond to potential security threats in real-time.
4. Heal itself: By automatically detecting and repairing errors, corruption, or other issues that may arise.
5. Scale up or down: To match changing workload demands, without the need for manual intervention.
The benefits of Autonomous AI Databases include:
1. Increased efficiency: By automating routine tasks, DBAs can focus on more strategic activities.
2. Improved performance: Autonomous AI Databases can optimize performance in real-time, leading to faster query response times and better overall system performance.
3. Enhanced security: AI-powered security tools can detect and respond to threats more quickly and effectively than human administrators.
4. Reduced costs: By minimizing the need for manual intervention, Autonomous AI Databases can help reduce labor costs and improve resource utilization.
5. Improved reliability: Autonomous AI Databases can detect and repair errors more quickly, reducing downtime and improving overall system reliability.
Oracle Autonomous AI Database is a popular example of an Autonomous AI Database, which was introduced in 2018. Other vendors, such as Amazon, Microsoft, and Google, also offer Autonomous AI Database services as part of their cloud offerings.
In summary, Autonomous AI Databases are designed to be self-managing, self-optimizing, and self-healing, which can lead to improved performance, security, and efficiency, while reducing costs and administrative burdens.


--
--Clear the profile
--
BEGIN
   DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('GENAI');

PL/SQL procedure successfully completed.

Example: Select AI with OCI Generative AI Resource Principal

To use resource principal with OCI Generative AI, Oracle Cloud Infrastructure tenancy administrator must grant access for Generative AI resources to a dynamic group. See Perform Prerequisites to Use Resource Principal with Autonomous AI Database to provide access to a dynamic group.

Set the required policies to obtain access to all Generative AI resources. See Getting Access to Generative AI to know more about Generative AI policies.

Connect as an administrator and enable OCI resource principal. See ENABLE_PRINCIPAL_AUTH Procedure to configure the parameters.

Note: If you do not specify the model_name parameter, OCI Generative AI uses the default model as per the table in Select your AI Provider and LLMs. To learn more about the parameters, see Profile Attributes.

-- Connect as Administrator user and enable OCI resource principal.
BEGIN
  DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(provider  => 'OCI');
END;
/

--
-- Create AI profile
--
BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
      profile_name =>'GENAI',
      attributes =>'{"provider": "oci",
        "credential_name": "OCI$RESOURCE_PRINCIPAL",
		"object_list": [{"owner": "SH", "name": "customers"},
                            {"owner": "SH", "name": "countries"},
                            {"owner": "SH", "name": "supplementary_demographics"},
                           {"owner": "SH", "name": "profits"},
                           {"owner": "SH", "name": "promotions"},
                           {"owner": "SH", "name": "products"}]
       }');
END;
/

PL/SQL procedure successfully completed.

--
-- Enable AI profile in current session
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');

PL/SQL procedure successfully completed.

--
-- Get Profile in current session
--
SELECT DBMS_CLOUD_AI.get_profile() from dual;

DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"GENAI"

--
-- Use AI
--
SQL> select ai how many customers exist;
Number of Customers
-------------------
              55500

SQL> select ai how many customers in San Francisco are married;
COUNT(DISTINCTC."CUST_ID")
--------------------------
                        28

SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(DISTINCT c."CUST_ID")
FROM "SH"."CUSTOMERS" c
JOIN "SH"."COUNTRIES" co ON c."COUNTRY_ID" = co."COUNTRY_ID"
WHERE c."CUST_CITY" = 'San Francisco' AND c."CUST_MARITAL_STATUS" = 'married'

SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
Here is the Oracle SQL query to find the number of customers in San Francisco who are married:

    SELECT COUNT(*)
    FROM "SH"."CUSTOMERS" c
    WHERE c."CUST_CITY" = 'San Francisco'
    AND c."CUST_MARITAL_STATUS" = 'Married';

Explanation:
* We use the `COUNT(*)` aggregate function to count the number of rows that match the conditions.
* We specify the table alias `c` for the `"SH"."CUSTOMERS"` table to make the query more readable.
* We use the `WHERE` clause to filter the rows based on two conditions:
    + `c."CUST_CITY" = 'San Francisco'`: We filter the rows where the customer's city is San Francisco.
    + `c."CUST_MARITAL_STATUS" = 'Married'`: We filter the rows where the customer's marital status is Married.
* The double quotes around the table and column names are used to make the query case-sensitive, as required in Oracle SQL.
Note: The column names and table names are assumed to be case-sensitive, so we enclose them in double quotes. If the column names and table names are not case-sensitive, you can remove the double quotes.
Collapse
 This snippet was truncated for display; see it in full

SQL> select ai narrate how many customers in San Francisco are married;

RESPONSE
--------------------------------------------------------
There are 28 customers in San Francisco who are married.


SQL> select ai chat what is Autonomous AI Database;

RESPONSE
An Autonomous AI Database is a type of database that uses artificial intelligence (AI) and machine learning (ML) to automate many of the administrative and maintenance tasks typically performed by a database administrator (DBA). This allows the database to manage itself, without human intervention, to a large extent.
Autonomous AI Databases are designed to be self-driving, self-securing, and self-repairing, which means they can:

1. Automate administrative tasks: Such as provisioning, patching, upgrading, and tuning, which frees up DBAs to focus on higher-level tasks.
2. Optimize performance: By automatically adjusting parameters, indexing, and caching to ensure optimal performance and efficiency.
3. Detect and respond to security threats: By using AI-powered security tools to identify and respond to potential security threats in real-time.
4. Heal itself: By automatically detecting and repairing errors, corruption, or other issues that may arise.
5. Scale up or down: To match changing workload demands, without the need for manual intervention.
The benefits of Autonomous AI Databases include:
1. Increased efficiency: By automating routine tasks, DBAs can focus on more strategic activities.
2. Improved performance: Autonomous AI Databases can optimize performance in real-time, leading to faster query response times and better overall system performance.
3. Enhanced security: AI-powered security tools can detect and respond to threats more quickly and effectively than human administrators.
4. Reduced costs: By minimizing the need for manual intervention, Autonomous AI Databases can help reduce labor costs and improve resource utilization.
5. Improved reliability: Autonomous AI Databases can detect and repair errors more quickly, reducing downtime and improving overall system reliability.
Oracle Autonomous AI Database is a popular example of an Autonomous AI Database, which was introduced in 2018. Other vendors, such as Amazon, Microsoft, and Google, also offer Autonomous AI Database services as part of their cloud offerings.
In summary, Autonomous AI Databases are designed to be self-managing, self-optimizing, and self-healing, which can lead to improved performance, security, and efficiency, while reducing costs and administrative burdens.

--
--Clear profile
--
BEGIN
   DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('GENAI');

PL/SQL procedure successfully completed.

Example: Specify a Different Region for OCI Generative AI Profile

This example shows specifying an OCI Generative AI supported region in your profile. See Regions with Generative AI.

BEGIN
	  DBMS_CLOUD_AI.CREATE_PROFILE(
		  profile_name => 'GENAI',
		  attributes   =>'{"provider": "oci",
		    "object_list": [
                        {"owner": "SH", "name": "customers"},
                        {"owner": "SH", "name": "countries"},
                        {"owner": "SH", "name": "supplementary_demographics"},
                        {"owner": "SH", "name": "profits"},
                        {"owner": "SH", "name": "promotions"},
                        {"owner": "SH", "name": "products"}
                        ]
			"region":"eu-frankfurt-1",
			"model": "meta.llama-3.3-70b-instruct",
            "credential_name": "GENAI_CRED",
            "oci_compartment_id": "ocid1.compartment.oc1..."}');
END;
/

Example: Select AI with OCI Generative AI Using Grok Model

This example demonstrates how you can use xAI’s Grok models with OCI Generative AI support. Review Perform Prerequisites for Select AI.

--Create your AI Profile

BEGIN
    DBMS_CLOUD_AI.create_profile(
            profile_name =>'grok',
            attributes   =>'{"provider": "oci",
              "credential_name": "OCI_CRED",
              "object_list": [ {"owner": "SH"}],
              "oci_compartment_id": "ocid1.compartment.oc1..aaaaa...",
              "model":"xai.grok-3"
            }');
 END;
 /

PL/SQL procedure successfully completed.

--Set Profile

exec dbms_cloud_ai.set_profile('grok');

PL/SQL procedure successfully completed.


--Use Select AI

select ai how many customers exist;

TOTAL_CUSTOMERS
---------------
          55500

select ai how many customers in San Francisco are married;

TOTAL_MARRIED_CUSTOMERS
-----------------------
                     46

select ai showsql how many customers in San Francisco are married;

RESPONSE
----------------------------------------------------------
SELECT COUNT(*) AS total_married_customers
FROM "SH"."CUSTOMERS" c
WHERE UPPER(c."CUST_CITY") = UPPER('San Francisco')
AND UPPER(c."CUST_MARITAL_STATUS") = UPPER('married')


select ai explainsql how many customers in San Francisco are married;

RESPONSE
-------------------
### Oracle SQL Query

sql SELECT COUNT(*) AS “Total_Married_Customers” FROM “SH”.”CUSTOMERS” “cust” WHERE UPPER(“cust”.”CUST_CITY”) = UPPER(‘San Francisco’) AND UPPER(“cust”.”CUST_MARITAL_STATUS”) = UPPER(‘married’)

### Detailed Explanation
1. **Table and Schema Naming**:
   - The table `"CUSTOMERS"` is referenced with its schema name `"SH"` as `"SH"."CUSTOMERS"`. This ensures that the query explicitly points to the correct schema and table, avoiding ambiguity.
   - A table alias `"cust"` is used for the `"CUSTOMERS"` table to make the query more readable and concise when referencing columns.

2. **Column Naming**:
   - The result of the `COUNT(*)` function is aliased as `"Total_Married_Customers"` for clarity and readability. This descriptive name indicates exactly what the count represents.
   - All column names (e.g., `"CUST_CITY"`, `"CUST_MARITAL_STATUS"`) are enclosed in double quotes to maintain case sensitivity as per Oracle's naming conventions when explicitly defined.

3. **String Comparison in WHERE Clause**:
   - The strings 'San Francisco' and 'married' in the question are not enclosed in double quotes. As per the provided rules, case-insensitive comparison is required.
   - Therefore, the `UPPER()` function is applied to both the column values (`"cust"."CUST_CITY"` and `"cust"."CUST_MARITAL_STATUS"`) and the literal strings ('San Francisco' and 'married') to ensure the comparison ignores case differences.
   - This means that records with values like 'SAN FRANCISCO', 'san francisco', or any other case variation of 'San Francisco' will match, and similarly for 'married'.

4. **Purpose of the Query**:
   - The query counts the total number of customers who are located in 'San Francisco' and have a marital status of 'married'.
   - The `COUNT(*)` function is used to return the total number of rows that satisfy the conditions specified in the `WHERE` clause.

5. **Readability**:
   - The query uses consistent formatting with indentation for the `WHERE` clause conditions to improve readability.
   - The use of a meaningful alias (`"cust"`) and a descriptive result column name (`"Total_Married_Customers"`) makes the query easier to understand at a glance.

This query will return a single number representing the count of married customers in San Francisco, handling case variations in the data appropriately.

sql select ai narrate what are the top 3 customers in San Francisco;

RESPONSE

I’m showing you the top 3 customers from San Francisco, based on their unique id entification numbers, sorted from lowest to highest. Here’s who they are:

select ai chat what is Autonomous AI Database;

RESPONSE

An Autonomous AI Database is a cloud-based database solution that uses artificial intelligence (AI) and machine learning (ML) to automate many of the routine tasks associated with managing and maintaining a database. It is designed to operate with minimal human intervention, allowing organizations to focus on data-driven insights and application development rather than database administration. The concept is often associated with Oracle’’s Autonomous AI Database, which was one of the first widely recognized implementations, but other cloud providers like AWS, Microsoft Azure, and Google Cloud also offer similar self-managing database services.

Key Features of an Autonomous AI Database:

  1. Self-Driving: Automates tasks such as provisioning, patching, tuning, and upgrading without manual input. It uses AI to optimize performance and adapt to workload changes in real-time.
  2. Self-Securing: Automatically applies security updates, detects vulnerabilities, and protects against threats using built-in mechanisms like encryption and threat detection, reducing the risk of human error.
  3. Self-Repairing: Identifies and resolves issues such as system failures or performance bottlenecks autonomously, ensuring high availability and minimizing downtime.
  4. Scalability: Dynamically scales resources (compute and storage) up or down based on demand, optimizing cost and performance.
  5. Data Management: Supports various data types and workloads, including transactional (OLTP), analytical (OLAP), and mixed workloads, often in a single converged database environment.

Benefits:

Use Cases:

Example:

Oracle Autonomous AI Database, available on Oracle Cloud, offers two primary services:

In essence, an Autonomous AI Database represents a shift toward intelligent, self-managing data systems that leverage automation and AI to simplify database operations, enhance security, and improve efficiency in a cloud environment. If you have a specific provider or context in mind, let me know, and I can dive deeper!



Example: Select AI with OCI Generative AI Using LLAMA Model

This example showcases the `chat` feature from OCI Generative AI. It highlights the model's capabilities through two prompts: analyzing customer comments to gauze their sentiment and generate an introductory paragraph on rock climbing.

BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => ‘GENAI_CRED’, user_ocid => ‘ocid1.user.oc1..aaa’, tenancy_ocid => ‘ocid1.tenancy.oc1..aaa’, private_key => ‘', fingerprint => '' ); END; /

PL/SQL procedure successfully completed.

BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name => ‘GENAI’, attributes =>’{“provider”: “oci”, “object_list”: [ {“owner”: “SH”, “name”: “customers”}, {“owner”: “SH”, “name”: “countries”}, {“owner”: “SH”, “name”: “supplementary_demographics”}, {“owner”: “SH”, “name”: “profits”}, {“owner”: “SH”, “name”: “promotions”}, {“owner”: “SH”, “name”: “products”} ] “model”: “meta.llama-3.3-70b-instruct”, “oci_apiformat”:”GENERIC”, “credential_name”: “GENAI_CRED”, “oci_compartment_id”: “ocid1.compartment.oc1…”}’); END; /

PL/SQL procedure successfully completed.

– –Set profile – EXEC DBMS_CLOUD_AI.SET_PROFILE(‘GENAI’);

PL/SQL procedure successfully completed.

SQL> set linesize 150 SQL> SELECT AI chat what is the sentiment of this comment I am not going to waste my time filling up this three page form. Lousy idea; SQL> RESPONSE ——————————————————————————————————————————————————

The sentiment of this comment is strongly negative. The user is expressing frustration and annoyance with the idea of filling out a three-page form, an
   d is explicitly stating that they consider it a "lousy idea". The use of the phrase "waste my time" also implies that they feel the task is unnecessary
   and unproductive. The tone is dismissive and critical.

SQL> SELECT AI chat Write an enthusiastic introductory paragraph on how to get started with rock climbing with Athletes as the target audience; RESPONSE —————————————————————————————————————————————————— Rock climbing is an exhilarating and challenging sport that’s perfect for athletes looking to push their limits and test their strength, endurance, and mental toughness. Whether you’re a seasoned athlete or just starting out, rock climbing offers a unique and rewarding experience that will have you hooked from the very first climb. With its combination of physical and mental challenges, rock climbing is a great way to build strength, improve flexibility, and develop problem-solving skills. Plus, with the supportive community of climbers and the breathtaking views from the top of the climb, you’ll be hooked from the very first climb. So, if you’re ready to take on a new challenge and experience the thrill of adventure, then it’s time to get started with rock climbing!



Using OCI Generative AI with the Default Model

The following example uses the default OCI Generative AI Chat Model. If you do not specify the `model_name` parameter, OCI Generative AI uses the default model as per the table in [Select your AI Provider and LLMs](select-ai-about.html#GUID-FDAEF22A-5DDF-4BAE-A465-C1D568C75812).

BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name => ‘OCI_DEFAULT’, attributes => ‘{“provider”: “oci”, “credential_name”: “OCI_CRED”, “object_list”: [{“owner”: “ADB_USER”}] }’); END; /



Using OCI Generative AI with Chat Model

The following example uses `cohere.command-r-plus-08-2024` as the OCI Generative AI Chat Model.

BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name => ‘OCI_COHERE_COMMAND_R_PLUS’, attributes => ‘{“provider”: “oci”, “credential_name”: “OCI_CRED”, “object_list”: [{“owner”: “ADB_USER”}], “model”: “cohere.command-r-plus-08-2024” }’); END; /



Using OCI Generative AI with Chat Model Endpoint ID

The following example demonstrates how to specify the OCI Generative AI Chat Model endpoint ID instead of `model`. If you are using Meta Llama Chat Model endpoint ID, then specify `oci_apiformat` as `GENERIC`.

BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name => ‘OCI_CHAT_ENDPOINT’, attributes => ‘{“provider”: “oci”, “credential_name”: “OCI_CRED”, “object_list”: [{“owner”: “ADB_USER”}], “oci_endpoint_id”: “<endpoint_id>”, “oci_apiformat”: “GENERIC” }’); END; /



Using OCI Generative AI with Chat Model OCID

This example demonstrates how to specify the OCI Generative AI Cohere Chat Model endpoint ID instead of `model`. If you are using Meta Llama Chat Model endpoint ID, then specify `oci_apiformat` as `GENERIC`.

BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name => ‘OCI_CHAT_OCID’, attributes => ‘{“provider”: “oci”, “credential_name”: “OCI_CRED”, “object_list”: [{“owner”: “ADB_USER”}], “model”: “<model_ocid>”, “oci_apiformat”: “COHERE” }’); END; /



## Example: Select AI with OpenAI {#GUID-D33BA5D9-59FB-4B98-948F-D768D13ED01B}

This example shows how you can use OpenAI to generate SQL statements from natural language prompts.

 Note:  Only a DBA can run `EXECUTE` privileges and network ACL procedure.
 {: .infoboxnote}

–Grants EXECUTE privilege to ADB_USER – SQL> grant execute on DBMS_CLOUD_AI to ADB_USER;

– Grant Network ACL for OpenAI endpoint

SQL> BEGIN DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( host => ‘api.openai.com’, ace => xs$ace_type(privilege_list => xs$name_list(‘http’), principal_name => ‘ADB_USER’, principal_type => xs_acl.ptype_db) ); END; /

PL/SQL procedure successfully completed.

– – Create Credential for AI provider – EXEC DBMS_CLOUD.CREATE_CREDENTIAL( CREDENTIAL_NAME => ‘OPENAI_CRED’, username => ‘OPENAI’, password => ‘<your_api_token>’);

PL/SQL procedure successfully completed.

– – Create AI profile – BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name => ‘OPENAI’, attributes =>’{“provider”: “openai”, “credential_name”: “OPENAI_CRED”, “object_list”: [{“owner”: “SH”, “name”: “customers”}, {“owner”: “SH”, “name”: “countries”}, {“owner”: “SH”, “name”: “supplementary_demographics”}, {“owner”: “SH”, “name”: “profits”}, {“owner”: “SH”, “name”: “promotions”}, {“owner”: “SH”, “name”: “products”}], “conversation”: “true” }’); END; /

PL/SQL procedure successfully completed.

– – Enable AI profile in current session – SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE(‘OPENAI’);

PL/SQL procedure successfully completed.

– – Get Profile in current session – SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;

DBMS_CLOUD_AI.GET_PROFILE()

“OPENAI”

– – Use AI – SQL> select ai how many customers exist;

CUSTOMER_COUNT

     55500

SQL> select ai how many customers in San Francisco are married;

MARRIED_CUSTOMERS

           18

SQL> select ai showsql how many customers in San Francisco are married;

RESPONSE

SELECT COUNT(*) AS married_customers_count FROM SH.CUSTOMERS c WHERE c.CUST_CITY = ‘San Francisco’ AND c.CUST_MARITAL_STATUS = ‘Married’

SQL> select ai explainsql how many customers in San Francisco are married;

RESPONSE

SELECT COUNT(*) AS customer_count FROM SH.CUSTOMERS AS c WHERE c.CUST_STATE_PROVINCE = ‘San Francisco’ AND c.CUST_MARITAL_STATUS = ‘Married’;

Explanation:

Remember to adjust the table and column names based on your actual schema if they differ from the example.

Feel free to ask if you have more questions related to SQL or database in general.

SQL> select ai narrate what are the top 3 customers in San Francisco;

RESPONSE

The top 3 customers in San Francisco are:

  1. Hector Colven - Total amount sold: $52,025.99
  2. Milburn Klemm - Total amount sold: $50,842.28
  3. Gavin Xie - Total amount sold: $48,677.18

SQL> select ai chat what is Autonomous AI Database;

RESPONSE

Autonomous AI Database is a cloud-based database service provided by Oracle. It is designed to automate many of the routine tasks involved in managing a database, such as patching, tuning, and backups. Autonomous AI Database uses machine learning and automation to optimize performance, security, and availability, allowing users to focus on their applications and data rather than database administration tasks. It offers both adb_tp (ATP) for transactional workloads and Autonomous AI Lakehouse for analytical workloads. Autonomous AI Database provides high performance, scalability, and reliability, making it an ideal choice for modern cloud-based applications.

– –Clear the profile – BEGIN DBMS_CLOUD_AI.CLEAR_PROFILE; END; / PL/SQL procedure successfully completed.

– –Drop the profile – SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE(‘OPENAI’);

PL/SQL procedure successfully completed.



The following example shows specifying a different model in your AI profile:

BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name => ‘OPENAI’, attributes =>’{“provider”: “openai”, “credential_name”: “OPENAI_CRED”, “model”: “gpt-3.5-turbo”, “object_list”: [{“owner”: “SH”, “name”: “customers”}, {“owner”: “SH”, “name”: “countries”}, {“owner”: “SH”, “name”: “supplementary_demographics”}, {“owner”: “SH”, “name”: “profits”}, {“owner”: “SH”, “name”: “promotions”}, {“owner”: “SH”, “name”: “products”}], “conversation”: “true” }’); END; /



## Example: Select AI with Cohere {#GUID-63BC0C29-CC19-4BE5-9014-7EB0B623FABE}

This example shows how you can use Cohere to generate SQL statements from natural language prompts.


 Note:  Only a DBA can run `EXECUTE` privileges and network ACL procedure.
 {: .infoboxnote}

–Grants EXECUTE privilege to ADB_USER – SQL>GRANT execute on DBMS_CLOUD_AI to ADB_USER; – – Create Credential for AI provider – EXEC DBMS_CLOUD.CREATE_CREDENTIAL( CREDENTIAL_NAME => ‘COHERE_CRED’, username => ‘COHERE’, password => ‘your_api_token’);

PL/SQL procedure successfully completed.

– – Grant Network ACL for Cohere endpoint – SQL> BEGIN DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( host => ‘api.cohere.ai’, ace => xs$ace_type(privilege_list => xs$name_list(‘http’), principal_name => ‘ADB_USER’, principal_type => xs_acl.ptype_db) ); END; / /

PL/SQL procedure successfully completed.

– – Create AI profile – BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name => ‘COHERE’, attributes =>’{“provider”: “cohere”, “credential_name”: “COHERE_CRED”, “object_list”: [{“owner”: “SH”, “name”: “customers”}, {“owner”: “SH”, “name”: “sales”}, {“owner”: “SH”, “name”: “products”}, {“owner”: “SH”, “name”: “countries”}] }’); END; /

PL/SQL procedure successfully completed.

– – Enable AI profile in current session – SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE(‘COHERE’);

PL/SQL procedure successfully completed.

– – Get Profile in current session – SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;

DBMS_CLOUD_AI.GET_PROFILE()

“COHERE”

– – Use AI – SQL> select ai how many customers exist;

CUSTOMER_COUNT

     55500

– –Clear the profile – BEGIN DBMS_CLOUD_AI.CLEAR_PROFILE; END; / PL/SQL procedure successfully completed. – –Drop the profile –

SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE(‘COHERE’);

PL/SQL procedure successfully completed.



The following example shows specifying a different model and custom attributes in your AI profile:

BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name => ‘COHERE’, attributes => ‘{“provider”: “cohere”, “credential_name”: “COHERE_CRED”, “model”: “cohere.command-a-03-2025”, “object_list”: [{“owner”: “ADB_USER”}], “max_tokens”:512, “stop_tokens”: [”;”], “temperature”: 0.5, “comments”: true }’); END; /



## Example: Select AI with Azure OpenAI Service {#GUID-6E4C4B05-F387-4DE6-B884-DE97254D1D1D}

The following examples shows how you can enable access to Azure OpenAI Service using your API key or use Azure OpenAI Service Principal, create an AI profile, and generate SQL from natural language prompts.

– Create Credential for AI integration – EXEC DBMS_CLOUD.CREATE_CREDENTIAL( CREDENTIAL_NAME => ‘AZURE_CRED’, username => ‘AZUREAI’, password => ‘your_api_token’);

PL/SQL procedure successfully completed.

– – Grant Network ACL for OpenAI endpoint – BEGIN DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( host => ‘<azure_resource_name>.openai.azure.com’, ace => xs$ace_type(privilege_list => xs$name_list(‘http’), principal_name => ‘ADB_USER’, principal_type => xs_acl.ptype_db) ); END; /

PL/SQL procedure successfully completed.

– – Create AI profile – BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name=> ‘AZUREAI’, attributes=> ‘{“provider”: “azure”, “azure_resource_name”: “<azure_resource_name>”, “azure_deployment_name”: “<azure_deployment_name>” “credential_name”: “AZURE_CRED”, “object_list”: [{“owner”: “SH”, “name”: “customers”}, {“owner”: “SH”, “name”: “countries”}, {“owner”: “SH”, “name”: “supplementary_demographics”}, {“owner”: “SH”, “name”: “profits”}, {“owner”: “SH”, “name”: “promotions”}, {“owner”: “SH”, “name”: “products”}], “conversation”: “true” }’); END; /

PL/SQL procedure successfully completed.

– – Enable AI profile in current session – EXEC DBMS_CLOUD_AI.SET_PROFILE(‘AZUREAI’);

PL/SQL procedure successfully completed.

– – Get Profile in current session – SELECT DBMS_CLOUD_AI.get_profile() from dual;

DBMS_CLOUD_AI.GET_PROFILE()

“AZUREAI”

– – Use AI – select ai how many customers exist;

CUSTOMER_COUNT

     55500

select ai how many customers in San Francisco are married;

MARRIED_CUSTOMERS

           18

select ai showsql how many customers in San Francisco are married;

RESPONSE

SELECT COUNT(*) AS married_customers_count FROM SH.CUSTOMERS c WHERE c.CUST_CITY = ‘San Francisco’ AND c.CUST_MARITAL_STATUS = ‘Married’

select ai explainsql how many customers in San Francisco are married;

RESPONSE

SELECT COUNT(*) AS customer_count FROM SH.CUSTOMERS AS c WHERE c.CUST_STATE_PROVINCE = ‘San Francisco’ AND c.CUST_MARITAL_STATUS = ‘Married’;

Explanation:

Remember to adjust the table and column names based on your actual schema if they differ from the example.

Feel free to ask if you have more questions related to SQL or database in general.

select ai narrate what are the top 3 customers in San Francisco;

RESPONSE

The top 3 customers in San Francisco are:

  1. Hector Colven - Total amount sold: $52,025.99
  2. Milburn Klemm - Total amount sold: $50,842.28
  3. Gavin Xie - Total amount sold: $48,677.18

select ai chat what is Autonomous AI Database;

RESPONSE

Autonomous AI Database is a cloud-based database service provided by Oracle. It is designed to automate many of the routine tasks involved in managing a database, such as patching, tuning, and backups. Autonomous AI Database uses machine learning and automation to optimize performance, security, and availability, allowing users to focus on their applications and data rather than database administration tasks. It offers both adb_tp (ATP) for transactional workloads and Autonomous AI Lakehouse for analytical workloads. Autonomous AI Database provides high performance, scalability, and reliability, making it an ideal choice for modern cloud-based applications.

– –Clear the profile – BEGIN DBMS_CLOUD_AI.CLEAR_PROFILE; END; /

PL/SQL procedure successfully completed.

–Drop the profile – EXEC DBMS_CLOUD_AI.DROP_PROFILE(‘AZUREAI’);

PL/SQL procedure successfully completed.



The following example shows specifying a different model in your AI profile:

BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name=>’AZUREAI’, attributes=>’{“provider”: “azure”, “credential_name”: “AZURE$PA”, “model”: “gpt-3.5-turbo”, “object_list”: [{“owner”: “SH”, “name”: “customers”}, {“owner”: “SH”, “name”: “countries”}, {“owner”: “SH”, “name”: “supplementary_demographics”}, {“owner”: “SH”, “name”: “profits”}, {“owner”: “SH”, “name”: “promotions”}, {“owner”: “SH”, “name”: “products”}], “azure_resource_name”: “", "azure_deployment_name": "" }'); END; /



Example: Select AI with Azure OpenAI Service Principal

Connect as a database administrator to provide access to Azure service principal authentication and then grant the network ACL permissions to the user (`ADB_USER`) who wants to use Select AI. To provide access to Azure resources, see [Use Azure Service Principal to Access Azure Resources](azure-service-principal.html#GUID-29ED76DB-8202-44B3-9ADF-D5352334D7B4).

  Note:  Only a DBA user can run `EXECUTE` privileges and network ACL procedure.
  {: .infoboxnote}

– 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//oauth2/v 2.0/authorize?client_id=&response_type=code&scope=User.read

– On the Azure OpenAI IAM console, search for the Azure application name and assign the permission to the application. – You can get the application name in the cloud_integrations view. SQL> select param_value from CLOUD_INTEGRATIONS where param_name = ‘azure_app_name’; PARAM_VALUE ——————————————————————————– ADBS_APP_DATABASE_OCID

– – Grant Network ACL for Azure OpenAI endpoint –SQL> BEGIN DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( host => ‘azure_resource_name.openai.azure.com’, ace => xs$ace_type(privilege_list => xs$name_list(‘http’), principal_name => ‘ADB_USER’, principal_type => xs_acl.ptype_db) ); END; /

PL/SQL procedure successfully completed.

– – Create AI profile – BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name=>’AZUREAI’, attributes=>’{“provider”: “azure”, “credential_name”: “AZURE$PA”, “object_list”: [{“owner”: “SH”, “name”: “customers”}, {“owner”: “SH”, “name”: “countries”}, {“owner”: “SH”, “name”: “supplementary_demographics”}, {“owner”: “SH”, “name”: “profits”}, {“owner”: “SH”, “name”: “promotions”}, {“owner”: “SH”, “name”: “products”}], “azure_resource_name”: “<azure_resource_name>”, “azure_deployment_name”: “<azure_deployment_name>” }’); END; /

PL/SQL procedure successfully completed.

– – Enable AI profile in current session – EXEC DBMS_CLOUD_AI.SET_PROFILE(‘AZUREAI’);

PL/SQL procedure successfully completed.

– – Get Profile in current session – SELECT DBMS_CLOUD_AI.get_profile() from dual;

DBMS_CLOUD_AI.GET_PROFILE()

“AZUREAI”

– – Use AI – select ai how many customers exist;

CUSTOMER_COUNT

     55500

select ai how many customers in San Francisco are married;

MARRIED_CUSTOMERS

           18

select ai showsql how many customers in San Francisco are married;

RESPONSE

SELECT COUNT(*) AS married_customers_count FROM SH.CUSTOMERS c WHERE c.CUST_CITY = ‘San Francisco’ AND c.CUST_MARITAL_STATUS = ‘Married’

select ai explainsql how many customers in San Francisco are married;

RESPONSE

SELECT COUNT(*) AS customer_count FROM SH.CUSTOMERS AS c WHERE c.CUST_STATE_PROVINCE = ‘San Francisco’ AND c.CUST_MARITAL_STATUS = ‘Married’;

Explanation:

Remember to adjust the table and column names based on your actual schema if they differ from the example.

Feel free to ask if you have more questions related to SQL or database in general.

select ai narrate what are the top 3 customers in San Francisco;

RESPONSE

The top 3 customers in San Francisco are:

  1. Hector Colven - Total amount sold: $52,025.99
  2. Milburn Klemm - Total amount sold: $50,842.28
  3. Gavin Xie - Total amount sold: $48,677.18

select ai chat what is Autonomous AI Database;

RESPONSE

Autonomous AI Database is a cloud-based database service provided by Oracle. It is designed to automate many of the routine tasks involved in managing a database, such as patching, tuning, and backups. Autonomous AI Database uses machine learning and automation to optimize performance, security, and availability, allowing us ers to focus on their applications and data rather than database administration tasks. It offers both adb_tp (ATP) for transactional workloads and Autonomous AI Lakehouse for analytical workloads. Autonomous AI Database provides high performance, scalability, and reliability, making it an ideal choice for modern cloud-based applications.

EXEC DBMS_CLOUD_AI.DROP_PROFILE(‘AZUREAI’);

PL/SQL procedure successfully completed.



## Example: Select AI with Google {#GUID-9EF51CF2-76F2-499A-8E9C-CE807AA7E9B8}

This example shows how you can use Google to generate, run, and explain SQL from natural language prompts or chat using the Google Gemini LLM.

The following example demonstrates using Google as your AI provider. The example demonstrates using your Google API signing key to provide network access, creating an AI profile, using Select AI actions to generate SQL queries from natural language prompts and chat responses.

–Grants EXECUTE privilege to ADB_USER – SQL> grant EXECUTE on DBMS_CLOUD_AI to ADB_USER;

– – Create Credential for AI provider – SQL> BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => ‘GOOGLE_CRED’, username => ‘GOOGLE’, password => ‘' ); END; /

PL/SQL procedure successfully completed.

– – Grant Network ACL for Google endpoint – SQL> SQL> BEGIN DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( host => ‘generativelanguage.googleapis.com’, ace => xs$ace_type(privilege_list => xs$name_list(‘http’), principal_name => ‘ADB_USER’, principal_type => xs_acl.ptype_db) ); END; /

PL/SQL procedure successfully completed.

– – Create AI profile – SQL> BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name =>’GOOGLE’, attributes =>’{“provider”: “google”, “credential_name”: “GOOGLE_CRED”, “object_list”: [{“owner”: “SH”, “name”: “customers”}, {“owner”: “SH”, “name”: “countries”}, {“owner”: “SH”, “name”: “supplementary_demographics”}, {“owner”: “SH”, “name”: “profits”}, {“owner”: “SH”, “name”: “promotions”}, {“owner”: “SH”, “name”: “products”}] }’); END; /

PL/SQL procedure successfully completed.

– – Enable AI profile in current session – SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE(‘GOOGLE’);

PL/SQL procedure successfully completed.

– – Use AI – SQL> select ai how many customers exist;

CUSTOMER_COUNT

     55500

SQL> select ai how many customers in San Francisco are married;

MARRIED_CUSTOMERS

           18

SQL> select ai showsql how many customers in San Francisco are married;

RESPONSE

SELECT COUNT(*) AS married_customers_count FROM SH.CUSTOMERS c WHERE c.CUST_CITY = ‘San Francisco’ AND c.CUST_MARITAL_STATUS = ‘Married’

SQL> select ai explainsql how many customers in San Francisco are married;

RESPONSE

SELECT COUNT(*) AS customer_count FROM SH.CUSTOMERS AS c WHERE c.CUST_STATE_PROVINCE = ‘San Francisco’ AND c.CUST_MARITAL_STATUS = ‘Married’;

Explanation:

Remember to adjust the table and column names based on your actual schema if they differ from the example.

Feel free to ask if you have more questions related to SQL or database in general.

SQL> select ai narrate what are the top 3 customers in San Francisco;

RESPONSE

The top 3 customers in San Francisco are:

  1. Hector Colven - Total amount sold: $52,025.99
  2. Milburn Klemm - Total amount sold: $50,842.28
  3. Gavin Xie - Total amount sold: $48,677.18

SQL> select ai chat what is Autonomous AI Database;

RESPONSE

Autonomous AI Database is a cloud-based database service provided by Oracle. It is designed to automate many of the routine tasks involved in managing a database, such as patching, tuning, and backups. Autonomous AI Database uses machine learning and automation to optimize performance, security, and availability, allowing users to focus on their applications and data rather than database administration tasks. It offers both adb_tp (ATP) for transactional workloads and Autonomous Lakehouse for analytical workloads. Autonomous AI Database provides high performance, scalability, and reliability, making it an ideal choice for modern cloud-based applications.

– –Clear the profile – BEGIN DBMS_CLOUD_AI.CLEAR_PROFILE; END; / PL/SQL procedure successfully completed.

– –Drop the profile – EXEC DBMS_CLOUD_AI.DROP_PROFILE(‘GOOGLE’);

PL/SQL procedure successfully completed.



The following example shows specifying a different model in your AI profile:

BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name =>’GOOGLE’, attributes =>’{“provider”: “google”, “credential_name”: “GOOGLE_CRED”, “model”: “gemini-1.5-pro”, “object_list”: [{“owner”: “SH”, “name”: “customers”}, {“owner”: “SH”, “name”: “countries”}, {“owner”: “SH”, “name”: “supplementary_demographics”}, {“owner”: “SH”, “name”: “profits”}, {“owner”: “SH”, “name”: “promotions”}, {“owner”: “SH”, “name”: “products”}] }’); END; /



## Example: Select AI with Anthropic {#GUID-0C4BF8C3-CF2D-4284-80F7-64799E4BAEE4}

This example shows how you can use Anthropic to generate, run, and explain SQL from natural language prompts or chat using the Anthropic Claude LLM.

The following example demonstrates using Anthropic as your AI provider. The example demonstrates using your Anthropic API signing key to provide network access, creating an AI profile, and using Select AI actions to generate SQL queries from natural language prompts and chat using the Anthropic Claude LLM.

See [Profile Attributes](dbms-cloud-ai-package.html#GUID-12D91681-B51C-48E0-93FD-9ABC67B0F375) to supply the profile attributes.

–Grant EXECUTE privilege to ADB_USER

SQL>GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;

– – Create Credential for AI provider –

SQL>BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => ‘ANTHROPIC_CRED’, username => ‘ANTHROPIC’, password => ‘' ); END; /

PL/SQL procedure successfully completed.

– – Grant Network ACL for Anthropic endpoint – SQL>BEGIN DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( host => ‘api.anthropic.com’, ace => xs$ace_type(privilege_list => xs$name_list(‘http’), principal_name => ‘ADB_USER’, principal_type => xs_acl.ptype_db) ); END; /

PL/SQL procedure successfully completed.

– – Create AI profile – SQL>BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name =>’ANTHROPIC’, attributes =>’{“provider”: “anthropic”, “credential_name”: “ANTHROPIC_CRED”, “object_list”: [{“owner”: “SH”, “name”: “customers”}, {“owner”: “SH”, “name”: “countries”}, {“owner”: “SH”, “name”: “supplementary_demographics”}, {“owner”: “SH”, “name”: “profits”}, {“owner”: “SH”, “name”: “promotions”}, {“owner”: “SH”, “name”: “products”}] }’); END; /

PL/SQL procedure successfully completed.

– – Enable AI profile in current session – SQL>EXEC DBMS_CLOUD_AI.SET_PROFILE(‘ANTHROPIC’);

PL/SQL procedure successfully completed.

– – Use AI – SQL> select ai how many customers exist;

CUSTOMER_COUNT

     55500

SQL> select ai how many customers in San Francisco are married;

MARRIED_CUSTOMERS

           18

SQL> select ai showsql how many customers in San Francisco are married;

RESPONSE

SELECT COUNT(*) AS married_customers_count FROM SH.CUSTOMERS c WHERE c.CUST_CITY = ‘San Francisco’ AND c.CUST_MARITAL_STATUS = ‘Married’

SQL> select ai explainsql how many customers in San Francisco are married;

RESPONSE

SELECT COUNT(*) AS customer_count FROM SH.CUSTOMERS AS c WHERE c.CUST_STATE_PROVINCE = ‘San Francisco’ AND c.CUST_MARITAL_STATUS = ‘Married’;

Explanation:

Remember to adjust the table and column names based on your actual schema if they differ from the example.

Feel free to ask if you have more questions related to SQL or database in general.

SQL> select ai narrate what are the top 3 customers in San Francisco;

RESPONSE

The top 3 customers in San Francisco are:

  1. Hector Colven - Total amount sold: $52,025.99
  2. Milburn Klemm - Total amount sold: $50,842.28
  3. Gavin Xie - Total amount sold: $48,677.18

SQL> select ai chat what is Autonomous AI Database;

RESPONSE

Autonomous AI Database is a cloud-based database service provided by Oracle. It is designed to automate many of the routine tasks involved in managing a database, such as patching, tuning, and backups. Autonomous AI Database uses machine learning and automation to optimize performance, security, and availability, allowing users to focus on their applications and data rather than database administration tasks. It offers both adb_tp (ATP) for transactional workloads and Autonomous AI Lakehouse for analytical workloads. Autonomous AI Database provides high performance, scalability, and reliability, making it an ideal choice for modern cloud-based applications.

– –Clear the profile – BEGIN DBMS_CLOUD_AI.CLEAR_PROFILE; END; / PL/SQL procedure successfully completed.

– –Drop the profile – EXEC DBMS_CLOUD_AI.DROP_PROFILE(‘ANTHROPIC’);

PL/SQL procedure successfully completed.



The following example shows specifying a different model in your AI profile:

BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name =>’ANTHROPIC’, attributes =>’{“provider”: “anthropic”, “credential_name”: “ANTHROPIC_CRED”, “model”: “claude-3-opus-20240229”, “object_list”: [{“owner”: “SH”, “name”: “customers”}, {“owner”: “SH”, “name”: “countries”}, {“owner”: “SH”, “name”: “supplementary_demographics”}, {“owner”: “SH”, “name”: “profits”}, {“owner”: “SH”, “name”: “promotions”}, {“owner”: “SH”, “name”: “products”}] }’); END; /



## Example: Select AI with Hugging Face {#GUID-0DCA4130-36AF-452C-8432-C8EC11D19F23}

This example shows how you can use Hugging Face to generate, run, and explain SQL from natural language prompts or chat using the Hugging Face LLM.

The following example demonstrates using Hugging Face as your AI provider. The example demonstrates using your Hugging Face API signing key to provide network access, creating an AI profile, and using Select AI actions to generate SQL queries from natural language prompts and chat using the Hugging Face LLM.

–Grant EXECUTE privilege to ADB_USER

SQL>GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;

– – Create Credential for AI provider – SQL>BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => ‘HF_CRED’, username => ‘HF’, password => ‘<your_api_key>’ ); END; /

PL/SQL procedure successfully completed.

– – Grant Network ACL for Hugging Face endpoint – SQL>BEGIN DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( host => ‘api-inference.huggingface.co’, ace => xs$ace_type(privilege_list => xs$name_list(‘http’), principal_name => ‘ADB_USER’, principal_type => xs_acl.ptype_db) ); END; /

PL/SQL procedure successfully completed.

– – Create AI profile – SQL>BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name =>’HF’, attributes =>’{“provider”: “huggingface”, “credential_name”: “HF_CRED”, “object_list”: [{“owner”: “SH”, “name”: “customers”}, {“owner”: “SH”, “name”: “countries”}, {“owner”: “SH”, “name”: “supplementary_demographics”}, {“owner”: “SH”, “name”: “profits”}, {“owner”: “SH”, “name”: “promotions”}, {“owner”: “SH”, “name”: “products”}], “model” : “Qwen/Qwen2.5-72B-Instruct” }’); END; /

PL/SQL procedure successfully completed.

– – Enable AI profile in current session – SQL>EXEC DBMS_CLOUD_AI.SET_PROFILE(‘HF’);

PL/SQL procedure successfully completed.

– – Use AI –SQL> select ai how many customers exist;

Customer_Count

     55500

SQL> select ai how many customers in San Francisco are married;

Married_Customers

           46

SQL> select ai showsql how many customers in San Francisco are married;

RESPONSE

SELECT COUNT(“CUST_ID”) AS “Married_Customers” FROM “SH”.”CUSTOMERS” “C” WHERE “CUST_CITY” = ‘San Francisco’ AND “CUST_MARITAL_STATUS “ = ‘Married’

SQL> select ai explainsql how many customers in San Francisco are married;

RESPONSE

To answer the question “How many customers in San Francisco are married?”, we need to query the “SH”.”CUSTOMERS” table and filter the results based on the city and marital status. We will use table aliases to make the query more readable and ensure that the string comparisons follow the specified rules.

Here is the Oracle SQL query:

SELECT COUNT("CUST_ID") AS "Married_Customers_in_San_Francisco"
FROM "SH"."CUSTOMERS" c
WHERE c."CUST_CITY" = 'San Francisco' -- Case insensitive comparison
  AND UPPER(c."CUST_MARITAL_STATUS") = UPPER('Married'); --
Case insensitive comparison

Explanation:

  1. Table Alias:

RESPONSE

  1. Column Selection:
    • We select the count of “CUST_ID” to get the number of customers who meet the criteria. We alias this count as “Married_Customers_in_San_Francisco” for clarity.
  2. WHERE Clause:
    • City Filter: c."CUST_CITY" = 'San Francisco'
      • Since ‘San Francisco’ is not in double quotes, we use a case-insensitive comparison.
    • Marital Status Filter: UPPER(c."CUST_MARITAL_STATUS") = UPPER('Married')
      • Since ‘Married’ is not in double quotes, we use a case-insensitive comparison by applying the UPPER function to both sides of the comparison.

This query will return the number of customers in San Francisco who are married.

SQL> select ai narrate what are the top 3 customers in San Francisco;

RESPONSE

The top 3 customers in San Francisco are:

These customers have the highest total sales among all customers in San Francisco.

SQL> select ai chat what is Autonomous AI Database;

RESPONSE

An Autonomous AI Database is a type of database management system that uses artificial intelligence (AI) and machine learning (ML) to automate many of the routine tasks typically performed by database administrators (DBAs). These tasks include provisioning, patching, backups, tuning, and security. The goal of an Autonomous AI Database is to reduce the need for human intervention, minimize errors, and optimize performance,thereby allowing organizations to focus on more strategic activities.

Key Features of Autonomous AI Databases:

RESPONSE

  1. Automated Provisioning: The database can be set up and configured automatically, reducing the time and effort required to get it up and running.
  2. Self-Patching and Updates: The database automatically applies security patches and updates without downtime, ensuring that the system remains secure and up-to-date.
  3. Self-Tuning: The database continuously monitors its performance and adjusts settings to optimize query execution and resource utilization.
  4. Self-Backup and Recovery: Automated backup and recovery processes ensure that data is protected and can be restored quickly in the event of a failure.
  5. Security: Advanced security features, including threat detection and response, are built into the database to protect against cyber threats.
  6. Scalability: The database can automatically scale resources up or down based on demand, ensuring optimal performance and cost efficiency.
  7. Monitoring and Diagnostics: Real-time monitoring and diagnostics help identify and resolve issues before they impact performance.

RESPONSE

Benefits of Autonomous AI Databases:

RESPONSE

Use Cases:

RESPONSE

Examples of Autonomous AI Databases:

Autonomous AI Databases represent a significant advancement in database technology, offering organizations a more efficient, secure, and cost-effective way to manage their data.

– –Clear the profile – BEGIN DBMS_CLOUD_AI.CLEAR_PROFILE; END; / PL/SQL procedure successfully completed. – –Drop the profile – SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE(‘HF’);

PL/SQL procedure successfully completed.



## Example: Select AI with AWS {#GUID-A2B7D8BB-5CBF-49D4-B12C-8AB54BA7D0A2}

This example shows how you can use AWS to generate, run, and explain SQL from natural language prompts or chat using the models available with AWS.

The following example shows how to use AWS as the AI provider with Amazon Bedrock and its foundation models. The example shows creating AWS credentials, provide network access, creating an AI profile, and using Select AI actions to generate SQL queries from natural language prompts and chat using the AWS foundation models.

To use AWS, obtain access key, secret keys, and model ID. See [Use AWS](select-ai-manage-profiles.html#GUID-B71D2617-F079-4982-A979-6C1C8C58B577). Use the model ID as the `model` attribute in the `DBMS_CLOUD_AI.CREATE_PROFILE` procedure. You must specify the `model` attribute explicitly, as no default model is provided.

–Grant EXECUTE privilege to ADB_USER GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;

– – Create Credential for AI provider – BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => ‘AWS_CRED’, username => ‘', password => '' ); END; /

PL/SQL procedure successfully completed.

– – Grant Network ACL for AWS – BEGIN DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( host => ‘bedrock-runtime.us-east-1.amazonaws.com’, ace => xs$ace_type(privilege_list => xs$name_list(‘http’), principal_name => ‘ADB_USER’, principal_type => xs_acl.ptype_db) ); END; /

PL/SQL procedure successfully completed.

– – Create AI profile – BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name =>’AWS’, attributes =>’{“provider”: “aws”, “credential_name”: “AWS_CRED”, “object_list”: [{“owner”: “SH”, “name”: “customers”}, {“owner”: “SH”, “name”: “countries”}, {“owner”: “SH”, “name”: “supplementary_demographics”}, {“owner”: “SH”, “name”: “profits”}, {“owner”: “SH”, “name”: “promotions”}, {“owner”: “SH”, “name”: “products”}], “model” : “anthropic.claude-v2”, “conversation” : “true” }’); END; /

PL/SQL procedure successfully completed.

– – Enable AI profile in current session – EXEC DBMS_CLOUD_AI.SET_PROFILE(‘AWS’);

PL/SQL procedure successfully completed.

– – Use AI –

SELECT AI how many customers exist; “RESPONSE” “COUNT(*)” 55500

SELECT AI how many customers in San Francisco are married; “RESPONSE” “COUNT(*)” 46

SELECT AI showsql how many customers in San Francisco are married; “RESPONSE” “SELECT COUNT(*) AS “Number of Married Customers in San Francisco” FROM “SH”.”CUSTOMERS” C WHERE UPPER(C.”CUST_CITY”) = UPPER(‘San Francisco’) AND UPPER(C.”CUST_MARITAL_STATUS”) = UPPER(‘Married’)”

SELECT AI explainsql how many customers in San Francisco are married;

“RESPONSE”“SELECT COUNT(*) AS “Number of Married Customers in San Francisco” FROM “SH”.”CUSTOMERS” C WHERE C.”CUST_CITY” = ‘San Francisco’ AND C.”CUST_MARITAL_STATUS” = ‘Married’

Explanation:

SELECT AI narrate what are the top 3 customers in San Francisco; “RESPONSE” The top 3 customers in San Francisco ordered by credit limit in descending order are:

  1. Bert Katz
  2. Madallyn Ladd
  3. Henrietta Snodgrass

SELECT AI chat what is Autonomous AI Database; “RESPONSE” “An Autonomous AI Database is a cloud database service provided by Oracle Corporation. Some key features of Oracle Autonomous AI Database include:

So in summary, an Oracle Autonomous AI Database is a fully automated, self-driving, self-securing, and self-repairing database provided as a simple cloud service. The automation provides high performance, elasticity, and availability with minimal human labor required.”

– –Clear the profile – BEGIN DBMS_CLOUD_AI.CLEAR_PROFILE; END; / PL/SQL procedure successfully completed.

– –Drop the profile – EXEC DBMS_CLOUD_AI.DROP_PROFILE(‘AWS’);

PL/SQL procedure successfully completed.



## Example: Select AI with OpenAI-Compatible Providers {#GUID-C84FAFC3-DFB9-4EBA-B354-993787A54149}

This example shows how you can use OpenAI-compatible providers to generate, run, and explain SQL from natural language prompts or chat using the models available with OpenAI-compatible providers.

The following example shows how to use Fireworks AI as an OpenAI-compatible provider. It demonstrates how to create credentials using your Fireworks AI API signing key, configure network access, create an AI profile, and use Select AI actions to generate SQL queries from natural language prompts and chat using the Fireworks AI LLM model.

Before You Begin

Review [Perform Prerequisites for Select AI](select-ai-manage-profiles.html#GUID-3721296F-14A1-428A-B464-7FA25E9EC8F3)

To use Fireworks AI, specify `provider_endpoint` as an attribute in the `DBMS_CLOUD_AI.CREATE_PROFILE` procedure instead of the `provider` attribute. See [Use OpenAI-Compatible Providers](select-ai-manage-profiles.html#GUID-F8EAAF29-9750-4DFD-AE2B-DBFC509AD436) to obtain the attribute. You must specify the `model` attribute explicitly, as no default model is provided.

–Grant EXECUTE privilege to ADB_USER GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;

– – Create Credential for AI provider – BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => ‘FIREWORKS_CRED’, username => ‘FIREWORKS’, password => ‘' ); END; /

PL/SQL procedure successfully completed.

– – Grant Network ACL for Fireworks AI endpoint – BEGIN DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( host => ‘api.fireworks.ai’, ace => xs$ace_type(privilege_list => xs$name_list(‘http’), principal_name => ‘ADB_USER’, principal_type => xs_acl.ptype_db) ); END; /

PL/SQL procedure successfully completed.

– – Create AI profile – BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name =>’FIREWORKS’, attributes =>’{ “credential_name”: “FIREWORKS_CRED”, “object_list”: [{“owner”: “SH”, “name”: “customers”}, {“owner”: “SH”, “name”: “countries”}, {“owner”: “SH”, “name”: “supplementary_demographics”}, {“owner”: “SH”, “name”: “profits”}, {“owner”: “SH”, “name”: “promotions”}, {“owner”: “SH”, “name”: “products”}], “model” : “accounts/fireworks/models/llama-v3p1-405b-instruct”, “provider_endpoint” : “api.fireworks.ai/inference”, “conversation” : “true” }’); END; /

PL/SQL procedure successfully completed.

– – Enable AI profile in current session – EXEC DBMS_CLOUD_AI.SET_PROFILE(‘FIREWORKS’);

PL/SQL procedure successfully completed.

– – Use AI –

select ai how many customers exist; “RESPONSE” “COUNT(*)” 55500

select ai how many customers in San Francisco are married; “RESPONSE” “COUNT(*)” 46

select ai showsql how many customers in San Francisco are married;

“RESPONSE” “SELECT COUNT(*) FROM ““SH””.”“CUSTOMERS”” ““c”” WHERE UPPER(““c””.”“CUST_CITY””) = UPPER(‘San Francisco’) AND UPPER(““c””.”“CUST_MARITAL_STATUS””) = UPPER(‘Married’)”

select ai explainsql how many customers in San Francisco are married;

“RESPONSE” “Here is the Oracle SQL query:

SELECT COUNT(*)
FROM ""SH"".""CUSTOMERS"" ""c""
WHERE UPPER(""c"".""CUST_CITY"") = UPPER('San Francisco')
  AND UPPER(""c"".""CUST_MARITAL_STATUS"") = UPPER('Married')

Explanation:

select ai narrate what are the top 3 customers in San Francisco; “RESPONSE” The top 3 customers in San Francisco are:

  1. Lyndon Baltzer
  2. Mike Crocker
  3. Milburn Klemm

These customers are identified by their unique customer IDs, which are used to track their information and interactions. The list shows the customer’s first and last names.

select ai chat what is Autonomous AI Database; “RESPONSE” “An Autonomous AI Database is a type of database that uses artificial intelligence (AI) and machine learning (ML) to automate many of the tasks traditionally performed by database administrators (DBAs). The goal of an Autonomous AI Database is to provide a self-managing, self-securing, and self-repairing database that can operate with minimal human intervention.

Autonomous AI Databases use advanced algorithms and machine learning techniques to:

  1. Automate database administration: Tasks such as patching, backups, and performance tuning are automated, freeing up DBAs to focus on higher-level tasks.
  2. Predict and prevent issues: The database uses machine learning to predict and prevent issues such as downtime, data breaches, and performance problems.
  3. Optimize performance: The database continuously monitors and optimizes its own performance, ensuring that it is running at peak efficiency.
  4. Enhance security: Autonomous AI Databases use advanced security features, such as encryption and access controls, to protect data from unauthorized access.
  5. Improve data management: Autonomous AI Databases can automatically manage data, including data ingestion, processing, and storage.

The benefits of Autonomous AI Databases include:

  1. Increased efficiency: By automating routine tasks, Autonomous AI Databases can reduce the workload of DBAs and improve overall efficiency.
  2. Improved security: Autonomous AI Databases can detect and respond to security threats in real-time, reducing the risk of data breaches.
  3. Enhanced performance: Autonomous AI Databases can optimize their own performance, ensuring that applications run quickly and efficiently.
  4. Reduced costs: By automating routine tasks and improving efficiency, Autonomous AI Databases can help reduce costs associated with database management.

Examples of Autonomous AI Databases include:

  1. Oracle Autonomous AI Database
  2. Microsoft Azure SQL Database
  3. Amazon Aurora
  4. Google Cloud SQL

Overall, Autonomous AI Databases represent a significant shift in the way databases are managed and maintained, using AI and ML to automate many of the tasks traditionally performed by DBAs.”

– –Clear the profile – BEGIN DBMS_CLOUD_AI.CLEAR_PROFILE; END; / PL/SQL procedure successfully completed.

– –Drop the profile – EXEC DBMS_CLOUD_AI.DROP_PROFILE(‘FIREWORKS’);

PL/SQL procedure successfully completed.



Example: Use a Custom LLM Deployed in OCI Data Science with Select AI

In this configuration, Select AI uses an OCI user credential and wraps it with OpenAI-style bearer token authentication. The AI profile uses provider: `openai` together with a custom `provider_endpoint` that points to the OCI Data Science model deployment endpoint.

Before You Begin

Review:

1. [Perform Prerequisites for Select AI](select-ai-manage-profiles.html#GUID-3721296F-14A1-428A-B464-7FA25E9EC8F3)

2. Create an OCI Data Science model deployment using AI Quick Actions (AQUA).

3. Note the model deployment endpoint URL.


For information about creating and deploying models using OCI Data Science AI Quick Actions (AQUA), see [AI Quick Actions](https://docs.oracle.com/en-us/iaas/Content/data-science/using/ai-quick-actions.htm) and [Model Deployment](https://docs.oracle.com/en-us/iaas/Content/data-science/using/model-dep-about.htm) in OCI Data Science documentation.

The following example shows how to create credentials using an OCI user credential that wraps authentication in an OpenAI-style Bearer token, configure network access, create an AI profile, and use Select AI `chat` action using the OCI Data Science (AQUA) LLM model.

To use OCI Data Science (AQUA), specify `provider_endpoint`, `provider: openai`, and `model: odsc-llm` as attributes in the `DBMS_CLOUD_AI.CREATE_PROFILE` procedure.

sql –Grant EXECUTE privilege to ADB_USER GRANT EXECUTE ON DBMS_CLOUD_AI TO ADB_USER; – Create a credential for OCI request signing BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => ‘OCI_GENAI_CRED’, user_ocid => ‘', tenancy_ocid => '', private_key => '', fingerprint => '' ); END; /

– Grant network access to the OCI Data Science model deployment host BEGIN DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( host => ‘*.oci.customer-oci.com’, ace => xs$ace_type( privilege_list => xs$name_list(‘http’), principal_name => ‘ADB_USER’, principal_type => xs_acl.ptype_db ) ); END; /

– Drop the profile if it already exists BEGIN DBMS_CLOUD_AI.DROP_PROFILE( profile_name => ‘OCI_DS_LLAMA_MODEL_PROFILE’, force => TRUE ); END; /

– Create the AI profile for the OCI Data Science model deployment BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name => ‘OCI_DS_LLAMA_MODEL_PROFILE’, attributes => ‘{ “credential_name”: “OCI_GENAI_CRED”, “model”: “odsc-llm”, “provider”: “openai”, “provider_endpoint”: “https://modeldeployment..oci.customer-oci.com//predict", "conversation": "true", "object_list": [ {"owner": "ADB_USER", "name": "CUSTOMERS"} ] }' ); END; /

– Set the AI profile for the current session BEGIN DBMS_CLOUD_AI.SET_PROFILE(‘OCI_DS_LLAMA_MODEL_PROFILE’); END; /

– Use Select AI test the profile with DBMS_CLOUD_AI.GENERATE SELECT DBMS_CLOUD_AI.GENERATE( prompt => ‘what is oracle autonomous database’, profile_name => ‘OCI_DS_LLAMA_MODEL_PROFILE’, action => ‘chat’ ) FROM dual;

Select AI sends the prompt to the OCI Data Science model deployment and returns the generated response.

**Example: Use an OpenAI-Compatible Provider xAI with Select AI**

The following example shows how to use xAI as an OpenAI-compatible provider. It demonstrates how to create credentials using your xAI API key, configure network access, create an AI profile, and use Select AI actions to generate SQL queries from natural language prompts and chat using an xAI model.

To use xAI, specify the `provider_endpoint` attribute in the `DBMS_CLOUD_AI.CREATE_PROFILE` procedure. You must also specify the `model` attribute explicitly, as no default model is provided.

sql –Grant EXECUTE privilege to ADB_USER GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;

–Create a Credential

BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => ‘XAI_CRED’, username => ‘XAI’, password => ‘' ); END; /

–Grant Network Access BEGIN DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( host => ‘api.x.ai’, ace => xs$ace_type( privilege_list => xs$name_list(‘http’), principal_name => ‘ADB_USER’, principal_type => xs_acl.ptype_db ) ); END; /

–Create an AI Profile BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name => ‘XAI_PROFILE’, attributes => ‘{ “credential_name”: “XAI_CRED”, “provider_endpoint”: “https://api.x.ai”, “model”: “grok-4-1-fast-reasoning”, “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; /

--Set the profile
EXEC DBMS_CLOUD_AI.SET_PROFILE('XAI_PROFILE');

--Use Select AI
SELECT AI how many customers exist;


## Example: Enable Conversations in Select AI {#GUID-9F0524DB-EA1E-430D-9243-D8F6917DC989}

These examples illustrates enabling conversations in Select AI.

**Before You Begin**

Review [Perform Prerequisites for Select AI](select-ai-manage-profiles.html#GUID-3721296F-14A1-428A-B464-7FA25E9EC8F3).

**Note:**   A user with administrator privileges (ADMIN) must grant `EXECUTE` and enable network access control list (ACL).
{: .infoboxnote}


**Session-Based Conversations**

Create your AI profile. Set the `conversation` attribute to `true` in the profile, this action includes content from prior interactions or prompts, potentially including schema metadata, and set your profile. Once the profile is enabled, you can begin having conversations with your data. Use natural language to ask questions and follow up as needed.

–Grants EXECUTE privilege to ADB_USER – SQL> grant execute on DBMS_CLOUD_AI to ADB_USER;

– Grant Network ACL for OpenAI endpoint

SQL> BEGIN DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( host => ‘api.openai.com’, ace => xs$ace_type(privilege_list => xs$name_list(‘http’), principal_name => ‘ADB_USER’, principal_type => xs_acl.ptype_db) ); END; /

PL/SQL procedure successfully completed.

– – Create Credential for AI provider – EXEC DBMS_CLOUD.CREATE_CREDENTIAL( CREDENTIAL_NAME => ‘OPENAI_CRED’, username => ‘OPENAI’, password => ‘<your_api_token>’);

PL/SQL procedure successfully completed.

– – Create AI profile – BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name => ‘OPENAI’, attributes =>’{“provider”: “openai”, “credential_name”: “OPENAI_CRED”, “object_list”: [{“owner”: “SH”, “name”: “customers”}, {“owner”: “SH”, “name”: “countries”}, {“owner”: “SH”, “name”: “supplementary_demographics”}, {“owner”: “SH”, “name”: “profits”}, {“owner”: “SH”, “name”: “promotions”}, {“owner”: “SH”, “name”: “products”}], “conversation”: “true” }’); END; /

PL/SQL procedure successfully completed.

– – Enable AI profile in current session – SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE(‘OPENAI’);

PL/SQL procedure successfully completed.

– – Get Profile in current session – SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;

DBMS_CLOUD_AI.GET_PROFILE()

“OPENAI”

– – Use AI – what are the total number of customers;

CUSTOMER_COUNT

     55500

break out count of customers by country;

RESPONSE

COUNTRY_NAME CUSTOMER_COUNT Italy 7780 Brazil 832 Japan 624 United Kingdom 7557 Germany 8173 United States of America 18520 France 3833 Canada 2010 Spain 2039 China 712 Singapore 597 New Zealand 244 Poland 708 Australia 831 Argentina 403 Denmark 383 South Africa 88 Saudi Arabia 75 Turkey 91

what age group is most common;

RESPONSE

AGE_GROUP CUSTOMER_COUNT 65+ 28226

select ai keep the top 5 customers and their country by their purchases and include a rank in the result;

RESPONSE

RANK CUSTOMER_NAME COUNTRY PURCHASES 1 Abigail Ruddy Japan 276 2 Abigail Ruddy Italy 168 3 Abigail Ruddy Japan 74 3 Abner Robbinette Germany 74 5 Abner Everett France 68

SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE(‘OPENAI’);

PL/SQL procedure successfully completed.



**Customizable Conversations**

The following examples demonstrate using the conversation management API supporting customizable conversations. To use Select AI for multiple conversations:
1. Create a conversation

2. Set the conversation in the current user session

3. Use `Select AI <action> <prompt>`


You can create and set the conversation in the following ways:
- Use `DBMS_CLOUD_AI.CREATE_CONVERSATION` function and then set the conversation using `DBMS_CLOUD_AI.SET_CONVERSATION_ID`.

- Call the `DBMS_CLOUD_AI.CREATE_CONVERSATION` procedure directly to create and set the conversation in one step.
**Example: Create and Set Customizable Conversations**

The following example demonstrates how to create a conversation using `DBMS_CLOUD_AI.CREATE_CONVERSATION` function and set it using the `DBMS_CLOUD_AI.SET_CONVERSATION_ID` procedure.

SELECT DBMS_CLOUD_AI.CREATE_CONVERSATION; – in 19c, run SELECT DBMS_CLOUD_AI.create_conversation FROM dual;

CREATE_CONVERSATION

30C9DB6E-EA4D-AFBA-E063-9C6D46644B92

EXEC DBMS_CLOUD_AI.SET_CONVERSATION_ID(‘30C9DB6E-EA4D-AFBA-E063-9C6D46644B92’);

PL/SQL procedure successfully completed



The following example demonstrates running the `DBMS_CLOUD_AI.CREATE_CONVERSATION` procedure to create and set the `conversation_id` directly.

EXEC DBMS_CLOUD_AI.create_conversation;

PL/SQL procedure successfully completed.



You can also customize the conversation attributes such as `title`, `description`, `retention_days`, and `conversation_length` attributes.

SELECT DBMS_CLOUD_AI.CREATE_CONVERSATION( attributes => ‘{“title”:”My first conversation”, “description”:”this is my first conversation”, “retention_days”:5, “conversation_length”:5}’);

CREATE_CONVERSATION

38F8B874-7687-2A3F-E063-9C6D4664EC3A



You can view if a certain conversation exists by querying `DBA/USER_CLOUD_AI_CONVERSATIONS` view.

– Verify the setup SELECT conversation_id, conversation_title, description, retention_days, conversation_length FROM DBA_CLOUD_AI_CONVERSATIONS WHERE conversation_id = ‘38F8B874-7687-2A3F-E063-9C6D4664EC3A’;

CONVERSATION_ID CONVERSATION_TITLE DESCRIPTION RETENTION_DAYS CONVERSATION_LENGTH ———————————— ———————————————– ———————————- —————————— ——————- 38F8B874-7687-2A3F-E063-9C6D4664EC3A My first conversation this is my first conversation +00005 00:00:00.000000 5



You can also verify if a conversation is set by calling the `DBMS_CLOUD_AI.GET_CONVERSATION_ID` function.

SELECT DBMS_CLOUD_AI.GET_CONVERSATION_ID;

30C9DB6E-EA4F-AFBA-E063-9C6D46644B92



**Example: Use Customizable Conversations with Select AI**

After you create and set the conversation and enable your AI profile, you can start interacting with your data. Use natural language to ask questions and follow up as needed.

Use `SELECT AI <`ACTION`> <`PROMPT`>`.

SELECT AI CHAT What is the difference in weather between Seattle and San Francisco?;

RESPONSE

Seattle and San Francisco are both located on the West Coast of the United State s, but they have distinct weather patterns due to their unique geography and cli mate conditions. Here are the main differences:

  1. Rainfall: Seattle is known for its rainy reputation, with an average annu al rainfall of around 37 inches (94 cm). San Francisco, on the other hand, recei ves significantly less rainfall, with an average of around 20 inches (51 cm) per year.
  2. Cloud Cover: Seattle is often cloudy, with an average of 226 cloudy days per year. San Francisco is also cloudy, but to a lesser extent, with an average of 165 cloudy days per year.

……

SELECT AI CHAT Explain the difference again in one paragraph only.;

RESPONSE

Seattle and San Francisco have different weather patterns despite both experienc ing a mild oceanic climate. San Francisco tends to be slightly warmer, with aver age temperatures ranging from 45?F to 67?F, and receives less rainfall, around 2 0 inches per year, mostly during winter. In contrast, Seattle is cooler, with te mperatures ranging from 38?F to 64?F, and rainier, with around 37 inches of rain fall per year, distributed throughout the year. San Francisco is also known for its fog, particularly during summer, and receives more sunshine, around 160 sunn y days per year, although it’s often filtered through the fog. Overall, San Fran cisco’s weather is warmer and sunnier, with more pronounced seasonal variations, while Seattle’s is cooler and rainier, with more consistent temperatures throug hout the year.



**Example: Use GENERATE Function to Compare Two Conversations**

The following example show how two conversations are used interchangeably to ask questions and verify accurate responses. Each conversation begins with a different question focused on comparison. Later, when you ask the same follow-up question in both conversations, each returns a different answer based on its prior context.

– First conversation SELECT DBMS_CLOUD_AI.CREATE_CONVERSATION;

CREATE_CONVERSATION

30C9DB6E-EA4D-AFBA-E063-9C6D46644B92

– Second conversation SELECT DBMS_CLOUD_AI.CREATE_CONVERSATION;

CREATE_CONVERSATION

30C9DB6E-EA4E-AFBA-E063-9C6D46644B92

– Call generate using the first conversation. SELECT DBMS_CLOUD_AI.GENERATE( prompt => ‘What is the difference in weather between Seattle and San Francisco?’, profile_name => ‘GENAI’, action => ‘CHAT’, params => ‘{“conversation_id”:”30C9DB6E-EA4D-AFBA-E063-9C6D46644B92”}’) AS RESPONSE;

RESPONSE

Seattle and San Francisco, both located in the Pacific Northwest and Northern Ca lifornia respectively, experience a mild oceanic climate. However, there are som e notable differences in their weather patterns:

  1. Temperature: San Francisco tends to be slightly warmer than Seattle, espe cially during the summer months. San Francisco’s average temperature ranges from 45?F (7?C) in winter to 67?F (19?C) in summer, while Seattle’s average temperat ure ranges from 38?F (3?C) in winter to 64?F (18?C) in summer.

  2. Rainfall: Seattle is known for its rainy reputation, with an average annu al rainfall of around 37 inches (94 cm). San Francisco receives less rainfall, w ith an average of around 20 inches (51 cm) per year. However, San Francisco’s ra infall is more concentrated during the winter months, while Seattle’s rainfall i s more evenly distributed throughout the year.

……

– Call generate using the second conversation. SELECT DBMS_CLOUD_AI.GENERATE( prompt => ‘How does the cost of living compare between New York and Los Angeles?’, profile_name => ‘GENAI’, action => ‘CHAT’, params => ‘{“conversation_id”:”30C9DB6E-EA4E-AFBA-E063-9C6D46644B92”}’) AS RESPONSE;

RESPONSE

The cost of living in New York and Los Angeles is relatively high compared to ot her cities in the United States. However, there are some differences in the cost of living between the two cities. Here’s a comparison of the cost of living in New York and Los Angeles:

  1. Housing: The cost of housing is significantly higher in New York than in Los Angeles. The median home price in New York is around $999,000, while in Los Ange les it’s around $849,000. Rent is also higher in New York, with the average rent for a one-bedroom apartment being around $3,000 per month, compared to around $ 2,400 per month in Los Angeles.

  2. Food: The cost of food is relatively similar in both cities, with some variat ion in the cost of certain types of cuisine. However, eating out in New York can be more expensive, with the average cost of a meal at a mid-range restaurant be ing around $15-20 per person, compared to around $12-18 per person in Los Angele s.

……

– Call generate using the first conversation. SELECT DBMS_CLOUD_AI.GENERATE( prompt => ‘Explain the difference again in one paragraph only.’, profile_name => ‘GENAI’, action => ‘CHAT’, params => ‘{“conversation_id”:”30C9DB6E-EA4D-AFBA-E063-9C6D46644B92”}’) AS RESPONSE;

RESPONSE

Seattle and San Francisco have different weather patterns despite both experienc ing a mild oceanic climate. San Francisco tends to be slightly warmer, with aver age temperatures ranging from 45?F to 67?F, and receives less rainfall, around 2 0 inches per year, mostly during winter. In contrast, Seattle is cooler, with te mperatures ranging from 38?F to 64?F, and rainier, with around 37 inches of rain fall per year, distributed throughout the year. San Francisco is also known for its fog, particularly during summer, and receives more sunshine, around 160 sunn y days per year, although it’s often filtered through the fog. Overall, San Fran cisco’s weather is warmer and sunnier, with more pronounced seasonal variations, while Seattle’s is cooler and rainier, with more consistent temperatures throug hout the year.

– Call generate using the second conversation. SELECT DBMS_CLOUD_AI.GENERATE( prompt => ‘Explain the difference again in one paragraph only.’, profile_name => ‘GENAI’, action => ‘CHAT’, params => ‘{“conversation_id”:”30C9DB6E-EA4E-AFBA-E063-9C6D46644B92”}’) AS RESPONSE;

RESPONSE

The cost of living in New York is approximately 20-30% higher than in Los Angele s, mainly due to the higher cost of housing and transportation. New York has a m edian home price of around $999,000 and average rent of $3,000 per month for a o ne-bedroom apartment, compared to Los Angeles’ median home price of $849,000 and average rent of $2,400 per month. While the cost of food and utilities is relat ively similar in both cities, the cost of transportation is higher in Los Angele s due to its car-centric culture, but the cost of public transportation is highe r in New York. Overall, the total monthly expenses for a single person in New Yo rk can range from $4,600, compared to around $4,050 in Los Angeles, making New Y ork the more expensive city to live in.



You may call the `DBMS_CLOUD_AI.GENERATE` function without specifying a conversation; however, in such cases, a meaningful response should not be expected.

– Ask SELECT AI using the second conversation. SELECT DBMS_CLOUD_AI.GENERATE( prompt => ‘Explain the difference again in one paragraph only.’, profile_name => ‘GENAI’, action => ‘CHAT’) AS RESPONSE;

RESPONSE

There is no previous explanation to draw from, as this is the beginning of our c onversation. If you would like to ask a question or provide a topic, I would be happy to explain the differences related to it in one paragraph.



**Example: Verify Conversations through DBMS_CLOUD_AI Views**

You can query the `DBMS_CLOUD_AI` conversation views to review conversation and prompt details. See [DBMS_CLOUD_AI Views](dbms-cloud-ai-views.html#GUID-5C826A3E-E60B-4D1C-AD0B-E385F6ECEBA5) for more details.

  **Note:**  The Views with the `DBA_` prefix are available only to users with administrator privileges (ADMIN).
  {: .infoboxnote}

SELECT conversation_id, conversation_title, description FROM dba_cloud_ai_conversations;

CONVERSATION_ID

CONVERSATION_TITLE —————————————————————————————————- DESCRIPTION ——————————————————————————– 30C9DB6E-EA4D-AFBA-E063-9C6D46644B92 Seattle vs San Francisco Weather The conversation discusses the comparison of weather patterns between Seattle an d San Francisco, focusing on the differences in temperature, rainfall, fog, suns hine, and seasonal variation between the two cities.

30C9DB6E-EA4E-AFBA-E063-9C6D46644B92 NY vs LA Cost Comparison The conversation discusses and compares the cost of living in New York and Los A ngeles, covering housing, food, transportation, utilities, and taxes to provide an overall view of the expenses in both cities.

SELECT conversation_id, count(*) FROM dba_cloud_ai_conversation_prompts GROUP BY conversation_id;

CONVERSATION_ID COUNT(*) ———————————— ———- 30C9DB6E-EA4D-AFBA-E063-9C6D46644B92 2 30C9DB6E-EA4E-AFBA-E063-9C6D46644B92 2



**Example: Update Conversation Details**

You can update the `title`, `description`, and `retention_days` of a conversation using the `DBMS_CLOUD_AI.UPDATE_CONVERSATION` procedure. You can verify the update by querying the `DBMS_CLOUD_AI` conversation view.

– Update the second conversation’s title, description and retention_days SQL> EXEC DBMS_CLOUD_AI.update_conversation(conversation_id => ‘30C9DB6E-EA4E-AFBA-E063-9C6D46644B92’, attributes => ‘{“retention_days”:20, “description”:”This a description”, “title”:”a title”, “conversation_length”:20}’);

PL/SQL procedure successfully completed.

– Verify the information for the second conversation SQL> SELECT conversation_title, description, retention_days FROM dba_cloud_ai_conversations WHERE conversation_id = ‘30C9DB6E-EA4E-AFBA-E063-9C6D46644B92’;

CONVERSATION_TITLE DESCRIPTION RETENTION_DAYS LENGTH ————————– ———————————— ————– ————– a title This a description 20 20



**Example: Delete a Prompt**

You can delete an individual prompt from your conversations and verify the modification by querying the `DBMS_CLOUD_AI` conversation view.

– Find the latest prompt for first conversation SELECT conversation_prompt_id FROM dba_cloud_ai_conversation_prompts WHERE conversation_id = ‘30C9DB6E-EA4D-AFBA-E063-9C6D46644B92’ ORDER BY created DESC FETCH FIRST ROW ONLY;

CONVERSATION_PROMPT_ID

30C9DB6E-EA61-AFBA-E063-9C6D46644B92

– Delete the prompt EXEC DBMS_CLOUD_AI.DELETE_CONVERSATION_PROMPT(‘30C9DB6E-EA61-AFBA-E063-9C6D46644B92’);

PL/SQL procedure successfully completed.

– Verify if the prompt is deleted SELECT conversation_prompt_id FROM dba_cloud_ai_conversation_prompts WHERE conversation_id = ‘30C9DB6E-EA4D-AFBA-E063-9C6D46644B92’;

– Only one prompt now CONVERSATION_PROMPT_ID ———————————— 30C9DB6E-EA5A-AFBA-E063-9C6D46644B92



**Example: Drop a Conversation**

You can delete the entire conversation, which also removes all prompts associated with it.

– Delete the first conversation EXEC DBMS_CLOUD_AI.DROP_CONVERSATION(‘30C9DB6E-EA4D-AFBA-E063-9C6D46644B92’);

PL/SQL procedure successfully completed.

– Verify if the conversation and its prompts are removed SELECT conversation_id FROM dba_cloud_ai_conversations;

– We only have the second conversation now CONVERSATION_ID ———————————— 30C9DB6E-EA4E-AFBA-E063-9C6D46644B92

SELECT conversation_id, count(*) FROM dba_cloud_ai_conversation_prompts GROUP BY conversation_id;

– We only have prompts in the second conversation CONVERSATION_ID COUNT(*) ———————————— ———- 30C9DB6E-EA4E-AFBA-E063-9C6D46644B92 2



## Example: Set Up and Use Select AI with RAG {#GUID-2FBD7DDB-CAC3-47AF-AB66-17F44C2ADAA4}

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.

**Before You Begin**

Review:


- [Perform Prerequisites for Select AI](select-ai-manage-profiles.html#GUID-3721296F-14A1-428A-B464-7FA25E9EC8F3)

- [DBMS_CLOUD_AI Vector Index Attributes](dbms-cloud-ai-package.html#GUID-F6A65B2A-AE6D-4751-BDD7-137D49248160)


<div class="infoboxnote" markdown="1">

**Note:**
- You can specify the content location for creating a vector index by using either an object storage URI or a database directory file. This approach provides flexibility in sourcing files from object storage or from files managed through Oracle AI database directories.

- Multibyte characters are not supported in file names for input documents. Files with multibyte characters are in the file name are skipped during vectorization.

</div>

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 AI Database 26ai.

–Grants EXECUTE privilege to ADB_USER GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;

–Grants EXECUTE privilege DBMS_CLOUD_PIPELINE to ADB_USER GRANT EXECUTE on DBMS_CLOUD_PIPELINE to ADB_USER;

– Create the OpenAI credential BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => ‘OPENAI_CRED’, username => ‘OPENAI_CRED’, password => ‘' ); 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 => ‘', 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 }’); 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’, wait_for_completion => false, 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:



The `WAIT_FOR_COMPLETION` parameter is included in the `DBMS_CLOUD_AI.CREATE_VECTOR_INDEX` call. The default value is `FALSE`, meaning the procedure returns immediately after scheduling vectorization.

If you run `NARRATE` prompts before the vector index is fully created, Select AI may produce incomplete or suboptimal responses because not all documents are available for retrieval. To avoid this, you can set `WAIT_FOR_COMPLETION` to `TRUE`. When set to `TRUE`, the procedure does not return until vectorization is complete. This ensures all documents are indexed before you begin issuing RAG-based prompts. For large document collections, this may take a significant amount of time. See [DBMS_CLOUD_AI .CREATE_VECTOR_INDEX Procedure](dbms-cloud-ai-package.html#GUID-CB37AB86-B625-4798-A3F4-8DD6DBA8B491) for more details.

**Example: Specify Content Location for Vector Index Creation**

You can specify content location for vector index creation using either Object Storage URIs or database directory files.

**Object Storage URI**

Specify a `location_uri` that points to the object storage location containing the documents you want to index. For example,

https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace/b/bucket/o/documents/



**Directory Files**

You can also use directory objects to reference local files. Use this format `MY_DIR:file.txt`.

The directory name (`MY_DIR`) is case-insensitive by default. The file name is case-sensitive.

Wildcards are supported only for filenames:


- Use `*` for multiple characters: `MY_DIR:*`


- Use `?` for a single character: `MY_DIR:file?.txt`


- To specify a case-sensitive directory name, wrap it in double quotes: `"MyDir":*`


- To include a quote character in the filename, use two quotes: `MY_DIR:''file.txt`


The following example creates a vector index named `RAG_INDEX_DIR` using all `.txt` files located in the directory object `MY_DIR`.

sql – 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”: “RAG_INDEX_DIR”, “temperature”: 0.2, “max_tokens”: 4096 }’); 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 => ‘RAG_INDEX_DIR’, attributes => ‘{“vector_db_provider”: “oracle”, “location”: “MY_DIR:*.txt”, “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.

**Example: Enable or Disable Source Content in RAG Output**

Select AI RAG includes an `enable_sources` attribute that controls whether source documents retrieved during vector search appear in the final response. This attribute is enabled by default. When set to `true`, Select AI validates and includes the retrieved source documents in the output. When set to `false`, Select AI still validates the sources but does not include the `Sources` section in the response. See [DBMS_CLOUD_AI Vector Index Attributes](dbms-cloud-ai-package.html#GUID-F6A65B2A-AE6D-4751-BDD7-137D49248160) for more information.

The following example shows `enable_sources` set to `true`. Select AI validates source documents and includes them in the response output.

– enable_sources = true (default) 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:



The following example shows `enable_sources` set to `false`. Select AI still validates the source documents but does not print the `Sources` section in the output.

sql – enable_sources = false (sources validated but not displayed) 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.

## Example: Select AI with In-database Transformer Models {#GUID-812B2B7E-F460-4418-BA88-2FA572A1778E}

This example demonstrates how you can import a pretrained transformer model that is stored in Oracle object storage into your Oracle AI Database 26ai 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 AI Database 26ai instance.


- optionally, access to Oracle object storage.


**Import a Pretrained Transformer Model into your Oracle AI Database 26ai From Oracle Object Storage**

Review the steps in [Import Pretrained Models in ONNX Format for Vector Generation Within the Database](/pls/topic/lookup?ctx=en/cloud/paas/autonomous-database/serverless/adbsb&id=VECSE-GUID-D8140BF9-08E9-4B3F-9E28-E40A6FD181A4) and the blog [Pre-built Embedding Generation model for Oracle AI Database 26ai](https://blogs.oracle.com/machinelearning/post/use-our-prebuilt-onnx-model-now-available-for-embedding-generation-in-oracle-database-23ai) 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.

– 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 [Perform Prerequisites for Select AI](select-ai-manage-profiles.html#GUID-3721296F-14A1-428A-B464-7FA25E9EC8F3) to complete the prerequisites.

The following is an example for generating vector embeddings only:

sql 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 [Select your AI Provider and LLMs](select-ai-about.html#GUID-FDAEF22A-5DDF-4BAE-A465-C1D568C75812) 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:

sql BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => ‘GENAI_CRED’, user_ocid => ‘ocid1.user.oc1..aaaa…’, tenancy_ocid => ‘ocid1.tenancy.oc1..aaaa…’, private_key => ‘', 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](/pls/topic/lookup?ctx=en/cloud/paas/autonomous-database/serverless/adbsb&id=DMPRG-GUID-34AABD01-9FF9-4C1A-A2A3-89A1827D10AE) 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;

sql 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:

sql 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 [Perform Prerequisites for Select AI](select-ai-manage-profiles.html#GUID-3721296F-14A1-428A-B464-7FA25E9EC8F3) to provide the necessary privileges.

–Grant create any directory privilege to the user GRANT CREATE ANY DIRECTORY to ADB_USER;

– 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 => ‘', password => '' ); END; /

PL/SQL procedure successfully completed.

– Create the profile with Oracle AI 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;

– 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 => ‘', 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 => '', 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:



## Example: Improve SQL Query Generation {#GUID-F9C4BEFB-1570-430F-AB36-347F1A4B8A71}

These examples demonstrate how comments, annotations, foreign key, and referential integrity constraints in database tables and columns can improve the generation of SQL queries from natural language prompts.

**Example: Improve SQL Generation with Table and Column Comments**

If you have table and column comments in your database tables, enable `"comments":"true"` parameter in `DBMS_CLOUD_AI.CREATE_PROFILE` function to retrieve table level and column level comments. The comments are added to the metadata of the LLM for a better SQL generation.

– 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



**Example: Improve SQL Query Generation with Table and Column Annotations**

This example demonstrates the integration of annotations in Select AI, applicable in Oracle AI Database 26ai. The annotations are added to the metadata that is sent to the LLM.

If you have a table with annotations in your schema, enable `"annotations":"true"` in the `DBMS_CLOUD_AI.CREATE_PROFILE` function to instruct Select AI to add annotations to the metadata.

– – Annotations –

CREATE TABLE emp2 ( empno NUMBER, ename VARCHAR2(50) ANNOTATIONS (display ‘lastname’), salary NUMBER ANNOTATIONS (“person_salary”, “column_hidden”), deptno NUMBER ANNOTATIONS (display ‘department’) )ANNOTATIONS (requires_audit ‘yes’, version ‘1.0’, owner ‘HR Organization’);

Table created.

BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name => ‘GOOGLE_ANNOTATIONS’, attributes => ‘{“provider”: “google”, “credential_name”: “GOOGLE_CRED”, “object_list”: [{“owner”: “ADB_USER”, “name”: “emp2”}], “annotations” : “true” }’); END; /

PL/SQL procedure successfully completed.

EXEC DBMS_CLOUD_AI.SET_PROFILE(‘GOOGLE_ANNOTATIONS’);

PL/SQL procedure successfully completed.



**Example: Improve SQL Query Generation with Foreign Key and Referential Key Constraints**

This example demonstrates the ability of the LLM to generate accurate `JOIN` conditions by retrieving the foreign key and referential key constraints into the metadata of the LLM. The foreign key and referential key constraints provide structured relationship data between the tables to the LLM.

Enable `"constraints":"true"` in the `DBMS_CLOUD_AI.CREATE_PROFILE` function for Select AI to retrieve foreign key and referential key.

– – Referential Constraints – CREATE TABLE dept_test ( deptno NUMBER PRIMARY KEY, dname VARCHAR2(50) );

Table created.

CREATE TABLE emp3 ( empno NUMBER PRIMARY KEY, ename VARCHAR2(50), salary NUMBER, deptno NUMBER, CONSTRAINT emp_dept_fk FOREIGN KEY (deptno) REFERENCES dept_test(deptno) );

Table created.

BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name=>’GOOGLE_CONSTRAINTS’, attribues =>’{“provider”: “google”, “credential_name”: “GOOGLE_CRED”, “object_list”: [{“owner”: “ADB_USER”, “name”: “dept_test”}, {“owner”: “ADB_USER”, “name”: “emp3”}], “constraints” : “true” }’); END; /

PL/SQL procedure successfully completed.

EXEC DBMS_CLOUD_AI.SET_PROFILE(‘GOOGLE_CONSTRAINTS’);

PL/SQL procedure successfully completed.



**Example: Automatically Detect Relevant Table Metadata**

These examples shows how Select AI automatically detects relevant tables and sends metadata only for those specific tables relevant to the query in Oracle AI Database 26ai. To enable this feature, set `object_list_mode` to *`automated`*. This automatically creates a vector index named `<profile_name>_OBJECT_LIST_VECINDEX`. The vector index is initialized with default attributes and values such as `refresh_rate`, `similarity_threshold`, and `match_limit`. You can modify some of the attributes through `DBMS_CLOUD_AI.UPDATE_VECTOR_INDEX`. See [UPDATE_VECTOR_INDEX Procedure](dbms-cloud-ai-package.html#GUID-125F4C49-27FC-4E13-A278-2512D4B8DBCC) for more information.

One profile is configured to use `object_list` to specify the schema or the objects in the schema while the other does not specify `object_list`. However, the same SQL construct is expected.

Review [Perform Prerequisites for Select AI](select-ai-manage-profiles.html#GUID-3721296F-14A1-428A-B464-7FA25E9EC8F3) to provide access to the `DBMS_CLOUD_AI` package and provide network access to the AI provider.

BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name=>’OCI_AUTO’, attributes=>’{“provider”: “oci”, “credential_name”: “GENAI_CRED”, “object_list”: [{“owner”: “SH”}], “oci_compartment_id”: “ocid1.compartment.oc1..aaaa…”, “model” : “meta.llama-3.3-70b-instruct” }’); END; /

PL/SQL procedure successfully completed.

EXEC DBMS_CLOUD_AI.SET_PROFILE(‘OCI_AUTO’);

PL/SQL procedure successfully completed.

select ai showsql how many customers in San Francisco are married;

RESPONSE

SELECT COUNT(DISTINCT c.”CUST_ID”) AS “NUMBER_OF_CUSTOMERS” FROM “SH”.”CUSTOMERS” c WHERE UPPER(c.”CUST_CITY”) = UPPER(‘San Francisco’) AND UPPER(c.”CUST_MARITAL_STATUS”) = UPPER(‘married’)



The following example compares the same scenario without using `object_list`. When you don't specify `object_list`, Select AI automatically chooses all objects available to the current schema.

BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name=>’OCI_AUTO1’, attributes=>’{“provider”: “oci”, “credential_name”: “GENAI_CRED”, “oci_compartment_id”: “ocid1.compartment.oc1..aaaa…”, “object_list_mode”: “automated”, “model” : “meta.llama-3.3-70b-instruct” }’); END; / PL/SQL procedure successfully completed.

EXEC DBMS_CLOUD_AI.SET_PROFILE(‘OCI_AUTO1’);

PL/SQL procedure successfully completed.

select ai showsql how many customers in San Francisco are married?;

RESPONSE

SELECT COUNT(c.”CUST_ID”) AS “Number_of_Customers” FROM “SH”.”CUSTOMERS” c WHERE UPPER(c.”CUST_CITY”) = UPPER(‘San Francisco’) AND UPPER(c.”CUST_MARITAL_STATUS”) = UPPER(‘Married’)



## Example: Use Select AI with Database Links to Query Another Autonomous AI Database {#GUID-FF2F5067-F8F7-4D3C-B53E-38647DF73738}

This example shows how to set up a Database Link from Autonomous AI Database to the source database and use Select AI to generate SQL from natural language prompts. Select AI uses the metadata from the source database to generate SQL.

**Before You Begin**

Review
- [Perform Prerequisites for Select AI](select-ai-manage-profiles.html#GUID-3721296F-14A1-428A-B464-7FA25E9EC8F3)


- Download your cloud wallet credentials and upload to an Object Storage bucket:

  - Download the wallet (`cwallet.sso`) from your source database through OCI Console or Cloud Shell. See [Download Database Connection Information](connect-download-wallet.html#GUID-DED75E69-C303-409D-9128-5E10ADD47A35) for more details.


  - Upload the wallet file to an Object Storage bucket. See [Creating an Object Storage Bucket](https://docs.oracle.com/en-us/iaas/Content/Object/Tasks/managingbuckets_topic-To_create_a_bucket.htm) for more details.


This example shows how to set up a Database Link (DB Link) in an Autonomous AI Database to securely connect with another Autonomous AI Database. However, you can create DB Links to non-Autonomous AI Databases and third-party databases. Database links enable Select AI to query across remote data sets without replicating data through a wallet, credentials, and linked views.

You first create a credential to store your username and password to authenticate the source database. Create a directory to store the wallet files used for authentication when you are connecting to another Autonomous AI Database. Download the source database wallet credentials using `GET_OBJECT` procedure. Create a secure Database Link from Autonomous AI Database to the source Autonomous AI Database. You then create views on the remote tables. Create an AI profile with `object_list` attribute specifying the views as JSON objects and include the view name directly in `object_list` because Select AI profiles do not recognize database link syntax. Finally, issue any NL2SQL Select AI actions such as `runsql`, `showsql`, `explainsql`, `narrate`, or `chat`. This example uses `showsql`.

–Create Cloud Credential (run in Autonomous AI Database)

BEGIN DBMS_CLOUD.DROP_CREDENTIAL(credential_name => ‘DB_LINK_CRED’); EXCEPTION WHEN OTHERS THEN NULL; END; / BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => ‘DB_LINK_CRED’, username => ‘DB_USER’, – Username on source database password => ‘' -- Password for source database ); END; /

–Create Directory (run in Autonomous AI Database)

CREATE DIRECTORY dblink_wallet_dir AS ‘DATA_PUMP_DIR’;

–Prepare and Upload Source Database Wallet in Object Storage bucket and run in Autonomous AI Database: BEGIN DBMS_CLOUD.GET_OBJECT( credential_name => ‘DB_LINK_CRED’, object_uri => ‘https://objectstorage.ca-toronto-1.oraclecloud.com/n/namespace-string/b/bucketname/o/data_folder/cwallet.sso/cwallet.sso’, directory_name => ‘DBLINK_WALLET_DIR’ ); END; /

–Create Database Link (Drop dblink if it exists) to Source Database (run in Autonomous AI Database)

BEGIN DBMS_CLOUD_ADMIN.DROP_DATABASE_LINK(db_link_name => ‘MY_DATA_LINK’); EXCEPTION WHEN OTHERS THEN NULL; END; /

BEGIN DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK( db_link_name => ‘MY_DATA_LINK’, hostname => ‘adb.-1.oraclecloud.com', -- Source database hostname port => '1522', -- Source database port service_name => 'your_service_name.adb.oraclecloud.com', -- Source database service credential_name => 'DB_LINK_CRED', directory_name => 'DBLINK_WALLET_DIR' ); END; /

–Create Views (run in Autonomous AI Database)

CREATE VIEW customer_view AS SELECT * FROM customer@MY_DATA_LINK; CREATE VIEW streams_view AS SELECT * FROM streams@MY_DATA_LINK;

–Create an AI Profile (run in Autonomous AI Database)

BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name => ‘MY_AI_PROFILE’, attributes => JSON_OBJECT( ‘provider’ => ‘openai’, ‘credential_name’ => ‘OPENAI_CRED’, ‘object_list’ => JSON_ARRAY( JSON_OBJECT(‘owner’ => ‘SELECT_AI_USER’, ‘name’ => ‘CUSTOMER_VIEW’), JSON_OBJECT(‘owner’ => ‘SELECT_AI_USER’, ‘name’ => ‘STREAMS_VIEW’) ) ) ); END; /

–Showsql test:

SELECT AI SHOWSQL how many customers are there;

–Run on Source Database



Copy the generated SQL, remove `@MY_DATA_LINK` and run the query on your source database to verify.

## Example: Use Select AI with Database Links to Query Non-Oracle AI Database {#GUID-F2F87A66-F8AC-4D0C-A1D6-DE4BA800E4F9}

This example shows how Autonomous AI Database works as an AI Data Gateway and uses Select AI to generate federated SQL that joins local Oracle data with remote PostgreSQL data. Autonomous AI Database support for Oracle-managed heterogeneous connectivity makes it easy to create database links to non-Oracle databases. The PostgreSQL database is the official, authoritative source for the data.

**Before You Begin**

Review
- [Perform Prerequisites for Select AI](select-ai-manage-profiles.html#GUID-3721296F-14A1-428A-B464-7FA25E9EC8F3)


- Use a PostgreSQL user that has read access to the target schema or table


- Confirm network access from Autonomous AI Database to the PostgreSQL endpoint


**Use Case Scenario**


- Autonomous AI Database contains `CUSTOMER_REVENUE` table.


- PostgreSQL contains `support_ticket_metrics` table.


- Select AI generates SQL from a natural language prompt that joins both tables.


1. Sample `CUSTOMER_REVENUE` table in Autonomous AI Database:

CREATE TABLE customer_revenue ( customer_id NUMBER NOT NULL, customer_name VARCHAR2(100) NOT NULL, region VARCHAR2(50) NOT NULL, revenue_quarter VARCHAR2(7) NOT NULL, revenue_amount NUMBER(15,2) NOT NULL, CONSTRAINT customer_revenue_pk PRIMARY KEY (customer_id, revenue_quarter) );


2. Sample `support_ticket_metrics` table in PostgreSQL:
CREATE TABLE support_ticket_metrics (
ticket_id       BIGSERIAL   PRIMARY KEY,
customer_id         BIGINT      NOT NULL,
severity        VARCHAR(20)     NOT NULL,

opened_at       TIMESTAMP   NOT NULL,
resolved_at         TIMESTAMP,
resolution_time_hours   NUMERIC(10,2)
);
<pre class="copy"><code> 3. Create a credential that stores the PostgreSQL username and password.</code></pre>
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'POSTGRESQL_CRED',
    username        => 'app_user',
    password        => '<postgresql_password>'
  );
END;
/
<pre class="copy"><code> 4. Create a heterogeneous database link to PostgreSQL. This example uses `gateway_params` to set the database type and SSL. See [Create Database Links to Non-Oracle AI Databases with Oracle-Managed Heterogeneous Connectivity](database-links-other-databases-oracle-managed.html#GUID-9FBC138F-7B22-4D14-96CF-349ADE2959F5) for more details.</code></pre>
BEGIN
  DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
    db_link_name        => 'POSTGRESQL_LINK',
    hostname            => 'primary.***.postgresql.ca-toronto-1.oci.oraclecloud.com',
    port                =>  5432,
    service_name        => 'sales',
    credential_name     => 'POSTGRESQL_CRED',
    gateway_params      => JSON_OBJECT('db_type' VALUE 'postgres', 'enable_ssl' VALUE true),
    ssl_server_cert_dn  => NULL,
    private_target      => true
  );
END;
/
<pre class="copy"><code> 5. Create a local view on the PostgreSQL table and map the remote PostgreSQL table into the Autonomous AI Database schema with a view.</code></pre>
CREATE VIEW support_ticket_metrics AS
SELECT *
FROM "app_schema"."support_ticket_metrics"@postgresql_link;
<pre class="copy"><code> Select AI uses the metadata of the view during NL2SQL generation.  6. Configure network ACL access for the AI provider endpoint as an `ADMIN` user.</code></pre>
BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => 'api.openai.com',
    ace  => xs$ace_type(
              privilege_list => xs$name_list('http'),
              principal_name => 'ADMIN',
              principal_type => xs_acl.ptype_db
            )
  );
END;
/
<pre class="copy"><code> 7. Create a Select AI profile that includes local and remote objects. List both the local table and the view created on the PostgreSQL table in `object_list`.  **Note:**   This step assumes that you have created your OpenAI credentials. See [Example: Select AI with OpenAI](select-ai-examples.html#GUID-D33BA5D9-59FB-4B98-948F-D768D13ED01B) for more details. {: .infoboxnote}</code></pre>
BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name => 'OPENAI',
    attributes   => '{
      "provider": "openai",
      "model": "gpt-4.1",
      "credential_name": "OPENAI_CRED",
      "object_list": [
        {"owner":"ADB_USER","name":"SUPPORT_TICKET_METRICS"},
        {"owner":"ADB_USER","name":"CUSTOMER_REVENUE"}
      ]
    }'
  );
END;
/
<pre class="copy"><code> 8. Set the Select AI profile.</code></pre>
EXEC DBMS_CLOUD_AI.SET_PROFILE('OPENAI');
<pre class="copy"><code> The session sets the profile so Select AI uses the correct provider, credentials, and object metadata.  9. Test with Select AI.</code></pre>
select ai Which customers with over 1M USD in revenue last quarter
had critical support tickets, and what was the average resolution time by region;

RESPONSE:
REGION  CUSTOMER_NAME       AVG_RESOLUTION_TIME_HOURS

------  --------------------    -------------------------
MEA     Customer-5359       9
<pre class="copy"><code> Review the generated SQL by using `showsql`. You see that it is a join between two tables, one in Autonomous AI Database, and one in PostgreSQL.</code></pre>
select ai showsql Which customers with over 1M USD in revenue last quarter
had critical support tickets, and what was the average
resolution time by region;
RESPONSE

--------------------------------------------------------------------------------
SELECT
cr."CUSTOMER_NAME" AS customer_name,
cr."REGION" AS region,
AVG(stm."resolution_time_hours") AS avg_resolution_time_hours
FROM
"ADB_USER"."CUSTOMER_REVENUE" cr
JOIN "ADB_USER"."SUPPORT_TICKET_METRICS" stm
ON cr."CUSTOMER_ID" = stm."customer_id"
WHERE
cr."REVENUE_QUARTER" = (
SELECT MAX(cr2."REVENUE_QUARTER")
FROM "ADB_USER"."CUSTOMER_REVENUE" cr2
)
AND cr."REVENUE_AMOUNT" > 1000000
AND stm."severity" = 'Critical'
GROUP BY
cr."CUSTOMER_NAME",
cr."REGION"
<pre class="copy"><code>  Select AI augments the prompt with table and view metadata, then sends it to the LLM to generate federated SQL. The AI Data Gateway coordinates the query and accesses PostgreSQL through the database link.  ## Example: Use Select AI with Cloud Links to Query Another Autonomous AI Database {#GUID-2A666234-4B8F-432A-8A52-80BFD3DA236A}  This example shows how to use Cloud Links to access data stored in another Autonomous AI Database and query it using Select AI.  Cloud Links provide read-only access to registered tables and views across databases within a tenancy, compartment, or region.  **Before You Begin**  Review - [Perform Prerequisites for Select AI](select-ai-manage-profiles.html#GUID-3721296F-14A1-428A-B464-7FA25E9EC8F3)   This example walks through the complete flow required to make data available through Cloud Links and use it with Select AI.  Source Database: Oracle Autonomous AI Database where your data (tables or views) that you want to share resides.  Target Database (receiving side) acts as the AI Data Gateway, formerly called AI Proxy Database: Oracle Autonomous AI Database where you configure Select AI and issue natural language queries.  Cloud Links provide a secure, read-only mechanism for sharing data across Autonomous AI Databases without copying data, managing database credentials, or setting up network connections manually.  1. The `ADMIN` user authorizes a data owner to register tables and views for remote access. This step controls who can publish data for Cloud Link sharing.</code></pre>sql
--run on SOURCE database as ADMIN
BEGIN
  DBMS_CLOUD_LINK_ADMIN.GRANT_REGISTER(
    username => 'ADB_USER',
    scope    => 'MY$TENANCY'
  );
END;
/
<pre class="copy"><code> 2. The data owner registers a table (`CUSTOMERS`) and assigns it a namespace, name, and scope. Registration makes the data discoverable to other Autonomous AI Databases within the specified scope (tenancy, compartment, or region).</code></pre>sql
--run on SOURCE database as the Select AI user
BEGIN
  DBMS_CLOUD_LINK.REGISTER(
    schema_name => 'ADB_USER',
    schema_object => 'CUSTOMERS',                 -- Table or view name
    namespace => 'SALES_DATA',                    -- Namespace the user provides as a name for Cloud Link access
    name => 'CUSTOMERS',                          -- Name visible to consumers
    description => 'customer data',               -- Table or view description
    scope => 'MY$TENANCY'                         -- MY$COMPARTMENT, MY$TENANCY, or MY$REGION
  );
END;
/
<pre class="copy"><code> **Note:**  Metadata sync may take several minutes. During this window, the data set may not immediately appear on the target database.

  1. To verify registrations on the source database as ADMIN, query the data dictionary. This query confirms the namespace, name and the scope where the data set is visible.</code></pre>sql select namespace, name , json_value(scope,’$.TENANCY[]’) tenancy , json_value(scope,’$.COMPARTMENT[]’) compartments , json_value(scope,’$.REGION[*]’) region , description from dba_cloud_link_registrations;

    Returns:

    NAMESPACE NAME TENANCY COMPARTMENTS REGION DESCRIPTION SALES_DATA CUSTOMERS OCID1.TENANCY…. (null) (null) customer data
  2. On the receiving database, the ADMIN user grants read access so that users can consume registered Cloud Link data sets.</code></pre>sql BEGIN DBMS_CLOUD_LINK_ADMIN.GRANT_READ( username => ‘ADB_USER’ ); END; /
  3. The target database user can list or search available Cloud Link data sets to confirm access and identify the correct namespace and object names.</code></pre>sql – View all accessible data sets SELECT NAMESPACE, NAME, DESCRIPTION FROM ALL_CLOUD_LINK_ACCESS;

    Returns: NAMESPACE NAME DESCRIPTION SALES_DATA CUSTOMERS customer data
  4. Optionally, search for specific data sets. This enables you to search for data sets using keywords without knowing the exact namespace and name.</code></pre>sql – DECLARE result CLOB DEFAULT NULL; BEGIN DBMS_CLOUD_LINK.FIND(‘CUSTOMERS’, result); DBMS_OUTPUT.PUT_LINE(result); END; /

    Returns:

    [{“name”:”CUSTOMERS”,”namespace”:”SALES_DATA”,”description”:”customer data”}]

  5. Create local tables or views using Cloud Link syntax.</code></pre>sql CREATE VIEW customers_view AS SELECT * FROM SALES_DATA.CUSTOMERS@cloud$link;

    CREATE TABLE customers_table AS SELECT * FROM SALES_DATA.CUSTOMERS@cloud$link;

    
On the target database, create views or tables that reference the remote data using the `@cloud$link` syntax. These objects behave like local database objects but read data from the source database.
  6. Create a Select AI profile. Note: This step assumes that you have created your OCI credentials. See Example: Select AI with OCI Generative AI for more details. {: .infoboxnote}</code></pre>sql BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name => ‘MY_AI_PROFILE’, attributes => ‘{“provider”: “oci”, “credential_name”: “MY_AI_CRED”, “object_list”: [ {“owner”: “ADB_USER”, “name”: “CUSTOMERS_VIEW”}, {“owner”: “ADB_USER”, “name”: “CUSTOMERS_TABLE”} ] }’); END; / 
    
A Select AI profile includes the Cloud Link views or tables in its `object_list`. This step tells Select AI which objects it can use when generating SQL.
  7. Set the Select AI profile.</code></pre>sql EXEC DBMS_CLOUD_AI.SET_PROFILE(‘MY_AI_PROFILE’) 
    
The session sets the profile so Select AI uses the correct provider, credentials, and object metadata.
  8. Test with Select AI.</code></pre>sql SELECT AI SHOWSQL how many customers do I have; 
    
A natural language prompt such as *"how many customers do I have"* is submitted. Select AI uses the metadata from the Cloud Link table to generate SQL that queries the shared data.

    RESPONSE SELECT COUNT(“ct”.”ID”) AS “customer_count” FROM “ADB_USER”.”CUSTOMERS_TABLE” “ct”

    

In stateless environments (such as APEX or Database Actions SQL Worksheet), test Select AI using `DBMS_CLOUD_AI.GENERATE` and pass the profile name directly.

    sql DECLARE result CLOB; BEGIN result := DBMS_CLOUD_AI.GENERATE( prompt => ‘how many customers do I have’, profile_name => ‘MY_AI_PROFILE’, action => ‘showsql’ ); DBMS_OUTPUT.PUT_LINE(result); END; /

## Example: Use External Table over Table Hyperlink with Select AI {#GUID-51154601-2571-4D76-9556-3E2EDC3E6B44}

This example shows how an Autonomous AI Database (consumer database) acts as an AI Data Gateway, formerly called AI Proxy Database, to query remote data hosted in another Autonomous AI Database (provider database) using an External Table over a Table Hyperlink.

**Before You Begin**

Review
- [Perform Prerequisites for Select AI](select-ai-manage-profiles.html#GUID-3721296F-14A1-428A-B464-7FA25E9EC8F3)


- [DBMS_DATA_ACCESS CREATE_URL Procedure](dbms-data-access.html#GUID-48BA3347-B942-4FF9-AA42-467603660E2E)


- [DBMS_CLOUD CREATE_EXTERNAL_TABLE Procedure](dbms-cloud-subprograms.html#GUID-2AFBEFA4-992E-4F53-96DB-F560084C7DA9)


This example uses the SH schema tables `SH.CUSTOMERS` and `SH.SALES` in the provider Autonomous AI Database.

1. In the provider Autonomous AI Database (data owner), create Table Hyperlink sharing for the required tables.


    - Create a table hyperlink for `CUSTOMERS` table.

sql DECLARE hyperlink_status CLOB; BEGIN DBMS_DATA_ACCESS.CREATE_URL( schema_name => ‘SH’, schema_object_name => ‘CUSTOMERS’, expiration_minutes => 1440, – The hyperlink remains valid for 1440 minutes result => hyperlink_status );

     DBMS_OUTPUT.PUT_LINE(hyperlink_status);
  END;
  /
  <pre class="copy"><code> The result is similar to:</code></pre>
  RESULT:
  {
    "status" : "SUCCESS",
    "id" : "LYYPJrNCL-Fa6...9T",
    "preauth_url" : "https://dataaccess.adb.us-chicago-1.oraclecloudapps.com/adb/p/6ya...j9k/data",
    "expiration_ts" : "2026-01-31T08:46:29.250Z"
  }
  <pre class="copy"><code>  - Create a table hyperlink for `SALES` table.</code></pre>sql
  DECLARE
     hyperlink_status CLOB;
  BEGIN
     DBMS_DATA_ACCESS.CREATE_URL(
        schema_name          => 'SH',
        schema_object_name   => 'SALES',
        expiration_minutes   => 1440,   -- The hyperlink remains valid for 1440 minutes
        result               => hyperlink_status
     );

     DBMS_OUTPUT.PUT_LINE(hyperlink_status);
  END;
  /
  <pre class="copy"><code> The result is similar to:</code></pre>
  RESULT:
  {
    "status" : "SUCCESS",
    "id" : "ddzdq...",
    "preauth_url" : "https://dataaccess.adb.us-chicago-1.oraclecloudapps.com/adb/p/YvYb8eJ...JQE/data",
    "expiration_ts" : "2026-01-31T08:47:24.823Z"
  }
  <pre class="copy"><code> For supported parameters, see [Use Table Hyperlinks to Create an External Table](autonomous-table-hyperlink-external-table.html#GUID-6DB2EE81-8C51-4412-97B9-85CFFFBB9AA2).  This procedure generates a Table Hyperlink URL `preauth_url` (a PAR URL) and this URL exposes read-only access to the table and can be used to create an external table in another database.  2. In the consumer Autonomous AI Database (AI Proxy), create the External Table. Copy the Table hyperlink URL (PAR URL) generated in the Step 1 from the provider database to define the External Table.</code></pre>sql
BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
      table_name      => 'CUSTOMERS_EXT',
      credential_name => NULL,
      file_uri_list   => 'https://dataaccess.adb.us-chicago-1.oraclecloudapps.com/adb/p/6ya...j9k/data',
      format          => json_object('type' VALUE 'csv')
   );
END;
/
<pre class="copy"><code> If the two databases are in the same region, the URL points to a local OCID. For cross region, the URI must reference the remote region&#39;s endpoint.  3. Repeat the same for `SH.SALES`:</code></pre>sql
BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
      table_name      => 'SALES_EXT',
      credential_name => NULL,
      file_uri_list   => '<preauth_url_for_SALES>',
      format          => json_object('type' VALUE 'csv')
   );
END;
/
<pre class="copy"><code> Both `CUSTOMERS_EXT` and `SALES_EXT` now appear as local tables in the consumer database instance. The data comes from the remote Autonomous AI Database using the Table Hyperlink.  4. Verify if the external tables are created.</code></pre>sql
SELECT table_name
FROM user_tables
WHERE table_name LIKE '%EXT%';
<pre class="copy"><code> 5. Configure network ACL access for the AI provider endpoint as an `ADMIN` user.</code></pre>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;
/
<pre class="copy"><code> 6. Create a Select AI profile that includes the objects. List the local table created in the consumer Autonomous AI Database under `object_list`.   **Note:**   This step assumes that you have created your OpenAI credentials. See [Example: Select AI with OpenAI](select-ai-examples.html#GUID-D33BA5D9-59FB-4B98-948F-D768D13ED01B) for more details. {: .infoboxnote}</code></pre>sql
BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name => 'AI_HYPERLINK_PROFILE',

    attributes   => '{
      "provider": "openai",
      "credential_name": "OPENAI_CRED",
      "object_list": [
        {"owner":"ADB_USER","name":"CUSTOMERS_EXT"},
        {"owner":"ADB_USER","name":"SALES_EXT"}
      ],
      "conversation": "true"
    }'
  );
END;
/
<pre class="copy"><code>  7. Set the Select AI profile.</code></pre>sql
EXEC DBMS_CLOUD_AI.SET_PROFILE('AI_HYPERLINK_PROFILE');
<pre class="copy"><code> The session sets the profile so Select AI uses the correct provider, credentials, and object metadata.  8. Test with Select AI. Select AI runs the prompt on the External Tables.</code></pre>
SELECT AI SHOWSQL how many customers do I have;
<pre class="copy"><code> Review the generated SQL.  The output may be similar to:</code></pre>
SELECT COUNT("CUST_ID") AS "Total_Customers" FROM "ADB_USER"."CUSTOMERS_EXT"
<pre class="copy"><code>  Select AI augments the prompt with table and view metadata, then sends it to the LLM to generate federated SQL. Select AI treats the external tables as local objects while the data remains in the remote Autonomous AI Database.  In stateless environments (such as APEX or Database Actions SQL Worksheet), test Select AI using `DBMS_CLOUD_AI.GENERATE` and pass the profile name directly.</code></pre>sql DECLARE   result CLOB; BEGIN   result := DBMS_CLOUD_AI.GENERATE(
prompt       => 'how many customers do I have',
profile_name => 'AI_HYPERLINK_PROFILE',
action       => 'showsql'   );   DBMS_OUTPUT.PUT_LINE(result); END; /

## Example: Use Federated Table with Select AI {#GUID-9E48B119-69AC-4FC1-8D7C-9850D609FBA9}

This example shows how an Autonomous AI Database (consumer) uses Select AI to query a Federated Table that automatically connects to a remote Autonomous AI Database (provider). The (consumer database) acts as an AI Data Gateway (formerly called AI Proxy Database) to query remote data hosted in another Autonomous AI Database (provider database).

**Before You Begin**

Review
- [Perform Prerequisites for Select AI](select-ai-manage-profiles.html#GUID-3721296F-14A1-428A-B464-7FA25E9EC8F3)


- [Workflow to Create Federated Tables](create-federated-tables.html#GUID-34ADE919-7873-437D-A562-0CB936548D07)


- [CREATE_FEDERATED_TABLE Procedure](dbms-data-access.html#GUID-CFF6AFB2-7BB5-4644-BB77-20E6377BDB6A)


This example uses the SH schema table `SH.CUSTOMERS` in the provider Autonomous AI Database. Both databases belong to the same tenancy and compartment.

1. In the provider Autonomous AI Database (data owner), as an `ADMIN`, allow the user (data owner) to register tables for federated access.

sql BEGIN DBMS_DATA_ACCESS_ADMIN.GRANT_REGISTER( username => ‘DATA_OWNER’, scope => ‘MY$COMPARTMENT’ ); END; /


 This grants the user the ability to register their own tables and views for remote access within the specified scope.

2. As an `ADMIN` grant execute privilege on `DBMS_DATA_ACCESS_SCOPE`. The `DATA_OWNER` user needs permission to register tables for access scopes.

sql grant execute on DBMS_DATA_ACCESS_SCOPE to DATA_OWNER; <pre class="copy">

  1. As DATA_OWNER, register the schema or specific tables for federated access. Run this in the provider database as user DATA_OWNER. You can register all tables in the schema or specify a single table such as CUSTOMERS.</code></pre>sql BEGIN DBMS_DATA_ACCESS_SCOPE.REGISTER_CREATION_SCOPE( schema_name => ‘DATA_OWNER’, schema_object_name => NULL, –or provide specific table names scope => ‘MY$COMPARTMENT’ ); END; / 
    
This registration exposes the schema (or specific tables) for federated access to other databases within the same compartment.

  2. In the consumer Autonomous AI Database, as ADMIN grant privileges that allow the consumer user (DATA_USER) to create and query federated tables.</code></pre>sql GRANT EXECUTE ON DBMS_DATA_ACCESS TO DATA_USER;

    GRANT CREATE SESSION TO DATA_USER; GRANT CREATE TABLE TO DATA_USER; ALTER USER DATA_USER QUOTA UNLIMITED ON DATA;

    – if the user will manage other objects GRANT PDB_DBA TO DATA_USER;
  3. As ADMIN grant read access to the remote schema and object. Allow the consumer user (DATA_USER) in the consumer database to read the shared object (CUSTOMERS) from the provider.</code></pre>sql BEGIN DBMS_DATA_ACCESS_ADMIN.GRANT_READ( username => ‘DATA_USER’, remote_schema_name => ‘DATA_USER_SCHEMA’, remote_schema_object_name=> ‘CUSTOMERS’ ); END; / 
    
This step authorizes the consumer user `DATA_USER` to access the specified table in the provider database.
  4. Run this in the consumer database as user DATA_USER. The db_ocids argument specifies the provider database's region and OCID. Use the region short code (for example, ORD for us-chicago-1).</code></pre>sql BEGIN DBMS_DATA_ACCESS.CREATE_FEDERATED_TABLE( table_name => ‘FEDERATED_CUSTOMERS’, remote_schema_name => ‘DATA_USER_SCHEMA’, remote_schema_object_name => ‘CUSTOMERS’, db_ocids => ‘[{“region”: “ORD”,”db_ocid”: “OCID1.AUTONOMOUSDATABASE.OC1.US-CHICAGO-1.ANXX…”}]’ ); END; / 
    

**Note:**  The Database OCID (`db_ocid`) must be in Uppercase.

  1. Configure network ACL access for the AI provider endpoint as an ADMIN user.</code></pre>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 => ‘DATA_USER’, principal_type => xs_acl.ptype_db ) ); END; /
  2. Create a Select AI profile that includes the objects. List the local table created in the consumer Autonomous AI Database under object_list. Note: This step assumes that you have created your OpenAI credentials. See Example: Select AI with OpenAI for more details. {: .infoboxnote}</code></pre>sql BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name => ‘AI_FEDERATED_TABLE_PROFILE’, attributes => ‘{ “provider”: “openai”, “credential_name”: “OPENAI_CRED”, “object_list”: [ {“owner”:”DATA_USER_SCHEMA”,”name”:”FEDERATED_CUSTOMERS”} ], “conversation”: “true” }’ ); END; /
  3. Set the Select AI profile.</code></pre>sql EXEC DBMS_CLOUD_AI.SET_PROFILE(‘AI_FEDERATED_TABLE_PROFILE’); 
    
The session sets the profile so Select AI uses the correct provider, credentials, and object metadata.
  4. Test with Select AI. Select AI runs the prompt on the federated tables.</code></pre> SELECT AI SHOWSQL how many customers do I have; 
    
Review the generated SQL.

The output may be similar to:

    SELECT COUNT(“CUST_ID”) AS “Total_Customers” FROM “DATA_USER_SCHEMA”.”FEDERATED_CUSTOMERS”

    

Select AI augments the prompt with table and view metadata, then sends it to the LLM to generate federated SQL. Select AI treats the federated tables as local objects while the data remains in the remote Autonomous AI Database.

In stateless environments (such as APEX or Database Actions SQL Worksheet), test Select AI using `DBMS_CLOUD_AI.GENERATE` and pass the profile name directly.

    sql DECLARE result CLOB; BEGIN result := DBMS_CLOUD_AI.GENERATE( prompt => ‘how many customers do I have’, profile_name => ‘AI_FEDERATED_TABLE_PROFILE’, action => ‘showsql’ ); DBMS_OUTPUT.PUT_LINE(result); END; /

## Example: Generate Synthetic Data {#GUID-9990ACE7-BCEF-4597-8FA6-CFD6D834B510}

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

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: Interrupting Synthetic Data Generation Task**

When you start generating large synthetic data sets, the system splits the task into smaller subtasks and runs them in parallel. If you interrupt the session (for example, using `Ctrl + C`), the process may continue in the background because the subtasks do not end automatically.

If you want to end a running task, take the following steps:

– Find the operation ID of the running Synthetic Data Generation (SDG) process SELECT * FROM user_load_operations WHERE type = ‘SYNTHETIC_DATA’;

– Delete a specific SDG operation EXEC dbms_cloud.delete_operation();

– Delete all SDG operations EXEC dbms_cloud.delete_all_operations(‘SYNTHETIC_DATA’);



If the commands above do not stop the background processes, end the session manually:

SELECT sid, serial# FROM v$session WHERE audsid = userenv(‘sessionid’); ALTER SYSTEM KILL SESSION ‘,<serial#>' IMMEDIATE;



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

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

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

sql 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](dbms-cloud-ai-package.html#GUID-818B6825-FBF4-4EE9-9CE5-D3C6A74462AA__TITLE_VSP_4BB_PCC) for more details.

sql – 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`*.

sql – 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 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](dbms-cloud-ai-package.html#GUID-818B6825-FBF4-4EE9-9CE5-D3C6A74462AA__TITLE_VSP_4BB_PCC).

## Example: Enable or Disable Data Access {#GUID-0C71F758-5A2B-41B1-AC1C-0C00FA20FE19}

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 [Perform Prerequisites for Select AI](select-ai-manage-profiles.html#GUID-3721296F-14A1-428A-B464-7FA25E9EC8F3) 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 [Perform Prerequisites for Select AI](select-ai-manage-profiles.html#GUID-3721296F-14A1-428A-B464-7FA25E9EC8F3) 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.



## Example: Select AI Feedback {#GUID-28F56879-4070-429A-B3FA-78BC868337CF}

These examples demonstrate how you can use the `DBMS_CLOUD_AI.FEEDBACK` procedure and the different scenarios for providing feedback to improve subsequent SQL query generation.

**Before You Begin**

Review [Perform Prerequisites for Select AI](select-ai-manage-profiles.html#GUID-3721296F-14A1-428A-B464-7FA25E9EC8F3).


 **Note:**   You can provide feedback for Select AI SQL prompts even if the prompt has not been run previously. Select AI does not require the SQL prompt to be used in a query before submitting feedback; you may supply feedback for any valid prompt at any time.
 {: .infoboxnote}


**Example: Provide Negative Feedback**

The following example demonstrates providing corrections to the generated SQL as feedback (negative feedback) using `feedback_type` as *`negative`* and providing your SQL query.

You add your feedback to the AI profile named `OCI_FEEDBACK1` by calling the `DBMS_CLOUD_AI.FEEDBACK` procedure with the `sql_text` parameter containing the prompt. See [FEEDBACK Procedure](dbms-cloud-ai-package.html#GUID-0780F523-AAB2-400E-A6A9-0F00D8BDB6EC) to learn about the attributes. Then, you retrieve the `content` and `attributes` columns from the `<profile_name>_FEEDBACK_VECINDEX$VECTAB` table, which is linked to that specific SQL query. Select AI automatically creates this vector table when you first use the feedback feature. See [Vector Index for FEEDBACK](dbms-cloud-ai-package.html#GUID-921A9A09-E9BB-43EF-ABCA-803AA1E333A7) for more information.

SQL> select ai showsql how many movies;

RESPONSE

SELECT COUNT(m.”MOVIE_ID”) AS “Number of Movies” FROM “ADB_USER”.”MOVIES” m

SQL> exec DBMS_CLOUD_AI.FEEDBACK(profile_name=>’OCI_FEEDBACK1’, sql_text=> ‘select ai showsql how many movies’, feedback_type=> ‘negative’, response=>’SELECT SUM(1) FROM “ADB_USER”.”MOVIES”’);

PL/SQL procedure successfully completed. SQL> select CONTENT, ATTRIBUTES from OCI_FEEDBACK1_FEEDBACK_VECINDEX$VECTAB where JSON_VALUE(attributes, ‘$.sql_text’) = ‘select ai showsql how many movies’;

CONTENT

how many movies ATTRIBUTES —————————————————————————————————-

{“response”:”SELECT SUM(1) FROM “ADB_USER”.”MOVIES””,”feedback_type”:”negative”,”sql_id”:null,”sql_text”:”select ai showsql how many movies”,”feedback_content”:null}



**Example: Provide Positive Feedback**

The following example demonstrates providing your approval that you agree and confirm the generated SQL (positive feedback) using `feedback_type` as *`positive`*.

In this example, the query retrieves the `sql_id` from the `v$mapped_sql` view for the given prompt. See [V_MAPPED_SQL](/pls/topic/lookup?ctx=en/cloud/paas/autonomous-database/serverless/adbsb&id=REFRN-GUID-3405881C-F4A7-4E89-9031-4B155EA0CEAC) for more information.

You add your feedback to the AI profile named `OCI_FEEDBACK1` by calling the `DBMS_CLOUD_AI.FEEDBACK` procedure with the `sql_id` parameter. Then, you retrieve the `content` and `attributes` columns from the `<profile_name>_FEEDBACK_VECINDEX$VECTAB` table, which is linked to that specific SQL query. Select AI automatically creates this vector table when you first use the feedback feature. See [Vector Index for FEEDBACK](dbms-cloud-ai-package.html#GUID-921A9A09-E9BB-43EF-ABCA-803AA1E333A7) for more information.

SQL> select ai showsql how many distinct movie genres?;

RESPONSE

SELECT COUNT(DISTINCT g.”GENRE_NAME”) AS “Number of Movie Genres” FROM “ADB_USER”.”GENRES” g

SQL> SELECT sql_id FROM v$mapped_sql WHERE sql_text = ‘select ai showsql how many distinct movie genres?’;

SQL_ID

852w8u83gktc1

SQL> exec DBMS_CLOUD_AI.FEEDBACK(profile_name=>’OCI_FEEDBACK1’, sql_id=> ‘852w8u83gktc1’, feedback_type=>’positive’, operation=>’add’);

PL/SQL procedure successfully completed.

SQL> SELECT content, attributes FROM OCI_FEEDBACK1_FEEDBACK_VECINDEX$VECTAB WHERE JSON_VALUE(attributes, ‘$.sql_id’) =’852w8u83gktc1’;

CONTENT

how many distinct movie genres? ATTRIBUTES —————————————————————————————————- {“response”:”SELECT COUNT(DISTINCT g.”GENRE_NAME”) AS “Number of Movie Genres” FROM “ADB_USER”.”GENRES” g”,”feedback_type”:”positive”,”sql_id”:”852w8u83gktc1”,”sql_text”:”select ai showsql how many distinct movie genres?”,”feedback_content”:null}



**Example: Provide Feedback Without Prior Usage**

You may provide feedback for SQL prompts even if the prompt has not been used previously. For example:

BEGIN DBMS_CLOUD_AI.FEEDBACK( profile_name=>’AI_PROFILE’, sql_text=>’select ai runsql how many products named PAD’, – Prior usage not required feedback_type=>’negative’, response=>’SELECT COUNT(*) AS “Num” FROM “PRODUCTS”.”CATG” o WHERE UPPER(o.”NAME”) LIKE ‘‘%PAD%’’’, feedback_content=>’Use LIKE instead of =’ ); END; /



In this case, feedback for the prompt `select ai runsql how many products named PAD` is submitted without needing to use the prompt beforehand.

**Example: Add or Delete Your Feedback for the Generated SQL**

The following example demonstrates adding or deleting your feedback for the generated SQL by specifying the `DBMS_CLOUD_AI.FEEDBACK` procedure parameters. This example demonstrates using `sql_id` and `sql_text` along with other parameters.

**Note:**  Select AI allows only a single feedback entry for each `sql_id`. If you provide additional feedback for the same `sql_id`, Select AI replaces the previous entry with the new one.
{: .infoboxnote}


See [FEEDBACK Procedure](dbms-cloud-ai-package.html#GUID-0780F523-AAB2-400E-A6A9-0F00D8BDB6EC) for more details on the parameters.

EXEC DBMS_CLOUD_AI.FEEDBACK(profile_name=>’OCI_FEEDBACK1’, sql_id=> ‘852w8u83gktc1’, feedback_type=>’positive’, operation=>’add’); EXEC DBMS_CLOUD_AI.FEEDBACK(profile_name=>’OCI_FEEDBACK1’, sql_text=> ‘select ai showsql how many movies’, feedback_type=> ‘negative’, response=>’SELECT SUM(1) FROM “ADB_USER”.”MOVIES”’, feedback_content=>’Use SUM instead of COUNT’); EXEC DBMS_CLOUD_AI.FEEDBACK(profile_name=>’OCI_FEEDBACK1’, sql_id=> ‘852w8u83gktc1’, operation=>’delete’);



**Example: Use Feedback Action with the Last AI SQL to Provide Negative Feedback**

This example demonstrates using `feedback` action to improve the generated SQL by suggesting the modifications using natural language.

BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name=>’OCI_FEEDBACK1’, attributes=>’{“provider”: “oci”, “credential_name”: “GENAI_CRED”, “oci_compartment_id”: “ocid 1.compartment.oc1..aaaa…”, “object_list”: [{“owner”: “ADB_USER”, “name”: “users”}, {“owner”: “ADB_USER”, “name”: “movies”}, {“owner”: “ADB_USER”, “name”: “genres”}, {“owner”: “ADB_USER”, “name”: “watch_history”}, {“owner”: “ADB_USER”, “name”: “movie_genres”}, {“owner”: “ADB_USER”, “name”: “employees1”}, {“owner”: “ADB_USER”, “name”: “employees2”} ] }’); END; /

EXEC DBMS_CLOUD_AI.SET_PROFILE(‘OCI_FEEDBACK1’);

PL/SQL procedure successfully completed.

select ai showsql rank movie duration;

RESPONSE

SELECT “DURATION” AS “Movie Duration” FROM “ADB_USER”.”MOVIES” ORDER BY “DURATION”

select ai feedback use ascending sorting;

RESPONSE

Based on your feedback, the SQL query for prompt “rank movie duration” is successfully refined. The refined SQL query as following: SELECT m.”DURATION” AS “Movie Duration” FROM “ADB_USER.”MOVIES” m ORDER BY m.”DURATION” ASC

select ai showsql rank the movie duration;

RESPONSE

SELECT m.”DURATION” AS “Movie Duration” FROM “ADB_USER.”MOVIES” m ORDER BY m.”DURATION” ASC



**Example: Use Feedback Action with the Last AI SQL to Provide Positive Feedback**

This example demonstrates using the `feedback` action to accept the generated SQL using natural language.

–Positive feedback

select ai showsql which movies are comedy?;

RESPONSE

SELECT DISTINCT m.”TITLE” AS “Movie Title” FROM “ADB_USER”.”MOVIES” m INNER JOIN “ADB_USER”.”MOVIE_GENRES” mg ON m.”MOVIE_ID” = mg.”MOVIE_ID” INNER JOIN “ADB_USER”.”GENRES” g ON mg.”GENRE_ID” = g.”GENRE_ID” WHERE g.”GENRE_NAME” = ‘comedy’

select ai feedback this is correct;

RESPONSE

Thank you for your positive feedback. The SQL query for prompt “which movies are comedy?” is correctly implemented and delivering the expected results. It will be referenced for future optimizations and improvements.

Select AI Feedback Action Referring SQL_ID



**Example: Use Feedback Action with SQL_ID to Provide Feedback**

This example demonstrates using `SQL_ID` with the `feedback` action to provide feedback for a particular generated SQL query. You can obtain the `SQL_ID` by querying the `v$MAPPED_SQL` table.

– Query mentioned with SQL_ID

select ai showsql how many movies are in each genre;

RESPONSE

SELECT g.”GENRE_NAME” AS “Genre Name”, COUNT(m.”MOVIE_ID”) AS “Number of Movies” FROM “ADB_USER”.”MOVIES” m INNER JOIN “ADB_USER”.”MOVIE_GENRES” mg ON m.”MOVIE_ID” = mg.”MOVIE_ID” INNER JOIN “ADB_USER”.”GENRES” g ON mg.”GENRE_ID” = g.”GENRE_ID” GROUP BY g.”GENRE_NAME”

select sql_id from v$cloud_ai_sql where sql_text = ‘select ai showsql how many movies are in each genre’;

SQL_ID

8azkwc0hr87ga

select ai feedback for query with sql_id = ‘8azkwc0hr87ga’, rank in descending sorting;

RESPONSE

Based on your feedback, the SQL query for prompt “how many movies are in each genre” is successfully refined. The refined SQL query as following: SELECT g.”GENRE_NAME” AS “Genre Name”, COUNT(m.”MOVIE_ID”) AS “Number of Movies” FROM “ADB_USER”.”MOVIES” m INNER JOIN “ADB_USER”.”MOVIE_GENRES” mg ON m.”MOVIE_ID” = mg.”MOVIE_ID” INNER JOIN “ADB_USER”.”GENRES” g ON mg.”GENRE_ID” = g.”GENRE_ID” GROUP BY g.”GENRE_NAME” ORDER BY COUNT(m.”MOVIE_ID”) DESC



**Example:Use Feedback Action with Query Text**

This example shows the `feedback` action for a specific Select AI query by including the Select AI prompt in quotes followed by your feedback.

-Query mentioned with SQL_TEXT

select ai showsql how many watch history in total;

RESPONSE

SELECT COUNT(w.”WATCH_ID”) AS “Total Watch History” FROM “ADB_USER”.”WATCH_HISTORY” w

select ai feedback for query “select ai showsql how many watch history in total”, name the column as total_watch;

RESPONSE

Based on your feedback, the SQL query for prompt “how many watch history in total” is successfully refined. The refined SQL query as following: SELECT COUNT(w.”WATCH_ID”) AS “total_watch” FROM “ADB_USER”.”WATCH_HISTORY” w



## Example: Select AI Summarize {#GUID-22D738E1-BC06-47F0-9684-CD698DD8C492}

These examples show how to use the `summarize` action and `DBMS_CLOUD_AI.SUMMARIZE` function. Also, customize the summary generation for your content using the function.

**Before You Begin**

Review [Perform Prerequisites for Select AI](select-ai-manage-profiles.html#GUID-3721296F-14A1-428A-B464-7FA25E9EC8F3).

**Example: Use Summarize Action on SQL Command Line**

The following example uses `SUMMARIZE` as a Select AI action. Use `SELECT AI SUMMARIZE <TEXT>` in the SQL command line to generate a summary of input text.

SELECT AI SUMMARIZE Like countless other people around the globe, I stream music, and like more than six hundred million of them I mainly use Spotify. Streaming currently accounts for about eighty per cent of the American recording industry’s revenue, and in recent years Spotify’s health is often consulted as a measure for the health of the music business over all. Last spring, the International Federation of the Phonographic Industry reported global revenues of $28.6 billion, making for the ninth straight year of growth. All of this was unimaginable in the two-thousands, when the major record labels appeared poorly equipped to deal with piracy and the so-called death of physical media. On the consumer side, the story looks even rosier. Adjusted for inflation, a

… (skipped 1000 rows in the middle)

Pelly writes of some artists, in search of viral fame, who surreptitiously use social media to effectively beta test melodies and motifs, basically putting together songs via crowdsourcing. Artists have always fretted about the pressure to conform, but the data-driven, music-as-content era feels different. “You are a Spotify employee at that point,” Daniel Lopatin, who makes abstract electronic music as Oneohtrix Point Never, told Pelly. “If your art practice is so ingrained in the brutal reality that Spotify has outlined for all of us, then what is the music that you’re not making? What does the music you’re not making sound like?” Listeners might wonder something similar. What does the music we’re not hearing sound like?;

RESPONSE

The music streaming industry, led by Spotify, has revolutionized the way people consume music, with streaming accounting for 80% of the American recording industry’s revenue. However, this shift has also complicated the lives of artists, who struggle to survive in a hyper-abundant present where music is often valued for its convenience rather than its artistic merit. Spotify’s algorithms prioritize popularity and profitability over artistic diversity, leading to a homogenization of music and a devaluation of the labor that goes into creating it. Meanwhile, the company’s executives reap enormous profits, with CEO Daniel Ek’s net worth rivaling that of the wealthiest musicians. As music critic Liz Pelly argues, the streaming economy raises important questions about autonomy, creativity, and the value of art in a world where everything is readily available and easily accessible.



<div class="infoboxtip" markdown="1">

In SQL\*Plus, a single quotation mark (`'`) is treated as a string delimiter. If your text contains single quotes, either escape the quote by doubling it (`'` to `''`), or enclose the text using the `q'[]'` quoting mechanism. If your text contains empty double quotes (`""`), enclose the text using `q'[]'` mechanism. For example:

sql SELECT AI SUMMARIZE q’[this’s a text]’;

</div>

**Example: Use DBMS_CLOUD_AI.SUMMARIZE Procedure to Generate a Summary**

These examples demonstrate generating a summary by using different parameters from the `DBMS_CLOUD_AI.SUMMARIZE` procedure.

You can generate a summary from 3000+ word text stored in an OCI object storage by specifying the object storage link as the `location_uri` parameter and your cloud account credentials as `credential_name` using the `DBMS_CLOUD_AI.SUMMARIZE`

SELECT DBMS_CLOUD_AI.SUMMARIZE( location_uri => ‘https://objectstorage.ca-toronto-1.oraclecloud.com/n/’ || ‘namespace-string/b/bucketname/o/data_folder/’ || ‘summary/test_4000_words.txt’, credential_name => ‘STORE_CRED’, profile_name => ‘GENAI’) from DUAL;



Another way to generate a summary from a text that is stored in an OCI object storage is by using the `content` parameter to call the `DBMS_CLOUD.GET_OBJECT` procedure.

SELECT DBMS_CLOUD_AI.SUMMARIZE( content => TO_CLOB( DBMS_CLOUD.GET_OBJECT( credential_name => ‘STORE_CRED’, location_uri => ‘https://objectstorage.ca-toronto-1.oraclecloud.com/n/’ || ‘namespace-string/b/bucketname/o/data_folder/’ || ‘summary/test_4000_words.txt’)), profile_name => ‘GENAI’>) from DUAL;



**Example: Generate a Summary by Specifying User Prompt, Minimum Words, and Maximum Words**

The following example demonstrates generating a summary of a 3000+ word text by specifying the following parameters:
- `user_prompt`: *The summary should start with \'The summary of the article is: \'*

- `min_words`: *`50`*

- `max_words`: *`100`*

SELECT DBMS_CLOUD_AI.SUMMARIZE( content => TO_CLOB( DBMS_CLOUD.GET_OBJECT( credential_name =>’STORE_CRED’, location_uri =>’https://objectstorage.ca-toronto-1.oraclecloud.com/n/’ || ‘namespace-string/b/bucketname/o/data_folder/’ || ‘summary/test_4000_words.txt’)), profile_name => ‘GENAI’, user_prompt => ‘The summary should start with ‘‘The summary of ‘ || ‘the article is: ‘’’, params => ‘{“min_words”:50,”max_words”:100}’) As response FROM dual;

RESPONSE

The summary of the article is: The music streaming industry, led by Spotify, has revolutionized the way people consume music, with streaming accounting for abou t eighty per cent of the American recording industry’s revenue. However, this sh ift has also raised concerns about the impact on artists, with many struggling t o make a living due to low royalty rates and the dominance of playlists. The art icle explores the history of music streaming, from the early days of Napster to the current landscape, and how it has changed the way people listen to music. It also delves into the issues of autonomy and creativity in the music industry, w ith some artists feeling pressured to conform to certain styles or formulas to s ucceed on platforms like Spotify. The article cites examples of artists who have spoken out against the streaming economy, including Taylor Swift and Neil Young , and discusses the rise of alternative platforms like Bandcamp and Nina. Ultima tely, the article suggests that the streaming economy has created a perverse vis ion for art, where music is valued for its ability to be ignored rather than app reciated, and that this has significant implications for the future of music and creativity. With the rise of AI-generated music and the increasing importance o f data-driven decision making in the music industry, the article asks what the m usic we’re not hearing sounds like, and what the consequences of this shift will be for artists and listeners alike. The article concludes by highlighting the n eed for a more nuanced understanding of the music industry and the impact of str eaming on artists and listeners, and for alternative models that prioritize crea tivity and autonomy over profit and convenience.



**Example: Generate a Summary by Specifying User Prompt, Maximum Words, and Summary Style**

The following example demonstrates generating a summary of a 12000+ word text by specifying the following parameters:


- `user_prompt`: *The summary should start with \'The summary of the article is: \'*

- `max_words`: *`100`*

- `summary_style`: *`list`*

SELECT DBMS_CLOUD_AI.SUMMARIZE( location_uri => ‘https://objectstorage.ca-toronto-1.’ || ‘oraclecloud.com/n/namespace-string/b/’ || ‘/bucketname/o/data_folder/’ || ‘summary/dreams.txt’, credential_name => ‘STORE_CRED’, profile_name => ‘GENAI’, user_prompt => ‘The summary should start with ‘‘The summary of ‘ || ‘the article is: ‘’’, params => ‘{“max_words”:100, “summary_style”:”list”}’) As response FROM dual;

RESPONSE

The summary of the article is:



**Example: Generate a Summary of a Book**

This example demonstrates passing a 35.66 MiB file as an input to generate a summary. The `DBMS_CLOUD_AI.SUMMARIZE` function uses iterative refinement method to process the chunks. See [Iterative Refinement](select-ai-concepts.html#GUID-CCBDA0E8-3B91-4264-9A2B-8690CA10B95A) for more information.

SELECT DBMS_CLOUD_AI.SUMMARIZE( location_uri => ‘https://objectstorage.ca-toronto-1.oraclecloud.com/n/namespace-string/b/’ || ‘bucketname/o/data_folder/summary/Descartes_An_Intellectual_Biography.pdf’, credential_name => ‘STORE_CRED’, profile_name => ‘GENAI’, params => ‘{“chunk_processing_method”:”iterative_refinement”}’) AS response FROM dual;

RESPONSE

Stephen Gaukroger’s intellectual biography of Rene Descartes provides a detailed examination of the philosopher’s crucial role in shaping modern thought, placin g him within the cultural, religious, and scientific context of the early sevent eenth century. It traces Descartes’ intellectual journey from his education at L a Fleche, where he rejected Aristotelian logic, to his influential interactions with figures like Isaac Beeckman, which shaped his mechanistic worldview evident in works like his hydrostatics manuscript and Compendium Musicae. The biograp hy underscores Descartes’ dual commitment to philosophy and science, highlightin g his social status among the gentry, mathematical innovations such as solving t he Pappus problem through algebraic geometry, and his epistemology based on clea r and distinct ideas. It explores his mechanistic explanations of bodily functio ns, challenging traditional soul-body distinctions, and his extensive natural ph ilosophy in texts like Le Monde and L’Homme. Gaukroger also delves into Desc artes’ cosmological theories, including the vortex theory and laws of motion lin ked to divine immutability, as well as his nuanced perspectives on animal cognit ion versus human consciousness. Central to the narrative is Descartes’ use of hy perbolic doubt to combat skepticism and establish metaphysical foundations throu gh the cogito, alongside his classification of ideas and theological proofs of God’s existence. The complex relationship between his natural philosophy and me taphysics, especially in defining motion as a mode, and his innovative approach to the passions in Passions of the Soul, rejecting Stoic views for a mind-body union, are key themes. This portrayal captures Descartes’ struggle with traditi onal paradigms during a transformative era, emphasizing his enduring impact on p hilosophy and science.



## Example: Select AI Translate {#GUID-8EA7C9C8-E449-4961-9746-D537E00547F2}

These examples demonstrate how you can use the `translate` capability.

**Before You Begin**

Review:


- [Perform Prerequisites for Select AI](select-ai-manage-profiles.html#GUID-3721296F-14A1-428A-B464-7FA25E9EC8F3)


- [Profile Attributes](dbms-cloud-ai-package.html#GUID-12D91681-B51C-48E0-93FD-9ABC67B0F375)


- [Use AI Keyword to Enter Prompts](select-ai-keyword-prompts.html#GUID-B3E0EE68-3B4C-4002-9B45-BBE258A2F15A)


**OCI**

To use the Select AI translation feature, you must have the appropriate IAM policy permissions to access Oracle Cloud Infrastructure Language services.

Grant the permission to use `ai-service-language-family` resource in your IAM policy. An example policy statement to grant permission to a user group in a specific compartment is:

allow group to use ai-service-language-family in compartment 




- If using Resource Principal credential, assign the permission to the Dynamic Group.


- If using Private Key credential, assign the permission to the User Group.


A Dynamic Group identifies resources such as databases or functions by matching their OCIDs or tags, while a User Group contains individual IAM users.

Use a dynamic group when the policy applies to OCI resources, and use a user group when the policy applies to human users. For detailed steps to create dynamic and user groups, see [Managing Dymanic Groups](https://docs.oracle.com/en-us/iaas/Content/Identity/Tasks/managingdynamicgroups.htm).

See [Language Policies](https://docs.oracle.com/en-us/iaas/Content/language/using/policies.htm) for more information.

**Example: Use Translate Action on the SQL Command Line**

The following example shows using the `translate` action on the SQL command line.

 **Note:**  Your AI profile must specify the target language. This example has OCI as the AI provider.
 {: .infoboxnote}

–Create an AI profile with language parameters BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name =>’GENAI_NEW’, attributes =>’{“provider”: “oci”, “credential_name”: “GENAI_CRED”, “target_language”: “french”, “object_list”: [{“owner”: “SH”, “name”: “customers”}, {“owner”: “SH”, “name”: “countries”}, {“owner”: “SH”, “name”: “supplementary_demographics”}, {“owner”: “SH”, “name”: “profits”}, {“owner”: “SH”, “name”: “promotions”}, {“owner”: “SH”, “name”: “products”}] }’); END; / PL/SQL procedure successfully completed.

SQL> exec DBMS_CLOUD_AI.SET_PROFILE(‘GENAI_NEW’);

PL/SQL procedure successfully completed.

SQL> select ai translate I need to translate this;

RESPONSE

Je dois traduire ceci



**Example: Use Translate in DBMS_CLOUD_AI.GENERATE Function**

The following examples show using translate as a Select AI action within the `DBMS_CLOUD_AI.GENERATE` function. See [GENERATE Function](dbms-cloud-ai-package.html#GUID-7B438E87-0E9A-4318-BA01-3BE1A5851229) for more information.

**Note:**  The AI profile can skip specifying the target language parameter if it is passed as an attribute in `DBMS_CLOUD_AI.GENERATE`.
{: .infoboxnote}


The `translate` action is supplied in the `DBMS_CLOUD_AI.GENERATE` function along with `target_language` and `source_language`. This example uses generative AI translation. The input text `this is a document` in English (`source_language: "en"`) is translated into French (`target_language: "fr"`).

SELECT DBMS_CLOUD_AI.GENERATE(‘select ai translate text to be translated’) FROM dual;

  DECLARE
     l_attributes  clob := '{"target_language": "fr", "source_language": "en"}';
     output clob;
  BEGIN
     output := DBMS_CLOUD_AI.GENERATE(
                    prompt            => 'this is a document',
                    profile_name      => 'oci_translate',
                    action            => 'translate',
                    attributes        => l_attributes
                 );



**Example: Use DBMS_CLOUD_AI.TRANSLATE Function for Translation**

This example calls the `DBMS_CLOUD_AI.TRANSLATE` function to use generative AI translation, converting the input text from English (`source_language`) into French (`target_language`) using the specified AI profile.

See [TRANSLATE Function](dbms-cloud-ai-package.html#GUID-AA63AA18-3F49-4BDA-BEBE-85179D46C170) for more details.

BEGIN output_text := DBMS_CLOUD_AI.TRANSLATE( profile_name => ‘GENAI_NEW’ text => ‘text to be translated’, source_language => ‘English’, target_language => ‘French’); END; /



**Example: Display Supported Languages for a Provider**

Query the `AI_TRANSLATION_LANGUAGES` view to see a list of languages that your AI provider supports. See [AI_TRANSLATION_LANGUAGES View](dbms-cloud-ai-views.html#GUID-05CB91E6-A689-44D7-9522-0C2032EA730B) for details.

SELECT * FROM AI_TRANSLATION_LANGUAGES;

LANGUAGE_NAME LANGUAGE_CODE PROVIDER ——————– ————— ————— ARABIC ar OCI ARABIC ar GOOGLE ARABIC ar AZURE ARABIC ar AWS CROATIAN hr OCI CROATIAN hr GOOGLE CROATIAN hr AZURE CROATIAN hr AWS CZECH cs OCI CZECH cs GOOGLE CZECH cs AZURE

–Query for all languages a certain provider supports

SELECT * FROM AI_TRANSLATION_LANGUAGES WHERE provider = ‘GOOGLE’;

LANGUAGE_NAME LANGUAGE_CODE PROVIDER ——————– ————— ————— ARABIC ar GOOGLE CROATIAN hr GOOGLE CZECH cs GOOGLE DANISH da GOOGLE GERMAN de GOOGLE GREEK el GOOGLE ENGLISH en GOOGLE SPANISH es GOOGLE FINNISH fi GOOGLE FRENCH fr GOOGLE FRENCH CANADA fr-CA GOOGLE



## Example: Restrict Table Access in AI Profile {#GUID-D10D0AEB-0977-46B5-87D4-6356A3632BC1}

This example demonstrates how to restrict table access and instruct the LLM to use only the tables specified in the `object_list` of the AI profile.

Set `enforce_object_list` to *`true`* to restrict table access to the LLM.

As a database user, create and configure your AI profile. Review [Perform Prerequisites for Select AI](select-ai-manage-profiles.html#GUID-3721296F-14A1-428A-B464-7FA25E9EC8F3) to configure your AI profile.

BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name =>’GOOGLE_ENFORCED’, attributes =>’{“provider”: “google”, “credential_name”: “GOOGLE_CRED”, “object_list”: [{“owner”: “ADB_USER”, “name”: “GENRE”}, {“owner”: “ADB_USER”, “name”: “CUSTOMER”}, {“owner”: “ADB_USER”, “name”: “PIZZA_SHOP”}, {“owner”: “ADB_USER”, “name”: “STREAMS”}, {“owner”: “ADB_USER”, “name”: “MOVIES”}, {“owner”: “ADB_USER”, “name”: “ACTIONS”}], “enforce_object_list” : “true” }’); END; /

PL/SQL procedure successfully completed.

EXEC DBMS_CLOUD_AI.set_profile(‘GOOGLE_ENFORCED’);

PL/SQL procedure successfully completed.

select ai showsql please list the user tables;

RESPONSE

SELECT ‘ADB_USER.GENRE’ AS TABLE_NAME FROM DUAL UNION ALL SELECT ‘ADB_USER.CUSTOMER’ AS TABLE_NAME FROM DUAL UNION ALL SELECT ‘ADB_USER.PIZZA_SHOP’ AS TABLE_NAME FROM DUAL UNION ALL SELECT ‘ADB_USER.STREAMS’ AS TABLE_NAME FROM DUAL UNION ALL SELECT ‘ADB_USER.MOVIES’ AS TABLE_NAME FROM DUAL –



Setting `enforce_object_list` to *`false`* instructs the LLM to use other tables and views based on its prior knowledge.

BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name =>’GOOGLE_ENFORCED1’, attributes =>’{“provider”: “google”, “credential_name”: “GOOGLE_CRED”, “object_list”: [{“owner”: “ADB_USER”, “name”: “GENRE”}, {“owner”: “ADB_USER”, “name”: “CUSTOMER”}, {“owner”: “ADB_USER”, “name”: “PIZZA_SHOP”}, {“owner”: “ADB_USER”, “name”: “STREAMS”}, {“owner”: “ADB_USER”, “name”: “MOVIES”}, {“owner”: “ADB_USER”, “name”: “ACTIONS”}], “enforce_object_list” : “false” }’); END; /

PL/SQL procedure successfully completed.

EXEC DBMS_CLOUD_AI.set_profile(‘GOOGLE_ENFORCED1’);

PL/SQL procedure successfully completed.

select ai showsql please list the user tables; RESPONSE ———————————- SELECT TABLE_NAME FROM USER_TABLES



## Example: Specify Case Sensitivity for Columns {#GUID-4348B42D-FDB3-43EE-97C7-125204D42009}

This example shows how you can set case sensitivity for columns in AI profile.

Set `case_sensitive_values` to *`false`* to retrieve queries that are not case sensitive.

As a database user, create and configure your AI profile. Review [Perform Prerequisites for Select AI](select-ai-manage-profiles.html#GUID-3721296F-14A1-428A-B464-7FA25E9EC8F3) to configure your AI profile.

BEGIN DBMS_CLOUD_AI.create_profile( profile_name =>’GOOGLE’, attributes =>’{“provider”: “google”, “credential_name”: “GOOGLE_CRED”, “object_list”: [{“owner”: “ADB_USER”, “name”: “GENRE”}, {“owner”: “ADB_USER”, “name”: “CUSTOMER”}, {“owner”: “ADB_USER”, “name”: “PIZZA_SHOP”}, {“owner”: “ADB_USER”, “name”: “STREAMS”}, {“owner”: “ADB_USER”, “name”: “MOVIES”}, {“owner”: “ADB_USER”, “name”: “ACTIONS”}], “case_sensitive_values” : “false” }’); END; /

PL/SQL procedure successfully completed.

– With “case_sensitive_values” set to “false”, LLM will give back case insensitive query.

select ai showsql how many people watch Inception;

RESPONSE

SELECT COUNT(DISTINCT c.CUSTOMER_ID) AS “COUNT” FROM “ADB_USER”.”CUSTOMER” c JOIN “ADB_USER”.”STREAMS” s ON c.CUSTOMER_ID = s.CUSTOMER_ID JOIN “ADB_USER”.”MOVIES” m ON s.MOVIE_ID = m.MOVIE_ID WHERE UPPER(m.TITLE) = UPPER(‘Inception’)



You can specify case sensitive query using double quotes even though the `case_sensitive_values` is set to *`false`*.

select ai showsql how many people watch “Inception”;

RESPONSE

SELECT COUNT(DISTINCT c.CUSTOMER_ID) AS “COUNT” FROM “ADB_USER”.”CUSTOMER” c JOIN “ADB_USER”.”STREAMS” s ON c.CUSTOMER_ID = s.CUSTOMER_ID JOIN “ADB_USER”.”MOVIES” m ON s.MOVIE_ID = m.MOVIE_ID WHERE m.TITLE = ‘Inception’



## Example: Select AI for Property Graphs {#GUID-A401F267-1026-4057-A503-3B0C5D055478}

This example shows how you can use the `DBMS_CLOUD_AI.GENERATE` procedure and a natural language prompt to generate PGQ graph queries to query graph data.

**Before You Begin**

Review [Perform Prerequisites for Select AI](select-ai-manage-profiles.html#GUID-3721296F-14A1-428A-B464-7FA25E9EC8F3).

**Example: Create Property Graph Tables**

The following example creates sample tables and a property graph.

sql – Create tables CREATE TABLE Customers ( ID NUMBER, NAME VARCHAR2(10) ); INSERT INTO Customers VALUES(1, ‘Kate’); INSERT INTO Customers VALUES(2, ‘Mark’); COMMIT;

CREATE TABLE Products ( ID NUMBER, NAME VARCHAR2(10) );

INSERT INTO Products VALUES(1, ‘Dress’); COMMIT;

CREATE TABLE Buys ( ID NUMBER, CUST NUMBER, PROD NUMBER ); INSERT INTO Buys VALUES(1,1,1); COMMIT;

– Create property graph CREATE PROPERTY GRAPH G VERTEX TABLES( PRODUCTS KEY(ID), CUSTOMERS KEY(ID) ) EDGE TABLES( BUYS KEY(ID) SOURCE KEY(CUST) REFERENCES CUSTOMERS(ID) DESTINATION KEY(PROD) REFERENCES PRODUCTS(ID) NO PROPERTIES );

**Example: Create an AI Profile with Single Property Graph Object**

The following example shows creating an AI profile and supplying property graph object in the `object_list` parameter.

–oci provider, default model SQL> BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name =>’OPGAI’, attributes =>’{“provider”: “oci”, “credential_name”: “OCI_CRED”, “object_list”: [{“owner”: “ADB_USER”, “name”: “G”}], “oci_compartment_id” : “ocid1.tenancy.oc1..aaaa…” }’); END; /

PL/SQL procedure successfully completed.

– openai provider, gpt-4o model SQL> BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name =>’OPENAI’, attributes =>’{“provider”: “openai”, “model”: “gpt-4o”, “credential_name”: “OPENAI_CRED”, “object_list”: [{“owner”: “ADB_USER”, “name”: “G”}] }’); END; /

PL/SQL procedure successfully completed.



**Example: Query Using DBMS_CLOUD_AI.GENERATE Procedure**

The following examples show how to run a natural language query using the `DBMS_CLOUD_AI.GENERATE` procedure with the AI profile named `OPGAI`, which includes a defined property graph. The examples show the `showsql` and `narrate` actions.

– showsql action SQL> SELECT DBMS_CLOUD_AI.GENERATE(prompt => ‘how many customers are there’, profile_name => ‘OPGAI’, action => ‘showsql’) FROM dual;

DBMS_CLOUD_AI.GENERATE(PROMPT=>’HOWMANYCUSTOMERS’,PROFILE_NAME=>’OPGAI’,ACTION=>

SELECT COUNT(*) AS customer_count FROM GRAPH_TABLE ( G MATCH (c IS CUSTOMERS) COLUMNS (1 AS dummy_value) )

1 row selected.

– narrate action SELECT DBMS_CLOUD_AI.GENERATE(prompt => ‘how many products are there’, profile_name => ‘OPGAI’, action => ‘narrate’) FROM dual; SQL> DBMS_CLOUD_AI.GENERATE(PROMPT=>’HOWMANYPRODUCTS’,PROFILE_NAME=>’OPGAI’,ACTION=>’ ——————————————————————————– There is 1 product.

1 row selected.



**Example: Query with a Prompt in SQL Command Line**

The following example shows how to run a natural language query in SQL command line using the AI profile named `OPENAI`, which includes a defined property graph. This example uses `select ai <prompt>`. The default action is `runsql`.

First, set the active AI profile, and then issue a `SELECT AI` statement. The `runsql` action is used by default.

SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE(profile_name => ‘OPENAI’);

PL/SQL procedure successfully completed.

SQL> select ai who bought a dress;

CUSTOMER_N

Kate

1 row selected.



**Example: Running Different Select AI Actions on a Property Graph**

This example shows how the LLM defined in your AI profile interprets the same natural language query: *how many customers are there*, using different actions. Each action shows how Select AI translates natural language queries into graph queries with the `GRAPH_TABLE` operator.

–runsql action

SQL> SELECT AI RUNSQL how many customers are there;

CUSTOMER_COUNT

     2

1 row selected.

SQL> SELECT AI how many customers are there;

CUSTOMER_COUNT

     2

1 row selected.

–showsql action

SQL> SELECT AI SHOWSQL how many customers are there;

RESPONSE

SELECT COUNT(*) AS customer_count FROM GRAPH_TABLE(G MATCH (c IS CUSTOMERS) COLUMNS (1 AS dummy_value))

1 row selected.

–explainsql action

SQL> SELECT AI EXPLAINSQL how many customers are there;

RESPONSE

SELECT COUNT(*) AS customer_count
FROM GRAPH_TABLE("G"
MATCH (v IS "CUSTOMERS")
COLUMNS(1 as dummy_value))

Explanation To find the number of customers, we use the GRAPH_TABLE operator to access the g raph data. In the MATCH clause, we specify the pattern to match vertices with th e label “CUSTOMERS”. Since we don’t need any specific properties, we use a dummy value (1) in the COLUMNS clause. Finally, we use the COUNT(*) function outside of the GRAPH_TABLE operator to count the number of matched vertices, which repre sents the total number of customers.

1 row selected.

–narrate action

SQL> SELECT AI NARRATE how many customers are there;

RESPONSE

There are 2 customers.

1 row selected.

–showprompt SQL> SELECT AI SHOWPROMPT how many customers are there;

SQL> SELECT AI SHOWPROMPT how many customers are there; –shows the truncated response for showprompt action [ { “role” : “system”, “content” : “# Role and Objective\nYou are an Oracle SQL/PGQ expert.\nSQL/PGQ …” . . . } ]



Example: Using Conversation Context to Query a Property Graph

This example shows how Select AI retains conversation context and queries the property graph during:
- Session-based short-term conversations: when the `conversation` parameter is set to `true` in your AI profile.


- Customizable long-term conversations: when you use conversation APIs.


See [Example: Enable Conversations in Select AI](select-ai-examples.html#GUID-9F0524DB-EA1E-430D-9243-D8F6917DC989) for more details.

– Create tables SQL> CREATE TABLE Customers ( ID NUMBER, NAME VARCHAR2(10), AGE NUMBER );

Table created.

SQL> INSERT INTO Customers VALUES(1, ‘Kate’, 25);

1 row created.

SQL> INSERT INTO Customers VALUES(2, ‘Mark’, 30);

1 row created.

SQL> INSERT INTO Customers VALUES(3, ‘Alex’, 25);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> CREATE TABLE Products ( 2 ID NUMBER, 3 NAME VARCHAR2(10) 4 );

Table created.

SQL> INSERT INTO Products VALUES(1, ‘Dress’);

1 row created.

SQL> INSERT INTO Products VALUES(2, ‘Socks’);

1 row created.

SQL> INSERT INTO Products VALUES(3, ‘Shirt’);

1 row created.

SQL> INSERT INTO Products VALUES(4, ‘Pants’);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> CREATE TABLE Buys ( ID NUMBER, CUST NUMBER, PROD NUMBER, PRICE NUMBER );

Table created.

SQL> INSERT INTO Buys VALUES(1,1,1,50);

1 row created.

SQL> INSERT INTO Buys VALUES(2,1,2,20);

1 row created.

SQL> INSERT INTO Buys VALUES(3,2,3,40);

1 row created.

SQL> INSERT INTO Buys VALUES(4,3,4,50);

1 row created.

SQL> COMMIT;

Commit complete.

– Create property graph SQL> CREATE PROPERTY GRAPH G VERTEX TABLES( PRODUCTS KEY(ID), CUSTOMERS KEY(ID) ) EDGE TABLES( BUYS KEY(ID) SOURCE KEY(CUST) REFERENCES CUSTOMERS(ID) DESTINATION KEY(PROD) REFERENCES PRODUCTS(ID) PROPERTIES(PRICE) );

Property graph created.

– Create profile with conversation set to TRUE SQL> BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name =>’OPGAI’, attributes =>’{“provider”: “openai”, “model”: “gpt-4o”, “credential_name”: “OPENAI_CRED”, “object_list”: [{“owner”: “ADB_USER”, “name”: “G”}], “conversation”: “TRUE”}’); END; /

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE(‘OPGAI’);

PL/SQL procedure successfully completed.

SQL> select ai what are the total number of customers;

TOTAL_CUSTOMERS

      3

1 row selected.

SQL> select ai has any of them bought a shirt;

CUSTOMERS_BOUGHT_SHIRT

	     1

1 row selected.

– LONG TERM CONVERSATION – BEGIN DBMS_CLOUD_AI.DROP_PROFILE( profile_name =>’OPGAI’); END; /

PL/SQL procedure successfully completed.

BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name =>’OPGAI’, attributes =>’{“provider”: “openai”, “model”: “gpt-4o”, “credential_name”: “OPENAI_CRED”, “object_list”: [{“owner”: “ADB_USER”, “name”: “G”}]}’); END; /

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE(‘OPGAI’);

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_CLOUD_AI.CREATE_CONVERSATION;

CREATE_CONVERSATION

4309AAED-0EE7-3C23-E063-77634664063F

1 row selected.

SQL> EXEC DBMS_CLOUD_AI.SET_CONVERSATION_ID(‘4309AAED-0EE7-3C23-E063-77634664063F’);

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_CLOUD_AI.GET_CONVERSATION_ID;

GET_CONVERSATION_ID

4309AAED-0EE7-3C23-E063-77634664063F

1 row selected.

SQL> SELECT AI Who is the oldest customer;

CUSTOMER_N CUSTOMER_AGE ———- ———— Mark 30

1 row selected.

SQL> SELECT AI Show his age only;

CUSTOMER_AGE

  30

1 row selected.



Example: Specify Multiple Graphs in your AI Profile

This example shows how to define multiple property graphs in your AI profile, including a sample query and its output.

BEGIN DBMS_CLOUD_AI.CREATE_PROFILE( profile_name =>’OPENAI’, attributes =>’{“provider”: “openai”, “model”: “gpt-4o”, “credential_name”: “OPENAI_CRED”, “object_list”: [{“owner”: “ADB_USER”, “name”: “LDBC_GRAPH”}, {“owner”: “ADB_USER”, “name”: “G”}] }’); END; /

SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE(profile_name => ‘OPENAI’);

PL/SQL procedure successfully completed.

SQL> select ai who bought a dress;

CUSTOMER_N

Kate

1 row selected.



## Example: Sample Prompts for Property Graphs {#GUID-082664EE-C2E6-470B-A27E-1E5736F11075}

These examples show how to create sample data and display the generated SQL using the `showsql` action for a given prompt.

Before You Begin

Review [Perform Prerequisites for Select AI](select-ai-manage-profiles.html#GUID-3721296F-14A1-428A-B464-7FA25E9EC8F3).

Example: Create Property Graph Table

The following example creates sample tables and a property graph.

sql CREATE TABLE Person ( id NUMBER PRIMARY KEY, firstName VARCHAR2(20 CHAR), lastName VARCHAR2(20 CHAR), age NUMBER, jsonProp VARCHAR2(40 CHAR) );

CREATE TABLE Post ( id NUMBER PRIMARY KEY, content VARCHAR2(20 CHAR) );

CREATE TABLE personLikesPost ( idPerson NUMBER REFERENCES Person (id), idPost NUMBER REFERENCES Post (id) );

CREATE TABLE personKnowsPerson ( idPerson1 NUMBER REFERENCES Person (id), idPerson2 NUMBER REFERENCES Person (id) );

CREATE PROPERTY GRAPH person_graph VERTEX TABLES ( Person KEY (id) LABEL Person PROPERTIES (firstName, lastName, age, jsonProp), Post KEY (id) LABEL Post PROPERTIES(content) ) EDGE TABLES ( personLikesPost KEY(idPerson, idPost) SOURCE KEY (idPerson) REFERENCES Person (id) DESTINATION KEY (idPost) REFERENCES POST (id) LABEL Likes NO PROPERTIES, personKnowsPerson KEY(idPerson1, idPerson2) SOURCE KEY (idPerson1) REFERENCES Person (id) DESTINATION KEY (idPerson2) REFERENCES Person (id) LABEL Knows NO PROPERTIES );

insert into Person values (1, ‘John’, ‘Doe’,23, ‘{“key1”:”value1”,”key2”:”value2”}’); insert into Person values (2, ‘Scott’, ‘Tiger’, 25, ‘{“key1”:”value3”,”key2”:”value4”}’); insert into Person values (3, ‘Max’, ‘Power’, 27, ‘{“key1”:”value5”,”key2”:”value6”}’); insert into Person values (4, ‘Jane’, ‘Doe’, 22, ‘{“key1”:”value7”,”key2”:”value8”}’); insert into Person (id, Firstname, age) values (5, ‘Hans’, 23); insert into Person (id, Firstname, age) values (6, ‘Franz’, 24);

INSERT INTO Post VALUES (10, ‘Lorem ipsum…’); INSERT INTO Post VALUES (11, ‘Nulla facilisi…’); INSERT INTO Post VALUES (12, ‘Vestibulum eget ..’); INSERT INTO Post VALUES (13, ‘Sed fermentum…’); INSERT INTO Post VALUES (14, ‘Fusce at …’); INSERT INTO Post VALUES (15, ‘Pellentesque sit …’); INSERT INTO Post VALUES (16, ‘Integer…’); INSERT INTO Post VALUES (17, ‘Curabitur luctus …’); INSERT INTO Post VALUES (18, ‘Nam in …’); INSERT INTO Post VALUES (19, ‘Etiam ac …’);

insert into personKnowsPerson values (1, 2); insert into personKnowsPerson values (2, 3); insert into personKnowsPerson values (3, 4); insert into personKnowsPerson values (4, 5); insert into personKnowsPerson values (5, 6); insert into personKnowsPerson values (6, 2); insert into personKnowsPerson values (5, 3);

INSERT INTO personLikesPost VALUES (1, 10); INSERT INTO personLikesPost VALUES (1, 11); INSERT INTO personLikesPost VALUES (1, 12); INSERT INTO personLikesPost VALUES (2, 10); INSERT INTO personLikesPost VALUES (2, 13); INSERT INTO personLikesPost VALUES (2, 14); INSERT INTO personLikesPost VALUES (3, 11); INSERT INTO personLikesPost VALUES (3, 15); INSERT INTO personLikesPost VALUES (3, 16); INSERT INTO personLikesPost VALUES (4, 12); INSERT INTO personLikesPost VALUES (4, 17); INSERT INTO personLikesPost VALUES (4, 18); INSERT INTO personLikesPost VALUES (5, 13); INSERT INTO personLikesPost VALUES (5, 14); INSERT INTO personLikesPost VALUES (5, 19); INSERT INTO personLikesPost VALUES (6, 15); INSERT INTO personLikesPost VALUES (6, 16); INSERT INTO personLikesPost VALUES (6, 17); INSERT INTO personLikesPost VALUES (1, 18); INSERT INTO personLikesPost VALUES (2, 19);

commit;

**Example: Matching of Vertices Without Labels**

Prompt: `Find all the people IDs`

SELECT person_id FROM GRAPH_TABLE(“ADB_USER”.”PERSON_GRAPH” MATCH (v IS “PERSON”) COLUMNS (VERTEX_ID(v) AS person_id))



**Example: Matching of Edges Without Labels**

Prompt: `Find all the edge IDs`

SELECT edge_id FROM GRAPH_TABLE(“ADB_USER”.”PERSON_GRAPH” MATCH (v1) -[e]-> (v2) COLUMNS (EDGE_ID(e) AS edge_id))



**Example: Matching of Edges With Labels**

Prompt: `Find all the knows relationship IDs`

SELECT knows_id FROM GRAPH_TABLE(“ADB_USER”.”PERSON_GRAPH” MATCH (p1 IS “PERSON”) -[e IS “KNOWS”]-> (p2 IS “PERSON”) COLUMNS (EDGE_ID(e) AS knows_id))



**Example: Matching of Path Patterns Including One or Multiple Hops**

Prompt: `List all people who know someone who liked the post 'Sed fermentum...'`

SELECT person_id, person_firstname, person_lastname FROM GRAPH_TABLE(“ADB_USER”.”PERSON_GRAPH” MATCH (p1 IS “PERSON”) -[e1 IS “KNOWS”]-> (p2 IS “PERSON”) -[e2 IS “LIKES”]-> (post IS “POST”) WHERE UPPER(post.”CONTENT”) = UPPER(‘Sed fermentum…’) COLUMNS (VERTEX_ID(p1) AS person_id, p1.”FIRSTNAME” AS person_firstname, p1.”LASTNAME” AS person_lastname))



**Example: Accessing Vertex and Edge Properties**

Prompt: `Find first name and last name of all people`

SELECT first_name, last_name FROM GRAPH_TABLE(“ADB_USER”.”PERSON_GRAPH” MATCH (p IS “PERSON”) COLUMNS (p.”FIRSTNAME” AS first_name, p.”LASTNAME” AS last_name))



**Example: Filtering data**

Prompt: `Find post contents liked by John Doe`

SELECT post_content FROM GRAPH_TABLE(“ADB_USER”.”PERSON_GRAPH” MATCH (p IS “PERSON”) -[e IS “LIKES”]-> (post IS “POST”) WHERE UPPER(p.”FIRSTNAME”) = UPPER(‘John’) AND UPPER(p.”LASTNAME”) = UPPER(‘Doe’) COLUMNS (post.”CONTENT” AS post_content))



**Example: Functions and Expressions**

Prompt: `Show all people (full name) and display their key1 value from jsonProp property`

SELECT person_fullname, json_key1 FROM GRAPH_TABLE(“ADB_USER”.”PERSON_GRAPH” MATCH (p IS “PERSON”) COLUMNS ( (p.”FIRSTNAME” || ‘ ‘ || p.”LASTNAME”) AS person_fullname, JSON_QUERY(p.”JSONPROP”, ‘$.key1’) AS json_key1 ) )



**Example: Sorting Data**

Prompt: `Find friends of Scott Tiger ordered by their last name`

SELECT friend_firstname, friend_lastname FROM GRAPH_TABLE(“ADB_USER”.”PERSON_GRAPH” MATCH (p IS “PERSON”) -[e IS “KNOWS”]-> (f IS “PERSON”) WHERE UPPER(p.”FIRSTNAME”) = UPPER(‘Scott’) AND UPPER(p.”LASTNAME”) = UPPER(‘Tiger’) COLUMNS (f.”FIRSTNAME” AS friend_firstname, f.”LASTNAME” AS friend_lastname) ) ORDER BY friend_lastname



**Example: Row Limiting**

Prompt: `Find all people ordered by first name. Skip one result and return 2 results only`

SELECT person_firstname FROM GRAPH_TABLE(“ADB_USER”.”PERSON_GRAPH” MATCH (p IS “PERSON”) COLUMNS (p.”FIRSTNAME” AS person_firstname)) ORDER BY person_firstname OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY



**Unsupported Queries**

For the following queries certain LLMs generate valid NL2SQL, but the resulting SQL uses features that are not yet supported in Oracle AI Database 26ai.

**Example: Queries Requiring not to Match a Certain Pattern**

Prompt: `Find people that do not know Scott.`

`EXISTS` subquery is not supported.

SELECT person_id, first_name, last_name FROM GRAPH_TABLE(“ADB_USER”.”PERSON_GRAPH” MATCH (p1 IS “PERSON”) WHERE NOT EXISTS ( SELECT 1 FROM GRAPH_TABLE(“ADB_USER”.”PERSONGRAPH” MATCH (p2 IS “PERSON”) -[e IS “PERSONKNOWSPERSON”]-> (p3 IS “PERSON” ) WHERE p2.”ID” = p1.”ID” AND UPPER(p3.”FIRSTNAME”) = UPPER(‘Scott’) COLUMNS (1 AS dummy_value)) ) COLUMNS (p1.”ID” AS person_id, p1.”FIRSTNAME” AS first_name, p1.”LASTNAME” A S last_name))



**Example: Queries Requiring to Optionally Match a Certain Pattern**

Prompt: `Show all people and how many posts they have liked (show people even if they have not liked a post).`

`OPTIONAL` match is not supported.

SELECT person_id, person_firstname, person_lastname, liked_post_ids FROM GRAPH_TABLE(“ADB_USER”.”PERSON_GRAPH” MATCH (p is “PERSON”) OPTIONAL MATCH (p) -[l is “PERSONLIKESPOST”]-> (post is “POST”) COLUMNS( VERTEX_ID(p) as person_id, p.”FIRSTNAME” as person_firstname, p.”LASTNAME” as person_lastname, JSON_ARRAYAGG(VERTEX_ID(post)) as liked_post_ids ))



**Example: Unbounded Recursive Path Patterns**

Prompt: `Find all people that Scott can reach.`

Queries that use unbounded quantifiers are not supported.

SELECT person_id, person_firstname, person_lastname FROM GRAPH_TABLE(“ADB_USER”.”PERSONGRAPH” MATCH (src IS “PERSON”) -[e IS “PERSONKNOWSPERSON”]->* (dst IS “PERSON”) WHERE src.”FIRSTNAME” = ‘Scott’ COLUMNS ( VERTEX_ID(dst) AS person_id, dst.”FIRSTNAME” AS person_firstname, dst.”LASTNAME” AS person_lastname ) )



**Intermittent Queries**

LLMs have been shown to struggle when translating queries that require more than one `GRAPH_TABLE` operator. The following are such examples:

Prompt: `Show people who have liked all the same posts as Hans`

SELECT person_id, person_name FROM GRAPH_TABLE(“PERSON_GRAPH” MATCH (hans is “PERSON”) -[likes_hans is “PERSONLIKESPOST”]-> (post is “POST”), (other_person is “PERSON”) -[likes_other is “PERSONLIKESPOST”]-> (post) WHERE hans.”FIRSTNAME” = ‘Hans’ COLUMNS (VERTEX_ID(other_person) as person_id, other_person.”FIRSTNAME” AS person_name) ) WHERE NOT EXISTS ( SELECT 1 FROM GRAPH_TABLE(“PERSONGRAPH” MATCH (hans is “PERSON”) -[likes_hans is “PERSONLIKESPOST”]-> (post is “POST”) WHERE hans.”FIRSTNAME” = ‘Hans’ COLUMNS (VERTEX_ID(post) as post_id) ) hans_posts LEFT JOIN GRAPH_TABLE(“PERSONGRAPH” MATCH (other_person is “PERSON”) -[likes_other is “PERSONLIKESPOST”]-> (post is “POST”) COLUMNS (VERTEX_ID(post) as post_id) ) other_posts ON hans_posts.post_id = other_posts.post_id WHERE other_posts.post_id IS NULL )



**Example: Matching of Recursive Path Patterns with Defined Bounds.**

Prompt: `Find all names of the people that can be reached in a 1 to 3 edge path`

SELECT person_name FROM GRAPH_TABLE(“ADB_USER”.”PERSON_GRAPH” MATCH (start_person IS “PERSON”) -[e IS “KNOWS”]->{1,3} (end_person IS “PERSON”) COLUMNS (end_person.”FIRSTNAME” AS person_name))



**Example: Filtering Data for Nodes Along a Recursive Path**

Prompt: `Find all names of the people that can be reached in a 1 to 3 edge path where each person is younger than the next one`

SELECT person_name FROM GRAPH_TABLE(“ADB_USER”.”PERSON_GRAPH” MATCH (start_person IS “PERSON”) ((v1 IS “PERSON”) -[e IS “KNOWS”]-> (v2 IS”PERSON”) WHERE v1.”AGE” < v2.”AGE”){1,3} (end_person IS “PERSON”) COLUMNS (end_person.”FIRSTNAME” AS person_name))



**Example: Grouping and Aggregation**

LLMs often struggle with translating queries that require grouping and aggregation. A common mistake is placing aggregations in the `COLUMNS` clause instead of the `SELECT` clause.

Prompt: `Find the average number of posts liked by all the users`

SELECT AVG(COUNT(post)) AS average_liked_count FROM GRAPH_TABLE(“PERSON_GRAPH” MATCH (p IS “PERSON”) -[e IS “PERSONLIKESPOST”]-> (post IS “POST”) COLUMNS (VERTEX_ID(p) AS person, VERTEX_ID(post) AS post)) GROUP BY person; ```