3.6 GENERATE Function Signature 2
This function generates a response for a given prompt.
When in JSON mode, this function returns a stringified JSON object.
Syntax
FUNCTION apex_ai.generate (
p_prompt IN CLOB,
p_system_prompt IN CLOB DEFAULT NULL,
p_service_static_id IN VARCHAR2 DEFAULT NULL,
p_temperature IN NUMBER DEFAULT NULL,
p_attachments IN t_attachments DEFAULT NULL,
p_response_json_schema IN CLOB DEFAULT NULL,
p_tools IN t_tools DEFAULT NULL,
p_response_handler_procedure IN VARCHAR2 DEFAULT NULL,
p_max_tool_roundtrips IN PLS_INTEGER DEFAULT NULL )
RETURN CLOB;Parameters
| Parameter | Description |
|---|---|
p_prompt |
The user prompt. |
p_system_prompt |
(Optional) The instructions defining the AI's role, rules, and behavior. |
p_service_static_id |
The Generative AI Service static ID If not provided, the app's default AI Service is used. |
p_temperature |
The temperature to use. How the temperature is interpreted depends on the Generative AI service implementation. Higher temperatures result in more 'creative' responses. See the documentation of the Generative AI provider for details and allowed values. |
p_attachments |
An optional collection of file attachments. Whether a specific file type is supported depends on the AI provider and model. |
p_response_json_schema |
Optionally provide a JSON Schema which the AI provider follows when generating the response. |
p_tools |
Optional collection of tool definitions the AI provider may call while generating a response. Tools enable function calling to retrieve additional data or trigger actions. |
p_response_handler_procedure |
Advanced. Optional PL/SQL procedure invoked to post-process provider responses. Use to customize how tool calls or partial responses are handled before returning the final result. |
p_max_tool_roundtrips |
(Optional) Limit the number of network roundtrips that can be made when responding to tool calls. |
Returns
The response for the given prompt.
Example 1
The following example generates a response with the configured Generative AI Service MY_AI_SERVICE for the given prompt.
declare
l_response clob;
begin
l_response :=
apex_ai.generate (
p_prompt => 'What is Oracle APEX?',
p_system_prompt => 'You are an expert in Low Code Application Platforms',
p_service_static_id => 'MY_AI_SERVICE' );
end;
Example 2
The following example generates a JSON object with the configured Generative AI Service MY_AI_SERVICE for the given prompt.
declare
l_response clob;
begin
l_response :=
apex_ai.generate (
p_prompt => 'What is Oracle APEX?',
p_system_prompt => 'You are an expert in Low Code Application Platforms',
p_service_static_id => 'MY_AI_SERVICE',
p_response_json_schema => q'~{
"type": "object",
"properties": {
"description": {
"type": "string"
},
"releaseYear": {
"type": "integer"
},
"tags": {
"type": "array",
"items": {
"type": "string"
}
}
},
"required": [
"description",
"releaseYear",
"tags"
],
"additionalProperties": false
}~' );
end;
Example 3
The following example shows how to provide a tool to the LLM to execute as needed when coming up with a response. Each tool must specify a callback_procedure parameter, specifying the name of the stored procedure responsible for handling the tool call.
set serveroutput on;
create or replace procedure convert_currency (
p_param in apex_ai.t_tool_exec_param,
p_result in out nocopy apex_ai.t_tool_exec_result )
as
begin
p_result.result := to_char (
my_pkg.convert_currency (
p_amount => p_param.args_json.get_number( 'amount' ),
p_from => p_param.args_json.get_string( 'from_currency' ),
p_to => p_param.args_json.get_string( 'to_currency' ),
p_date => nvl( to_date( p_param.args_json.get_string( 'as_of_date' ), 'YYYY-MM-DD' ), sysdate ) ) );
end convert_currency;
/
show errors;
declare
l_response clob;
begin
apex_session.create_session (
p_app_id => 100,
p_page_id => 1,
p_username => 'USER' );
l_response :=
apex_ai.generate (
p_service_static_id => 'MY_AI_SERVICE',
p_prompt => 'How much is 100 USD in Euros?',
p_tools =>
apex_ai.t_tools (
apex_ai.t_tool (
name => 'convert_currency',
description => 'Converts money between currencies using an exchange rate for a given date (or latest available)',
parameters =>
apex_ai.t_tool_parameters (
apex_ai.t_tool_parameter (
name => 'amount',
data_type => apex_ai.c_tool_param_type_number ),
apex_ai.t_tool_parameter (
name => 'from_currency',
description => 'ISO 4217 currency code to convert from (e.g., "USD").' ),
apex_ai.t_tool_parameter (
name => 'to_currency',
description => 'ISO 4217 currency code to convert to (e.g., "EUR").' ),
apex_ai.t_tool_parameter (
name => 'as_of_date',
description => 'Optional date for the rate in YYYY-MM-DD format. If omitted, the latest rate will be returned.',
is_required => false ) ),
callback_procedure => 'convert_currency' ) ) );
sys.dbms_output.put_line( l_response );
apex_session.delete_session;
exception
when others then
apex_session.delete_session;
raise;
end;
/
Example 4
Alternatively, a response_handler_procedure can be provided, responsible for handling all incoming tool calls.
set serveroutput on;
create or replace procedure response_handler_proc (
p_param in apex_ai.t_chat_response_handler_param,
p_result in out nocopy apex_ai.t_chat_response_handler_result )
as
l_result clob;
begin
if p_result.response.type = apex_ai.c_response_type_tool_calls then
for i in 1 .. p_param.pending_tool_calls.count loop
if p_param.pending_tool_calls( i ).name = 'convert_currency' then
l_result := to_char (
my_pkg.convert_currency (
p_amount => p_param.pending_tool_calls( i ).args_json.get_number( 'amount' ),
p_from => p_param.pending_tool_calls( i ).args_json.get_string( 'from_currency' ),
p_to => p_param.pending_tool_calls( i ).args_json.get_string( 'to_currency' ),
p_date => nvl( to_date( p_param.pending_tool_calls( i ).args_json.get_string( 'as_of_date' ), 'YYYY-MM-DD' ), sysdate ) ) );
end if;
apex_ai.set_tool_result (
p_response_handler_param => p_param,
p_response_handler_result => p_result,
p_tool_call => p_param.pending_tool_calls( i ),
p_result => l_result );
end loop;
end if;
end response_handler_proc;
/
show errors;
declare
l_response clob;
begin
apex_session.create_session (
p_app_id => 100,
p_page_id => 1,
p_username => 'USER' );
l_response :=
apex_ai.generate (
p_service_static_id => 'MY_AI_SERVICE',
p_prompt => 'How much is 100 USD in Euros?',
p_tools =>
apex_ai.t_tools (
apex_ai.t_tool (
name => 'convert_currency',
description => 'Converts money between currencies using an exchange rate for a given date (or latest available)',
parameters =>
apex_ai.t_tool_parameters (
apex_ai.t_tool_parameter (
name => 'amount',
data_type => apex_ai.c_tool_param_type_number ),
apex_ai.t_tool_parameter (
name => 'from_currency',
description => 'ISO 4217 currency code to convert from (e.g., "USD").' ),
apex_ai.t_tool_parameter (
name => 'to_currency',
description => 'ISO 4217 currency code to convert to (e.g., "EUR").' ),
apex_ai.t_tool_parameter (
name => 'as_of_date',
description => 'Optional date for the rate in YYYY-MM-DD format. If omitted, the latest rate will be returned.',
is_required => false ) ) ) ),
p_response_handler_procedure => 'response_handler_proc' );
sys.dbms_output.put_line( l_response );
apex_session.delete_session;
exception
when others then
apex_session.delete_session;
raise;
end;
/
Parent topic: APEX_AI