Autonomous Database : Use Select AI to Generate SQL from Natural Language Prompts using Cohere

Autonomous Database now has a SELECT AI feature that generates SQL from natural language, enabling you to query your data using natural language.

As of September 2023, OpenAI and Cohere are available as AI providers for the SELECT AI function. Cohere was used as the AI provider for this post.

As a prerequisite create an account with AI providers like, OpenAI and Cohere and create an API Key. For Cohere, create an API Key from the API Key page under Cohere Dashboard after logging into your Cohere account.

Here are the steps for the "MAGIC" to work -

1. Provision an Autonomous Database.
2. Create user and grant roles/privilegesConnect to Autonomous Database from SQL*Plus as the admin user. As Select AI is not supported in Database Actions or APEX Service, I will be using the Oracle Instant Client on Mac to connect to ADB. Create ADB_USER -

Grant execution privileges for the DBMS_CLOUD_AI package required to use the SELECT AI function to ADB_USER.

Allow ADB_USER HTTP access to Cohere's API host api.cohere.ai

1. Prepare for using OpenAI with SELECT AIConnect to Autonomous Database as ADB_USER from SQL*Plus and create a credential that is used to sign LLM API requests. DBMS_CLOUD.CREATE_CREDENTIAL procedure is used to create a credential cohere_cred. For password, set the value of the API key obtained from Cohere.

Create a profile that describes your LLM provider and the metadata (schemas, tables, views, etc.) that can be used for natural language queries. Use DBMS_CLOUD_AI.CREATE_PROFILE to create a profile by passing the following -

• profile_name : A name for the AI profile
• For credential_name, specify the credential to connect to the Cohere API created earlier.
• Specify the owner and object name of the schema object used in this profile in the object_list of attributes. In this example, the four tables customers, sales, produces, and countries in schema SH provided by default in Autonomous Database are registered as objects to be used in this profile.

1. Execute natural language queries using the SELECT AI

Set an AI profile using the DBMS_CLOUD_AI.SET_PROFILE procedure to be used in the current session.

Let's use SELECT AI to perform a natural language query.

Check the SQL generated by SELECT AI SHOWSQL.

Documentation : Using Select AI with Autonomous Database

Live Lab : Chat with Your Data in Autonomous Database Using Generative AI

Blog Posts : Autonomous Database speaks “human”

Introducing Select AI - Natural Language to SQL Generation on Autonomous Database