Manage AI Profiles

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

See Also: Use Select AI for Natural Language Interaction with your Database

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.

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.

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: