Oracle Autonomous Database Select AI is a powerful tool that allows you to use generative AI from a wide range of third-party AI providers– right from your database. Select AI quickly and easily enables you to build AI-enabled applications supporting many use cases involving natural language-to-SQL query generation, retrieval augmented generation (RAG), synthetic data generation, sentiment analysis, text summarization, or content generation, among others. Select AI automates the orchestration of various specialized tools – whether from Oracle or third parties. 

One of the lesser-known use cases, however, is using Select AI right within your SQL queries to achieve scalable AI pipelines for AI-based content generation, filtering, and aggregation. This allows you to take advantage of the core Oracle SQL engine to process your results in a scalable and cost-effective manner. 

AI SQL Capabilities Enabled using Select AI

A core strength of Select AI lies in its ability to build powerful AI pipelines directly within Oracle’s SQL environment. There’s no need to separate data processing from AI processing. Instead, you have a well-integrated platform for building AI pipelines supporting your AI-enabled applications. 

In this blog, I’ll highlight a few use cases illustrating how you can benefit from generative AI functionality directly in SQL queries using the DBMS_CLOUD_AI.GENERATE function:

  1. Using generative AI over an entire table to generate content based on each row in the table 
  2. Using AI-based filtering in WHERE clauses
  3. Aggregating content as input to your LLM enabling GROUP BY processing 

There’s no need for specialized operators, as DBMS_CLOUD_AI.GENERATE gives you the flexibility and control to achieve the results you require: The DBMS_CLOUD_AI package supports the Select AI functionality. The GENERATE function enables invoking Select AI actions in PL/SQL scripts and SQL queries, as we’ll see. 

Use case 1a: Generate personalized emails

This example uses generative AI to generate content for each row in a table. Let’s say you want to generate personalized emails for each of your customers with vacation activity ideas. The AI-generated content is customized using information you have in your database regarding customer demographic and preferences, like vacation destination. 

In this first example, you use Select AI to iterate over each row in our travel_recommendations table, so that the LLM generates a custom email for travel recommendations for each customer. Here’s the table definition:

CREATE TABLE travel_recommendations (
    id           NUMBER,
    task         VARCHAR(4000),
    task_rules   VARCHAR(4000),
    last_name    VARCHAR(50),
    first_name   VARCHAR(50),
    location     VARCHAR(100),
    age          NUMBER,
    gender       VARCHAR(10),
    has_kids     VARCHAR(5),
    num_cars     NUMBER,
    income_level VARCHAR(50),
    dog_owner    VARCHAR(5)
);

The query below performs the following:

a)    The nested SELECT statement retrieves the content needed from the travel_recommendations table and converts it to a JSON object that the LLM can easily consume it as a prompt. 
b)    Using the GENERATE function from the DBMS_CLOUD_AI package, it receives the JSON output as the prompt and also specifies the AI profile name, with the specific AI provider and LLM you want to use. In this case, our AI provider is the OCI Generative AI Service using the default llama3 LLM. 
c)    The ‘chat’ action parameter in the GENERATE function serves as a pass-through to the LLM to process our prompt.

SELECT id, 
       DBMS_CLOUD_AI.GENERATE(prompt       => prompt,  
                              profile_name => 'OCI_PROFILE', 
                              action       => 'chat') email_text
FROM (SELECT id, JSON_OBJECT(*) prompt
      FROM  (select id, task, task_rules, last_name, first_name, location, age,
                    gender, has_kids, num_cars, income_level, dog_owner
              FROM  travel_recommendations))

The screen capture below shows the output using Oracle Machine Learning Notebooks. You see one email generated per customer.

Figure 1

Use case 1b: Identify sentiment of customer product feedback

This example uses the ‘chat’ action with a prompt that concatenates the instructions with the content from the product_feedback column of our customer_feedback table. Here’s the table definition:

CREATE TABLE customer_feedback (
    id               NUMBER,
    first_name       VARCHAR2(50),
    last_name        VARCHAR2(50),
    product          VARCHAR2(100),
    product_feedback VARCHAR2(4000)
);

As you see below, our instruction asks the LLM to return the sentiment of the feedback using only the values positive, neutral, or negative, and nothing else. Again, use the GENERATE function iterating over the customer_feedback table. 

