DBMS_CLOUD_AI Package

The DBMS_CLOUD_AI package facilitates and configures the translation of natural language prompts to SQL statements.

Prerequisites

Oracle Public Cloud
Exadata Cloud@Customer

An outbound connectivity must have been configured using a NAT gateway, by your fleet administrator as described below:

Create a NAT gateway in the Virtual Cloud Network (VCN) where your Autonomous Database resources reside by following the instructions in Create a NAT Gateway in Oracle Cloud Infrastructure Documentation.
After creating the NAT gateway, add a route rule and an egress security rule to each subnet (in the VCN) where Autonomous Database resources reside so that these resources can use the gateway to obtain a public key from your Azure AD instance:
1. Go to the Subnet Details page for the subnet.
2. In the Subnet Information tab, click the name of the subnet's Route Table to display its Route Table Details page.
3. In the table of existing Route Rules, check whether there is already a rule with the following characteristics:
  - Destination: 0.0.0.0/0
  - Target Type: NAT Gateway
  - Target: The name of the NAT gateway you just created in the VCN
  If such a rule does not exist, click Add Route Rules and add a route rule with these characteristics.
4. Return to the Subnet Details page for the subnet.
5. In the subnet's Security Lists table, click the name of the subnet's security list to display its Security List Details page.
6. In the side menu, under Resources, click Egress Rules.
7. In the table of existing Egress Rules, check whether there is already a rule with the following characteristics:
  - Destination Type: CIDR
  - Destination: 0.0.0.0/0
  - IP Protocol: TCP
  - Source Port Range: 443
  - Destination Port Range: All
  If such a rule does not exist, click Add Egress Rules and add an egress rule with these characteristics.

The HTTP Proxy settings in your environment must allow the database to access the cloud service provider.

These settings are defined by your fleet administrator while creating the Exadata Cloud@Customer infrastructure as described in Using the Console to Provision Exadata Database Service on Cloud@Customer .

Note:

The network configuration including the HTTP Proxy can only be edited until the Exadata Infrastructure is in Requires Activation state. Once it is activated, you cannot edit those settings.

Setting up an HTTP Proxy for an already provisioned Exadata Infrastructure needs a Service Request (SR) in My Oracle Support. See Create a Service Request in My Oracle Support for details.

Related Topics

Use Select AI to Generate SQL from Natural Language Prompts

Summary of DBMS_CLOUD_AI Subprograms

This section covers the DBMS_CLOUD_AI subprograms provided with Autonomous Database.

Note:

You can use the DBMS_CLOUD_AI package only with Autonomous Database versions 19.22 or later.

Subprogram	Description
CREATE_PROFILE Procedure	This procedure new AI profile for translating natural language prompts to SQL statements.
DISABLE_PROFILE Procedure	This procedure disables an AI profile in the current database.
DROP_PROFILE Procedure	This procedure drops an existing AI profile.
ENABLE_PROFILE Procedure	This procedure enables an AI profile to use in the current database.
GENERATE Function	This function generates a SQL statement using AI to translate.
SET_ATTRIBUTE Procedure	This procedure sets AI profile attributes.
SET_PROFILE Procedure	This procedure sets AI profile for the current database.

CREATE_PROFILE Procedure

The procedure creates a new AI profile for translating natural language prompts to SQL statement.

Syntax

DBMS_CLOUD_AI.CREATE_PROFILE
   profile_name        IN  VARCHAR2,
   attributes          IN  CLOB      DEFAULT NULL,
   status              IN  VARCHAR2  DEFAULT NULL,
   description         IN  CLOB      DEFAULT NULL
);

Parameters

Parameter	Description
`profile_name`	A name for the AI profile. The profile name must follow the naming rules of Oracle SQL identifier. Maximum length of profile name is 125 characters. This is a mandatory parameter.
`attributes`	Profile attributes in JSON format. See AI Profile Attributes for more details. The default value is NULL.
`status`	Status of the profile. The default value is enable.
`description`	Description for the AI profile. The default value is NULL.

Example

