Oracle Machine Learning builds models inside the database and scores rows with SQL functions such as PREDICTION. A prediction is more useful when you know why it was made. PREDICTION_DETAILS returns, for one row, the attributes that weighed most in its prediction, with their values and weights, so a result can be explained to the people who act on it.
Code for This Guide
The main examples are in the examples/machine-learning folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
prediction_details(model [, class_value [, top_n]] using *) -- returns XML prediction(model using *) prediction_probability(model [, class_value] using *)
USING * passes every column of the row to the model by name. Building models needs the CREATE MINING MODEL privilege.
Training Data and a Model
ML_TICKETS describes each ticket with the traveller's loyalty tier and points, how many days ahead it was booked, the distance, the number of legs, and the traveller's age. The target is the cabin.
Example:
create or replace view ml_tickets as
select t.ticket_id,
t.cabin,
nvl(json_value(c.loyalty, '$.tier'), 'None') as tier,
nvl(json_value(c.loyalty, '$.points' returning number), 0) as points,
trunc(cast(f.scheduled_departure as date) - cast(b.booked_at as date)) as days_ahead,
r.distance_km,
(select count(*) from tickets t2 where t2.booking_id = t.booking_id) as legs,
trunc(months_between(date '2026-03-15', c.date_of_birth) / 12) as age
from tickets t
join bookings b on b.booking_id = t.booking_id
join customers c on c.customer_id = b.customer_id
join flights f on f.flight_id = t.flight_id
join routes r on r.route_id = f.route_id;
select * from ml_tickets where ticket_id <= 4;Output:
View ML_TICKETS created.
TICKET_ID CABIN TIER POINTS DAYS_AHEAD DISTANCE_KM LEGS AGE
____________ __________ _______ _________ _____________ ______________ _______ ______
1 ECONOMY Blue 3303 74 4844 1 56
4 ECONOMY Blue 3296 65 7979 1 54
3 ECONOMY Blue 3694 86 1137 2 30
2 ECONOMY Blue 3694 68 1137 2 30A decision tree learns to predict the cabin from four in five tickets; the fifth, with ticket IDs divisible by 5, is kept for testing.
Example:
declare
v_settings dbms_data_mining.setting_list;
begin
v_settings('ALGO_NAME') := 'ALGO_DECISION_TREE';
v_settings('PREP_AUTO') := 'ON';
v_settings('TREE_TERM_MAX_DEPTH') := '5';
dbms_data_mining.create_model2(
model_name => 'CABIN_CLASS',
mining_function => 'CLASSIFICATION',
data_query => 'select * from ml_tickets where mod(ticket_id, 5) <> 0',
set_list => v_settings,
case_id_column_name => 'TICKET_ID',
target_column_name => 'CABIN');
end;
/
select model_name, mining_function, algorithm, build_duration, model_size
from user_mining_models
where model_name = 'CABIN_CLASS';Output:
PL/SQL procedure successfully completed. MODEL_NAME MINING_FUNCTION ALGORITHM BUILD_DURATION MODEL_SIZE ______________ __________________ ________________ _________________ _____________ CABIN_CLASS CLASSIFICATION DECISION_TREE 1 829
Predict and Score
Example:
select ticket_id, tier, days_ahead, cabin as actual,
prediction(cabin_class using *) as predicted,
round(prediction_probability(cabin_class using *), 3) as probability,
round(prediction_probability(cabin_class, 'BUSINESS' using *), 3) as p_business
from ml_tickets
where ticket_id in (10, 395, 540, 765, 850, 1005)
order by ticket_id;Output:
TICKET_ID TIER DAYS_AHEAD ACTUAL PREDICTED PROBABILITY P_BUSINESS
____________ ___________ _____________ ___________ ____________ ______________ _____________
10 Silver 72 ECONOMY ECONOMY 1 0
395 Platinum 17 ECONOMY BUSINESS 0.83 0.83
540 Silver 3 BUSINESS ECONOMY 0.723 0.277
765 Platinum 20 BUSINESS BUSINESS 0.83 0.83
850 Platinum 26 BUSINESS ECONOMY 0.769 0.231
1005 Gold 5 ECONOMY BUSINESS 0.83 0.83
6 rows selected.PREDICTION returns the most likely cabin and PREDICTION_PROBABILITY its probability, or the probability of a named class. Tickets 540 and 850 were business but predicted economy, and 395 and 1005 the other way round.
Why Business?
Example:
select ticket_id,
xmlserialize(content prediction_details(cabin_class, 'BUSINESS', 2 using *) indent)
as details
from ml_tickets
where ticket_id = 765;Output:
TICKET_ID DETAILS
____________ ___________________________________________________________________________
765 <Details algorithm="Decision Tree" class="BUSINESS" node="5">
<Attribute name="DAYS_AHEAD" actualValue="20" weight=".29" rank="1"/>
<Attribute name="POINTS" actualValue="179303" weight=".278" rank="2"/>
</Details>For ticket 765, the tree predicts business mainly because the ticket was booked only 20 days ahead, and second because of the traveller's 179,303 loyalty points. Each attribute has a weight and a rank.
The Top Reason as a Column
Example:
-- the single strongest reason for each business prediction, as columns
select ticket_id,
xmlcast(xmlquery('/Details/Attribute[1]/@name'
passing prediction_details(cabin_class using *)
returning content) as varchar2(30)) as top_reason
from ml_tickets
where ticket_id in (395, 765, 1005);Output:
TICKET_ID TOP_REASON
____________ _____________
395 DAYS_AHEAD
765 DAYS_AHEAD
1005 DAYS_AHEADXMLQUERY reads the name of the first attribute in the details, so the top reason becomes an ordinary column for each predicted ticket. The example builds its own copy of the view and model and drops them afterward.
Things to Know
- The view ML_TICKETS and the model CABIN_CLASS are reused in other machine learning guides; drop them with DBMS_DATA_MINING.DROP_MODEL('CABIN_CLASS') and DROP VIEW when done.
- Weights are relative to the prediction for that row; compare ranks within a row, not weights across rows.
- PREDICTION_DETAILS works for classification, regression, and anomaly detection models.
Related Guides
Conclusion
PREDICTION_DETAILS explains each prediction of an Oracle Machine Learning model with the attributes that mattered most, their values, and weights. Return it as XML, or pull out the top reasons as columns, to show why a row got its prediction.
