DBMS_CLOUD_AI Package

The DBMS_CLOUD_AI package, with Select AI, facilitates and configures the translation of natural language prompts to generate, run, explain SQL statements. Also, enables retrieval augmented generation and natural language-based interactions, including chatting with LLMs.

Prerequisites

• Oracle Public Cloud or Multicloud
• 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 AI 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 AI 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.

Summary of DBMS_CLOUD_AI Subprograms

This section covers the DBMS_CLOUD_AI subprograms provided with Autonomous AI Database.

Subprogram	Description
CREATE_PROFILE Procedure	This procedure creates a 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
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
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
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
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 in Oracle Database 19c Database PL/SQL Language Reference or Oracle Database 26ai Database PL/SQL Language Reference for more details.

Syntax

DBMS_CLOUD_AI.SET_PROFILE(
    profile_name      IN  VARCHAR2,
);

Parameters

Parameter	Description
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;

GENERATE_SYNTHETIC_DATA Function

Use this procedure to generate synthetic data for a single table, multiple tables or a full schema.

The following is the syntax to generate synthetic data for a single table.

Syntax

DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
  profile_name        IN  VARCHAR2,
  object_name         IN  DBMS_ID,
  owner_name          IN  DBMS_ID,
  record_count        IN  NUMBER,
  user_prompt         IN  CLOB DEFAULT NULL,
  params              IN  CLOB DEFAULT NULL
);

The following is the syntax to generate synthetic data for multiple tables.

DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
  profile_name        IN  VARCHAR2,
  object_list         IN  CLOB,
  params              IN  CLOB DEFAULT NULL
);

If you do not want table data or vector search documents to be sent to an LLM, a user with administrator privileges can disable such access for all users of the given database. This, in effect, disables the narrate action.

Parameters

Parameter	Mandatory	Description
profile_name	Yes	The AI profile containing necessary LLM service information. This can be created by CREATE_PROFILE Procedure.
object_name	Yes	Specify a table name to populate synthetic data. SELECT and INSERT privilege on the table objects are needed for the user using it. The table is either empty or have records in it.
owner_name	No	Specify the database user who owns the referenced object. If no specific owner is provided, the procedure defaults to using the schema of the user running it.
record_count	No	The number of records to be synthetically generated.
user_prompt	No	Additional information that a user can mention to generate synthetic data. For example, to generate a record for a table called MOVIE with a release_date column, the user_prompt can be: the release date for the movies should be in 2019
params	No	Optional attributes provided in JSON object string format to modify the behavior of an API. See Optional Parameters.
object_list	Yes	Use this parameter for generating synthetic data on multiple tables. This parameter takes in table object information along with it's arguments and contains the same arguments provided in the single table. See object_list Parameters.

Optional Parameters

Parameter	Value Datatype	Value	Description
sample_rows	Number	0 <= sample_rows <= 100	Specify the number of rows from the table to use as a sample to guide the LLM in data generation. A value of 0 means no sample rows will be used. The default value is 0.
table_statistics	Boolean	True False	Enable or disable the use of table statistics information. The default value is False.
priority	String	Valid values: HIGH MEDIUM LOW	Assign a priority value that defines the number of parallel requests sent to the LLM for generating synthetic data. Tasks with a higher priority will consume more database resources and complete faster. The default value is HIGH HIGH: Specifies the number of parallel LLM requests based on the database's ECPU count (or OCPU count if your database uses OCPUs). MEDIUM: Sets the number of concurrent processes according to the concurrency limit for Medium service. The default value is 4. LOW: Runs the pipeline job in a serial order, without parallel processing. The maximum number of concurrent parallel processes used for synthetic data generation is limited to 64.
comments	Boolean	True False	Enable or disable sending comments to the LLM to guide data generation. The default value is False.

object_list Parameters

Parameter	Value Datatype	Mandatory	Description
owner	String	Yes	Specifies the database user who owns the object being referenced. If no specific owner is provided, the procedure will default to using the schema of the user running it.
name	String	No	Specify a table name to populate synthetic data. SELECT and INSERT privilege on the table objects are needed for the user using it.The table is either empty or have records in it.
record_count	Number	No	The number of records to be synthetically generated. Provide a number greater than 0. Supply record_count or record_count_percentage.
record_count_percentage	Number	No	The percentage of number of records to be synthetically generated. Provide a number greater than 0. For a Metadata Clone database, where the table metadata including statistics is preserved, the record_count_percentage parameter is supported. Supply record_count or record_count_percentage. When using the record_count_percentage parameter, the final record count in the table is calculated as: Original_Num_Rows * record_count_percentage
user_prompt	String	No	Same as user_prompt in Parameters. The user_prompt is associated with a specific table object.

Examples

The following examples show the DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA function for generating synthetic data for a single table and multiple tables. For a complete example and to view more examples, see .

BEGIN
    DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
        profile_name => 'GENAI',
        object_name  => 'Director',
        owner_name   => 'ADB_USER',
        record_count => 5
    );
END;
/
PL/SQL procedure successfully completed.

BEGIN
    DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
        profile_name => 'GENAI',
        object_list => '[{"owner": "ADB_USER", "name": "Director","record_count":5},
                         {"owner": "ADB_USER", "name": "Movie_Actor","record_count":5},
                         {"owner": "ADB_USER", "name": "Actor","record_count":10},
                         {"owner": "ADB_USER", "name": "Movie","record_count":5,"user_prompt":"all movies are released in 2009"}]'
    );
END;
/
PL/SQL procedure successfully completed.

ENABLE_DATA_ACCESS Procedure

This procedure enables sending data to LLM for applicable Select AI features, which is the default behavior. Only an administrator can run this procedure.

This procedure controls data access for the following Select AI capabilities:

• narrate action
• Retrieval Augmented Generation (RAG)
• Synthetic Data Generation

Syntax

DBMS_CLOUD_AI.ENABLE_DATA_ACCESS();

Parameters

This procedure does not require any parameters.

Example to Enable Data Access

BEGIN
  DBMS_CLOUD_AI.ENABLE_DATA_ACCESS();
END;
/

DISABLE_DATA_ACCESS Procedure

This procedure disables sending data to LLM for applicable Select AI features. Only an administrator can run this procedure.

This procedure limits the following Select AI capabilities:

• narrate action
• Retrieval Augmented Generation (RAG)
• Synthetic Data Generation

Syntax

DBMS_CLOUD_AI.DISABLE_DATA_ACCESS();

Parameters

This procedure does not require any parameters.

Example to Disable Data Access

BEGIN
  DBMS_CLOUD_AI.DISABLE_DATA_ACCESS();
END;
/

CREATE_VECTOR_INDEX Procedure

This procedure creates a vector index in the specified vector database, and populates it with data from an object store using an asynchronous scheduler job.

Syntax

PROCEDURE CREATE_VECTOR_INDEX(                                              
   index_name          IN  VARCHAR2,                                        
   attributes          IN  CLOB      DEFAULT NULL,                          
   status              IN  VARCHAR2  DEFAULT NULL,                          
   description         IN  CLOB      DEFAULT NULL                           
); 

Parameters

Parameter	Description
index_name	Name of the vector index. The vector index name must follow the naming rules of Oracle SQL identifier. Maximum length of vector store name is 125 characters. This is a mandatory parameter.
attributes	Custom attributes for the vector index in JSON. To see a list of configurable parameters, see . The default value is NULL.
status	Status of the vector index. The possible values are: Enabled Disabled The default value is Disabled.
description	Description for the vector index. The default value is NULL.

Example

The following example demonstrates how to create a vector index and configure the attributes as JSON parameters.

BEGIN                                                                
       DBMS_CLOUD_AI.CREATE_VECTOR_INDEX(                                 
            index_name    => 'MY_INDEX'                                   
            attributes    => JSON_OBJECT(                                 
                       'vector_db_provider' value 'oracle',
                       'vector_table_name'  value 'oracle_mycollection',              
                       'profile_name'      value 'OCIGENAI',         
                       'location'          value                          
                         'https://objectstorage.us-phoenix-1.' ||         
                         'oraclecloud.com/n/mynamespace/b/mybucket',      
                       'object_store_credential_name'   value 'OS_CRED',              
                       'chunk_size'        value 2048,                    
                       'chunk_overlap'     value 256,                     
                       'refresh_rate'      value 720)                     
       );                                                                 
END;                                                                 
/

DROP_VECTOR_INDEX Procedure

This procedure removes a vector store index. It normally removes the vector store index object and deletes the vector store. If set to FALSE, the argument include_data ensures the procedure only removes the vector store index object while retaining the vector store.

Syntax

PROCEDURE DROP_VECTOR_INDEX(                                                
   index_name          IN  VARCHAR2,           
   include_data        IN  BOOLEAN DEFAULT TRUE,                            
   force               IN  BOOLEAN DEFAULT FALSE                           
);

Parameters

Parameter	Description
index_name	Name of the vector index. The vector index name must follow the naming rules of Oracle SQL identifier. Maximum length of vector store name is 125 characters. This is a mandatory parameter.
include_data	Indicates whether to delete both the customer's vector store and vector index along with the vector index object. Possible values: TRUE FALSE The default value is TRUE.
force	Indicates whether to ignore errors that occur if the vector index does not exist. Possible values: TRUE FALSE If set to TRUE, this parameter bypasses any errors that occur if the vector index does not exist. The default value is FALSE.

Example

BEGIN
DBMS_CLOUD_AI.DROP_VECTOR_INDEX(
        index_name     => 'MY_INDEX',
        include_data   => FALSE,
        force          => TRUE
     );                                                                 
END;                                                                 
/

DISABLE_VECTOR_INDEX Procedure

This procedure disables a vector index object in the current database. When disabled, an AI profile cannot use the vector index, and the system does not load data into the vector store as new data is added to the object store and does not perform indexing, searching or querying based on the index.

Syntax

DBMS_CLOUD_AI.DISABLE_VECTOR_INDEX(                                              
   index_name       IN  VARCHAR2                                            
);

Parameters

Parameter	Description
index_name	Name of the vector index. The vector index name must follow the naming rules of Oracle SQL identifier. Maximum length of vector store name is 125 characters. This is a mandatory parameter.

Example

You can disable a vector index by providing the name of the vector index.

BEGIN                                                                
   DBMS_CLOUD_AI.DISABLE_VECTOR_INDEX(index_name => 'MY_INDEX');       
END;                                                                 
/

ENABLE_VECTOR_INDEX Procedure

This procedure enables or activates a previously disabled vector index object. Generally, when you create a vector index, by default it is enabled such that the AI profile can use it to perform indexing and searching.

When enabled, a vector index allows an AI profile to use it for loading new data from an object store into a vector store at a user-specified refresh rate. You can specify the refresh_rate parameter through the JSON object list. To configure the JSON attributes, see .

Syntax

DBMS_CLOUD_AI.ENABLE_VECTOR_INDEX(                                              
   index_name       IN  VARCHAR2                                            
);

Parameters

Parameter	Description
index_name	Name of the vector index. The vector index name must follow the naming rules of Oracle SQL identifier. Maximum length of vector store name is 125 characters. This is a mandatory parameter.

Example

You can enable or activate a vector index by specifying the vector index name as follows:

BEGIN                                                                
   DBMS_CLOUD_AI.ENABLE_VECTOR_INDEX(index_name => 'MY_INDEX');       
END;                                                                 
/

UPDATE_VECTOR_INDEX Procedure

This procedure updates an existing vector store index with a specified value of the vector index attribute.

It is overloaded to accept:

• attribute values of various types.
• vector index attributes as a JSON document and updates one or more attributes of an existing vector store index with the specified attribute name and value pair.

Syntax

DBMS_CLOUD_AI.UPDATE_VECTOR_INDEX(
   index_name         IN  VARCHAR2,
   attributes         IN  CLOB
);

DBMS_CLOUD_AI.UPDATE_VECTOR_INDEX(
     index_name         IN  VARCHAR2,
     attribute_name     IN  VARCHAR2,
     attribute_value    IN  VARCHAR2
);

DBMS_CLOUD_AI.UPDATE_VECTOR_INDEX(                                                 
     index_name         IN  VARCHAR2,                                         
     attribute_name     IN  VARCHAR2,                                         
     attribute_value    IN  CLOB     DEFAULT NULL                           
  );

Parameters

Parameter	Description
index_name	Name of the vector index. The vector index name must follow the naming rules of Oracle SQL identifier. Maximum length of vector store name is 125 characters. This is a mandatory parameter.
attributes	Specifies vector index attributes in JSON format. This is a mandatory parameter.
attribute_name	Name of the custom attributes specified as JSON parameters in CREATE_VECTOR_INDEX procedure. You cannot modify the following attributes: location vector_table_name chunk_size chunk_overlap vector_distance_metric vector_dimension This is a mandatory parameter.
attribute_value	User specified value for the custom attribute_name. Value can be CLOB, NUMBER or VARCHAR2 type. The default value is NULL.

Note:

Use either the attributes parameter to specify attribute_name and value pairs in JSON format or the attribute_name and attribute_value parameters together.

Examples

BEGIN                                                                
   DBMS_CLOUD_AI.UPDATE_VECTOR_INDEX(                                 
      index_name       => 'MY_INDEX',                                   
      attribute_name   => 'object_storage_credential_name',                
      attribute_value  => 'NEW_CRED'                           
   );                                                                 
END;                                                                 
/

The following example accepts NUMBER type as the attribute_value.

