How to Make LLM Calls Reliable in PL/SQL

A call to a language model is a network request to someone else's service. It can be slow, it can fail because the provider is busy or your quota is used up, and every call costs tokens you pay for. An application should neither lose a request nor show a raw database error to a user, and you should be able to see what each call cost.

This guide shows how to see the tokens a Gemini call uses, log every call to a table, retry busy or rate-limited calls automatically, and avoid the most expensive mistake: calling a model once per row of a query that runs again and again.

Code for This Guide

The examples are files 09 to 11 in the examples/ch10 folder of the Oracle AI code repository on GitHub, each with its output.

These examples come from AI Applications with Oracle Database 26ai and APEX 26.1, a book of 237 tested examples of AI in Oracle Database and APEX.

They extend the LLM_MODELS table and GENERATE function from how to call an LLM from PL/SQL with UTL_TO_GENERATE_TEXT. The token example calls Gemini's REST API with an APEX web credential, so the API key never appears in the code.

See the Tokens a Call Uses

UTL_TO_GENERATE_TEXT returns only the answer. Gemini's full response also says how many tokens the call used, which is what you pay for. To see it, call the REST API yourself with APEX_WEB_SERVICE.

Example:

-- calling Gemini's REST API directly, to see the tokens a call used
declare
  l_response clob;
begin
  apex_util.set_workspace('ATLAS');
  apex_web_service.set_request_headers('Content-Type', 'application/json');
  l_response := apex_web_service.make_rest_request(
    p_url  => 'https://generativelanguage.googleapis.com/v1beta/models/'
              || 'gemini-flash-latest:generateContent',
    p_http_method => 'POST',
    p_body => '{"contents": [{"parts": [{"text":
                 "In at most ten words: why do customers contact a help desk?"}]}]}',
    p_credential_static_id => 'credentials-for-gemini');

  dbms_output.put_line('Answer:   ' || json_value(l_response,
                         '$.candidates[0].content.parts[0].text' returning clob));
  dbms_output.put_line('Model:    ' || json_value(l_response, '$.modelVersion'));
  dbms_output.put_line('Prompt:   '
    || json_value(l_response, '$.usageMetadata.promptTokenCount') || ' tokens');
  dbms_output.put_line('Thinking: '
    || json_value(l_response, '$.usageMetadata.thoughtsTokenCount') || ' tokens');
  dbms_output.put_line('Answer:   '
    || json_value(l_response, '$.usageMetadata.candidatesTokenCount') || ' tokens');
end;
/

Output:

Answer:   To solve problems, get assistance, and find answers.
Model:    gemini-3.8-flash
Prompt:   14 tokens
Thinking: 413 tokens
Answer:   11 tokens

PL/SQL procedure successfully completed.

A 14-token prompt and an 11-token answer, plus hundreds of thinking tokens billed at the more expensive output rate. For a simple question, the thinking costs many times the answer, which is a strong reason to turn thinking off where it is not needed.

Log Every Call and Retry When Busy

Calls fail for ordinary reasons: the model is busy, you exceeded a rate limit, a key is wrong. Calls should also be logged, with what was asked, what came back, how long it took, and what failed. The log is how you find slow features, odd answers, and costs.

This new version of GENERATE adds three things:

• It retries busy or rate-limited calls up to three times, waiting a little longer each time.
• It logs every call to LLM_CALLS in an autonomous transaction, so the log survives even when the caller rolls back.
• It removes thinking settings for a model that does not accept them, so callers can pass the same options to any model.

Example:

create table llm_calls (
  call_id     number generated always as identity constraint llm_calls_pk primary key,
  called_at   timestamp default systimestamp not null,
  model       varchar2(30) not null,
  prompt      clob,
  response    clob,
  attempts    number,
  elapsed_ms  number,
  error       varchar2(4000)
);