BEGIN
     DBMS_CLOUD_AI.CREATE_PROFILE(
          profile_name    => 'OpenAI,
          attributes      => JSON_OBJECT('provider' value 'openai',
                                         'credential_name' value 'openai_cred'),
          description     => 'AI profile to use OpenAI for SQL translation'
     );
END;
/

DROP_PROFILE Procedure

The procedure drops an existing AI profile. If the profile does not exist, then the procedure throws an error.

Syntax

DBMS_CLOUD_AI.DROP_PROFILE(
       profile_name        IN   VARCHAR2,
       force               IN   BOOLEAN DEFAULT FALSE
 );

Parameters

Parameter Description

Parameter	Description
`profile_name`	Name of the AI profile
`force`	If `TRUE`, then the procedure ignores errors if AI profile does not exist. The default value for this parameter is `FALSE`.

profile_name

Name of the AI profile

force

If TRUE, then the procedure ignores errors if AI profile does not exist.

The default value for this parameter is FALSE.

Example

BEGIN
     DBMS_CLOUD_AI.DROP_PROFILE(profile_name => 'OPENAI');
END;
/

Usage Notes

Use force to drop a profile and ignore errors if AI profile does not exist.

ENABLE_PROFILE Procedure

This procedure enables the AI profile that the user specifies. The procedure changes the status of the AI profile to ENABLED.

Syntax

DBMS_CLOUD_AI.ENABLE_PROFILE(
     profile_name         IN   VARCHAR2
 );

Parameters

Parameter Description

Parameter	Description
`profile_name`	Name for the AI profile to enable This parameter is mandatory.

profile_name

Name for the AI profile to enable

This parameter is mandatory.

Example to Enable AI Profile

BEGIN
     DBMS_CLOUD_AI.ENABLE_PROFILE(
         profile_name    => 'OPENAI'
     );
END;
/

DISABLE_PROFILE Procedure

This procedure disables the AI profile in the current database. The status of the AI profile is changed to DISABLED by this procedure.

Syntax

DBMS_CLOUD_AI.DISABLE_PROFILE(
      profile_name  IN  VARCHAR2
);

Parameters

Parameter Description

Parameter	Description
`profile_name`	Name for the AI profile. This parameter is mandatory.

profile_name

Name for the AI profile.

This parameter is mandatory.

Example

BEGIN
     DBMS_CLOUD_AI.DISABLE_PROFILE(
         profile_name    => 'OPENAI'
     );
END;
/

SET_ATTRIBUTE Procedure

This procedure enables you to set AI profile attributes.

Syntax

DBMS_CLOUD_AI.SET_ATTRIBUTE(
      profile_name         IN   VARCHAR2,
      attribute_name       IN   VARCHAR2,
      attribute_value      IN   CLOB
);

Parameters

Only the owner can set or modify the attributes of the AI profile. For a list of supported attributes, see Profile Attributes.

Parameter Description

Parameter	Description
`profile_name`	Name of the AI profile for which you want to set the attributes. This parameter is mandatory.
`attribute_name`	Name of the AI profile attribute This parameter is mandatory.
`attribute_value`	Value of the profile attribute. The default value is NULL.

profile_name

Name of the AI profile for which you want to set the attributes.

This parameter is mandatory.

attribute_name

Name of the AI profile attribute

This parameter is mandatory.

attribute_value

Value of the profile attribute.

The default value is NULL.

Example

BEGIN
 DBMS_CLOUD_AI.SET_ATTRIBUTE(
   profile_name    => 'OPENAI',
   attribute_name  => 'credential_name',
   attribute_value => 'OPENAI_CRED_NEW'
 );
END;
/

SET_PROFILE Procedure

This procedure sets AI profile for current session.

After setting an AI profile for the database session, any SQL statement with the prefix SELECT AI is considered a natural language prompt. Depending on the action specified with the AI prefix, a response is generated using AI. To use the AI prefix, see Use AI Keyword to Enter Prompts. Optionally, it is possible to override the profile attributes or modify attributes by specifying them in JSON format. See SET_ATTRIBUTE Procedure for setting the attributes.

The AI profile can only be set for current session if the owner of the AI profile is the session user.

To set an AI profile for all sessions of a specific database user or all user sessions in the database, consider using a database event trigger for AFTER LOGON event on the specific user or the entire database. See CREATE TRIGGER Statement for more details.

