How to Run AI Jobs in the Background with DBMS_SCHEDULER

Calls to an AI provider do not belong in a transaction a user waits for. A provider can take seconds, be busy, or be down, and a new ticket should never fail to save because Gemini was slow. The pattern is simple: save at once, then embed and classify in the background, log what happened, and let the next run pick up anything that failed.

This guide builds that pattern in Oracle AI Database 26ai: a log table for background work, an embedding procedure that logs its failures instead of printing them, a procedure that runs all the AI work, and a DBMS_SCHEDULER job that runs it every minute.

Code for This Guide

The examples are files 01 to 03 in the examples/ch29 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.

The background work is the batch embedding from how to generate embeddings with Gemini from PL/SQL and the batch classification from how to classify data with an LLM in Oracle SQL.

Log Background Work to a Table

DBMS_OUTPUT is read by nobody when a job runs in the background; a table is. AI_JOB_LOG records each task, its status, how many rows it handled, and its error. LOG_JOB writes to it in an autonomous transaction, and a new version of EMBED_PENDING logs what it embedded, or the error when the provider fails, leaving the rows for the next run.

Example:

create table ai_job_log (
  logged_at  timestamp default systimestamp not null,
  task       varchar2(30)  not null,
  status     varchar2(10)  not null,      -- Done or Failed
  done       number,
  message    varchar2(4000)
);

create or replace procedure log_job (
  p_task in varchar2, p_status in varchar2, p_done in number default null,
  p_message in varchar2 default null)
is
  pragma autonomous_transaction;
begin
  insert into ai_job_log (task, status, done, message)
  values (p_task, p_status, p_done, substr(p_message, 1, 4000));
  commit;
end;
/

-- EMBED_PENDING, with its failures in the log instead of printed
create or replace procedure embed_pending (p_batch_size in pls_integer default 100)
is
  l_chunks   sys.vector_array_t;
  l_results  sys.vector_array_t;
  l_params   json;
  l_done     pls_integer := 0;
begin
  select params into l_params from embedding_models where name = 'GEMINI';
  loop
    select json_object('chunk_id'   value ticket_id,
                       'chunk_data' value subject || '. ' || description
                       returning clob)
    bulk   collect into l_chunks
    from   tickets
    where  gemini_embedding is null
    fetch  first p_batch_size rows only;
    exit when l_chunks.count = 0;

    l_results := dbms_vector_chain.utl_to_embeddings(l_chunks, l_params);
    forall i in 1 .. l_results.count
      update tickets
      set    gemini_embedding = to_vector(json_value(l_results(i), '$.embed_vector'
                                                     returning clob))
      where  ticket_id = json_value(l_results(i), '$.embed_id');
    l_done := l_done + l_results.count;
    commit;
  end loop;
  if l_done > 0 then log_job('EMBED_PENDING', 'Done', l_done); end if;
exception
  when others then
    rollback;
    log_job('EMBED_PENDING', 'Failed', l_done, sqlerrm);   -- the next run tries again
end;
/

Output:

Table AI_JOB_LOG created.

Procedure LOG_JOB compiled

Procedure EMBED_PENDING compiled

Failed rows need no special handling: they still have no Gemini embedding, so the next run selects them again.

Schedule the Work with CREATE_JOB

DBMS_SCHEDULER runs jobs inside the database: a procedure, an anonymous block, or a program, on a schedule written as a calendar expression such as FREQ=MINUTELY; INTERVAL=1. The CREATE JOB privilege comes with DB_DEVELOPER_ROLE.

Syntax:

dbms_scheduler.create_job(
  job_name         varchar2,
  job_type         varchar2,        -- 'STORED_PROCEDURE', 'PLSQL_BLOCK', ...
  job_action       varchar2,
  repeat_interval  varchar2 default null,
  enabled          boolean  default false,
  comments         varchar2 default null)

RUN_AI_JOBS embeds the new tickets and classifies the unclassified ones, logging each step, and the job ATLAS_AI_JOBS runs it every minute.

Example:

-- the background work of Atlas Support: embed and classify new tickets
create or replace procedure run_ai_jobs
is
  l_before  pls_integer;
  l_after   pls_integer;
begin
  embed_pending;

  select count(*) into l_before from tickets where ai_done_at is null;
  if l_before > 0 then
    begin
      classify_tickets;
      select count(*) into l_after from tickets where ai_done_at is null;
      log_job('CLASSIFY_TICKETS', 'Done', l_before - l_after);
    exception
      when others then
        log_job('CLASSIFY_TICKETS', 'Failed', null, sqlerrm);
    end;
  end if;
end;
/

begin
  dbms_scheduler.create_job(
    job_name        => 'ATLAS_AI_JOBS',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'RUN_AI_JOBS',
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=1',
    enabled         => true,
    comments        => 'Embeds and classifies new tickets');
end;
/

select job_name, enabled, state, repeat_interval from user_scheduler_jobs;

Output:

Procedure RUN_AI_JOBS compiled

PL/SQL procedure successfully completed.

JOB_NAME         ENABLED    STATE        REPEAT_INTERVAL
________________ __________ ____________ ____________________________
ATLAS_AI_JOBS    TRUE       SCHEDULED    FREQ=MINUTELY; INTERVAL=1

The job's first run starts within a minute. When there is nothing to do, RUN_AI_JOBS makes no call to any model and finishes in milliseconds.

Watch a New Ticket Get Processed

This example saves a new ticket, shows that it has no Gemini embedding or category yet, waits 70 seconds, and shows it again with the job log.

Example:

-- a new ticket, saved at once; the job embeds and classifies it within a minute
insert into tickets (ticket_id, customer_id, product_id, subject, description,
                     priority, status, category, channel, created_at)
values (9004, 3, 1, 'Pipeline board empty',
        'Since this morning the deals board shows nothing at all, just a spinning wheel.',
        'High', 'Open', 'Bug', 'Chat', systimestamp);
commit;

select ticket_id, ai_category, vector_dimension_count(gemini_embedding) as gemini
from   tickets
where  ticket_id = 9004;

exec dbms_session.sleep(70)

select ai_category, ai_summary, vector_dimension_count(gemini_embedding) as gemini
from   tickets
where  ticket_id = 9004;

select to_char(logged_at, 'HH24:MI:SS') as at, task, status, done
from   ai_job_log
order  by logged_at;

Output:

1 row inserted.

Commit complete.

   TICKET_ID AI_CATEGORY       GEMINI
____________ ______________ _________
        9004

PL/SQL procedure successfully completed.

AI_CATEGORY    AI_SUMMARY                                                           GEMINI
______________ _________________________________________________________________ _________
Bug            Deals board is empty and displays a continuous spinning wheel.         3072

AT          TASK                STATUS       DONE
___________ ___________________ _________ _______
08:58:04    EMBED_PENDING       Done            1
08:58:06    CLASSIFY_TICKETS    Done            1

The ticket was saved at once, with an in-database embedding from its trigger. Within the minute, the job gave it its Gemini embedding, category, and summary, and logged both steps. The customer who submitted it waited for none of it. Had Gemini been down, the log would show the failure and the next run would try again.

Manage the Job

Pause the job when nobody is working, for example in a development database, and create it disabled in new environments, enabling it when the environment is ready.

Conclusion

Run AI work in Oracle in the background: save rows at once, then let a DBMS_SCHEDULER job embed and classify them every minute in batches. Log each step and each failure to a table with an autonomous transaction, design the work so that failed rows are selected again by the next run, and make the job cheap when idle, so that it calls no model when there is nothing to do.