The Select AI feature allows Autonomous AI Database to use generative AI with Large Language Models (LLMs) to convert user's input text into Oracle SQL. Select AI processes the natural language prompt, supplements the prompt with metadata, and then generates and runs a SQL query.

Select AI is supported on Autonomous AI Database Serverless and Autonomous AI Database on Dedicated Exadata Infrastructure and Cloud at Customers.

Using natural language to interact with your database data is now achievable with LLMs. This means you can use natural language, for example plain English, to query the database.

When you use Select AI, Autonomous AI Database manages the process of converting natural language into SQL. This means you can provide a natural language prompt instead of SQL code to interact with your data. Select AI serves as a productivity tool for SQL users and developers and enables non-expert SQL users to derive useful insights from their data, without having to understand data structures or technical languages.

Provides usage guidelines that ensure effective and proper usage of natural language prompts for SQL generation to ensure an enhanced user experience.

Intended Use

This feature is intended for the generation and running of SQL queries resulting from user-provided natural language prompts. It automates what a user could do manually based on their schema metadata in combination with a large language model (LLM) of their choice.

While any prompt can be provided, including those that do not relate to the production of SQL query results, Select AI focuses on SQL query generation. Select AI enables submitting general requests with the chat action.

Prompt Augmentation Data

The database augments the user-specified prompt with database metadata to mitigate hallucinations from the LLM. The augmented prompt is then sent to the user-specified LLM to produce the query.

The database augments the prompt with schema metadata only. This metadata may include schema definitions, table and column comments, and content available from the data dictionary and catalog. For the purposes of SQL generation, the database does not provide table or view contents (actual row or column values) when augmenting the prompt.

The narrate action, however, does provide the result of the query, which may contain database data, to the user-specified LLM from which to generate natural language text describing the query results.

Choose an AI provider and LLM that meets your security standards and aligns with your specific needs, like text or code generation.

Explores the concepts and terms related to Select AI.

Using natural language to interact with your database data is now achievable with LLMs. This means you can use natural language, for example plain English, to query the database.

When you use Select AI, Autonomous AI Database manages the process of converting natural language into SQL. This means you can provide a natural language prompt instead of SQL code to interact with your data. Select AI serves as a productivity tool for SQL users and developers and enables non-expert SQL users to derive useful insights from their data, without having to understand data structures or technical languages.

Autonomous AI Database uses AI profiles to facilitate and configure access to an LLM and to setup for the generation of SQL statements from natural language prompts.

AI profiles include database objects that are the target for natural language queries. Metadata used from these targets can include database table names, column names, column data types, and comments. You create and configure AI profiles using the DBMS_CLOUD_AI.CREATE_PROFILE and DBMS_CLOUD_AI.SET_PROFILE procedures.

The following are required to run DBMS_CLOUD_AI:

