Manage AI Profiles

You can create and manage your AI profiles through DBMS_CLOUD_AI package.

See Also:

Use DBMS_CLOUD_AI to Configure AI Profiles

Autonomous AI Database uses AI profiles to facilitate and configure access to an LLM and to setup for generating, running, and explaining SQL based on natural language prompts. It also facilitates retrieval augmented generation using embedding models and vector indexes and allows for chatting with the LLM.

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.

Perform Prerequisites for Select AI

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

The following are required to use DBMS_CLOUD_AI:

Grant Privileges for Select AI

To use Select AI, the administrator must grant the EXECUTE privilege on the DBMS_CLOUD_AI package. Learn about additional privileges required for Select AI and its features.

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 the system administrator has EXECUTE privilege. The administrator can grant EXECUTE privilege to other users.

  2. Grant EXECUTE privilege on DBMS_CLOUD_PIPELINE to the user who wants to use Select AI with RAG.

    Note: If the user already has the DWROLE role, this privilege is included and additional grant is not required.

  3. Grant network ACL access to the user who wants to use Select AI and for the AI provider endpoint.

    The system administrator can grant network ACL access. See APPEND_HOST_ACE Procedure for more information.

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

    See CREATE_CREDENTIAL Procedure for more information.

  5. Grant quotas in tablespace to manage the amount of space in a specific tablespace to the user who wants to use Select AI with RAG.

Examples of Privileges to Run Select AI

Review examples of privileges required to use Select AI and its features.

The following example grants the EXECUTE privilege to ADB_USER:

GRANT execute on DBMS_CLOUD_AI to ADB_USER;

The following example grants EXECUTE privilege for the DBMS_CLOUD_PIPELINE package required for RAG:

GRANT EXECUTE on DBMS_CLOUD_PIPELINE to ADB_USER;

To check the privileges granted to a user for the DBMS_CLOUD_AI and DBMS_CLOUD_PIPELINE packages, an administrator can run the following:

SELECT table_name AS package_name, privilege
 FROM DBA_TAB_PRIVS
 WHERE grantee = '<username>'
 AND   (table_name = 'DBMS_CLOUD_PIPELINE'
        OR table_name = 'DBMS_CLOUD_AI');

The following example grants ADB_USER the privilege to use the api.openai.com endpoint.

Note: This procedure is not applicable to OCI Generative AI.

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

The parameters are:

The following example creates a credential to enable access to OpenAI.

EXEC
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name   => 'OPENAI_CRED',
username          =>  'OPENAI',
password          =>  '<your_api_token>');

The parameters are:

The following example grants quotas on tablespace to the ADB_USER to use Select AI with RAG:

ALTER USER ADB_USER QUOTA 1T ON <tablespace_name>;

To the check the tablespace quota granted to a user, run the following:

SELECT TABLESPACE_NAME, BYTES, MAX_BYTES
FROM DBA_TS_QUOTAS
WHERE USERNAME = '<username>' AND
      TABLESPACE_NAME LIKE 'DATA%';

The parameters are:

Configure Select AI to Use Supported AI Providers

Explore how to enable your AI providers to use with Select AI.

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

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

Note: Imported model is not supported with Bedrock Converse API.

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

Use Azure OpenAI Service

To enable Azure OpenAI Service to generate SQL and text responses to your 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:

    • Make note of the resource name and deployment name as those parameters are used to provide network access permission and to create your Azure OpenAI Service profile using the DBMS_CLOUD_AI.CREATE_PROFILE procedure.

    • To know about rate limits for token per minute on a model, see Azure OpenAI Service quotas and limits.

  3. Allow access to Azure OpenAI Service:

Use Cohere

To enable Cohere to generate SQL and text responses to your natural language prompts, obtain API keys from your Cohere paid account.

  1. Login to Cohere's website with your credentials. Cohere Dashboard appears by default.

  2. Alternately,click Dashboard.

  3. Click API Keys on the left navigation. Copy the default API key or create another key. See API-Keys for more information.

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

Set Up Google Cloud Authentication for Gemini Enterprise Agent Platform

Gemini Enterprise Agent Platform uses Google Cloud OAuth 2.0 authentication. Before you configure a Select AI profile for the Google Gemini Enterprise Agent Platform, set up a Google Cloud project and obtain the OAuth 2.0 credentials that the profile requires.

For information about Google’s current product terminology, see Gemini Enterprise Agent Platform name changes.

Set Up the Google Cloud Project

  1. Sign in to the Google Cloud console.

  2. Create a Google Cloud project, or select an existing project.

    For information about setting up a project and development environment, see Set up a project and a development environment.

    For general information about creating and managing Google Cloud projects, see Create and manage projects.

  3. Record the Project ID.

    Google Cloud assigns a unique Project ID to each project. Specify this value for the vertexai_project_id attribute when you create the Select AI profile. See Profile Attributes for more information.

  4. Enable the Gemini Enterprise Agent Platform API for the project and be sure that your Google account has the required permissions.

    See Set up a project and a development environment.

  5. Select a Google Cloud region that supports the endpoint and models that you want to use.

    Note: Select AI supports published Google Gemini models that use the generateContent API for content generation and published Google Gemini embedding models that use the embedContent API for embedding generation. Select AI does not support other models available through Gemini Enterprise Agent Platform that use different inference APIs, including Models as a Service (MaaS), partner models, and open models. For a list of models supported for Gemini Enterprise Agent Platform, see Model Garden on Gemini Enterprise Agent Platform.

    Record the region identifier, such as us-central1. Specify this value for the region attribute when you create the Select AI profile. See Profile Attributes for more information.

    For information about supported locations, see Gemini Enterprise Agent Platform locations.

