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.
- DBMS_CLOUD_AI Overview
Describes the use of theDBMS_CLOUD_AIpackage. - Summary of DBMS_CLOUD_AI Subprograms
This section covers theDBMS_CLOUD_AIsubprograms provided with Autonomous Database.
Parent topic: Autonomous Database Supplied Package Reference
DBMS_CLOUD_AI Overview
Describes the use of the DBMS_CLOUD_AI package.
Use the DBMS_CLOUD_AI package
to create AI profiles and configure them for access to a Large Language Model (LLM).
Set the AI profile in the current database user session to perform tasks such as
generating, running, and explaining SQL. Additionally, enable retrieval augmented
generation and natural language-based interactions, including chatting with
LLMs.
To know more about supported platforms and LLMs, see About Select AI.
Parent topic: DBMS_CLOUD_AI Package
Summary of DBMS_CLOUD_AI Subprograms
This section covers the DBMS_CLOUD_AI subprograms provided with Autonomous Database.
| Subprogram | Description |
|---|---|
|
This procedure creates a new AI profile for translating natural language prompts to SQL statements. |
|
|
Provides AI profile attributes that you can configure. |
|
|
This procedure clears an active AI profile in the current session. |
|
|
This procedure disables an AI profile in the current database. |
|
|
This procedure drops an existing AI profile. |
|
|
This procedure enables an AI profile to use in the current database. |
|
| This function enables you to use Select AI in a stateless manner with your existing profile. | |
|
This function generates synthetic data. |
|
|
This function returns the profile name used in the current session. |
|
|
This procedure sets AI profile attributes. |
|
|
This procedure sets AI profile for the current database. |
|
|
Use this procedure to enable sending data to your LLM. |
|
|
Use this procedure to disable sending data to your LLM. |
|
|
Use this procedure to potentially improve query generation accuracy by providing a feedback to Select AI. |
|
|
This is a default vector index created when you first use
|
|
|
This procedure helps you to create a conversation. |
|
|
This function helps you to create a conversation and use the conversation ID in other procedures. |
|
|
Use the conversation attributes to customize your conversations. |
|
|
This procedure updates an existing procedure with user-specified parameters. |
|
|
This procedure sets conversation support in the current session. |
|
|
This procedure helps you to get the |
|
|
This procedure helps you to clear any
|
|
|
This procedure deletes a particular prompt. |
|
|
This procedure deletes an entire conversation and its metadata. |
|
|
This function summarizes your content based on the parameters. |
|
|
Use the summarize attributes to customize summary generation. |
|
|
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. |
|
|
This procedure removes a vector store index. It normally removes the vector store index object and deletes the vector database. |
|
|
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. |
|
|
This procedure enables or activates a previously disabled vector index object. |
|
|
This procedure updates an existing vector store index with a specified value of the vector index attribute. |
|
|
Provides vector index profile attributes that you can configure. |
- CREATE_PROFILE Procedure
The procedure creates a new AI profile for translating natural language prompts to SQL statement. - 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. - CLEAR_PROFILE Procedure
This procedure clears any active AI profile set in the current session. Once you clear an active profile, you can no longer useSELECT AIon the SQL command line or use the set AI profile when callingDBMS_CLOUD_AI.GENERATE. This procedure does not drop the profile. - DROP_PROFILE Procedure
The procedure drops an existing AI profile. If the profile does not exist, then the procedure throws an error. - ENABLE_PROFILE Procedure
This procedure enables the AI profile that the user specifies. The procedure changes the status of the AI profile toENABLED. - DISABLE_PROFILE Procedure
This procedure disables the AI profile in the current database. The status of the AI profile is changed toDISABLEDby this procedure. - 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. - Vector Index for FEEDBACK
Select AI creates a default vector index named<profile_name>_FEEDBACK_VECINDEXwith default attributes when you use the feedback feature for the first time. - GET_PROFILE Function
This function returns the AI profile name set in the current session. - SET_ATTRIBUTE Procedure
This procedure enables you to set AI profile attributes. It is overloaded to accept attribute values of various types. - 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 asshowsql,runsql,explainsql,narrate,summarize, andchat. The default action isshowsql. - SET_PROFILE Procedure
This procedure sets AI profile for current session. - CREATE_CONVERSATION Procedure
This procedure enables you to create a conversation and automatically set theconversation_idwithin the procedure. - CREATE_CONVERSATION Function
This function creates a conversation and returns itsconversation_idthat can be used in other procedures or functions such asDBMS_CLOUD_AI.SET_CONVERSATION_IDandDBMS_CLOUD_AI.GENERATE. - 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. - UPDATE_CONVERSATION Procedure
This procedure updates an existing conversation with a specified value of the conversation attributes. - 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. - GET_CONVERSATION_ID Function
This function returns the conversation ID currently set in the session using either theDBMS_CLOUD_AI.SET_CONVERSATION_IDorDBMS_CLOUS_AI.CREATE_CONVERSATIONprocedure. 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. - CLEAR_CONVERSATION_ID Procedure
This procedure clears a conversation ID set in the session to disable the conversation feature forSELECT AI <ACTION> <PROMPT>. If you did not set a conversation, the system does not raise any error. - DELETE_CONVERSATION_PROMPT Procedure
The procedure removes a certain prompt from the conversation. - DROP_CONVERSATION Procedure
The procedure removes the conversation and all its associated prompts and the associated responses. Once dropped, theconversation_idbecomes invalid. If a conversation is dropped while it's set in the session, it is cleared automatically. - SUMMARIZE Function
This function summarizes your content based on the customization options you provide as parameters. - SUMMARIZE Parameters
These attributes manage generating summary with custom parameters. Some attributes are optional and have a default value. - GENERATE_SYNTHETIC_DATA Function
Use this procedure to generate synthetic data for a single table, multiple tables or a full schema. - 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. - DISABLE_DATA_ACCESS Procedure
This procedure disables sending data to LLM for applicable Select AI features. Only an administrator can run this procedure. - 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 store. If set toFALSE, the argumentinclude_dataensures the procedure only removes the vector store index object while retaining the vector store. - 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. - 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. - 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
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.
Parent topic: DBMS_CLOUD_AI Package
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 |
|---|---|
|
|
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. |
|
|
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 enabled. |
|
|
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'),
status => 'enabled',
description => 'AI profile to use OpenAI for SQL translation'
);
END;
/Profile Attributes
Attributes
| Attribute Name | Description |
|---|---|
|
|
Provides additional metadata about your database tables and columns using the 23ai annotations feature. The
valid values are
|
|
|
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
|
|
|
Name of the Azure OpenAI deployed embedding model. The name can only include alphanumeric characters, underscore, and hyphen. The name can't start or end with a hyphen or underscore. |
|
|
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 |
|
|
Specifies whether Select AI should instruct the LLM to generate case-insensitive SQL for columns containing string or text values. The supported values are:
The default value is |
|
|
Includes table and column comments in the
metadata used for translating natural language prompts using AI.
|
|
|
Manages whether to include referential integrity constraints such as primary and foreign keys in the metadata sent to the LLM. The
valid values are
|
|
|
A |
|
|
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. Vault Secret credentials are also supported. Principal authentication, for example, Azure service principal, is also supported. For more information on how to configure it, see Use Azure Service Principal to Access Azure Resources. This is a mandatory attribute. See CREATE_CREDENTIAL Procedure. |
|
|
The embedding model defined in the AI profile. The following are the supported AI providers for the embedding models with their default values:
Note:
|
|
|
Specifies whether to restrict the LLM to generate SQL that uses only tables covered by the object list. The supported values are:
The values are not case sensitive. The default value
is |
|
|
Denotes the number of tokens to predict per generation. Default is 1024. See Tokens and Tokenizers for more details. |
|
|
The name of the AI model being used to generate responses. See Select your AI Provider and LLMs and specify the model name. Note:
|
|
|
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. The following types of objects can be used:
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:
External tables created using sync of OCI Data Catalog or AWS Glue can also be used the object list. This helps in managing metadata in central Data Catalogs and use the metadata directly for translating natural language prompts using AI. |
|
|
Specifies whether to send metadata for the most relevant tables or all tables to the LLM. Supported values are:
When |
|
|
Specifies the format in which the API expects data
to be sent and received. Use this attribute to generate text
responses. This attribute applies to OCI Generative AI Chat
Models in a dedicated AI
cluster.
Specify this attribute when you specify a model OCID in the
Supported values are:
Note: Use this attribute for OCI Generative AI Chat Models |
|
|
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 PDB. |
|
|
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. |
|
|
This attribute indicates the runtime type of the
provided model. This attribute
is
applicable to OCI Generate Text models in a dedicated AI
cluster. Specify this attribute when you specify a model OCID in
the All permitted values can be found in OCI Generative AI runtimeType. See LlmInferenceRequest Reference. The supported values are:
Note: This attribute is deprecated. Useoci_apiformat.
|
|
|
AI provider for the AI profile. Supported providers:
This is a mandatory attribute. |
|
|
Specifies the API endpoint for OpenAI-compatible
providers. This attribute is specific only to OpenAI-compatible
providers. Specify Example:
|
|
|
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, London, and other selected regions. See Regions with Generative AIto learn more.The default region for AWS is
The default region is
|
|
|
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.
|
|
|
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. |
|
|
Name of the vector index. The vector index name must follow the naming rules of Oracle SQL identifier. Maximum length of the vector store name is 125 characters. |
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_apiformat": "COHERE"
}');
END;
/ Parent topic: Summary of DBMS_CLOUD_AI Subprograms
CLEAR_PROFILE Procedure
This procedure clears any active AI profile set
in the current session. Once you clear an active profile, you can no longer use
SELECT AI on the SQL command line or use the set AI profile when
calling DBMS_CLOUD_AI.GENERATE. This
procedure does not drop the profile.
Syntax
DBMS_CLOUD_AI.CLEAR_PROFILE;Example
BEGIN
DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/Parent topic: Summary of DBMS_CLOUD_AI Subprograms
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 |
|---|---|
|
|
Name of the AI profile |
|
|
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.
Parent topic: Summary of DBMS_CLOUD_AI Subprograms
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 |
|---|---|
|
|
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;
/Parent topic: Summary of DBMS_CLOUD_AI Subprograms
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 |
|---|---|
|
|
Name for the AI profile. This parameter is mandatory. |
Example
BEGIN
DBMS_CLOUD_AI.DISABLE_PROFILE(
profile_name => 'OPENAI'
);
END;
/Parent topic: Summary of DBMS_CLOUD_AI Subprograms
FEEDBACK Procedure
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 |
|---|---|
|
|
Specifies the AI profile to use. If you do not
provide a This is a mandatory parameter. |
|
|
Identifies the SQL query. One
This is a mandatory parameter. |
|
|
Contains the full text of the SQL query. This is a mandatory parameter. |
|
|
Specifies the type of feedback. The available values
are:
Note: TheDBMS_CLOUD_AI.FEEDBACK procedure
enables you to specify sql_id or
sql_text, therefore, the
feedback_type is necessary whereas if you
are using the feedback action, the LLM
dynamically determines or interprets the feedback type.
This is a mandatory parameter when
|
|
|
Represents the correct SQL query result the user expects. This is a mandatory parameter when
|
|
|
Captures the user's natural language feedback. You
have the option to use this parameter along with
|
|
|
Specifies the operation to perform. The accepted
values are:
|
Example
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');Parent topic: Summary of DBMS_CLOUD_AI Subprograms
Vector Index for FEEDBACK
<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 ofmatch_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 |
|---|---|
|
|
Includes JSON object attributes as per the FEEDBACK Procedure. |
|
|
Contains the user prompt. |
|
|
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)Parent topic: Summary of DBMS_CLOUD_AI Subprograms
GET_PROFILE Function
This function returns the AI profile name set in the current session.
Syntax
DBMS_CLOUD_AI.GET_PROFILE
;Example
This example shows how you can display the name of the profile in the current session.
SELECT DBMS_CLOUD_AI.GET_PROFILE
from DUAL;Parent topic: Summary of DBMS_CLOUD_AI Subprograms
SET_ATTRIBUTE Procedure
This procedure enables you to set AI profile attributes. It is overloaded to accept attribute values of various types.
Syntax
DBMS_CLOUD_AI.SET_ATTRIBUTE(
profile_name IN VARCHAR2,
attribute_name IN VARCHAR2,
attribute_value IN {BOOLEAN|VARCHAR2}
);
DBMS_CLOUD_AI.SET_ATTRIBUTE(
profile_name IN VARCHAR2,
attribute_name IN VARCHAR2,
attribute_value IN CLOB DEFAULT NULL
);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 |
|---|---|
|
|
Name of the AI profile for which you want to set the attributes. This parameter is mandatory. |
|
|
Name of the AI profile attribute This parameter is mandatory. |
|
|
Value of the profile attribute. Value
can be The default value is NULL. |
Examples
BEGIN
DBMS_CLOUD_AI.SET_ATTRIBUTE(
profile_name => 'OPENAI',
attribute_name => 'credential_name',
attribute_value => 'OPENAI_CRED_NEW'
);
END;
/The following example accepts NUMBER type as the
attribute_value.
BEGIN
DBMS_CLOUD_AI.SET_ATTRIBUTE(
profile_name => 'OCI_PROFILE',
attribute_name => 'temperature',
attribute_value => 0.5
);
END;
/The following example accepts BOOLEAN type as the
attribute_value.
BEGIN
DBMS_CLOUD_AI.SET_ATTRIBUTE(
profile_name => 'OCI_PROFILE',
attribute_name => 'comments',
attribute_value => 'true'
);
END;
/The following example accepts VARCHAR2 type as the
attribute_value.
BEGIN
DBMS_CLOUD_AI.SET_ATTRIBUTE(
profile_name => 'OCI_PROFILE',
attribute_name => 'model',
attribute_value => 'meta.llama-3.3-70b-instruct'
);
END;
/Parent topic: Summary of DBMS_CLOUD_AI Subprograms
GENERATE Function
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 |
|---|---|
|
|
Natural language prompt to translate using AI. The prompt can include This parameter is mandatory. |
|
|
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 specifyprofile_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.
|
action |
Action for
translating natural prompt using AI. The supported actions
include |
attributes |
Override specific AI profile attributes by supplying attributes in JSON format. See Profile Attributes for more details. |
params |
Specify conversation parameters. See CREATE_CONVERSATION Attributes. You can specify only the following parameters:
|
Examples
Example: Using the GENERATE Function for Select AI Actions
The following examples illustrate runsql,
showsql, explainsql, narrate,
summarize, and chat
actions that can be used with the DBMS_CLOUD_AI.GENERATE function. See also Use AI Keyword 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;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.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;The following example shows how to use DBMS_CLOUD_AI.GENERATE function
in a conversation setting. This example assumes that a conversation has already been
created.
SELECT DBMS_CLOUD_AI.GENERATE(
prompt => 'What is the difference in weather between Seattle and San Francisco?',
profile_name => 'GENAI',
action => 'CHAT',
params => '{"conversation_id":"30C9DB6E-EA4D-AFBA-E063-9C6D46644B92"}') AS RESPONSE;Result:
RESPONSE
--------------------------------------------------------------------------------
Seattle and San Francisco, both located in the Pacific Northwest and Northern
California respectively, experience a mild oceanic climate. However, there are
some notable differences in their weather patterns:
1. **Temperature**: San Francisco tends to be slightly warmer than Seattle,
especially during the summer months. San Francisco's average temperature ranges
from 45?F (7?C) in winter to 67?F (19?C) in summer, while Seattle's average
temperature ranges from 38?F (3?C) in winter to 64?F (18?C) in summer.
2. **Rainfall**: Seattle is known for its rainy reputation, with an average
annual rainfall of around 37 inches (94 cm). San Francisco receives less rainfall,
with an average of around 20 inches (51 cm) per year. However, San Francisco's
rainfall is more concentrated during the winter months, while Seattle's rainfall
is more evenly distributed throughout the year.
......Parent topic: Summary of DBMS_CLOUD_AI Subprograms
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 the you specify with the AI prefix, a response is generated using AI. To use the AI prefix, see Examples of Using Select AI and Use AI Keyword to Enter Prompts. Optionally, it is possible to override the profile attributes or modify attributes by specifying them in JSON format. See SET_ATTRIBUTE Procedure for setting the attributes.
The AI profile can only be set for current session if the owner of the AI profile is the session user.
To set an AI profile for all sessions of a specific database user or all user sessions in the database, consider using a database event trigger for AFTER LOGON event on the specific user or the entire database. See CREATE TRIGGER Statement for more details.
Syntax
DBMS_CLOUD_AI.SET_PROFILE(
profile_name IN VARCHAR2,
);Parameters
| Parameter | Description |
|---|---|
|
|
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;
/Parent topic: Summary of DBMS_CLOUD_AI Subprograms
CREATE_CONVERSATION Procedure
This procedure enables you to create a conversation and
automatically set the conversation_id within the procedure.
Note:
If you are usingDBMS_CLOUD_AI.CREATE_COVERSATION procedure, you can
skip setting the conversation_id as the procedure automatically
sets it.
Syntax
DBMS_CLOUD_AI.CREATE_COVERSATION(
attributes IN CLOB DEFAULT NULL
);Parameters
| Parameter | Description |
|---|---|
|
|
Attributes for conversation in JSON format. See CREATE_CONVERSATION Attributes for more details. The default value is NULL. |
Example
The following example shows creating a conversation without any customization.
EXEC DBMS_CLOUD_AI.CREATE_COVERSATION;Result:
PL/SQL procedure successfully completed.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_COVERSATION(
attributes => '{"title":"Conversation 1",
"description":"this is a description",
"retention_days":5,
"conversation_length":5}')
AS conversation_id FROM dual;Parent topic: Summary of DBMS_CLOUD_AI Subprograms
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 usingDBMS_CLOUD_AI.CREATE_COVERSATION procedure, you can
skip setting the conversation_id as the procedure automatically
sets it.
Syntax
DBMS_CLOUD_AI.CREATE_COVERSATION(
attributes IN CLOB DEFAULT NULL
) RETURN VARCHAR2;Parameters
| Parameter | Description |
|---|---|
|
|
Attributes for conversation in JSON format. See CREATE_CONVERSATION Attributes for more details. The default value is NULL. |
Example
The following example shows using DBMS_CLOUD_AI.CREATE_COVERSATION function to create a
conversation without any
customization.
SELECT DBMS_CLOUD_AI.CREATE_COVERSATION FROM DUAL;Result:
CREATE_CONVERSATION
------------------------------------
30C9DB6E-EA4D-AFBA-E063-9C6D46644B92The following example shows using DBMS_CLOUD_AI.CREATE_COVERSATION function to specify
attributes such as title, retention_days and
conversation_length.
SELECT DBMS_CLOUD_AI.CREATE_COVERSATION(
attributes => '{"title":"This is a test conversation",
"retention_days":7,
"conversation_length":20}')
FROM DUAL;Parent topic: Summary of DBMS_CLOUD_AI Subprograms
CREATE_CONVERSATION Attributes
Attributes
| Attribute Name | Default Value | Description |
|---|---|---|
|
|
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. |
|
|
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. |
|
|
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
|
|
|
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
conversation_length parameter in the
DBMS_CLOUD_AI.GENERATE function or by
setting it in the AI profile using SELECT AI <ACTION>
<PROMPT>. Apply the following precedence
rules for conversation_length:
If none of them specify 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;Parent topic: Summary of DBMS_CLOUD_AI Subprograms
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 |
|---|---|
|
|
Unique number assigned to a conversation. This is a mandatory parameter. |
|
|
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}');Result:
PL/SQL procedure successfully completed.Parent topic: Summary of DBMS_CLOUD_AI Subprograms
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 |
|---|---|
|
|
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');
Result:
PL/SQL procedure successfully completed.Parent topic: Summary of DBMS_CLOUD_AI Subprograms
GET_CONVERSATION_ID Function
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-9C6D46644B92Parent topic: Summary of DBMS_CLOUD_AI Subprograms
CLEAR_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
--------------------------------------------------------------------------------Parent topic: Summary of DBMS_CLOUD_AI Subprograms
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 |
|---|---|
|
|
Unique number assigned to a prompt in a conversation. You can find the prompt
ID by querying
This is a mandatory parameter. |
|
|
If The default value for this parameter is
|
Example
EXEC DBMS_CLOUD_AI.DELETE_CONVERSATION_PROMPT('30C9DB6E-EA61-AFBA-E063-9C6D46644B92');Result:
PL/SQL procedure successfully completed.Parent topic: Summary of DBMS_CLOUD_AI Subprograms
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 |
|---|---|
|
|
Unique number assigned to a conversation. This is a mandatory parameter. |
|
|
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.Parent topic: Summary of DBMS_CLOUD_AI Subprograms
SUMMARIZE Function
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 |
|---|---|
|
|
Specifies the text you want to summarize. Either
This is not a mandatory parameter. |
|
|
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 providelocation_uri.
|
|
|
Provides the URI where the text is stored or the
path to a local file. Either For example: Object storage:
Local file:
|
|
|
Specifies the AI profile to use. If you do not
provide a The default value is NULL. |
|
|
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.
Parent topic: Summary of DBMS_CLOUD_AI Subprograms
SUMMARIZE Parameters
Attributes
| Attribute Name | Default Value | Description |
|---|---|---|
|
|
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. |
|
|
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. |
|
|
Paragraph |
Specifies the format style for the summary. The
following are the available summary format options:
|
|
|
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:
|
|
|
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. |
Parent topic: Summary of DBMS_CLOUD_AI Subprograms
GENERATE_SYNTHETIC_DATA Function
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 |
|---|---|---|
|
|
Yes |
The AI profile containing necessary LLM service information. This can be created by CREATE_PROFILE Procedure. |
|
|
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. |
|
|
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 |
|
|
No |
Optional attributes provided in JSON object string format to modify the behavior of an API. See Optional Parameters. |
|
|
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 |
|---|---|---|---|
|
|
Number |
|
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 |
|
|
Boolean |
|
Enable or disable the use of table statistics information. The default value is |
|
|
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. |
|
|
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 |
|---|---|---|---|
|
|
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. |
|
|
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.
|
|
|
Number |
No |
The number of records to be synthetically generated. Provide a number greater than 0. Supply |
|
|
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
record_count_percentage
parameter, the final record count in the table is calculated as:
|
|
|
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 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;
/
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.Parent topic: Summary of DBMS_CLOUD_AI Subprograms
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;
/Parent topic: Summary of DBMS_CLOUD_AI Subprograms
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;
/Parent topic: Summary of DBMS_CLOUD_AI Subprograms
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 |
|---|---|
|
|
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. |
|
|
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 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 |
|---|---|
|
|
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. |
|
|
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;
/Parent topic: Summary of DBMS_CLOUD_AI Subprograms
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 |
|---|---|
|
|
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;
/Parent topic: Summary of DBMS_CLOUD_AI Subprograms
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 |
|---|---|
|
|
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;
/Parent topic: Summary of DBMS_CLOUD_AI Subprograms
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.
Syntax
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 |
|---|---|
|
|
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. |
|
|
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. |
Example
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;
/Parent topic: Summary of DBMS_CLOUD_AI Subprograms
Vector Index Attributes
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. |
|
|
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 DBMS_CLOUD 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. |
|
|
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. |
|
|
NA | Yes |
Specifies the name of the credentials for accessing an object storage. |
|
|
<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. |
|
|
NA | Yes |
Name of the AI profile which is used for embedding source data and user prompts. |
|
|
1440 minutes (default)
|
No |
Interval of updating data in the vector store. The unit is minutes. |
|
|
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. |
|
|
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:
|
|
|
|
Yes |
Specifies the provider name that manages and serves as the vector store. |
|
|
NA | No |
Specifies the number of elements in each vector within the vector store, defining the size and structure of the data representation. |
|
|
|
No |
Specifies the name of the table or collection to store vector embeddings and chunked data. |
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;
/
/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;
/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;
/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;
/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;
/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;
/Parent topic: Summary of DBMS_CLOUD_AI Subprograms