Syntax

DBMS_CLOUD_AI.SET_PROFILE(
    profile_name      IN  VARCHAR2,
);

Parameters

Parameter Description

Parameter	Description
`profile_name`	A name for the AI profile in the current session. This parameter is mandatory.

profile_name

A name for the AI profile in the current session.

This parameter is mandatory.

Example


   BEGIN
        DBMS_CLOUD_AI.SET_PROFILE(
          profile_name    => 'OPENAI',
        );
   END;
   /

GENERATE Function

This function provides AI translation in a stateless manner. With your existing AI profile, you can use this function to perform the supported actions such as showsql, narrate, or chat. The default action is showsql.

Overriding some or all of the profile attributes is also possible using this function.

Syntax

DBMS_CLOUD_AI.GENERATE(
    prompt          IN  CLOB,
    profile_name    IN  VARCHAR2 DEFAULT NULL,
    action          IN  VARCHAR2 DEFAULT NULL,
    attributes      IN  CLOB     DEFAULT NULL
) RETURN CLOB;

Parameters

Parameter	Description
`prompt`	Natural language prompt to translate using AI. The prompt can include `SELECT AI <action>` as the prefix. The action can also be supplied separately as an "action" parameter. The action supplied in prompt overrides the "action" parameter. Default action is `showsql`. This parameter is mandatory.
`profile_name`	Name of the AI profile. This parameter is optional if an AI profile is already set in the session using `DBMS_CLOUD_AI.SET_PROFILE`. The default value is NULL. The following conditions apply: If a profile is set in the current session, the user may omit `profile_name` argument in the `DBMS_CLOUD_AI.GENERATE` function. If the `profile_name` argument is supplied in the `DBMS_CLOUD_AI.GENERATE` function, it overrides any value set in the session using the `DBMS_CLOUD_AI.SET_PROFILE` procedure. If there is no profile set in the session using the `DBMS_CLOUD_AI.SET_PROFILE` procedure, the `profile_name` argument must be supplied in the `DBMS_CLOUD_AI.GENERATE` function. Note: For Database Actions, you can either specify `profile_name` argument in `DBMS_CLOUD_AI.GENERATE` or you can run two steps as a PL/SQL script: `DBMS_CLOUD_AI.SET_PROFILE` and `DBMS_CLOUD_AI.GENERATE`. EXEC DBMS_CLOUD_AI.set_profile('OPENAI'); ----------------------------------------------- SELECT DBMS_CLOUD_AI.GENERATE(prompt => 'how many customers', profile_name => 'OPENAI', action => 'showsql') FROM dual; ------------------------------------------------ SELECT DBMS_CLOUD_AI.GENERATE(prompt => 'how many customers', profile_name => 'OPENAI', action => 'narrate') FROM dual; ------------------------------------------- SELECT DBMS_CLOUD_AI.GENERATE(prompt => 'what is oracle autonomous database', profile_name => 'OPENAI', action => 'chat') FROM dual; See Executing SQL Statements in the Code Editor for more information.
`action`	Action for translating natural prompt using AI. The supported actions include `showsql` (default), `narrate`, and `chat`. Descriptions of actions are included in Use AI Keywords to Enter Prompts. Note: This function does not support the `runsql` action. If you supply the `runsql` action, it returns the following error: `ORA-20000: runsql action is not supported by generate function ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line xxxx ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD_AI", line 2696 ORA-06512: at line x`
`attributes`	Override specific AI profile attributes by supplying attributes in JSON format. See Profile Attributes for more details.

Examples

The following examples illustrate showsql, narrate, and chat actions that can be used with the DBMS_CLOUD_AI.GENERATE function.

An example with showsql action is as follows:

SELECT DBMS_CLOUD_AI.GENERATE(prompt       => 'how many customers',
                              profile_name => 'OPENAI',
                              action       => 'showsql')
FROM dual;

An example with narrate action is as follows:

SELECT DBMS_CLOUD_AI.GENERATE(prompt       => 'how many customers',
                              profile_name => 'OPENAI',
                              action       => 'narrate')
