1. Using Oracle Autonomous Database Serverless
  2. Features
  3. Use Select AI to Generate SQL from Natural Language Prompts

Use Select AI to Generate SQL from Natural Language Prompts

Oracle Autonomous Database Select AI enables you to query your data using natural language.

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.

  • Usage Guidelines
    Provides usage guidelines that ensure effective and proper usage of natural language prompts for SQL generation to ensure an enhanced user experience.
  • 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.
  • Use DBMS_CLOUD_AI to Configure AI Profiles
    Oracle Autonomous Database Serverless uses AI profiles to facilitate and configure access to an LLM and to setup for the generation of SQL statements from natural language prompts.
  • Configure DBMS_CLOUD_AI Package
    Describes the steps to use DBMS_CLOUD_AI.
  • Create and Set an AI Profile
    Describes the steps to create and enable an AI profile.
  • Use AI Keyword to Enter Prompts
    Use AI as the keyword in a SELECT statement for interacting with the database using natural language prompts.
  • 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.
  • Terminology
    Definition of some of the terms used in Select AI feature are described.

Parent topic: Features

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.

Parent topic: Use Select AI to Generate SQL from Natural Language Prompts

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.

The 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 through DBMS_CLOUD_AI objects that the Autonomous Database uses.

Parent topic: Use Select AI to Generate SQL from Natural Language Prompts

Use DBMS_CLOUD_AI to Configure AI Profiles

Oracle Autonomous Database Serverless 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.

In addition to specifying tables and views in the AI profile, you can also specify tables mapped with external tables, including those described in Query External Data with Data Catalog. This enables you to query data not just inside the database, but also data stored in a data lake's object store.

Parent topic: Use Select AI to Generate SQL from Natural Language Prompts

Configure DBMS_CLOUD_AI Package

Describes the steps to use DBMS_CLOUD_AI.

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

To configure DBMS_CLOUD_AI:

  1. Grant the EXECUTE privilege on the DBMS_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 grant EXECUTE privilege to other users.

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

  3. Create a credential to enable access to your AI provider.

    See CREATE_CREDENTIAL Procedure for more information.

The following example grants the 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

host

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

ace

The access control entries (ACE). The XS$ACE_TYPE type is provided to construct each ACE entry for the ACL. For more details, see Creating ACLs and ACEs.

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

credential_name

The name of the credential to be stored. The credential_name parameter must conform to Oracle object naming conventions, which do not allow spaces or hyphens.

username

The username and password arguments together specify your AI provider credentials.

The username is a user-specified user name.

password

The username and password arguments together specify your AI provider credentials.

The password is your AI provider secret API key, and depends on the provider:

  • OpenAI: See Use OpenAI to get your API keys.
  • Cohere: See Use Cohere to get your API keys.
  • Azure OpenAI Service: See Use Azure OpenAI Service to get your API keys and how to configure the service.

    Note:

    If you are using the Azure OpenAI Service principle to authenticate, you can skip the DBMS_CLOUD.CREATE_CREDENTIAL procedure. See Examples of Using Select AI for an example of authenticating using Azure OpenAI Service principle.
  • Use OpenAI
    To enable OpenAI to generate SQL from natural language prompts, obtain API keys from your OpenAI paid account.
  • Use Cohere
    To enable Cohere to generate SQL from natural language prompts, obtain API keys from your Cohere paid account.
  • Use Azure OpenAI Service
    To enable Azure OpenAI Service to generate SQL from natural language prompts, configure and provide access to the AI provider.

Parent topic: Use Select AI to Generate SQL from Natural Language Prompts

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

Parent topic: Configure DBMS_CLOUD_AI Package

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

Parent topic: Configure DBMS_CLOUD_AI Package

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

Parent topic: Configure DBMS_CLOUD_AI Package

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.

Parent topic: Use Select AI to Generate SQL from Natural Language Prompts

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 the AI keyword.

Syntax

The syntax for running AI prompt is:
SELECT AI action natural_language_prompt

Parameters

The following are the parameters available for the action parameter:
Parameter Description

runsql

Run the provided SQL command using a natural language prompt. This is the default action and it is optional to specify this parameter.

showsql

Displays the SQL statement for a natural language prompt.

narrate

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.

chat

Generates a response directly from the LLM based on the prompt. If conversation in the DBMS_CLOUD_AI.CREATE_PROFILE function is set to true, this option includes content from prior interactions or prompts, potentially including schema metadata.

explainsql

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 only DBMS_CLOUD_AI.GENERATE function.

  • The AI keyword is supported only in a SELECT statement.

  • You cannot run PL/SQL statements, DDL statements, or DML statements using the AI keyword.

  • The sequence is SELECT followed by AI. These keywords are not case-sensitive. After a DBMS_CLOUD_AI.SET_PROFILE is configured, the text after SELECT 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, with SELECT AI chat, to learn more about SQL constructs. For better results with the chat 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.

Parent topic: Use Select AI to Generate SQL from Natural Language Prompts

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

Example: Select AI with OpenAI

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

Note:

Only an ADMIN user can run EXECUTE privileges and network ACL procedure. 