Obtain OAuth 2.0 Credentials Using Google Cloud Shell

Use Google Cloud Shell to generate Application Default Credentials (ADC) and obtain the OAuth 2.0 credential values that Select AI requires.

Note: Cloud Shell authenticates the active Google Cloud session automatically. In this procedure, you run gcloud auth application-default login specifically to create an ADC credential file that contains the OAuth 2.0 values required for the database credential.

  1. Open Cloud Shell for your Google Cloud project.

    Cloud Shell includes the Google Cloud CLI and automatically uses the active Google Cloud project.

    For information about Cloud Shell authorization, see Authorize with Cloud Shell.

  2. In Cloud Shell, run the following command:

    gcloud auth application-default login

  3. Follow the authentication instructions.

    Sign in with the Google account that has access to the Google Cloud project, and authorize the requested access.

  4. After you complete authentication, Google creates an Application Default Credentials JSON file.

    Google stores the file in the following default location:

    Linux and macOS

    $HOME/.config/gcloud/application_default_credentials.json

    Windows

    %APPDATA%\gcloud\application_default_credentials.json

  5. Open the ADC file and obtain the following OAuth 2.0 values:

    • refresh_token
    • client_id
    • client_secret

    Use these values when you create the database credential for the Select AI profile. See Grant Privileges for Select AI for more information.

    For more information about the command and ADC, see:

Note: Keep the ADC file and OAuth 2.0 credential values confidential. Do not share the credentials, commit them to source control, or include them in logs or documentation.

Google Cloud Session Control

A Google Cloud organization administrator can configure a session-control policy that requires users to reauthenticate periodically. If the organization applies this policy to the Google account associated with the OAuth 2.0 credential, the refresh token can stop working when the configured session expires.

Google can then return an invalid_grant error with an invalid_rapt error subtype.

To reauthenticate, run the following command again:

gcloud auth application-default login

After Google generates the updated credentials, use DBMS_CLOUD.UPDATE_CREDENTIAL to update the stored refresh_token. See UPDATE_CREDENTIAL Procedure for more information.

The Google Cloud organization’s session-control policy determines this behavior. Normal OAuth refresh-token expiration does not cause this restriction.

For more information, see Dealing with session control policies for Google Cloud Platform organizations.

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

Use OCI Generative AI

To enable OCI Generative AI to generate SQL and text responses to your natural language prompts, generate an API signing key.

Use the Console or command line to generate an API signing key for any Oracle AI Database instance. See How to Generate the API Signing Key.

Use OpenAI

To enable OpenAI to generate SQL and text responses to your natural language prompts, obtain API keys from your OpenAI paid account.

You can find your secret API key from your profile dashboard under API keys.

Use OpenAI-Compatible Providers

To enable providers that are compatible with OpenAI, obtain your API key.

OpenAI-compatible providers use bearer authentication. Obtain your API key and use it to create credentials through DBMS_CLOUD.CREATE_CREDENTIAL. For example, Fireworks AI is an OpenAI-compatible provider.

Follow the process to obtain your Fireworks AI API key, model base path URL, and model name:

  1. Sign up for a Fireworks AI account if you don't have one already.

  2. Create an API key from Fireworks AI console.

  3. Copy the generated API key and save it.

  4. Obtain the provider endpoint that you require in DBMS_CLOUD_AI.CREATE_PROFILE procedure:

    1. Select one of the available models by clicking on the available models.

    2. Scroll to the API Example and obtain the OpenAI-compatible base URL path.

      For Llama 3.2 3B Instruct model, the base path URL is: https://api.fireworks.ai/inference/v1/chat/completions. For provider_endpoint parameter, exclude /v1/chat/completions. The provider_endpoint for the mentioned model is https://api.fireworks.ai/inference.

    Note: Some providers may require a prefix for the model name, refer to the AI provider documentation for the correct format.

  5. From the same API Example, copy the model name. You require the model name in DBMS_CLOUD_AI.CREATE_PROFILE procedure. For example, accounts/fireworks/models/llama-v3p2-3b-instruct.

To use Firework AI as your OpenAI-compatible provider, see Example: Select AI with OpenAI-Compatible Providers.

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. Run 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 stateful database session (connection) before you use SELECT AI. If you are using a stateless connection, you must use the DBMS_CLOUD_AI.GENERATE function which enables you to specify the profile name in each invocation.

When you use the public endpoint for a supported AI provider, you do not need to specify provider_endpoint; Select AI uses the appropriate public endpoint. Specify provider_endpoint when you use a private endpoint or an endpoint for another provider that implements an API compatible with a provider that Select AI supports.

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
--BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
  profile_name   => 'OPENAI',
  attributes     =>'{"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
--
EXEC DBMS_CLOUD_AI.set_profile('OPENAI');

PL/SQL procedure successfully completed.

Note:

You can also use pre-built template notebooks in Oracle Machine Learning UI (OML) to create and configure AI profiles. In Oracle Machine Learning UI, navigate to Templates and then go to Examples. Filter notebooks using the keyword SelectAI to access setup examples using SQL and Python APIs.

Refer to the SelectAI4Py -0- Prerequisites and Setup and SelectAI4SQL -0- Prerequisites and Setup template notebooks for more information on: