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
As a developer, you can use DBMS_CLOUD procedures with Autonomous AI Databases deployed on Oracle Public Cloud, Multicloud or Exadata Cloud@Customer.
Depending on the deployment choice, the following prerequisites must be met to use the DBMS_CLOUD procedures with Amazon S3, Azure Blob Storage, and Google Cloud Storage service providers.
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:
-
Go to the Subnet Details page for the subnet.
-
In the Subnet Information tab, click the name of the subnet's Route Table to display its Route Table Details page.
-
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.
-
-
Return to the Subnet Details page for the subnet.
-
In the subnet's Security Lists table, click the name of the subnet's security list to display its Security List Details page.
-
In the side menu, under Resources, click Egress Rules.
-
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. |
| Profile Attributes | Provides AI profile attributes that you can configure. |
| 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. |
| DISABLE_PROFILE Procedure | This procedure disables an AI profile in the current database. |
| SET_ATTRIBUTE Procedure | This procedure sets AI profile attributes. |
| SET_PROFILE Procedure | This procedure sets AI profile for the current database. |
| GENERATE Function | This function generates a SQL statement using AI to translate. |
| GENERATE_SYNTHETIC_DATA Function | This function generates synthetic data. |
| ENABLE_DATA_ACCESS Procedure | Use this procedure to enable sending data to your LLM. |
| DISABLE_DATA_ACCESS Procedure | Use this procedure to disable sending data to your LLM. |
| 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. |
| DROP_VECTOR_INDEX Procedure | This procedure removes a vector store index. It normally removes the vector store index object and deletes the vector database. |
| 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. |
| ENABLE_VECTOR_INDEX Procedure | This procedure enables or activates a previously disabled vector index object. |
| UPDATE_VECTOR_INDEX Procedure | This procedure updates an existing vector store index with a specified value of the vector index attribute. |
| Vector Index Attributes | Provides vector index profile attributes that you can configure. |
| CREATE_CONVERSATION Procedure | This procedure helps you to create a conversation. |
| CREATE_CONVERSATION Function | This function helps you to create a conversation and use the conversation ID in other procedures. |
| CREATE_CONVERSATION Attributes | Use the conversation attributes to customize your conversations. |
| UPDATE_CONVERSATION Procedure | This procedure updates an existing procedure with user-specified parameters. |
| SET_CONVERSATION_ID Procedure | This procedure sets conversation support in the current session. |
| GET_CONVERSATION_ID Function | This procedure helps you to get the conversation_id parameter. |
| CLEAR_CONVERSATION_ID Procedure | This procedure helps you to clear any conversation_id set in the current session. |
| DELETE_CONVERSATION_PROMPT Procedure | This procedure deletes a particular prompt. |
| DROP_CONVERSATION Procedure | This procedure deletes an entire conversation and its metadata. |
| FEEDBACK Procedure | Use this procedure to potentially improve query generation accuracy by providing a feedback to Select AI. |
| Vector Index for FEEDBACK | This is a default vector index created when you first use feedback. |
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;
/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.
| 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. Note: Boolean values are not applicable in the |
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:
Note: The |
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:
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: |
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 All permitted values can be found in OCI Generative AI runtimeType. See LlmInferenceRequest Reference. The supported values are:
|
provider |
AI provider for the AI profile. Supported providers:
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](https://docs.oracle.com/en-us/iaas/Content/generative-ai/pretrained-models.htm#pretrained-models). The default region is `us-chicago-1`.The default region for AWS is us-east-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;
/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 The default value for this parameter is |
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 when using a stateless database connection. With your existing AI profile, you can use this function to perform the supported actions such as showsql, runsql, explainsql, narrate, summarize, and 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,
params IN CLOB
) RETURN CLOB;Parameters
| Parameter | Description |
|---|---|
prompt |
Natural language prompt to translate using AI. The prompt can include 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 The default value is NULL. The following conditions apply:
Note: For Database Actions, you can either specify See Executing SQL Statements in the Code Editor for more information. |
action |
Action for translating natural prompt using AI. The supported actions include:
Descriptions of actions are included in Use AI Keywords to Enter Prompts. |
attributes |
Override specific AI profile attributes by supplying attributes in JSON format. See Profile Attributes for more details. |
params |
Specify conversation parameter. See CREATE_CONVERSATION Attributes. You can specify only the following parameter:
|
Examples
Example: Using the GENERATE Function for Select AI Actions
The following examples illustrate runsql, showsql, explainsql, narrate, summarize, translate, and chat actions that can be used with the DBMS_CLOUD_AI.GENERATE function.
See also Use AI Keywords to Enter Prompts for more details.
An example with runsql action is as follows:
SELECT DBMS_CLOUD_AI.GENERATE(prompt => 'how many customers',
profile_name => 'OPENAI',
action => 'runsql')
FROM dual;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 explainsql action is as follows:
SELECT DBMS_CLOUD_AI.GENERATE(prompt => 'how many customers',
profile_name => 'OPENAI',
action => 'explainsql')
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;An example with summarize action is as follows:
SELECT DBMS_CLOUD_AI.GENERATE(
prompt => TO_CLOB(
DBMS_CLOUD.GET_OBJECT(
credential_name => 'STORE_CRED',
object_uri => 'https://objectstorage.ca-toronto-1.oraclecloud.com' ||
'/n/namespace-string/b/bucketname/o/data_folder/' ||
'summary/test_4000_words.txt')),
profile_name => 'GENAI_LLAMA',
action => 'SUMMARIZE')
from DUAL;Result:
The music streaming industry, led by Spotify, has revolutionized the way people consume music, with streaming accounting for 80% of the American recording industry's revenue. However, this shift has also complicated the lives of artists trying to survive in an on-demand, hyper-abundant present.
Spotify's business model, which pays royalties based on an artist's
popularity, has led to concerns about the fairness of the system, with some artists earning little to no royalties. The company's dominance has also changed the way people listen to music, with a focus on convenience and personalized playlists. Liz Pelly's book, "Mood Machine: The Rise of Spotify and the Costs of the Perfect Playlist," explores the impact of Spotify's rise on the music industry and listeners, arguing that the platform's emphasis on
affect and mood has led to a homogenization of music and a loss of autonomy for listeners. As the music industry continues to evolve, questions remain about the future of music creation and consumption, and whether artists will be able to thrive in a system that prioritizes convenience and profit over artistic expression.These examples show translate action:
The following examples shows using translate action in the prompt.
Note: Your AI profile must specify target language.
SELECT DBMS_CLOUD_AI.GENERATE('select ai translate text to be translated')
FROM dual;The following example shows translate action supplied in the DBMS_CLOUD_AI.GENERATE function along with target_language and source_language. This example uses generative AI translation. The input text this is a document in English (source_language: "en") is translated into French (target_language: "fr").
DECLARE
l_attributes clob := '{"target_language": "fr", "source_language": "en"}';
output clob;
BEGIN
output := DBMS_CLOUD_AI.GENERATE(
prompt => 'this is a document',
profile_name => 'oci_translate',
action => 'translate',
attributes => l_attributes
);Using the GENERATE Function in a Procedure
You can use DBMS_CLOUD_AI.GENERATE in a procedure and run the function. The following example takes an ai_prompt, profile_name, and action as input parameters and calls DBMS_CLOUD_AI.GENERATE.
create or replace FUNCTION call_select_ai (ai_prompt IN VARCHAR2,
ai_profile IN VARCHAR2,
ai_action IN VARCHAR2) -- valid for 'chat', 'narrate', 'showsql'
RETURN CLOB AS sai_resp clob;
BEGIN
sai_resp := DBMS_CLOUD_AI.GENERATE(prompt => ai_prompt,
profile_name => ai_profile,
action => ai_action);
return(sai_resp);
END call_select_ai;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.
|
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 |
table_statistics |
Boolean |
|
Enable or disable the use of table statistics information. The default value is |
priority |
String | Valid values:
|
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
The maximum number of concurrent parallel processes used for synthetic data generation is limited to 64. |
comments |
Boolean |
|
Enable or disable sending comments to the LLM to guide data generation. The default value is |
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_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 Supply When using the |
user_prompt |
String | No | Same as user_prompt in GENERATE_SYNTHETIC_DATA Function 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 Example: Generate Synthetic Data.
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_name => 'Director',
owner_name => 'ADB_USER',
record_count => 5
);
END;
/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;
/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:
-
narrateaction -
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:
-
narrateaction -
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 Vector Index Attributes. The default value is NULL. |
status |
Status of the vector index. The possible values are:
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:
The default value is |
force |
Indicates whether to ignore errors that occur if the vector index does not exist. Possible values:
If set to The default value is |
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 Vector Index Attributes.
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 You cannot modify the following attributes:
This is a mandatory parameter. |
attribute_value |
User specified value for the custom 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;
/Vector Index Attributes
Attributes of a vector index help to manage and configure the behavior of the vector index. You can add custom index attributes as necessary. Some attributes are optional and have a default value.
Attributes
| Attribute Name | Value | Mandatory | Description |
|---|---|---|---|
chunk_size |
1024 (default) |
No | Text size of chunking the input data. For text data, this means the number of characters. |
chunk_overlap |
128 (default) |
No | Specifies the amount of overlapping characters between adjacent chunks of text. This attribute is useful for ensuring contextual continuity and accuracy in text processing by allowing overlaps between segments, which helps prevent loss of contextual information at chunk boundaries. |
location |
NA | Yes | This parameter specifies source file URI or directories and source files. Wildcard patterns are supported for both source file URIs and directories. Cloud source file URIs: You can specify a source file URI for bucket or subfolder. You can use wildcards to specify subfolders or file names. The character " Example using wild cards: location_uri => 'https://objectstorage.my$region.oraclecloud.com/n/namespace-string/b/bucketname/o/year=????/month=??/*.csv The format of the URIs depends on the Cloud Object Storage service you are using, for details see Cloud Object Storage URI Formats. Directory: You can specify one directory and file name. The format to specify a directory is: You can only use wildcards to specify file names in a directory. The character Use double quotes to specify a case-sensitive directory name. For example: To include a quote character, use two quotes. For example: The files in this location can be documents in formats such as PDF, DOC, JSON, XML, or HTML. See Supported Document Formats. |
match_limit |
5 (default) |
No | Specifies the maximum number of results to return in a vector search query, controlling the output size and improving the efficiency of data retrieval operations. |
object_storage_credential_name |
NA | Yes | Specifies the name of the credentials for accessing an object storage. |
pipeline_name |
<vector_index_name>$VECPIPELINE |
No | Specifies the name of the vector index data load pipeline. This attribute is automatically set for the vector index, you cannot specify or modify. The pipeline name can be used to monitor the vector index data load using Monitor and Troubleshoot Pipelines. |
profile_name |
NA | Yes | Name of the AI profile which is used for embedding source data and user prompts. |
refresh_rate |
1440 minutes (default) |
No | Interval of updating data in the vector store. The unit is minutes. |
similarity_threshold |
0 (default) |
No | Defines the minimum level of similarity required for two items to be considered a match, useful for filtering results in matching algorithms to ensure relevance. |
vector_distance_metric |
A string corresponding to one of the values specified in the description. | No | Specifies the type of distance calculation used to compare vectors in a database, determining how similarity between items is quantified. Valid values for Oracle 23ai:
|
vector_db_provider |
oracle |
Yes | Specifies the provider name that manages and serves as the vector store. |
vector_dimension |
NA | No | Specifies the number of elements in each vector within the vector store, defining the size and structure of the data representation. |
vector_table_name |
<vector_index_name>$VECTAB (default) |
No | Specifies the name of the table or collection to store vector embeddings and chunked data. |
Example: Specify Object Storage URI Location
The following example demonstrates creating a vector index with OCI Generative AI vector store.
BEGIN
DBMS_CLOUD_AI.CREATE_VECTOR_INDEX(
index_name => 'MY_INDEX',
attributes => '{"vector_db_provider": "oracle",
"location": "https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/my_namespace/my_bucket/my_data_folder",
"object_storage_credential_name": "OCI_CRED",
"profile_name": "OPENAI_ORACLE",
"vector_dimension": 1024,
"vector_distance_metric": "cosine",
"chunk_overlap":128,
"chunk_size":1024
}');
END;
/
/Example: Specify Object Storage URI Location with Wild Card Pattern
This example specifies a wild card pattern (*) in the Object Storage URI as the location parameter. It loads all the CSV files from the Object Storage URI.
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.myregion.oraclecloud.com/n/my$namespace/b/bucketname/o/year=????/month=??/file*.csv)',
'object_storage_credential_name' value 'OS_CRED',
'chunk_size' value 2048,
'chunk_overlap' value 256,
'refresh_rate' value 720)
);
END;
/Example: Specify Directory Object Location with Wild Card Pattern
This example specifies directory objects in the location parameter using a wild card pattern. It loads all CSV files in the MY_DIR directory.
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 'MY_DIR:*.csv',
'object_storage_credential_name' value 'OS_CRED',
'chunk_size' value 2048,
'chunk_overlap' value 256,
'refresh_rate' value 720)
);
END;
/Example: Specify Case-Sensitive Directory Object Location with Wild Card Pattern
This example specifies a case-sensitive directory objects in the location parameter using a wild card pattern. It loads all CSV files in the My_Dir directory.
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 '"My_Dir":*.csv',
'object_storage_credential_name' value 'OS_CRED',
'chunk_size' value 2048,
'chunk_overlap' value 256,
'refresh_rate' value 720)
);
END;
/Example: Specify Case-Sensitive Directory Object with all Files as Wild Card Pattern
This example specifies a case-sensitive directory object in the location parameter using a wildcard pattern (*). It loads all files located in the My_Dir directory.
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 '"My_Dir":*',
'object_storage_credential_name' value 'OS_CRED',
'chunk_size' value 2048,
'chunk_overlap' value 256,
'refresh_rate' value 720)
);
END;
/Example: Specify a File Name Match in the Directory Object
This example specifies a directory object and uses a file name prefix, such as test, in the location parameter. It loads all files in the MY_DIR directory whose names begin with test.
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 'MY_DIR:test*',
'object_storage_credential_name' value 'OS_CRED',
'chunk_size' value 2048,
'chunk_overlap' value 256,
'refresh_rate' value 720)
);
END;
/CREATE_CONVERSATION Procedure
This procedure enables you to create a conversation and automatically set the conversation_id within the procedure.
Note: If you are using DBMS_CLOUD_AI.CREATE_CONVERSATION procedure, you can skip setting the conversation_id as the procedure automatically sets it.
Syntax
DBMS_CLOUD_AI.CREATE_CONVERSATION(
attributes IN CLOB DEFAULT NULL
);Parameters
| Parameter | Description |
|---|---|
attributes |
Attributes for conversation in JSON format. See CREATE_CONVERSATION Attributes for more details. The default value is NULL. |
Example
Example: Create Conversation
The following example shows creating a conversation without any customization.
EXEC DBMS_CLOUD_AI.CREATE_CONVERSATION;Example: Create Conversation with Custom Attributes
The following example shows creating a conversation with custom parameters such as title, description, retention_days and conversation_length.
-- Create conversation with custom attributes
SELECT DBMS_CLOUD_AI.CREATE_CONVERSATION(
attributes => '{"title":"Conversation 1",
"description":"this is a description",
"retention_days":5,
"conversation_length":5}')
AS conversation_id FROM dual;CREATE_CONVERSATION Function
This function creates a conversation and returns its conversation_id that can be used in other procedures or functions such as DBMS_CLOUD_AI.SET_CONVERSATION_ID and DBMS_CLOUD_AI.GENERATE.
Oracle recommends setting conversation_id to enable conversation. Alternately, you can set conversation_id in the DBMS_CLOUD_AI.GENERATE function.
Note: If you are using DBMS_CLOUD_AI.CREATE_CONVERSATION procedure, you can skip setting the conversation_id as the procedure automatically sets it.
Syntax
DBMS_CLOUD_AI.CREATE_CONVERSATION(
attributes IN CLOB DEFAULT NULL
) RETURN VARCHAR2;Parameters
| Parameter | Description |
|---|---|
attributes |
Attributes for conversation in JSON format. See CREATE_CONVERSATION Attributes for more details. The default value is NULL. |
Example
Example: Create Conversation
The following example shows using DBMS_CLOUD_AI.CREATE_CONVERSATION function to create a conversation without any customization.
SELECT DBMS_CLOUD_AI.CREATE_CONVERSATION FROM DUAL;Result:
CREATE_CONVERSATION
------------------------------------
30C9DB6E-EA4D-AFBA-E063-9C6D46644B92Example: Create Conversation with Custom Attributes
The following example shows using DBMS_CLOUD_AI.CREATE_CONVERSATION function to specify attributes such as title, retention_days and conversation_length.
SELECT DBMS_CLOUD_AI.CREATE_CONVERSATION(
attributes => '{"title":"This is a test conversation",
"retention_days":7,
"conversation_length":20}')
FROM DUAL;CREATE_CONVERSATION Attributes
These attributes manage conversation context, including how long to retain it, how many prompts with responses to store or display, and metadata like title and description for reference. Some attributes are optional and have a default value.
Attributes
| Attribute Name | Default Value | Description |
|---|---|---|
title |
New Conversation | The user-assigned name for the conversation. If not provided, Select AI will have the LLM generate one when the conversation is first used with a prompt. |
description |
NULL | Provides a user-defined description summarizing the purpose or context of the conversation. If it’s not provided, the LLM generates one when the conversation is first used with a prompt and update it again on the 5th use to include more accurate and relevant information. |
retention_days |
7 | Specify the number of days to retain the conversation history. This is stored in the database from its creation date. If you omit the value, the systems sets it to default value of 7. If you set it to 0, the system retains the conversation until you manually delete it using the DBMS_CLOUD_AI.DROP_CONVERSATION procedure or DBMS_CLOUD.DELETE_ALL_OPERATIONS('CONVERSATION'). |
conversation_length |
NULL | Specify the number of recent prompts and responses to include with the current prompt. The maximum allowed value is 999. You can override this value by specifying the
If none of them specify the |
The following example shows you how you can customize conversation attributes in the DBMS_CLOUD_AI.CREATE_CONVERSATION procedure.
-- Create conversation with custom attributes
SELECT DBMS_CLOUD_AI.CREATE_CONVERSATION(
attributes => '{"title":"Conversation 1",
"description":"this is a description",
"retention_days":5,
"conversation_length":5}')
AS conversation_id FROM dual;UPDATE_CONVERSATION Procedure
This procedure updates an existing conversation with a specified value of the conversation attributes.
Syntax
DBMS_CLOUD_AI.UPDATE_CONVERSATION(
conversation_id IN VARCHAR2,
attributes IN CLOB
);Parameters
| Parameter | Description |
|---|---|
conversation_id |
Unique number assigned to a conversation. This is a mandatory parameter. |
attributes |
Attributes for conversation in JSON format. See CREATE_CONVERSATION Attributes for more details. |
Example
EXEC DBMS_CLOUD_AI.UPDATE_CONVERSATION(
conversation_id => '30C9DB6E-EA4E-AFBA-E063-9C6D46644B92',
attributes => '{"retention_days":20,
"description":"This a sample description",
"title":"Sample title",
"conversation_length":20}');SET_CONVERSATION_ID Procedure
This procedure sets the current conversation to the specified ID. Subsequent prompts include existing conversation prompts based on the conversation’s configured attributes.
Syntax
DBMS_CLOUD_AI.SET_CONVERSATION_ID(
conversation_id IN VARCHAR2
);Parameters
| Parameter | Description |
|---|---|
conversation_id |
Unique number assigned to a conversation in the current session. This parameter is mandatory. |
Example
EXEC DBMS_CLOUD_AI.SET_CONVERSATION_ID('30C9DB6E-EA4D-AFBA-E063-9C6D46644B92');GET_CONVERSATION_ID Function
This function returns the conversation ID currently set in the session using either the DBMS_CLOUD_AI.SET_CONVERSATION_ID or DBMS_CLOUS_AI.CREATE_CONVERSATION procedure. If you did not set a conversation, the function returns NULL. If you drop the conversation, the system clears it in the session as well.See CLEAR_CONVERSATION_ID Procedure.
Syntax
DBMS_CLOUD_AI.GET_CONVERSATION_ID
RETURN VARCHAR2;Example
This example displays the conversation ID set in the current session.
SELECT DBMS_CLOUD_AI.GET_CONVERSATION_ID;Result:
--------------------------------------------------------------------------------
30C9DB6E-EA4F-AFBA-E063-9C6D46644B92CLEAR_CONVERSATION_ID Procedure
This procedure clears a conversation ID set in the session to disable the conversation feature for SELECT AI <ACTION> <PROMPT>. If you did not set a conversation, the system does not raise any error.
Syntax
DBMS_CLOUD_AI.CLEAR_CONVERSATION_ID;Example
This example demonstrates displaying the current conversation ID in the session, clearing the ID, and verifying the change.
-- A conversation id is set in the session
SELECT DBMS_CLOUD_AI.GET_CONVERSATION_ID FROM dual;
GET_CONVERSATION_ID
--------------------------------------------------------------------------------
3A88BFF0-1D7E-B3B8-E063-9C6D46640ECD
-- Clear the conversation id
EXEC DBMS_CLOUD_AI.CLEAR_CONVERSATION_ID;
PL/SQL procedure successfully completed.
-- The conversation id is removed from the session
SELECT DBMS_CLOUD_AI.GET_CONVERSATION_ID FROM dual;
GET_CONVERSATION_ID
--------------------------------------------------------------------------------DELETE_CONVERSATION_PROMPT Procedure
The procedure removes a certain prompt from the conversation.
Syntax
DBMS_CLOUD_AI.DELETE_CONVERSATION_PROMPT(
conversation_prompt_id IN VARCHAR2,
force IN BOOLEAN DEFAULT FALSE
);Parameters
| Parameter | Description |
|---|---|
conversation_prompt_id |
Unique number assigned to a prompt in a conversation. You can find the prompt ID by querying This is a mandatory parameter. |
force |
If The default value for this parameter is |
Example
EXEC DBMS_CLOUD_AI.DELETE_CONVERSATION_PROMPT('30C9DB6E-EA61-AFBA-E063-9C6D46644B92');DROP_CONVERSATION Procedure
The procedure removes the conversation and all its associated prompts and the associated responses. Once dropped, the conversation_id becomes invalid. If a conversation is dropped while it’s set in the session, it is cleared automatically.
Syntax
DBMS_CLOUD_AI.DROP_CONVERSATION(
conversation_id IN VARCHAR2,
force IN BOOLEAN DEFAULT FALSE
);Parameters
| Parameter | Description |
|---|---|
conversation_id |
Unique number assigned to a conversation. This is a mandatory parameter. |
force |
If The default value for this parameter is |
Example
EXEC DBMS_CLOUD_AI.DROP_CONVERSATION('30C9DB6E-EA4D-AFBA-E063-9C6D46644B92');Result:
PL/SQL procedure successfully completed.FEEDBACK Procedure
This procedure enables you to provide feedback to Select AI to potentially improve query generation accuracy. You have the option to provide positive or negative feedback, as well as textual comments or revised SQL queries.
Syntax
DBMS_CLOUD_AI.FEEDBACK(
profile_name IN VARCHAR2,
sql_id IN DBMS_ID,
feedback_type IN VARCHAR2 DEFAULT NULL,
response IN CLOB DEFAULT NULL,
feedback_content IN CLOB DEFAULT NULL,
operation IN VARCHAR2 DEFAULT 'ADD'
);
DBMS_CLOUD_AI.FEEDBACK(
profile_name IN VARCHAR2,
sql_text IN CLOB,
feedback_type IN VARCHAR2 DEFAULT NULL,
response IN CLOB DEFAULT NULL,
feedback_content IN CLOB DEFAULT NULL,
operation IN VARCHAR2 DEFAULT 'ADD'
);Parameters
| Parameter | Description |
|---|---|
profile_name |
Specifies the AI profile to use. If you do not provide a This is a mandatory parameter. |
sql_id |
Identifies the SQL query. One This is a mandatory parameter. |
sql_text |
Contains the full text of the SQL query. This is a mandatory parameter. |
feedback_type |
Specifies the type of feedback. The available values are:
Note: The This is a mandatory parameter when |
response |
Represents the correct SQL query result the user expects. This is a mandatory parameter when |
feedback_content |
Captures the user’s natural language feedback. You have the option to use this parameter along with response. |
operation |
Specifies the operation to perform. The accepted values are:
|
Example
Example: Provide Feedback for the Generated SQL Using Add or Delete Operations
The following example demonstrates using the DBMS_CLOUD_AI.FEEDBACK procedure to accept or improve the generated SQL by specifying the parameters from the procedure.
EXEC DBMS_CLOUD_AI.FEEDBACK(profile_name=>'OCI_FEEDBACK1',
sql_id=> '852w8u83gktc1',
feedback_type=>'positive',
operation=>'add');
EXEC DBMS_CLOUD_AI.FEEDBACK(profile_name=>'OCI_FEEDBACK1',
sql_text=> 'select ai showsql how many movies',
feedback_type=> 'negative',
response=>'SELECT SUM(1) FROM "ADB_USER"."MOVIES"',
feedback_content=>'Use SUM instead of COUNT');
EXEC DBMS_CLOUD_AI.FEEDBACK(profile_name=>'OCI_FEEDBACK1',
sql_id=> '852w8u83gktc1',
operation=>'delete');Vector Index for FEEDBACK
Select AI creates a default vector index named <*profile_name*>_FEEDBACK_VECINDEX with default attributes when you use the feedback feature for the first time.
You can modify its attributes such as similarity_threshold and match_limit by using the DBMS_CLOUD_AI.UPDATE_VECTOR_INDEX procedure. This index helps refine future generated SQL based on the feedback provided. This table is dropped when the associated AI profile is dropped. You can also drop <profile_name>_FEEDBACK_VECINDEX. When you do, Select AI no longer uses feedback as hints for the runsql, showsql, and explainsql actions. However, if you submit new feedback using the Select AI feedback feature, Select AI automatically creates a new feedback vector index
Note: The default value of match_limit for feedback is 3.
Vector Table Name
The table <profile_name>_FEEDBACK_VECINDEX$VECTAB contains vector representations (embeddings) of user feedback along with other parameters, which Select AI uses to improve SQL generation over time.
Parameters
| Column | Description |
|---|---|
attributes |
Includes JSON object attributes as per the FEEDBACK Procedure. |
content |
Contains the user prompt. |
embedding |
Contains vector representations (embeddings) of user prompt. |
Example
The following example demonstrates using the automatically generated vector index table to query and provide feedback.
SQL> select content, attributes from OCI_FEEDBACK1_FEEDBACK_VECINDEX$VECTAB where JSON_VALUE(attributes, '$.sql_text') = 'select ai showsql how many movies';CONTENT
----------------------------------------------------------------------------------------------------
how many movies
ATTRIBUTES
----------------------------------------------------------------------------------------------------
{"response":"SELECT SUM(1) FROM "ADB_USER"."MOVIES"","feedback_type":"negative","sql_id":null,"sql_text":"select ai showsql how many movies","feedback_content":null}
DBMS_CLOUD_AI.feedback Procedure(Positive Feedback)SUMMARIZE Function
This function summarizes your content based on the customization options you provide as parameters.
Syntax
DBMS_CLOUD_AI.SUMMARIZE(
content IN CLOB DEFAULT NULL,
credential_name IN VARCHAR2 DEFAULT NULL,
location_uri IN VARCHAR2 DEFAULT NULL,
profile_name IN VARCHAR2 DEFAULT NULL,
user_prompt IN CLOB DEFAULT NULL,
params IN CLOB DEFAULT NULL
) RETURN CLOB;Parameters
| Parameter | Description |
|---|---|
content |
Specifies the text you want to summarize. Either This is not a mandatory parameter. |
credential_name |
Identifies the credential object used to authenticate with the object store. You must create this credential using `DBMS_CLOUD.CREATE_CREDENTIAL`. Note: Use this parameter only when you provide `location_uri`. |
location_uri |
Provides the URI where the text is stored or the path to a local file. Either For example: Object storage: Local file: |
profile_name |
Specifies the AI profile to use. If you do not provide a The default value is NULL. |
user_prompt |
Supplies a natural language prompt to guide or customize the summary. You can include additional instructions beyond summary parameters. For example, The summary should start with ''The summary of the article is: ''' This parameter is not mandatory. |
params |
Defines summarization parameters. See SUMMARIZE Parameters. |
Example
See Example: Select AI Summarize to explore.
SUMMARIZE Parameters
These attributes manage generating summary with custom parameters. Some attributes are optional and have a default value.
Attributes
| Attribute Name | Default Value | Description |
|---|---|---|
min_words |
0 | Specifies the approximate minimum number of words the generated summary is expected to contain. Note: This parameter acts as a guideline rather than a strict limit: the actual length of the summary may vary depending on the content provided and the model's interpretation. |
max_words |
200 | Specifies the approximate maximum number of words the generated summary is expected to contain. Note: This parameter acts as a guideline rather than a strict limit, the actual length of the summary may vary depending on the content provided and the model's interpretation. |
summary_style |
Paragraph | Specifies the format style for the summary. The following are the available summary format options:
|
chunk_processing_method |
map_reduce |
When the text exceeds the token limit that the LLM can process, it must be split into manageable chunks. This parameter enables you to choose the method for processing these chunks. The following options are provided:
See Select AI Terminology for more details. |
extractiveness_level |
low |
Determines how closely the summary follows the original wording of the input. It controls the degree to which the model extracts versus rephrases it. The following are the options:
Note: This setting serves as guidance for the model's summarization behavior, it does not enforce a strict rule. The actual style and wording of the summary may vary based on the input content and model decisions. |
TRANSLATE Function
This function enables you to translate your text into the specified target_language.
You can supply the source_language and the target_language parameters in the function or they can be taken from the user’s AI profile. If your AI profile does not include a source_language attribute, the generative AI provider automatically detects the input language. If the target_language attribute is missing, Select AI returns an error.
Syntax
DBMS_CLOUD_AI.TRANSLATE(
profile_name IN VARCHAR2,
text IN CLOB,
source_language IN VARCHAR2 DEFAULT NULL,
target_language IN VARCHAR2 DEFAULT NULL
) RETURN CLOB;Parameters
| Parameter | Description |
|---|---|
profile_name |
Specifies the AI profile to use. This is not a mandatory parameter. |
text |
Specifies the text you want to translate. This is a mandatory parameter. |
source_language |
Language of the input text |
target_language |
Language into which the text is translated. |
Example
See Example: Select AI Translate to explore.