--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
--
SQL> EXEC DBMS_CLOUD.CREATE_CREDENTIAL('OPENAI_CRED', 'OPENAI', '<your api token>');
 
PL/SQL procedure successfully completed.
 
--
-- 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": "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 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 Database;
 
RESPONSE
--------------------------------------------------------------------------------
Autonomous 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 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 Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomo
us Database provides high performance, scalability, and reliability, making it a
n ideal choice for modern cloud-based applications.


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.
 
 
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('OPENAI');
 
PL/SQL procedure successfully completed.

Example: Select AI with Cohere

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

Note:

Only an ADMIN user can run EXECUTE privileges and network ACL procedure. 

--Grants EXECUTE privilege to ADB_USER
--
SQL>grant execute on DBMS_CLOUD_AI to ADB_USER;
--
-- Create Credential for AI provider
--
SQL> EXEC DBMS_CLOUD.CREATE_CREDENTIAL('COHERE_CRED', 'COHERE', '<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
--SQL> BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
      'COHERE',
      '{"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
 
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('COHERE');
 
PL/SQL procedure successfully completed.

Example: Select AI with Azure OpenAI Service API Key

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

-- Create Credential for AI integration
--
SQL> EXEC DBMS_CLOUD.CREATE_CREDENTIAL('AZURE_CRED', 'AZUREAI', '<your api token>');
  
PL/SQL procedure successfully completed.
  
--
-- Grant Network ACL for 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 => 'ADMIN',
                             principal_type => xs_acl.ptype_db)
       );
       END;
       /
  
PL/SQL procedure successfully completed.
  
--
-- Create AI profile
--
SQL> BEGIN                                                                         
    DBMS_CLOUD_AI.CREATE_PROFILE(                                               
      'AZUREAI',                                                              
      '{"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
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('AZUREAI');
  
PL/SQL procedure successfully completed.

--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
 
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"AZUREAI"
  
  
--
-- 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 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 Database;
  
RESPONSE
--------------------------------------------------------------------------------
Autonomous 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 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 Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomo
us Database provides high performance, scalability, and reliability, making it a
n ideal choice for modern cloud-based applications.


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.
 
   
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('AZUREAI');
  
PL/SQL procedure successfully completed.

Example: Select AI with Azure OpenAI Service Principle

Connect as an ADMIN to provide access to Azure service principle 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.

Note:

Only an ADMIN user can run EXECUTE privileges and network ACL procedure. 
-- 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/<tenant_id>/oauth2/v2.0/authorize?client_id=<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
--SQL> BEGIN                                                                         
    DBMS_CLOUD_AI.CREATE_PROFILE(                                               
      'AZUREAI',                                                              
      '{"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
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('AZUREAI');
  
PL/SQL procedure successfully completed.
  
--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
 
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"AZUREAI"  

--
-- 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 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 Database;
  
RESPONSE
--------------------------------------------------------------------------------
Autonomous 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 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 Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomo
us Database provides high performance, scalability, and reliability, making it a
n ideal choice for modern cloud-based applications.


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.
 
 
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('AZUREAI');
  
PL/SQL procedure successfully completed.

Select AI with OCI Generative AI API Key

The following example shows how you can access OCI Generative AI using your OCI API key, create an AI profile, and generate SQL from natural language prompts.

Note:

OCI Generative AI uses cohere.command as the default model if you do not specify the model_name. 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       => 'ocid1.user.oc1..aaaa...',
    tenancy_ocid    => 'ocid1.tenancy.oc1..aaaa...',
    private_key     => '<your_api_key>',
    fingerprint     => '<your_fingerprint>'      
  );                                                                          
END;                                                                         
/
 
--
-- Create AI profile
--
SQL> BEGIN                                                                        
  DBMS_CLOUD_AI.CREATE_PROFILE(                                              
      'GENAI',                                                             
      '{"provider": "oci",                                                                   
        "credential_name": "GENAI_CRED"               
       }');                                                                  
     END;                                                                         
     /  
   
PL/SQL procedure successfully completed.
   
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');
   
PL/SQL procedure successfully completed.
   
--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
 
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"GENAI"
  
--
-- Use AI
--  
   
SQL> select ai chat what is Autonomous Database;
   
RESPONSE
--------------------------------------------------------------------------------
Autonomous 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 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 Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomo
us Database provides high performance, scalability, and reliability, making it a
n ideal choice for modern cloud-based applications.


SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('GENAI');
  
PL/SQL procedure successfully completed.

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 Database to provide access to a dynamic group.

Set the required policies to obtain access to all Generative AI resources. See https://docs.oracle.com/en-us/iaas/Content/generative-ai/iam-policies.htm to know more about Generative AI policies.

  • To get access to all Generative AI resources in the entire tenancy, use the following policy:

    allow group <your-group-name> to manage generative-ai-family in tenancy

  • To get access to all Generative AI resources in your compartment, use the following policy:

    allow group <your-group-name> to manage generative-ai-family in compartment <your-compartment-name>

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

Note:

OCI Generative AI uses cohere.command as the default model if you do not specify the model_name. To learn more about the parameters, see Profile Attributes. 
-- Connect as ADMIN user and enable OCI resource principal.
BEGIN
  DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(provider  => 'OCI');
END;
/
 
--
-- Create AI profile
--SQL>BEGIN                                                                        
  DBMS_CLOUD_AI.CREATE_PROFILE(                                              
      'GENAI',                                                             
      '{"provider": "oci",                                                                   
        "credential_name": "OCI$RESOURCE_PRINCIPAL"                
       }');                                                                  
    END;                                                                         
    /
   
PL/SQL procedure successfully completed.
   
--
-- Enable AI profile in current session
--
SQL>EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');
   
PL/SQL procedure successfully completed.

--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
 
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"GENAI"   
   
-- Use AI
   
SQL> select ai chat what is Autonomous Database;
   
RESPONSE
--------------------------------------------------------------------------------
Autonomous 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 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 Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomo
us Database provides high performance, scalability, and reliability, making it a
n ideal choice for modern cloud-based applications.



SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('GENAI');
  
PL/SQL procedure successfully completed.

Select AI with OCI Generative AI Using LLMA Model

This example showcases the chat feature of the LLMA model from OCI Generative AI. It highlights the model's capabilities through two prompts: generating a friendly email to customers for movie recommendations and generate an introductory paragraph on rock climbing. 

SQL> BEGIN                                                                          
	  DBMS_CLOUD.CREATE_CREDENTIAL(                                                
		credential_name => 'GENAI_CRED',                                           
		user_ocid       => 'ocid1.user.oc1..aaa',
		tenancy_ocid    => 'ocid1.tenancy.oc1..aaa',
		private_key     => '<your_api_key>',
		fingerprint     => '<your_fingerprint>'       
	  );                                                                           
	 END;                                                                           
	/
	
	PL/SQL procedure successfully completed.
	
	BEGIN                                                                          
	  DBMS_CLOUD_AI.CREATE_PROFILE(                                                
		  'GENAI',
		  '{"provider": "oci",
			"model": "meta.llama-2-70b-chat",
			"oci_runtimetype":"LLAMA"}');
	END;                                                                           
	/
	
	PL/SQL procedure successfully completed.

SQL> BEGIN                                                                          
	  DBMS_CLOUD_AI.SET_ATTRIBUTE(                                                 
		  'GENAI', 'credential_name', 'GENAI_CRED');                               
	 END;                                                                           
	/                                                                              
	
	PL/SQL procedure successfully completed.

SQL > BEGIN                                                                          
	  DBMS_CLOUD_AI.SET_ATTRIBUTE(                                                 
		  'GENAI', 'oci_compartment_id', 'ocid1.compartment.oc1...');
	  END;                                                                           
	  /
	
	PL/SQL procedure successfully completed.

SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');
          
	PL/SQL procedure successfully completed.

SQL> set linesize 150                                                               
SQL> SELECT AI chat Generate a friendly email to customer Gilbert Lee with two action-thriller movie recommendations available through our MovieStream service;
SQL>  
	RESPONSE
	------------------------------------------------------------------------------------------------------------------------------------------------------
	.
	Subject: Action-packed movie recommendations for you!
	Dear Gilbert,
	I hope this email finds you well! I wanted to reach out to you today to recommend two action-thriller movies that are currently available on our Movie
	Stream service. I think you'll really enjoy them!
	The first movie I recommend is "John Wick" starring Keanu Reeves. This movie follows the story of a retired hitman who seeks vengeance against a power
	ful crime lord and his army of assassins. The action scenes are intense and non-stop, and Keanu Reeves delivers an outstanding performance.
	RESPONSE
	------------------------------------------------------------------------------------------------------------------------------------------------------
	The second movie I recommend is "Mission: Impossible - Fallout" starring Tom Cruise. This movie follows Ethan Hunt and his team as they try to prevent
	 a global catastrophe. The action scenes are heart-stopping and the stunts are truly impressive. Tom Cruise once again proves why he's one of the grea
	test action stars of all time.
	Both of these movies are sure to keep you on the edge of your seat and provide plenty of thrills and excitement. They're available to stream now on Mo
	vieStream, so be sure to check them out!
	If you have any questions or need assistance with MovieStream, please don't hesitate to reach out to me. I'm always here to help.
	Thank you for being a valued customer, and I hope you enjoy the movies!
	RESPONSE
	------------------------------------------------------------------------------------------------------------------------------------------------------
	Best regards,
	[Your Name]
	MovieStream Customer Service
	
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, an
	d 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 flex
	ibility, 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!

Improve SQL Query Generation by Adding Comments to Database Tables and Columns

This example demonstrates how comments in database tables and columns can improve the generation of SQL queries from natural language prompts. In this example, Azure OpenAI Service acts as the AI provider. The "comments":"true" parameter in DBMS_CLOUD_AI.CREATE_PROFILE function determines whether comments are passed to the model for 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

Parent topic: Use Select AI to Generate SQL from Natural Language Prompts

Terminology

Definition of some of the terms used in Select AI feature are described.

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.

Parent topic: Use Select AI to Generate SQL from Natural Language Prompts