SELECT id, product,
       DBMS_CLOUD_AI.GENERATE(prompt => 'Return the sentiment of the following text using only the values positive, neutral, or negative, nothing else: ' || product_feedback,  
                              profile_name => 'OCI_PROFILE', 
                              action => 'chat') sentiment
FROM   customer_feedback

And here, you see the sentiment for each product as the result of the query:

Figure 2

As a next step, you can even aggregate the counts of the sentiment through a simple addition to the query above. 

SELECT product, 
       DBMS_LOB.SUBSTR(DBMS_CLOUD_AI.GENERATE(prompt       => 'Return the sentiment of the following text using only the values positive, neutral, or negative, nothing else: ' 
                                                               || product_feedback,  
                                              profile_name => 'OCI_PROFILE', 
                                              action       => 'chat'), 4000,1) sentiment, 
       count(*) count
FROM   customer_feedback
GROUP BY product, sentiment
ORDER BY sentiment desc, product

Here are the aggregated sentiment counts:

Figure 3

Use case 2: Filter for product reviews with positive feedback

In this next example, instead of using AI in the SELECT clause, you perform AI-based filtering in the WHERE clause. The following query lets us select only those product reviews with positive feedback.

SELECT id, product
FROM   customer_feedback
WHERE DBMS_LOB.SUBSTR(DBMS_CLOUD_AI.GENERATE(prompt       => 'Return the sentiment of the following text using only the values positive, neutral, or negative, nothing else: ' || product_feedback,  
                                             profile_name => 'OCI_PROFILE', 
                                             action       => 'chat'), 4000,1) = 'positive'

You see each review id and the corresponding product in the output. 

Figure 4

Use case 3: Summarize feedback grouped by product

The last use case aggregates content as input to your LLM, enabling GROUP BY processing. Building on the product feedback example, let’s ask the LLM to summarize the feedback for each product. This requires grouping the comments by product and aggregating them into a single string for the LLM to summarize. 

The query below uses the JSON_ARRAYAGG function, which produces a JSON object that contains all for the feedback for each product. We’re also using the Select AI ‘summarize’ action, which simplifies text summarization because it eliminates the need for you to specify instructions explicitly in your prompt. If you want more control over the summary—like paragraph or bullet formatting, among others—you can set additional parameters.

SELECT product, DBMS_CLOUD_AI.GENERATE(prompt       => all_feedback, 
                                       profile_name => 'OCI_PROFILE', 
                                       action       => 'summarize') review_summary
FROM (SELECT product, 
             JSON_ARRAYAGG(JSON_OBJECT('feedback_text' VALUE product_feedback)) all_feedback
      FROM customer_feedback
      GROUP BY product)

Here are the review summaries:

Figure 5

Why choose Select AI?

Not only does Select AI enable you to use AI right within your SQL queries for scalable AI, it is also a compelling solution for the following reasons:

  • Deep Native Integration with Autonomous Database: Select AI is natively integrated into the Oracle Database engine, giving unparalleled access to database metadata, object definitions, and user privileges, allowing for more accurate, secure, and context-aware SQL generation.
  • Security that is built-In, not bolted on: Unlike external solutions, Select AI fully leverages Oracle Database’s 
  • security model and natively supports auditing, privilege enforcement, rate limiting, and row-level security. Select AI helps you to adhere to your organization’s governance and compliance standards.
  • LLM-agnostic and enterprise-ready: Choose from a wide range of AI providers, including public LLMs like OpenAI GPT-4o, Cohere Command-A, Gemini 2.5 Pro, Claude 3.5 Sonnet, and many others. Select AI securely connects to LLMs using cloud-native service principal tokens (AWS ARN, Google Cloud IAM, Azure SPs), eliminating the need to expose keys or secrets. And, coming soon, you can self-host using private endpoints on OCI. 
  • Supported both in the cloud and on-premises: In addition to using the full range of functionality on Oracle Autonomous Database on OCI and on leading hyperscalers, Select AI is also being rolled out for Oracle Database instances on-premises and in the cloud. The support for NL2SQL query generation started with Oracle Database 23.7, with RAG and other features to follow. 

Using your account and credentials to one of the many supported AI providers, get started with Select AI for free on OCI: Autonomous Database Free Trial

For more information…

See these resources:

Video

Blogs

Documentation

LiveLabs