OpenAI

The UC AI OpenAI package provides integration with OpenAI’s GPT models, allowing you to use state-of-the-art language models within your Oracle database applications.

Features

• Support for latest GPT models (GPT-4.5, GPT-4.1, GPT-4o, o1 series, o3 series, o4 series)
• Full function calling (tools) support
• Advanced reasoning capabilities with reasoning effort control
• Multi-modal support (text, images, PDFs)

Responses API

As of v26.2, UC AI routes OpenAI calls through the Responses API by default. This is OpenAI’s recommended endpoint for new models and features. If you need the legacy Chat Completions endpoint for any reason, set:

uc_ai_openai.g_use_responses_api := false;

Prerequisites

1. OpenAI API key
2. Oracle database with internet access to OpenAI’s API endpoints
3. UC AI package installed
4. Set up API key (guide)

You can get an API key by signing up at OpenAI Platform.

Creating a Web Credential

To use your OpenAI key with APEX Web Credentials, create a new web credential in your APEX workspace (Workspace Utilities → Web Credentials) with the following settings:

• Authentication Type: HTTP Header
• Credential Name: Authorization
• Credential Secret: Bearer <your_api_key>

We generally recommend also setting Valid for URLs to the OpenAI API (https://api.openai.com/) to limit the scope of the credential.

To use the web credential in your PL/SQL code, set the package variable uc_ai_openai.g_apex_web_credential to the static ID of your web credential before calling any UC AI functions:

uc_ai_openai.g_apex_web_credential := 'OPENAI';

Models

OpenAI offers a wide range of models optimized for different use cases. The UC AI OpenAI package provides constants for all current models:

Core GPT Models

Standard GPT (Generative Pre-trained Transformer) models are designed for general-purpose text generation tasks. The latest models include:

• uc_ai_openai.c_model_gpt_5_1
• uc_ai_openai.c_model_gpt_5
• uc_ai_openai.c_model_gpt_5_mini
• uc_ai_openai.c_model_gpt_5_nano
• uc_ai_openai.c_model_gpt_5_pro

Mini and Nano versions of these models are optimized for speed and cost:

• uc_ai_openai.c_model_gpt_4_1_mini
• uc_ai_openai.c_model_gpt_4_1_nano

The o suffix stands for “omni”, indicating these models can handle both text and images:

• uc_ai_openai.c_model_gpt_4o
• uc_ai_openai.c_model_gpt_4o_mini
• uc_ai_openai.c_model_chatgpt_4o

Reasoning Models

Reasoning models have the prefix o and are designed to think longer and harder about complex tasks. They are more expensive and slower, but provide better results for complex tasks:

• uc_ai_openai.c_model_gpt_o1
• uc_ai_openai.c_model_gpt_o1_pro
• uc_ai_openai.c_model_gpt_o1_mini
• uc_ai_openai.c_model_gpt_o3
• uc_ai_openai.c_model_gpt_o3_pro
• uc_ai_openai.c_model_gpt_o3_deep_research
• uc_ai_openai.c_model_gpt_o3_mini
• uc_ai_openai.c_model_gpt_o4_mini
• uc_ai_openai.c_model_gpt_o4_mini_deep_research

Legacy Models

• uc_ai_openai.c_model_gpt_4_turbo - GPT-4 Turbo
• uc_ai_openai.c_model_gpt_4 - GPT-4
• uc_ai_openai.c_model_gpt_4_32k - GPT-4 32k context
• uc_ai_openai.c_model_gpt_3_5_turbo - GPT-3.5 Turbo

See OpenAI’s pricing page for the latest model information and costs. Also see Artificial Analysis for a comparison of intelligence, speed, and price.

Tip

If a new model is not yet available as a constant, you can pass the model name directly as a string. See the FAQ for details.

Usage Examples

Basic Text Generation

declare
  l_result json_object_t;
begin
  l_result := uc_ai.generate_text(
    p_user_prompt => 'What is Oracle APEX?',
    p_provider => uc_ai.c_provider_openai,
    p_model => uc_ai_openai.c_model_gpt_4o_mini
  );

  dbms_output.put_line('AI Response: ' || l_result.get_string('final_message'));
end;
/

With System Prompt

declare
  l_result json_object_t;
begin
  l_result := uc_ai.generate_text(
    p_user_prompt => 'Write a SQL query to find all employees hired this year',
    p_system_prompt => 'You are a helpful SQL expert. Write clean, efficient queries.',
    p_provider => uc_ai.c_provider_openai,
    p_model => uc_ai_openai.c_model_gpt_4_1
  );

  dbms_output.put_line('SQL Query: ' || l_result.get_string('final_message'));
end;
/

Using Tools/Function Calling

All OpenAI models support tools/function calling. You can define tools in your application and GPT will call them as needed.

See the tools guide for details on how to set up and use tools.

Multi-modal Analysis

The GPT models with the o suffix (GPT-4o, GPT-4o-mini, etc.) have vision capabilities for Image and PDF Analysis.

Refer to the file analysis guide for examples on how to analyze images and PDFs.

Reasoning / Thinking

OpenAI’s reasoning models (o1, o3, o4 series) support enhanced reasoning capabilities. You can control the reasoning effort level to balance speed and cost:

declare
  l_result json_object_t;
begin
  uc_ai.g_enable_reasoning := true;
  uc_ai_openai.g_reasoning_effort := 'medium'; -- 'low', 'medium', 'high'

  l_result := uc_ai.generate_text(
    p_user_prompt => 'Answer in one sentence. If there is a great filter, are we before or after it and why.',
    p_provider => uc_ai.c_provider_openai,
    p_model => uc_ai_openai.c_model_gpt_o4_mini
  );

  dbms_output.put_line('AI Response: ' || l_result.get_string('final_message'));
end;
/

The reasoning effort parameter controls how many reasoning tokens are generated before creating a response. Higher effort levels result in more thorough reasoning but take longer and cost more.

Refer to OpenAI’s reasoning best practices guide for advanced usage and best practices.

Reasoning content, storage, and encryption

On the Responses API, OpenAI treats reasoning as server-side state. Every turn that uses a reasoning model emits a reasoning output item (rs_...), but whether that item carries any content you can see or round-trip depends on two settings:

uc_ai_responses_api.g_store_responses := false;            -- default
uc_ai_responses_api.g_include_encrypted_reasoning := false; -- default
uc_ai_responses_api.g_reasoning_summary := null;            -- null | 'concise' | 'detailed' | 'auto'

• g_store_responses — when true, OpenAI keeps the full reasoning state on their side and UC AI only has to pass the rs_... id back on follow-up turns. When false, nothing is persisted server-side.
• g_include_encrypted_reasoning — when true, OpenAI returns the reasoning inline as an encrypted blob (for Zero Data Retention), which UC AI stores in providerOptions.encrypted_content and sends back on follow-up turns.
• g_reasoning_summary — when non-null, OpenAI includes a human-readable summary of the thinking in the reasoning item. Useful for observability even when you don’t keep the full content.

The combinations that matter in practice:

store	encrypted_content	Behavior
true	either	Reasoning persisted by OpenAI, rs_... id round-trips. Works for multi-turn reasoning.
false	true	Encrypted blob returned inline, round-trips on follow-up turns. Required for ZDR.
false	false	Reasoning content is lost. See below.

When both store=false and g_include_encrypted_reasoning=false, there is no way to preserve the reasoning across turns — OpenAI doesn’t keep it, and it isn’t handed back to you. UC AI still surfaces a reasoning marker in the returned messages array (with the rs_... id in providerOptions for observability) so you can see that reasoning happened, but:

• The marker’s text and providerOptions.encrypted_content are null.
• On intermediate tool-calling turns, UC AI deliberately strips these id-only reasoning items before the follow-up request. Otherwise OpenAI rejects the call with "Item with id 'rs_...' not found. Items are not persisted when store is set to false."

This is an intentional trade-off: if you explicitly opt out of both storage and encryption, you get privacy at the cost of losing the reasoning trace. If you need the trace — for debugging, auditing, or simply to pass it back in a later turn — set g_store_responses := true or g_include_encrypted_reasoning := true.

Generating Embeddings

OpenAI provides powerful embedding models for semantic search, clustering, and other vector-based operations. UC AI supports embedding generation with OpenAI’s latest models:

• uc_ai_openai.c_model_text_embedding_3_small - Cost-effective, good for most use cases
• uc_ai_openai.c_model_text_embedding_3_large - Higher quality, more dimensions
• uc_ai_openai.c_model_text_embedding_ada_002 - Legacy model

Refer to the OpenAI embedding documentation for additional information and potential new models that are not listed here yet.

declare
  l_result json_array_t;
begin
  l_result := uc_ai.generate_embeddings(
    p_input => json_array_t('["Oracle APEX is a low-code development platform.", "UC AI allows you to easily use AI from PL/SQL."]'),
    p_provider => uc_ai.c_provider_openai,
    p_model => uc_ai_openai.c_model_text_embedding_3_small
  );

  -- l_result.get_size → 2 (one embedding per input string)
  -- l_result.get(0) → JSON array of embedding values for first input
end;
/

For more information about the generate_embeddings function, visit its documentation page.

Using OpenAI API compliant providers

Many providers like DeepSeek or Perplexity offer APIs that behave the same way as the OpenAI API. You can use them with UC AI as well even though it is not first level supported by the package.

Authorization

To use your provider specific key instead of a OpenAI key you have two methods:

1: Add key to key function

Add the provider key to your key function with a custom name:

  case p_provider
    when 'deepseek' then
      return 'your_deepseek_key';

When calling the provider make sure to override this global variable:

uc_ai.g_provider_override := 'deepseek';

2: Use an APEX Web Credential

Create an APEX Web Credential passing the secret in the desired format like described in this guide. Then reference the Web Credential the following way:

uc_ai_openai.g_apex_web_credential := 'STATIC_ID_OF_WEB_CREDENTIAL';

Calling the LLM Provider

-- override base url of provider
uc_ai.g_base_url := 'https://api.deepseek.com';

-- based on your chosen way of authentication either set web credential or provider override
-- uc_ai.g_provider_override := 'deepseek';
-- uc_ai_openai.g_apex_web_credential := 'MY_DEEPKSEEK_WEB_CREDENTIAL';

l_result := uc_ai.GENERATE_TEXT(
  p_user_prompt => 'I have tomatoes, salad, potatoes, olives, and cheese. What an I cook with that?',
  p_provider => uc_ai.c_provider_openai,
  p_model => 'deepseek-chat' -- set desired model
);