BEGIN                                                                
   DBMS_CLOUD_AI.UPDATE_VECTOR_INDEX(                                 
      index_name       => 'MY_INDEX',                                   
      attribute_name   => 'match_limit',                
      attribute_value  => 10                           
   );                                                                 
END;                                                                 
/

The following example accepts VARCHAR2 type as the attribute_value.

BEGIN                                                                
   DBMS_CLOUD_AI.UPDATE_VECTOR_INDEX(                                 
      index_name       => 'MY_INDEX',                                   
      attribute_name   => 'profile_name',                
      attribute_value  => 'AI_PROF2'                           
   );                                                                 
END;                                                                 
/

The following example accepts attributes in JSON format.

BEGIN
  DBMS_CLOUD_AI.UPDATE_VECTOR_INDEX(
    index_name => 'MY_VECTOR_INDEX', 
    attributes => '{"match_limit": 10, 
                    "refresh_rate": 30}'
  );
END;
/

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 data type is supported. The valid values are TRUE or FALSE for a string with VARCHAR2 data type. 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.
conversation	A VARCHAR2 attribute that indicates if conversation history is enabled for a profile. Only OpenAI and Azure OpenAI Service support conversation history. Valid values are true or false. The default value is false. The values are not case sensitive.
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.
embedding_model	The embedding model defined in the AI profile. The following are the supported AI providers for the embedding models with their default values: OCI GenAI: cohere.embed-english-v3.0 OpenAI: text-embedding-ada-002 Azure OpenAI: text-embedding-ada-002 Cohere: embed-english-v2.0 Google: text-embedding-004 Note: The embedding_model parameter is not applicable for Anthropic and Hugging Face.
max_tokens	Denotes the number of tokens to predict per generation. Default is 1024. See Tokens and Tokenizers for more details.
model	The name of the AI model being used to generate responses. 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 Cohere: command (default), command-nightly (experimental), command-light, and command-light-nightly (experimental). Smaller, "light" models are faster, while larger models perform better. Custom models can also be supplied with their full ID. OCI Generative AI: For OCI Generative AI: cohere.command (default). Pretrained models for OCI Generative AI are all supported by Select AI. Custom models can also be supplied with their full OCIDs. To know more about supported models in OCI Generative AI, see Pretrained Foundational Models in Generative AI. 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"} ]
oci_compartment_id	Specifies the OCID of the compartment you are permitted to access when calling the OCI Generative AI service. The compartment ID can contain alphanumeric characters, hyphens and dots. The default is the compartment ID of the Autonomous AI Database.
oci_endpoint_id	This attributes indicates the endpoint OCID of the Oracle dedicated AI hosting cluster. The endpoint ID can contain alphanumeric characters, hyphens and dots. To find the endpoint OCID, see Getting an Endpoint's Details in Generative AI. When you want to use the Oracle dedicated AI cluster, you must provide the endpoint OCID of the hosting cluster. By default, the endpoint ID is empty and the model is on-demand on a shared infrastructure.
oci_runtimetype	This attribute indicates the runtime type of the provided model. This attribute is required when the model attribute is specified and is applicable to OCI Generation Models. All permitted values can be found in OCI Generative AI runtimeType. See LlmInferenceRequest Reference. The supported values are: COHERE LLAMA
provider	AI provider for the AI profile. Supported providers: openai cohere azure oci google anthropic huggingface This is a mandatory attribute.
region	This attribute indicates the location of the Generative AI cluster that you want to use. The region can contain alphanumeric characters and hyphen characters. Note: The Oracle Generative AI cluster is available in Chicago, Frankfurt, and London regions. See Pretrained Foundational Models in Generative AI. The default region is us-chicago-1.
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 Generate Text 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": "ADB_USER"}],
        "max_tokens":512,
        "stop_tokens": [";"],
        "model": "command-nightly",
        "temperature": 0.5,
        "comments": true
       }');
END;
/

The following example shows custom profile attributes using OCI Generative AI:

BEGIN                                                                        
  DBMS_CLOUD_AI.CREATE_PROFILE(                                              
      profile_name => 'GENAI',                                                             
      attributes => '{"provider": "oci",                                                                   
        "credential_name": "GENAI_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"}],
        "oci_compartment_id": "ocid1.compartment.oc1...",
	"oci_endpoint_id": "ocid1.generativeaiendpoint.oc1.us-chicago-1....",
	"region": "us-chicago-1",
	"model": "cohere.command-light",
	"oci_runtimetype": "COHERE"            
       }');                                                                  
END;                                                                         
/ 

---

Title and Copyright Information

Copyright ©2024,2024,

Oracle and/or its affiliates.