FROM dual;

An example with chat action is as follows:

SELECT DBMS_CLOUD_AI.GENERATE(prompt       => 'what is oracle autonomous database',
                              profile_name => 'OPENAI',
                              action       => 'chat')
FROM dual;

Profile Attributes

Attributes of an AI profile help to manage and configure the behavior of the AI profile. Some attributes are optional and have a default value.

Attributes

Attribute Name	Description
`azure_deployment_name`	Name of the Azure OpenAI Service deployed model. The name can only include alphanumeric characters, underscore character (_) and a hyphen (-) character. The name cannot end with an underscore (_) or a hyphen (-). To know how to get the `azure_deployment_name`, see Create and deploy an Azure OpenAI Service resource.
`azure_resource_name`	Name of the Azure OpenAI Service resource. The resource name can only include alphanumeric characters and hyphens, and can't start or end with a hyphen. To know how to get the `azure_resource_name`, see Create and deploy an Azure OpenAI Service resource.
`comments`	Include column comments in the metadata used for translating natural language prompts using AI. `BOOLEAN` datatype is supported. The valid values are `TRUE` or `FALSE` for a string with `VARCHAR2` datatype. Note: Boolean values are not applicable in the `DBMS_CLOUD_AI.SET_ATTRIBUTE` procedure when setting a single attribute because `attribute_value` parameter is of `CLOB` datatype.
`credential_name`	The name of the credential to access the AI provider APIs. Credential using bearer tokens can be created by using the provider name as the user name and bearer token as the password. This is a mandatory attribute. See CREATE_CREDENTIAL Procedure.
`max_tokens`	Denotes the number of tokens to predict per generation. Default is 1024. See BPE Tokens for more details.
`model`	The name of the AI model being used to generate translation. Supported models: For OpenAI: gpt-4, gpt-4-0613, gpt-4-32k, gpt-4-32k-0613, gpt-3.5-turbo (default), gpt-3.5-turbo-0613, gpt-3.5-turbo-16k, gpt-3.5-turbo-16k-0613 For Cohere: command (default), command-nightly (experimental), command-light, and command-light-nightly (experimental). Smaller, "light" models are faster, while larger models will perform better. Custom models can also be supplied with their full ID. Note: This parameter is not used for Azure as the model is determined when you create your deployment in the Azure OpenAI Service portal.
`object_list`	Array of JSON objects specifying the owner and object names that are eligible for natural language translation to SQL. To include all objects of a given user, omit the "name" and only specify the "owner" key in the JSON object. For translation natural language to SQL, the object name, object owner, object columns and comments are sent to the AI provider using HTTPS requests. Avoid specifying objects with sensitive object name, column names or comments in the object list. AI providers may have limit on the size of metadata allowed in translation requests. Consider limiting the list of objects suitable for the natural language prompts by your application users. Format: `[ {"owner": "SH", "name": "SALES", {"owner": "TEST_USER"} ]`
`provider`	AI provider for the AI profile. Supported providers: openai cohere azure This is a mandatory attribute.
`stop_tokens`	The generated text will be terminated at the beginning of the earliest stop sequence. Sequence will be incorporated into the text. The attribute value must be a valid array of string values in JSON format. `stop_tokens` takes a JSON array as input. To learn more about stop tokens or stop sequences, see OpenAI or Cohere documentation.
`temperature`	Sampling from generation models incorporates randomness, so that the same prompt may yield different outputs each time you hit "generate". Temperature is a non-negative float number used to tune the degree of randomness. Lower temperatures mean less random generations. See Temperature for more details. This parameter is applicable to all the supported service providers.

The following example is using Cohere as the provider and displays custom profile attributes:

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
     profile_name => 'COHERE',
     attributes =>
      '{"provider": "cohere",
        "credential_name": "COHERE_CRED",
        "object_list": [{"owner": "ADMIN"}],
        "max_tokens":512,
        "stop_tokens": [";"],
        "model": "command-nightly",
        "temperature": 0.5,
        "comments": true
       }');
END;
/

Title and Copyright Information

DBMS_CLOUD_AI Package

Oracle and/or its affiliates.

Documentation Accessibility

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.