OpenAI

The UC AI OpenAI package integrates OpenAI’s GPT models into your Oracle database applications.

Features

• Support for the current GPT models (GPT-5.6 Sol, Terra, and Luna, and the GPT-5.x series)
• Full function calling (tools) support
• 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. OpenAI recommends this endpoint for new models and features. To fall back to the legacy Chat Completions endpoint, 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 (see Network Setup (ACL & Wallet))
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 configuration:

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

Also set 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 the current models:

Core GPT Models

• uc_ai_openai.c_model_gpt_6_astra - GPT-6 Astra, the current flagship, for long end-to-end work
• uc_ai_openai.c_model_gpt_6_1_sol - GPT-6.1 Sol, close to Astra in performance at a lower cost
• uc_ai_openai.c_model_gpt_6_sol - GPT-6 Sol
• uc_ai_openai.c_model_gpt_6_luna - GPT-6 Luna, the most efficient model for high-volume tasks

Other current models:

• uc_ai_openai.c_model_gpt_5_6_terra - GPT-5.6 Terra, a balance of intelligence and cost
• uc_ai_openai.c_model_gpt_5_5_pro - GPT-5.5 Pro
• uc_ai_openai.c_model_gpt_5_4_mini - GPT-5.4 mini
• uc_ai_openai.c_model_gpt_5_4_nano - GPT-5.4 nano

This list shows the newest model of each family. The package keeps the constants of older models for code that still references them.

Reasoning Models

All GPT-5.x models reason internally. You control the amount of reasoning with uc_ai_openai.g_reasoning_effort, as described in the Reasoning / Thinking section.

The package also has constants for the older o series (c_model_gpt_o1, c_model_gpt_o3, c_model_gpt_o4_mini, and more) and for the GPT-4 and GPT-3.5 generations. These models are legacy. OpenAI can retire them at any time, so use a GPT-5.x model for new code.

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_5_6_luna
  );

  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_5_6_terra
  );

  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

All GPT-5.x models accept text and image input. Use them for Image and PDF Analysis.

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

Reasoning / Thinking

The GPT-5.x models reason before they answer. 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'; -- 'minimal', 'low', 'medium', 'high', 'xhigh'

  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_5_6_terra
  );

  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 package variables:

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 two variables combine as follows:

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 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."

If you opt out of both storage and encryption, you get privacy and lose the reasoning trace. To keep the trace — for debugging, for auditing, or to pass it back in a later turn — set g_store_responses := true or g_include_encrypted_reasoning := true.

Generating Embeddings

OpenAI provides 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, although the package does not support them as first-class providers.

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 can I cook with that?',
  p_provider => uc_ai.c_provider_openai,
  p_model => 'deepseek-chat' -- set desired model
);