DBMS_CLOUD_AI Package
The DBMS_CLOUD_AI
package facilitates and configures
the translation of natural language prompts to SQL statements.
- Summary of DBMS_CLOUD_AI Subprograms
This section covers theDBMS_CLOUD_AI
subprograms provided with Autonomous Database.
Parent topic: Autonomous Database Supplied Package Reference
Summary of DBMS_CLOUD_AI Subprograms
This section covers the DBMS_CLOUD_AI
subprograms provided with Autonomous Database.
Subprogram | Description |
---|---|
This procedure new AI profile for translating natural language prompts to SQL statements. |
|
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 generates a SQL statement using AI to translate. | |
This function returns the profile name used in the current session. |
|
This procedure returns the profile name and the owner of the profile in the current session. |
|
This procedure sets AI profile attributes. |
|
This procedure sets AI profile for the current database. |
- CREATE_PROFILE Procedure
The procedure creates a new AI profile for translating natural language prompts to SQL statement. - 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 toDISABLED
by this procedure. - GET_PROFILE Procedure
This procedure returns the AI profile name set in the current session. - GET_PROFILE Procedure
This procedure returns the AI profile name and the owner set in the current session. - SET_ATTRIBUTE Procedure
This procedure enables you to set AI profile attributes. - SET_PROFILE Procedure
This procedure sets AI profile for current session. - 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
,narrate
, orchat
. The default action isshowsql
. - 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.
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 enable. |
|
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;
/
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
GET_PROFILE Procedure
This procedure returns the AI profile name set in the current session.
Syntax
DBMS_CLOUD_AI.GET_PROFILE
(
profile_name IN VARCHAR2,
);
Parameters
Parameter | Description |
---|---|
|
A name for the AI profile in the current session. This parameter is mandatory. |
Example
SELECT
DBMS_CLOUD_AI.GET_PROFILE
from DUAL;
Parent topic: Summary of DBMS_CLOUD_AI Subprograms
GET_PROFILE Procedure
This procedure returns the AI profile name and the owner set in the current session.
Syntax
DBMS_CLOUD_AI.GET_PROFILE
(
profile_name OUT VARCHAR2,
profile_owner OUT VARCHAR2
);
Parameters
Parameter | Description |
---|---|
|
A name for the AI profile in the current session. This parameter is mandatory. |
profile_owner |
Identifies the owner of the AI profile in the current session. |
Example
This example shows how you can display the name and owner of the profile in the current session.
DECLARE
l_profile_name DBMS_ID;
l_profile_owner DBMS_ID;
BEGIN
DBMS_CLOUD_AI.GET_PROFILE
(profile_name => l_profile_name,
profile_owner => l_profile_owner);
END;
Parent topic: Summary of DBMS_CLOUD_AI Subprograms
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 |
---|---|
|
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. 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;
/
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 specified with the AI
prefix, a response is
generated using AI. To use the AI
prefix, see Examples of Using Select AI. 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
GENERATE Function
showsql
, narrate
, or
chat
. The default action is showsql
.
Overriding some or all of the profile attributes is also possible using this function.
Syntax
DBMS_CLOUD_AI.GENERATE
(
prompt IN CLOB,
profile_name IN VARCHAR2 DEFAULT NULL,
action IN VARCHAR2 DEFAULT NULL,
attributes IN CLOB DEFAULT NULL
) RETURN CLOB;
Parameters
Parameter | Description |
---|---|
|
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 specify profile_name
argument in DBMS_CLOUD_AI.GENERATE or you can
run two steps as a PL/SQL script: DBMS_CLOUD_AI.SET_PROFILE and DBMS_CLOUD_AI.GENERATE .
|
action |
Action for translating natural prompt using AI. The supported actions include Note
This function does not support the runsql action. If you
supply the runsql action, it returns the
following
error:
|
attributes |
Override specific AI profile attributes by supplying attributes in JSON format. See Profile Attributes for more details. |
Examples
The following examples illustrate showsql
,
narrate
, and chat
actions that can be used
with the DBMS_CLOUD_AI.GENERATE
function.
An example with showsql
action is as follows:
SELECT DBMS_CLOUD_AI.GENERATE
(prompt => 'how many customers',
profile_name => 'OPENAI',
action => 'showsql')
FROM dual;
An example with narrate
action is as follows:
SELECT DBMS_CLOUD_AI.GENERATE
(prompt => 'how many customers',
profile_name => 'OPENAI',
action => 'narrate')
FROM dual;
An example with chat
action is as follows:
SELECT DBMS_CLOUD_AI.GENERATE
(prompt => 'what is oracle autonomous database',
profile_name => 'OPENAI',
action => 'chat')
FROM dual;
Parent topic: Summary of DBMS_CLOUD_AI Subprograms
Profile Attributes
Attributes
Attribute Name | Description |
---|---|
azure_deployment_name |
Name of the Azure OpenAI Service deployed model. The
name can only include alphanumeric characters, underscore
character (_) and a hyphen (-) character. The name cannot end
with an underscore (_) or a hyphen (-). To know how to get the
|
azure_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 |
|
Include column comments in the metadata used for
translating natural language prompts using AI.
Note
Boolean values are not applicable in the DBMS_CLOUD_AI.SET_ATTRIBUTE
procedure when setting a single attribute because
attribute_value parameter is of
CLOB datatype.
|
|
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. Principle authentication, for example, Azure service principle, 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. |
|
Denotes the number of tokens to predict per generation. Default is 1024. See BPE Tokens for more details. |
|
The name of the AI model being used to generate translation. Supported models:
Note
This parameter is not used for Azure as the model is determined when you create your deployment in the Azure OpenAI Service portal. |
|
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:
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 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 required when the
All allowed values can be found in OCI Generative AI runtimeType. See LlmInferenceRequest Reference. The default value is Select |
|
AI provider for the AI profile. Supported providers:
This is a mandatory attribute. |
|
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
default region is The Oracle Generative AI cluster is only available in the Chicago region. us-chicago-1 .
|
|
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 generation models incorporates randomness, so that the same prompt may yield different outputs each time you hit "generate". Temperature is a non-negative float number used to tune the degree of randomness. Lower temperatures mean less random generations. See Temperature for more details. This parameter is applicable to all the supported service providers. |
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE
(
profile_name => 'COHERE',
attributes =>
'{"provider": "cohere",
"credential_name": "COHERE_CRED",
"object_list": [{"owner": "ADMIN"}],
"max_tokens":512,
"stop_tokens": [";"],
"model": "command-nightly",
"temperature": 0.5,
"comments": true
}');
END;
/
The following example shows custom profile attributes using OCI Generative AI:
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE
(
'GENAI',
'{"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;
/
Parent topic: Summary of DBMS_CLOUD_AI Subprograms