A language model can classify a ticket with nothing but a prompt. Oracle Database has classified records for more than twenty years with machine learning models trained on labeled examples, today as Oracle Machine Learning. The two are often called old and new; they are better seen as two tools with different strengths, and embeddings make the older one surprisingly strong.
This guide trains models inside Oracle AI Database 26ai with DBMS_DATA_MINING, using ticket embeddings as features: a classifier compared with Gemini on familiar and new tickets, routing between the two by confidence, a baseline check, and k-means clustering whose topics a language model names.
Code for This Guide
The examples are in the examples/ch14 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 use the 400 tickets of the sample schema with their embeddings, from how to generate embeddings in SQL with VECTOR_EMBEDDING, and the language model's categories stored in AI_CATEGORY by how to classify data with an LLM in Oracle SQL. Training needs the CREATE MINING MODEL privilege.
Prepare Training and Test Data
A classifier learns from examples whose answers are known, here the categories agents gave the tickets, and is tested on examples it has not seen. Every fourth ticket is kept for testing.
Example:
-- the tickets with what a model may learn from; every 4th ticket is kept for testing
create table ml_tickets as
select t.ticket_id, t.category, t.priority, c.plan, t.product_id, t.channel, t.embedding,
case when mod(t.ticket_id, 4) = 0 then 'Test' else 'Train' end as data_set
from tickets t join customers c on c.customer_id = t.customer_id;
select data_set, count(*) as tickets from ml_tickets group by data_set order by data_set;Output:
Table ML_TICKETS created. DATA_SET TICKETS ___________ __________ Test 100 Train 300
Train a Classifier with CREATE_MODEL2
DBMS_DATA_MINING.CREATE_MODEL2 trains a model on the rows of a query and stores it in the schema. The settings choose the algorithm; PREP_AUTO lets the database normalize numbers and encode categories itself.
Syntax:
dbms_data_mining.create_model2( model_name varchar2, mining_function varchar2, -- 'CLASSIFICATION', 'REGRESSION', 'CLUSTERING', ... data_query clob, set_list dbms_data_mining.setting_list, case_id_column_name varchar2 default null, target_column_name varchar2 default null)
This example trains a support vector machine, an algorithm that suits many numeric features such as the 384 dimensions of an embedding, to predict a ticket's category from its embedding.
Example:
declare
l_settings dbms_data_mining.setting_list;
begin
l_settings('ALGO_NAME') := 'ALGO_SUPPORT_VECTOR_MACHINES';
l_settings('PREP_AUTO') := 'ON';
dbms_data_mining.create_model2(
model_name => 'CATEGORY_SVM',
mining_function => 'CLASSIFICATION',
data_query => 'select ticket_id, category, embedding from ml_tickets
where data_set = ''Train''',
set_list => l_settings,
case_id_column_name => 'TICKET_ID',
target_column_name => 'CATEGORY');
end;
/
select model_name, mining_function, algorithm, round(build_duration, 1) as build_seconds
from user_mining_models
where model_name = 'CATEGORY_SVM';Output:
PL/SQL procedure successfully completed. MODEL_NAME MINING_FUNCTION ALGORITHM BUILD_SECONDS _______________ __________________ __________________________ ________________ CATEGORY_SVM CLASSIFICATION SUPPORT_VECTOR_MACHINES 1
The model trained in about a second. A VECTOR column is an input like any other: the database uses each dimension as a feature.
Predict with PREDICTION
PREDICTION applies a classification model to a row and returns the predicted class; PREDICTION_PROBABILITY returns how probable the model finds it. Both are SQL functions.
Syntax:
prediction( model_name using { * | expression [ as alias ] [, ...] } )
prediction_probability( model_name [, class ] using { * | expression [ as alias ] [, ...] } )Example:
-- the 100 test tickets: the classifier and the model, against the agents
set timing on
select count(*) as tickets,
count(case when prediction(category_svm using m.embedding) = m.category then 1 end)
as classifier_right,
count(case when t.ai_category = m.category then 1 end) as llm_right
from ml_tickets m join tickets t on t.ticket_id = m.ticket_id
where m.data_set = 'Test';
set timing off
select ticket_id, category,
prediction(category_svm using embedding) as predicted,
round(prediction_probability(category_svm using embedding), 2) as probability
from ml_tickets
where data_set = 'Test'
order by ticket_id
fetch first 5 rows only;Output:
TICKETS CLASSIFIER_RIGHT LLM_RIGHT
__________ ___________________ ____________
100 100 86
Elapsed: 00:00:00.233
TICKET_ID CATEGORY PREDICTED PROBABILITY
____________ __________________ __________________ ______________
4 Question Question 0.61
8 Question Question 0.59
12 Feature Request Feature Request 0.63
16 Question Question 0.58
20 Question Question 0.58The classifier agreed with the agents on all 100 test tickets, the language model on 86. The classifier took a quarter of a second for all 100, with no call to any provider, and gives the same answer every time.
The perfect score needs a caution. These tickets describe the same few dozen problems again and again, so test tickets closely resemble training tickets. The classifier also learned the agents' conventions: it files "custom fields on invoices" under Feature Request because the agents did, where the language model said Question. A trained model reproduces its labels, quirks included; that is its strength and its limit.
Test on New Kinds of Problems
The limit shows when something new arrives. This example classifies eight tickets about problems no earlier ticket described, with the classifier and with Gemini.
Example:
-- eight tickets about problems that no Atlas ticket has described, with the right category
create table new_tickets (n number, text varchar2(200), category varchar2(20));
insert into new_tickets values
(1, 'Our GDPR officer needs to know in which country our data is stored.', 'Question'),
(2, 'The search box in contacts ignores accents, so we cannot find Muller.', 'Bug'),
(3, 'Please add a dark mode to the desktop app for our night shift.', 'Feature Request'),
(4, 'We were charged in dollars although our account is set to euros.', 'Billing'),
(5, 'A former employee still has access after we removed her from the team.', 'Account'),
(6, 'Exported CSV files open with broken characters in Excel.', 'Bug'),
(7, 'Can we get an invoice addressed to our parent company instead?', 'Billing'),
(8, 'Would it be possible to schedule reports in our local time zone?',
'Feature Request');
commit;
create or replace function llm_category (p_text in varchar2) return varchar2
is
begin
return json_value(generate(
'Classify this support ticket of Atlas Software. '
|| 'Categories: Account (sign-in, users, security), '
|| 'Billing (invoices, payments, plans, tax), Bug (something does not work), '
|| 'Question (how to do something), Feature Request (something that does not exist). '
|| 'Ticket: ' || p_text,
'GEMINI',
json('{"generationConfig": {"temperature": 0, "thinkingConfig": {"thinkingBudget": 0},
"responseMimeType": "application/json",
"responseSchema": {"type": "OBJECT", "required": ["category"], "properties":
{"category": {"type": "STRING", "enum": ["Account", "Billing", "Bug", "Question",
"Feature Request"]}}}}}')),
'$.category');
end;
/
with e as (
select n, text, category,
vector_embedding(all_minilm_l12_v2 using text as data) as embedding
from new_tickets)
select n, category,
prediction(category_svm using embedding) as classifier,
round(prediction_probability(category_svm using embedding), 2) as probability,
llm_category(text) as llm
from e
order by n;Output:
Table NEW_TICKETS created. 8 rows inserted. Commit complete. Function LLM_CATEGORY compiled N CATEGORY CLASSIFIER PROBABILITY LLM ____ __________________ __________________ ______________ __________________ 1 Question Question 0.4 Question 2 Bug Account 0.4 Bug 3 Feature Request Feature Request 0.65 Feature Request 4 Billing Billing 0.47 Billing 5 Account Account 0.58 Bug 6 Bug Bug 0.59 Bug 7 Billing Billing 0.39 Billing 8 Feature Request Question 0.5 Feature Request 8 rows selected.
The classifier got six of eight, the language model seven. The classifier's mistakes are tickets unlike anything it trained on, and both sit among its lowest probabilities: on familiar test tickets it was never below 0.52. A classifier is uncertain about what it has not seen, and says so. The language model's one miss is a judgment call between Account and Bug.
Route Between the Two by Confidence
The probability enables a simple combination: let the classifier decide when it is confident, and ask the language model only when it is not. The threshold here, 0.52, is the lowest probability the classifier gave a familiar ticket.
Example:
-- the classifier where it is sure, the language model where it is not
with e as (
select n, text, category,
vector_embedding(all_minilm_l12_v2 using text as data) as embedding
from new_tickets),
scored as (
select n, text, category,
prediction(category_svm using embedding) as classifier,
prediction_probability(category_svm using embedding) as probability
from e)
select n, category,
case when probability >= 0.52 then classifier
else llm_category(text) end as answer,
case when probability >= 0.52 then 'classifier'
else 'language model' end as decided_by
from scored
order by n;Output:
N CATEGORY ANSWER DECIDED_BY ____ __________________ __________________ _________________ 1 Question Question language model 2 Bug Bug language model 3 Feature Request Feature Request classifier 4 Billing Billing language model 5 Account Account classifier 6 Bug Bug classifier 7 Billing Billing language model 8 Feature Request Feature Request language model 8 rows selected.
All eight right, better than either alone, with five language model calls instead of eight. On a real help desk, where most tickets are familiar, the classifier would decide the large majority for free in microseconds. Measure the threshold on your own test data: higher sends more to the language model, lower trusts the classifier more.
Always Compare with a Baseline
The language model matched the agents' priority on under half of the tickets, because priority depends on things the text does not say. A classifier can use those things. This example trains a decision tree on the customer's plan, product, category, and channel, and compares it with the language model and with the simplest prediction: "Normal" for every ticket.
Example:
-- priority from the customer's plan, the product, the category, and the channel
declare
l_settings dbms_data_mining.setting_list;
begin
l_settings('ALGO_NAME') := 'ALGO_DECISION_TREE';
l_settings('PREP_AUTO') := 'ON';
dbms_data_mining.create_model2(
model_name => 'PRIORITY_TREE',
mining_function => 'CLASSIFICATION',
data_query => 'select ticket_id, priority, plan, product_id, category, channel
from ml_tickets where data_set = ''Train''',
set_list => l_settings,
case_id_column_name => 'TICKET_ID',
target_column_name => 'PRIORITY');
end;
/
select count(*) as tickets,
count(case when prediction(priority_tree using m.*) = m.priority then 1 end)
as decision_tree,
count(case when t.ai_priority = m.priority then 1 end) as language_model,
count(case when m.priority = 'Normal' then 1 end) as always_normal
from ml_tickets m join tickets t on t.ticket_id = m.ticket_id
where m.data_set = 'Test';Output:
PL/SQL procedure successfully completed.
TICKETS DECISION_TREE LANGUAGE_MODEL ALWAYS_NORMAL
__________ ________________ _________________ ________________
100 39 45 45The decision tree is right 39 times, the language model 45, and answering "Normal" every time is right 45 times. Neither beats the trivial answer: in this data, priority is close to random given what is recorded. Without the baseline, 45 percent might have looked like a start; with it, there is clearly nothing to learn. Compare every model with a baseline, such as the most common class, before trusting it.
Find Topics with Clustering
Clustering needs no labels: it groups records so each group's members are similar. On embeddings, similar means similar in meaning, so clusters of tickets are topics. This example trains a k-means model with eight clusters and counts the tickets in each with CLUSTER_ID.
Example:
declare
l_settings dbms_data_mining.setting_list;
begin
l_settings('ALGO_NAME') := 'ALGO_KMEANS';
l_settings('CLUS_NUM_CLUSTERS') := '8';
l_settings('PREP_AUTO') := 'ON';
dbms_data_mining.create_model2(
model_name => 'TICKET_CLUSTERS',
mining_function => 'CLUSTERING',
data_query => 'select ticket_id, embedding from ml_tickets',
set_list => l_settings,
case_id_column_name => 'TICKET_ID');
end;
/
select cluster_id(ticket_clusters using embedding) as cluster_id, count(*) as tickets
from ml_tickets
group by cluster_id(ticket_clusters using embedding)
order by cluster_id;Output:
PL/SQL procedure successfully completed.
CLUSTER_ID TICKETS
_____________ __________
3 21
5 14
7 16
9 32
11 72
13 58
14 93
15 94
8 rows selected.The cluster numbers are only identifiers: Oracle's k-means builds a tree of clusters and numbers its leaves. A cluster has no name, so the cheaper Lite model names each one from its tickets' subjects, with one call per cluster.
Example:
-- the language model names each cluster from the subjects of its tickets
select c.cluster_id, c.tickets,
generate('Give a name of at most four words, plain text, to this group of support '
|| 'tickets, from their subjects: ' || c.subjects, 'GEMINI_LITE') as name
from (select cluster_id(ticket_clusters using m.embedding) as cluster_id,
count(*) as tickets,
listagg(distinct t.subject, '; ') as subjects
from ml_tickets m join tickets t on t.ticket_id = m.ticket_id
group by cluster_id(ticket_clusters using m.embedding)) c
order by c.tickets desc;Output:
CLUSTER_ID TICKETS NAME
_____________ __________ _________________________________________
15 94 Billing, Invoices, and Data Issues
14 93 Account Management and Data Operations
11 72 Account Access and Notifications
13 58 Sync, API, and Data Issues
9 32 App Crashes and Loading Failures
3 21 Dark Mode Requests
7 16 Desktop App Deployment
5 14 Two-Factor Authentication Support
8 rows selected.Some clusters are crisp, such as dark mode requests or two-factor authentication; others are broad, and more clusters would split them. The database finds the groups in 400 tickets, or 400,000, in seconds at no cost, and the language model makes them readable: a support manager sees what customers write about without anyone reading every ticket.
Choose Between Them
| Trained model | Language model | |
|---|---|---|
| Needs | Labeled examples | A prompt |
| Speed and cost | Microseconds, free | Seconds, paid per call |
| Answers | The same every time | Can vary |
| Familiar cases | Reproduces your labels exactly | Applies its own understanding |
| New kinds of cases | Uncertain, often wrong | Usually right |
| Data it uses | Any columns, including structured data | The text in the prompt |
Conclusion
Oracle Machine Learning trains models inside the database with DBMS_DATA_MINING.CREATE_MODEL2, and embeddings make excellent features: a support vector machine on ticket embeddings matched human labels on familiar tickets instantly and for free. Use PREDICTION_PROBABILITY to route uncertain cases to a language model, compare every model with a trivial baseline, and cluster embeddings with k-means to discover topics that a language model then names.