create or replace function generate (
  p_prompt   in clob,
  p_model    in varchar2 default 'GEMINI',
  p_options  in json     default null
) return clob
is
  l_params    json;
  l_response  clob;
  l_start     timestamp := systimestamp;
  l_attempt   pls_integer := 0;
  l_error     varchar2(4000);
  l_thinking  varchar2(3);

  procedure log_call is
    pragma autonomous_transaction;       -- the log is kept even if the caller rolls back
    l_elapsed interval day to second := systimestamp - l_start;
  begin
    insert into llm_calls (model, prompt, response, attempts, elapsed_ms, error)
    values (p_model, p_prompt, l_response, l_attempt,
            round((extract(minute from l_elapsed) * 60 + extract(second from l_elapsed))
                  * 1000), l_error);
    commit;
  end;
begin
  select case when p_options is null then params
              else json_mergepatch(params, p_options returning json) end,
         thinking
  into   l_params, l_thinking
  from   llm_models
  where  name = p_model;
  if l_thinking = 'No' then          -- a model without thinking rejects thinking settings
    select json_transform(l_params, remove '$.generationConfig.thinkingConfig')
    into   l_params
    from   dual;
  end if;

  loop
    l_attempt := l_attempt + 1;
    begin
      l_response := dbms_vector_chain.utl_to_generate_text(p_prompt, l_params);
      l_error := null;
      exit;
    exception
      when others then
        l_error := substr(sqlerrm, 1, 4000);
        -- busy or over the rate limit: wait and try again, at most 3 times
        if l_attempt < 3
           and regexp_like(l_error, 'high demand|unavailable|429|RESOURCE_EXHAUSTED|503',
                           'i') then
          dbms_session.sleep(2 * l_attempt);
        else
          log_call;
          raise;
        end if;
    end;
  end loop;
  log_call;
  return l_response;
end;
/

select generate('Name the smallest planet of the solar system. Answer with one word.')
         as answer
from   dual;

select model, attempts, elapsed_ms, substr(prompt, 1, 40) || '...' as prompt,
       substr(response, 1, 20) as response
from   llm_calls
order  by call_id;

Output:

Table LLM_CALLS created.

Function GENERATE compiled

ANSWER
__________
Mercury

MODEL        ATTEMPTS    ELAPSED_MS PROMPT                                         RESPONSE
_________ ___________ _____________ ______________________________________________ ___________
GEMINI              1          2491 Name the smallest planet of the solar sy...    Mercury

One attempt, about 2.5 seconds. When Gemini is busy, the log shows two or three attempts. When a call fails for good, the log records the error and GENERATE raises it to the caller, which can then show the user a friendly message.

Do Not Call a Model for Every Row

GENERATE works in SQL like any function, which makes it easy to call a model for every row of a query, and easy to forget that each row is a paid call to the provider.

Example:

-- one call to Gemini for each of five tickets
set timing on
select ticket_id, subject,
       generate('Rewrite this support ticket subject in at most five words, '
                || 'plain text only: ' || subject,
                'GEMINI_LITE') as short_subject
from   tickets
where  ticket_id <= 5
order  by ticket_id;
set timing off

select count(*) as calls, sum(elapsed_ms) as total_ms
from   llm_calls
where  model = 'GEMINI_LITE';

Output:

   TICKET_ID SUBJECT                                     SHORT_SUBJECT
____________ ___________________________________________ ___________________________________
           1 Need to enable two-factor authentication    Enable Two-Factor Authentication
           2 Sync app shows pending forever              Sync stuck on pending
           3 App closes immediately on Android           App crashes on Android
           4 Connect Atlas to Outlook calendar           Connect Atlas to Outlook
           5 Report subscription stopped                 Subscription stopped working

Elapsed: 00:00:07.593

   CALLS    TOTAL_MS
________ ___________
       5        7305

Five rows, five calls, over seven seconds. A query over 400 tickets would take about ten minutes and make 400 paid calls, and an APEX report running that query would make them again on every page view.

The rule: generate once, store the result in a column, and show the column. Generate new values when the source row changes, ideally in a background job so that users never wait for the provider.

Conclusion

Make LLM calls from PL/SQL reliable by wrapping them in one function that logs every call with its prompt, response, attempts, time, and error in an autonomous transaction, and retries busy or rate-limited calls with a growing pause. Call the REST API when you need token counts, and watch for thinking tokens, which can cost far more than the answer. Never put a model call in a query that runs repeatedly: generate once and store the result.