• Access to an Oracle Cloud Infrastructure cloud account and to an Autonomous AI Database instance.
• A paid API account for a supported AI provider, one of:

  • OpenAI: To enable OpenAI to generate SQL from natural language prompts, obtain API keys from your OpenAI paid account.

    You can find your secret API key in your User settings.

  • Cohere: To enable Cohere to generate SQL from natural language prompts, obtain API keys from your Cohere paid account.

    Click Dashboard, and click API Keys on the left navigation. Copy the default API key or create another key. See API-Keys for more information.

  • Azure OpenAI Service: To enable Azure OpenAI Service to generate SQL from natural language prompts, configure and provide access to the AI provider.

    To use Azure OpenAI Service, perform the following steps:

    1. Obtain your secret API keys. You can find your API keys in the Resource Management section of your Azure portal. On your Azure OpenAI Service Resource page, click Keys and Endpoint. You can copy either KEY1 or KEY2.
    2. Create an Azure OpenAI Service resource and deploy a model: Create and deploy an Azure OpenAI Service resource.

       Tip:

       • Note the resource name and deployment name as those parameters are used to provide network access permission and creating your Azure OpenAI Service profile using the DBMS_CLOUD_AI.CREATE_PROFILE procedure.
       • To know more about rate limits for token per minute on a model, see Azure OpenAI Service quotas and limits.
    3. Allow access to Azure OpenAI Service:
       • You can use your secret API key to allow access to Azure OpenAI Service. To know more, see the example in .
  • OCI Generative AI: See How to Generate the API Signing Key.
  • Google: To enable Google AI Studio to generate SQL and text responses to your natural language prompts, obtain API keys from your Google AI Studio paid account.
    1. Go to Google AI Studio.
    2. Click Sign In to Google AI Studio.
    3. Click Get API key on the prompt screen.
    4. Select all the applicable options on the next page.
    5. Click Create API key.

    6. Click Create API key in new project.

       The screen displays the progress and generates an API key. Copy the key and save it.

  • Anthropic: To enable Anthropic Developer Console to generate SQL and text responses to your natural language prompts, obtain API keys from your Anthropic Developer Console paid account.
    1. Go to Anthropic Developer Console.
    2. Sign up for an account if you don't have one already.
    3. Once logged in, navigate to the API section or the dashboard.
    4. Look for an option to generate or view API keys.
    5. Click to create a new API key.

    6. Copy the generated API key and save it.

       The Claude API is a paid service. You'll need to add credits to your account before you can use the API key.

  • Hugging Face: To enable Hugging Face as your AI provider to generate SQL and text responses to your natural language prompts, obtain API keys from your Hugging Face paid account.
    1. Go to Hugging Face.
    2. Sign up for an account if you don't have one already.
    3. Navigate to your account settings.
    4. In the navigation menu locate the Access Tokens.
    5. Click to create a new API key.
    6. Copy the generated API key and save it.

  • AWS: To enable AWS, obtain your API key and model ID.

    Obtain your API key and use it to create credentials through DBMS_CLOUD.CREATE_CREDENTIAL.

    Follow this process to obtain your API key and model name:

    1. Sign up for an AWS account if you don't have one already.
    2. Create your access keys and secret keys from AWS Bedrock Console.
    3. Copy the generated keys and save it.
    4. Request access to their foundation models. See Access Amazon Bedrock foundation models.
    5. Obtain the model ID. You require the model ID in DBMS_CLOUD_AI.CREATE_PROFILE procedure. Model ID depends on the resources that you use. If you use:
       • a base model, specify the model ID or its ARN(Amazon Resource Names). For a list of model IDs for base models, see Amazon Bedrock base model IDs
       • an inference profile, specify the inference profile ID or its ARN. For a list of inference profile IDs, see Supported Regions and models for cross-region inference.
       • a provisioned model, specify the ARN of the Provisioned Throughput. For more information, see Run inference using a Provisioned Throughput.
       • a custom model, purchase Provisioned Throughput for it. Then, specify the ARN of the resulting provisioned model. For more information, see Use a custom model in Amazon Bedrock.
       • an Amazon Bedrock Marketplace model, specify the ID or ARN of the marketplace endpoint that you created, see Amazon Bedrock Marketplace.

       Note:

       Imported model is not supported with Bedrock Converse API.

    To use AWS as your provider, see Example: Select AI with AWS.

• Network ACL privileges to access your external AI provider.

  Note:

  Network ACL is not applicable for OCI Generative AI.
• A credential that provides access to the AI provider.

Before you use Select AI, here are the steps to enable DBMS_CLOUD_AI.

grant execute on DBMS_CLOUD_AI to ADB_USER;

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

EXEC DBMS_CLOUD.CREATE_CREDENTIAL('OPENAI_CRED', 'OPENAI', 'your_api_token');

Describes the steps to create and enable an AI profile.

-- Create AI profile
--
SQL> BEGIN
  DBMS_CLOUD_AI.create_profile(
      'OPENAI',
      '{"provider": "openai",
        "credential_name": "OPENAI_CRED",
        "object_list": [{"owner": "SH", "name": "customers"},
                        {"owner": "SH", "name": "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('OPENAI');
 
PL/SQL procedure successfully completed.

Use AI as the keyword in a SELECT statement for interacting with the database using natural language prompts.

The AI keyword in a SELECT statement instructs the SQL execution engine to use the LLM identified in the active AI profile to process natural language and to generate SQL.

You can use the AI keyword in a query with Oracle clients such as SQL Developer, OML Notebooks, and third-party tools, to interact with database in natural language.

SELECT AI action natural_language_prompt

These examples illustrate common Select AI actions.

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.