Use Select AI to Generate SQL from Natural Language Prompts
The Select AI feature allows Autonomous 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.
Related Topics
Terminology
It is important to understand the various terms used with Select AI before using it.
The following are the terms related to Select AI feature:
Term | Definition |
---|---|
Database Credential |
Database Credentials are authentication credentials used to access and interact with databases. They typically consist of a user name and a password, sometimes supplemented by additional authentication factors like security tokens. These credentials are used to establish a secure connection between an application or user and a database, ensuring that only authorized individuals or systems can access and manipulate the data stored within the database. |
Hallucination in LLM |
Hallucination in the context of Large Language Models refers to a phenomenon where the model generates text that is incorrect, nonsensical, or unrelated to the input prompt. Despite being a result of the model's attempt to generate coherent text, these instances can contain information that is fabricated, misleading, or purely imaginative. Hallucination can occur due to biases in training data, lack of proper context understanding, or limitations in the model's training process. |
IAM | Oracle Cloud Infrastructure Identity and Access Management (IAM) lets you control who has access to your cloud resources. You can control what type of access a group of users have and to which specific resources. To learn more, see Overview of Identity and Access Management. |
Large Language Model (LLM) |
Large Language Models refer to advanced artificial intelligence models that are trained on massive amounts of text data to understand and generate human-like language, software code, and database queries. These models are capable of performing a wide range of natural language processing tasks, including text generation, translation, summarization, question answering, sentiment analysis, and more. LLMs are typically neural network-based architectures that learn patterns, context, and semantics from the input data, enabling them to generate coherent and contextually relevant text. |
Natural Language Prompts |
Natural Language Prompts are human-readable instructions or requests provided to guide generative AI models, such as Large Language Models. Instead of using specific programming languages or commands, users can interact with these models by entering prompts in a more conversational or natural language form. The models then generate output based on the provided prompt. |
Network Access Control List (ACL) |
A Network Access Control List is a set of rules or permissions that define what network traffic is allowed to pass through a network device, such as a router, firewall, or gateway. ACLs are used to control and filter incoming and outgoing traffic based on various criteria such as IP addresses, port numbers, and protocols. They play a crucial role in network security by enabling administrators to manage and restrict network traffic to prevent unauthorized access, potential attacks, and data breaches. |
Examples of Using Select AI
Explore integrating Oracle's Select AI with various AI providers like OpenAI, Cohere, Azure Open AI, and OCI Generative AI to generate SQL queries directly from natural language.
These examples showcase common Select AI actions and guide you through setting up your profile with different AI providers to leverage those actions.
Example: Select AI Actions
The following example illustrates actions such as
runsql
, showsql
, narrate
,
chat
, and explainsql
that you can perform with
SELECT AI
. These examples use the sh
schema
with AI provider and profile attributes set in the DBMS_CLOUD_AI.CREATE_PROFILE
function.
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.
Usage Guidelines
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.
WARNING:
Large language models (LLMs) have been trained on a broad set of text documentation and content, typically from the Internet. As a result, LLMs may have incorporated patterns from invalid or malicious content, including SQL injection. Thus, while LLMs are adept at generating useful and relevant content, they also can generate incorrect and false information including SQL queries that produce inaccurate results and/or compromise security of your data.
The queries generated on your behalf by the user-specified LLM provider will be run in your database. Your use of this feature is solely at your own risk, and, notwithstanding any other terms and conditions related to the services provided by Oracle, constitutes your acceptance of that risk and express exclusion of Oracle’s responsibility or liability for any damages resulting from that use.
About SQL Generation
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 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.
DBMS_CLOUD_AI
package in Autonomous Database enables integration with a user-specified LLM for generating SQL code using natural language prompts. The package assists in supplying the LLM with knowledge of the database schema and instructing it to write a SQL query consistent with that schema. The DBMS_CLOUD_AI
package works with AI providers like OpenAI, Cohere, Azure OpenAI Service, and Oracle Cloud Infrastructure Generative AI.
Note:
Users must have an account with the AI provider and provide their credentials throughDBMS_CLOUD_AI
objects that the Autonomous Database uses.
Use DBMS_CLOUD_AI to Configure AI Profiles
Autonomous 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.
Requirements to Configure DBMS_CLOUD_AI Package
The following are required to run DBMS_CLOUD_AI
:
- Access to an Oracle Cloud Infrastructure cloud account and to an Autonomous 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:
- 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.
- 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.
- 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
- 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 Examples of Using Select AI.
- OCI Generative AI. See How to Generate the API Signing Key.
-
- 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.
Configure DBMS_CLOUD_AI Package
Describes the steps to use DBMS_CLOUD_AI
.
Configure DBMS_CLOUD_AI
DBMS_CLOUD_AI
:
-
Grant the
EXECUTE
privilege on theDBMS_CLOUD_AI
package to the user who wants to use Select AI.By default, only ADMIN user is granted the
EXECUTE
privilege. The ADMIN user can grantEXECUTE
privilege to other users. -
Grant network ACL access to the user who wants to use Select AI and for the AI provider endpoint.
The ADMIN user can grant network ACL access. See APPEND_HOST_ACE Procedure for more information.
- Create a credential to enable access to your AI provider.
See CREATE_CREDENTIAL Procedure for more information.
EXECUTE
privilege to
ADB_USER
:grant execute on DBMS_CLOUD_AI to ADB_USER;
The following example grants ADB_USER
the
privilege to use the api.openai.com 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;
/
APPEND_HOST_ACE Function Parameters
Parameter | Description |
---|---|
|
The host, which can be the name or the IP address of the host. You can use a wildcard to specify a domain or a IP subnet. The host or domain name is not case sensitive. For OpenAI, use api.openai.com. For Cohere, use api.cohere.ai. For Azure OpenAI Service, use <azure_resource_name>.openai.azure.com. See Profile Attributes to know more about |
|
The access control entries (ACE).
The |
Here is an example of how to create a credential to enable access to OpenAI.
EXEC DBMS_CLOUD.CREATE_CREDENTIAL
('OPENAI_CRED', 'OPENAI', 'your_api_token');
DBMS_CLOUD.CREATE_CREDENTIAL
Parameters
Parameter | Description |
---|---|
|
The name of the credential to be
stored. The |
|
The The |
|
The The |
Create and Set an AI Profile
Describes the steps to create and enable an AI profile.
Use DBMS_CLOUD_AI.CREATE_PROFILE
to create an AI profile. Next start DBMS_CLOUD_AI.SET_PROFILE
to enable the AI profile so that you can use SELECT AI
with a natural language prompt.
Note:
You must run DBMS_CLOUD_AI.SET_PROFILE
in each new database
session (connection) before you use SELECT
AI
.
The following example with the OpenAI provider creates an
AI
profile called OPENAI
and sets the
OPENAI
profile for the current user session.
-- 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 Keyword to Enter Prompts
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.
Note:
You cannot run PL/SQL statements, DDL statements, or DML statements using theAI
keyword.
Syntax
SELECT AI action natural_language_prompt
Parameters
Parameter | Description |
---|---|
|
Run the provided SQL command using a natural language prompt. This is the default action and it is optional to specify this parameter. |
|
Displays the SQL statement for a natural language prompt. |
|
The output of the prompt is explained in natural language. This option sends the SQL result to the AI provider to produce a natural language summary. |
|
Generates
a response directly from the LLM based on the prompt. If
|
|
The SQL generated from the prompt is explained in natural language. This option sends the generated SQL to the AI provider to produce a natural language explanation. |
Usage Notes
-
Select
AI
is not supported in Database Actions or APEX Service. You can use onlyDBMS_CLOUD_AI.GENERATE
function. -
The
AI
keyword is supported only in aSELECT
statement. -
You cannot run PL/SQL statements, DDL statements, or DML statements using the
AI
keyword. -
The sequence is
SELECT
followed byAI
. These keywords are not case-sensitive. After aDBMS_CLOUD_AI.SET_PROFILE
is configured, the text afterSELECT AI
is a natural language prompt. If an AI profile is not set,SELECT
AI
reports the following error:ORA-00923: FROM keyword not found where expected 00923. 00000 - "FROM keyword not found where expected"
-
Special character usage rules apply according to Oracle guidelines. For example, use single quotes twice if you are using an apostrophe in a sentence.
select ai how many customers in SF don''t own their own home
-
LLMs are subject to hallucinations and results are not always correct:
-
It is possible that
SELECT AI
may not be able to run the generated SQL for a specific natural language prompt. -
It is possible that
SELECT AI
may not be able to generate SQL for a specific natural language prompt.
In such a scenario,
SELECT AI
responds with information to assist you in generating valid SQL. -
-
Use the
chat
action, withSELECT AI chat
, to learn more about SQL constructs. For better results with thechat
action, use database views or tables with contextual column names or consider adding column comments explaining values stored in the columns. -
To access DBA or USER views, see DBMS_CLOUD_AI Views.
Use Select AI to Generate SQL from Natural Language Prompts
For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.
Access to Oracle Support
Oracle customers that have purchased support have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.