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)

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.

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.

OpenAI does not return reasoning summary messages like some other providers. It only returns the final message after reasoning is complete.

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