Oracle AI Database lets your company's database not only store your data, but also think about it — predicting which customers might leave, spotting fraud the moment it happens, and finding similar documents just by meaning, not just by matching words.
Machine Learning doesn't live outside the database anymore. It lives inside it — trained on your tables, stored as database objects, and called with the same SQL you already know. 🧠🗄️
This is a long, detailed guide. Grab a coffee ☕ — by the end, you will understand not just what each feature does, but why it exists, when to reach for it, and how to avoid the mistakes that trip up most beginners.
- What Is Machine Learning Inside Oracle AI Database?
- The Full Toolbox: Every Member of the OML Family
- Big Picture — How Does It All Work?
- Step 1 — Set Up Your Environment
- Step 2 — Your First Model, Using Only SQL
- Step 3 — Let AutoML Pick the Best Model
- Step 4 — Going Deeper With OML4Py
- Step 5 — Understanding Meaning With AI Vector Search
- Step 6 — Bringing In Your Own Model (ONNX Import)
- Step 7 — Spotting Unusual Patterns (Anomaly Detection)
- Step 8 — Forecasting With Time Series Models
- Step 9 — A Real-World Enterprise Pipeline
- Enterprise Architecture
- A Named Enterprise Scenario
- Best Practices & Common Mistakes
- Real-World Use Cases
- What's New and Trending
- Quick Summary
- FAQ
🧠 What Is Machine Learning Inside Oracle AI Database?
Normally, building a Machine Learning model looks like this: you pull data out of your database, send it to a separate ML platform, train a model there, save the model somewhere else, and then build a bridge to bring predictions back into your application. 🐢
Every one of those steps adds delay, adds cost, and adds risk — because every time data moves, there is another place it could leak, another system to secure, another team that needs access.
Oracle AI Database (built on Oracle Database 23ai/26ai) flips this around entirely. It lets you train, store, evaluate, and run Machine Learning models right where your data already lives. The model becomes just another object in the database, like a table or a view. 🏠
Think of it like this 👇
- You have a table full of past loan applications, with a column showing who defaulted 📋
- You tell the database: "Learn from this history and predict who might default next"
- The database trains a model, right there, using its own engine — no data ever leaves
- You then simply run a SQL query to get predictions on brand-new applicants ✅
Imagine a librarian who doesn't just store books, but has also read every single one of them cover to cover. She has noticed patterns — which books are always borrowed together, which readers are about to stop visiting, which pages have unusual smudges suggesting damage. You don't need to ship the books to a separate expert; the librarian who already holds them can answer your questions instantly. Oracle AI Database is that librarian for your company's data. 📚
Why This Matters More Than It Sounds
A traditional ML pipeline has at least five moving parts: a data export job, a separate storage location, a training platform, a model registry, and a deployment service that serves predictions back to your app. Each part needs its own security, its own monitoring, and its own maintenance.
In-database ML collapses all five into one place. The security model is the same one already protecting your data. The "deployment" step is simply a SQL function call. This isn't just convenient — it fundamentally changes how fast a team can go from an idea to a working prediction in production.
| Aspect | Traditional External ML Pipeline | In-Database ML (Oracle AI Database) |
|---|---|---|
| Data Movement | Data is exported, copied, and re-imported multiple times | Data never leaves the database |
| Security Surface | Multiple systems, multiple access policies to manage | One system, one set of database roles and grants |
| Latency To Predict | Network hop to a separate scoring service | A single SQL function call, same latency as any query |
| Tooling Needed | Separate ML platform, model registry, deployment service | Built into the database engine you already run |
| Best For | Massive custom deep learning, novel research architectures | Classic ML on transactional/relational data, semantic search, embedded scoring |
In-database ML doesn't replace external platforms for everything. It replaces them for the huge share of enterprise use cases — classification, regression, clustering, anomaly detection, time series, and semantic search — that work directly on data already sitting in relational tables. 🚀
In-database ML is not a chatbot, and it does not generate long creative text on its own. It runs classic and modern ML algorithms directly on your tables. For natural-language generation, you pair it with OCI Generative AI — we'll show exactly how, later in this guide. 🔒
🧰 The Full Toolbox: Meet Every Member of the OML Family
Oracle Machine Learning (OML) is not one single feature — it's a family of tools, each suited to a different kind of user and a different kind of problem. Let's go through each one slowly, because picking the right tool for the job is half the battle of building a good enterprise solution.
📊 OML4SQL — Machine Learning For SQL Developers
If your team already thinks in SQL, OML4SQL lets you build, evaluate, and
use ML models using the DBMS_DATA_MINING package and simple
SQL functions like PREDICTION. No new language to learn — you
extend the SQL you already know.
🐍 OML4Py — Machine Learning For Python Developers
If your data science team prefers Python, OML4Py gives them a familiar
pandas-like interface, but every operation is transparently
translated into SQL and executed inside the database. This means you get
Python's readability with the database's raw performance and security.
📈 OML4R — Machine Learning For R Developers
The same idea as OML4Py, but for teams who prefer the R language — common in statistics-heavy industries like insurance actuarial work, clinical research, and academic-adjacent finance teams.
🤖 AutoML — Machine Learning For Everyone Else
AutoML automatically explores multiple algorithms, tunes their settings, and selects features, so someone without deep ML expertise can still get a strong, well-tuned model. Think of it as a smart assistant standing between you and dozens of manual decisions.
🧭 AI Vector Search — Machine Learning For Meaning
Vector Search doesn't predict a number or a category. It turns unstructured data — text, images, even audio — into a numeric "fingerprint" of meaning, so you can search by similarity of ideas, not just similarity of spelling.
📦 ONNX Model Import — Machine Learning From Anywhere
If your team already trained a model somewhere else — perhaps a deep learning model from Hugging Face, or a scikit-learn model from a laptop — ONNX import lets you bring that model into the database and score it using the exact same SQL functions as any native in-database model.
🕵️ Anomaly Detection & ⏱️ Time Series — Machine Learning For The Unlabeled World
Not every problem has a neat "correct answer" column to learn from. Anomaly detection learns what "normal" looks like and flags anything unusual. Time series forecasting learns from the shape of the past to project what's likely to happen next — both without needing pre-labeled outcomes.
🗺️ Big Picture — How Does It All Work?
Before we touch any code, let's follow one dataset on its full journey through Oracle AI Database:
┌────────────────┐ ┌───────────────────────┐ ┌──────────────────────────┐
│ Your Table │────►│ OML Engine (SQL / │────►│ Trained Model Stored │
│ (e.g. customer │ │ Python / R / AutoML) │ │ Inside The Database │
│ history) │ └───────────────────────┘ └────────────┬─────────────┘
└────────────────┘ │
┌─────────────▼─────────────┐
│ New Data In → SQL Query │
│ → Instant Prediction │
└────────────┬──────────────┘
│
┌────────────▼─────────────┐
│ Dashboard / App / Alert │
└──────────────────────────┘
Simple flow: Data stays in the table → Model trains right there → You query for predictions → Results flow into dashboards, alerts, or apps. 🎯
🏗️ Step 1 — Set Up Your Oracle AI Database Environment
Before we train any model, let's get our workspace ready properly. Rushing setup is the single biggest cause of confusing errors later, so we'll go slowly here. 🔬
Step 1a: Get an Oracle AI Database
- Go to cloud.oracle.com and create a free Autonomous Database instance, or
- Download Oracle Database Free (23ai/26ai) to run it on your own laptop for learning
- Either path gives you the same in-database ML engine, at no extra licensing cost ✅
Step 1b: Open an OML Notebook
It's a web page, similar to a digital notebook, where you can write SQL or Python code, run it directly against your database, and see the results appear right below it — like a school notebook that instantly checks your homework and shows you the grade. 📓
Step 1c: Install the Python Client (for OML4Py)
It installs the OML4Py client on your machine, which is the bridge that lets your Python code send instructions into the database's own ML engine. You run this once, at setup time. 🔧
pip install oml
Step 1d: Secure Your Connection Properly
Instead of typing your password directly into a script, this code reads it from an environment variable on your machine. This means the password never appears in any file you might accidentally share, upload, or commit to a code repository. 🔐
import os
import oml
db_password = os.environ["ORACLE_DB_PASSWORD"]
oml.connect(
user="ml_user",
password=db_password,
dsn="mydb_high"
)
print("✅ Connected securely to Oracle AI Database.")
Never hardcode your database password directly in a notebook you plan to share or commit to Git. Use environment variables, a wallet file, or a secure secrets manager instead. 🔒
📊 Step 2 — Your First Machine Learning Model, Using Only SQL
This is the magic part. You don't need Python, R, or any external tool.
You can train a real ML model using plain SQL, through a built-in package
called DBMS_DATA_MINING. 🪄
Step 2a: Understand Your Algorithm Choices First
Before training anything, it helps to know what algorithms are available for classification — predicting a category, like churned or not churned. Oracle AI Database gives you several, and picking the right one matters for both accuracy and interpretability.
| Algorithm | Best For | Trade-off |
|---|---|---|
| Decision Tree | Easy-to-explain rules for business stakeholders | Can overfit on noisy data if left unpruned |
| Random Forest | Strong accuracy on messy, real-world data | Harder to explain a single decision path |
| Generalized Linear Model (GLM) | Fast training, clear coefficients, regulatory reporting | Weaker with highly non-linear relationships |
| Support Vector Machine (SVM) | High-dimensional data, text-derived features | Slower to train on very large datasets |
| Naive Bayes | Very fast baseline, works well with sparse text data | Assumes features are independent, which is rarely fully true |
You don't have to memorize this table before starting. Oracle AI Database lets you either pick an algorithm explicitly, or simply let AutoML try several and report back the winner — which we'll cover in Step 3. This table is here so that once you have a working model, you understand why it made the choices it did. 🧭
Step 2b: Train Your First Model
This code tells the database: "Look at this table of past customers, learn from the column that shows who churned before, and build a model that predicts future churn." You don't write any ML math — the database chooses a sensible default algorithm and settings for you. 🎯
BEGIN
DBMS_DATA_MINING.CREATE_MODEL2(
model_name => 'CHURN_PREDICTOR',
mining_function => 'CLASSIFICATION',
data_query => 'SELECT * FROM customer_history',
case_id_column_name => 'customer_id',
target_column_name => 'churned'
);
END;
/
Output:
PL/SQL procedure successfully completed. Model CHURN_PREDICTOR created and stored inside the database.
That's it — you just trained a Machine Learning model using nothing but SQL! 🎉
Step 2c: Choose A Specific Algorithm On Purpose
This code trains the same kind of model, but this time we deliberately choose the Random Forest algorithm, and we set a few of its settings ourselves — similar to choosing a specific recipe instead of letting the chef decide for you. 👨🍳
DECLARE
v_setlist DBMS_DATA_MINING.SETTING_LIST;
BEGIN
v_setlist('ALGO_NAME') := 'ALGO_RANDOM_FOREST';
v_setlist('RFOR_NUM_TREES') := '100';
v_setlist('PREP_AUTO') := 'ON'; -- let the database auto-prepare the data
DBMS_DATA_MINING.CREATE_MODEL2(
model_name => 'CHURN_PREDICTOR_RF',
mining_function => 'CLASSIFICATION',
data_query => 'SELECT * FROM customer_history',
case_id_column_name => 'customer_id',
target_column_name => 'churned',
model_settings => v_setlist
);
END;
/
Output:
PL/SQL procedure successfully completed. Model CHURN_PREDICTOR_RF created using 100 decision trees.
Step 2d: Get Predictions With a Simple SELECT
This code asks the trained model to look at brand-new customers and predict, for each one, whether they are likely to churn — right inside a normal SELECT query. 🔮
SELECT customer_id,
PREDICTION(CHURN_PREDICTOR_RF USING *) AS predicted_churn,
PREDICTION_PROBABILITY(CHURN_PREDICTOR_RF USING *) AS confidence
FROM new_customers;
Output:
CUSTOMER_ID PREDICTED_CHURN CONFIDENCE ----------- --------------- ---------- 1042 1 0.87 1043 0 0.95 1044 1 0.79
Customer 1042 is very likely to leave (87% confidence) — now your business can act before they walk away, not after. 📉➡️📈
Step 2e: Check If Your Model Is Actually Good
A model that runs without errors is not the same as a model that is accurate. Before trusting predictions in production, always check quality metrics like a confusion matrix, which shows how often the model was right versus wrong, broken down by category.
This code compares the model's predictions on a held-out test set against what actually happened, and builds a confusion matrix — a small table showing correct predictions versus mistakes, split by category. Think of it as a report card for the model. 📝
BEGIN
DBMS_DATA_MINING.COMPUTE_CONFUSION_MATRIX(
accuracy => :model_accuracy,
apply_result_table_name => 'CHURN_PREDICTIONS',
target_table_name => 'customer_test_actuals',
case_id_column_name => 'customer_id',
target_column_name => 'churned',
confusion_matrix_table_name => 'CHURN_CONFUSION_MATRIX'
);
END;
/
SELECT * FROM CHURN_CONFUSION_MATRIX;
Output:
ACTUAL_VALUE PREDICTED_VALUE COUNT ------------ --------------- ----- 1 1 182 1 0 21 0 1 14 0 0 733
Out of 203 customers who actually churned, the model correctly caught 182 of them — that's roughly 90% recall on the group you care about most. This is the kind of number you should always check before shipping a model to production. 📊
Always evaluate a model on data it has never seen during training, called a test set. A model can look perfect on the data it learned from and still fail badly on new, real-world data. 🧪
🤖 Step 3 — Let AutoML Pick The Best Model For You
Choosing the right algorithm and settings by hand, like we did manually above, can take days of trial and error for a data scientist. AutoML automates that entire search — trying different algorithms, tuning their settings, and picking the best-performing one. 🏆
Step 3a: Run Automated Algorithm Selection
This Python code connects to the database, points to a table of historical data, and asks OML4Py's AutoML feature to automatically explore multiple algorithms and settings, then hand back the single best model it found — along with its accuracy score. 🎯
import oml
from oml import automl
# Connect this Python session to your Oracle AI Database
oml.connect(user="ml_user", password=db_password,
dsn="mydb_high", automl=True)
# Point to the training table already sitting inside the database
train_data = oml.sync(table="customer_history")
# Ask AutoML to automatically find the best classification model
model_selection = automl.ModelSelection(
mining_function="classification",
score_metric="f1_macro" # good metric for imbalanced churn data
)
best_model = model_selection.select(
train_data, case_id="customer_id", target="churned"
)
print("Best algorithm chosen:", best_model.algorithm_name)
print("F1 score achieved:", best_model.score)
Output:
Best algorithm chosen: Gradient Boosted Trees F1 score achieved: 0.91
If only 15% of customers actually churn, a lazy model could just guess "never churns" and still be 85% "accurate" while being completely useless. F1 score balances catching real churners against avoiding false alarms, which is why it's a safer choice for imbalanced business problems like this one. ⚖️
Step 3b: Let AutoML Also Pick The Best Features
Real tables often have dozens of columns, but not all of them actually help the model. This code asks AutoML to rank which columns matter most, so you can drop the noisy, unhelpful ones and train a leaner, faster, often more accurate model. ✂️
from oml import automl
fs = automl.FeatureSelection(
mining_function="classification",
score_metric="f1_macro"
)
selected_features = fs.reduce(
train_data, case_id="customer_id", target="churned"
)
print("Top features selected by AutoML:")
print(selected_features.columns)
Output:
Top features selected by AutoML: ['monthly_spend', 'support_tickets_last_90d', 'contract_type', 'tenure_months']
Out of maybe 40 original columns, AutoML narrowed it down to the 4 that actually matter — saving both training time and future maintenance headaches. 🧹
🐍 Step 4 — Going Deeper With OML4Py
OML4Py isn't just a way to call AutoML from Python. It gives you proxy objects that behave like familiar pandas DataFrames, but every operation you perform on them is silently translated into SQL and executed inside the database — meaning you never actually pull the full dataset into your laptop's memory.
This code creates a proxy object pointing at a huge database table, filters it using familiar Python syntax, and groups it to compute averages — but none of that data actually gets copied to your machine. All the heavy lifting happens inside the database itself. 🏋️
import oml
customers = oml.sync(table="customer_history")
# This filter and groupby run INSIDE the database, not in your laptop's RAM
high_risk = customers[customers["support_tickets_last_90d"] > 3]
summary = high_risk.groupby("contract_type").agg({"monthly_spend": "mean"})
print(summary.pull()) # .pull() brings only the small summary result to Python
Output:
CONTRACT_TYPE MONTHLY_SPEND ------------- ------------- Monthly 42.10 Annual 67.85
Only call
.pull() on small, already-summarized results.
Pulling an entire multi-million row table into Python memory defeats the
whole purpose of in-database computation. 📏
🧭 Step 5 — Understanding Meaning With AI Vector Search
Normal search finds rows that match exact words. AI Vector Search finds rows that match meaning, even if the words are completely different. It works by turning text into a list of numbers, called a vector, which captures the idea behind the words. 🔢
Imagine every sentence gets its own unique GPS coordinate based on its meaning. Sentences with similar meaning end up close together on the map, even if they don't share a single word in common. "My wifi keeps disconnecting" and "unstable internet connection" would land near each other on this meaning-map, even though they share zero exact words. 🗺️
| Aspect | Traditional Keyword Search | AI Vector Search |
|---|---|---|
| Matches On | Exact or fuzzy text patterns | Semantic meaning of the content |
| Handles Synonyms? | Poorly, unless manually configured | Naturally, since meaning is preserved |
| Best For | Exact IDs, known codes, precise filters | Support tickets, documents, RAG retrieval |
| Can Combine With Filters? | Yes, natively | Yes — Oracle lets you mix WHERE clauses with vector distance |
Step 5a: Create a Vector Column
This code creates a table with a special new column type called
VECTOR, which is designed specifically to hold these
meaning-based number lists. 📐
CREATE TABLE support_articles ( article_id NUMBER PRIMARY KEY, article_text CLOB, category VARCHAR2(50), article_vector VECTOR(384, FLOAT32) );
Step 5b: Generate a Vector From Text
This code calls an embedding model already loaded into the database, and asks it to turn one plain sentence into a vector — a long list of numbers representing its meaning. 🧮
SELECT VECTOR_EMBEDDING(all_minilm_l12_v2 USING 'My internet connection keeps dropping' AS data)
AS my_vector;
Output:
MY_VECTOR ------------------------------------------------------ [-0.0386, 0.0728, -0.0070, -0.0073, 0.0088, ... ]
That long list of numbers is the "fingerprint" of the sentence's meaning! 🧬
Step 5c: Search By Meaning, Not Just Keywords
This code takes a new customer question, turns it into a vector on the fly, and finds the 3 support articles whose meaning is closest to it — even if none of the exact words match. 🔍
SELECT article_id, article_text
FROM support_articles
ORDER BY VECTOR_DISTANCE(
article_vector,
VECTOR_EMBEDDING(all_minilm_l12_v2 USING 'wifi keeps disconnecting' AS data),
COSINE
)
FETCH FIRST 3 ROWS ONLY;
Output:
ARTICLE_ID ARTICLE_TEXT ---------- ---------------------------------------- 201 How to fix an unstable internet connection 198 Troubleshooting router disconnections 215 Resetting your home network device
Notice none of these articles contain the exact words "wifi keeps disconnecting" — yet the database found them anyway, because it searched by meaning. 🤯
Step 5d: Combine Vector Search With Normal Filters (Hybrid Search)
This code does two things at once: it filters to only "Networking" category articles using a normal WHERE clause, and then ranks those filtered results by meaning-similarity. This is called hybrid search — combining exact filters with fuzzy meaning matching. 🧩
SELECT article_id, article_text
FROM support_articles
WHERE category = 'Networking'
ORDER BY VECTOR_DISTANCE(
article_vector,
VECTOR_EMBEDDING(all_minilm_l12_v2 USING 'wifi keeps disconnecting' AS data),
COSINE
)
FETCH FIRST 3 ROWS ONLY;
Output:
ARTICLE_ID ARTICLE_TEXT ---------- ---------------------------------------- 201 How to fix an unstable internet connection 198 Troubleshooting router disconnections
Step 5e: Speed Things Up With A Vector Index
On a small table, scanning every row for the closest vector is fine. But on millions of rows, that gets slow. This code builds a special vector index, so the database can jump almost straight to the closest matches instead of checking every single row one by one. ⚡
CREATE VECTOR INDEX support_articles_vec_idx ON support_articles (article_vector) ORGANIZATION NEIGHBOR PARTITIONS DISTANCE COSINE WITH TARGET ACCURACY 95;
A "target accuracy" of 95 means the index will return the correct closest matches about 95% of the time, in exchange for much faster search. For most enterprise search use cases, this trade-off is more than worth it. For legal or medical use cases needing exact recall, you might choose a higher target accuracy instead. ⚖️
📦 Step 6 — Bringing In Your Own Model (ONNX Import)
Sometimes you already have a model trained elsewhere — maybe from Hugging Face, or built by your data science team using scikit-learn or another framework. Oracle AI Database lets you import that model in ONNX format and run it right inside the database. 📥
ONNX is like a universal translator for ML models. No matter which tool a model was originally built in — PyTorch, TensorFlow, scikit-learn — converting it to ONNX format means Oracle AI Database can understand and run it using its built-in ONNX Runtime. 🌐
Step 6a: Import A Traditional ML Model
This code loads an ONNX model file (perhaps a scikit-learn classifier you already trained outside the database) into Oracle AI Database, giving it a name so you can call it later using the same scoring operators as any in-database model. 🏷️
BEGIN
DBMS_DATA_MINING.IMPORT_ONNX_MODEL(
model_name => 'MY_CUSTOM_MODEL',
file_path => 'MODEL_DIR',
file_name => 'my_model.onnx',
onnx_metadata => JSON('{"functionType":"classification"}')
);
END;
/
Output:
PL/SQL procedure successfully completed. Model MY_CUSTOM_MODEL is now available for scoring.
DBMS_VECTOR.LOAD_ONNX_MODEL as a simpler, more direct
path specifically for embedding models — worth checking your database
version's documentation, since both paths are valid depending on what
you're importing and which release you're running.
Step 6b: Import A Transformer Model For Embeddings
OML4Py can automatically fetch a text transformer model from Hugging Face, add the small pre-processing and post-processing steps it needs, convert it to ONNX format, and hand you a file ready to import — all in a few lines. 🤗
from oml import onnx_pipeline
# Download and convert a Hugging Face embedding model to ONNX automatically
onnx_pipeline.embedding_model_export(
model_name="sentence-transformers/all-MiniLM-L12-v2",
output_path="all_minilm_l12_v2.onnx"
)
print("✅ Model converted to ONNX and ready to import into the database.")
Output:
✅ Model converted to ONNX and ready to import into the database.
Step 6c: Common ONNX Import Errors, And What They Mean
| Error You Might See | What It Usually Means |
|---|---|
| ORA-XXXXX: unsupported ONNX operator | The model uses a layer type not yet supported by the in-database ONNX Runtime; try a simpler or older model architecture |
| Metadata mismatch on functionType | The JSON metadata you supplied doesn't match what the model was actually trained to do; double check classification vs regression vs embedding |
| File not found in directory object | The database directory object doesn't actually point to where the .onnx file was uploaded; verify with a directory listing query |
🕵️ Step 7 — Spotting Unusual Patterns (Anomaly Detection)
Some problems don't need a "yes or no" prediction — they need a "does this look weird?" check. Anomaly Detection learns what normal looks like, then flags anything unusual, like a security guard who notices when something is out of place. 🚨
Step 7a: Train An Anomaly Detection Model
This code trains an anomaly detection model purely on normal, historical transaction data — notice there is no "fraud" label column at all, because anomaly detection learns the shape of "normal" on its own, without needing pre-labeled examples of bad behavior. 🔍
BEGIN
DBMS_DATA_MINING.CREATE_MODEL2(
model_name => 'FRAUD_DETECTOR',
mining_function => 'ANOMALY_DETECTION',
data_query => 'SELECT * FROM past_transactions'
);
END;
/
Step 7b: Flag Suspicious New Transactions
SELECT transaction_id,
PREDICTION(FRAUD_DETECTOR USING *) AS is_anomaly
FROM new_transactions
WHERE PREDICTION(FRAUD_DETECTOR USING *) = 1;
Output:
TRANSACTION_ID IS_ANOMALY -------------- ---------- 88213 1 88399 1
Two suspicious transactions flagged automatically, ready for a human to double-check! ⚠️
⏱️ Step 8 — Forecasting The Future With Time Series Models
Anomaly detection asks "is this weird?" Time series forecasting asks a different question: "based on the past, what's likely to happen next?" It's the tool behind demand planning, sales forecasting, and capacity planning.
This code trains a forecasting model on two years of daily sales history, then asks it to project sales for the next 30 days — automatically detecting patterns like weekly cycles and overall trend direction. 📈
BEGIN
DBMS_DATA_MINING.CREATE_MODEL2(
model_name => 'SALES_FORECASTER',
mining_function => 'TIME_SERIES',
data_query => 'SELECT sales_date, daily_revenue FROM daily_sales_history',
case_id_column_name => 'sales_date',
target_column_name => 'daily_revenue'
);
END;
/
SELECT * FROM DM$VPSALES_FORECASTER
WHERE sales_date > SYSDATE
ORDER BY sales_date
FETCH FIRST 5 ROWS ONLY;
Output:
SALES_DATE FORECAST_VALUE LOWER_BOUND UPPER_BOUND ----------- -------------- ----------- ----------- 2026-07-06 48,230 44,100 52,360 2026-07-07 51,940 47,500 56,380 2026-07-08 49,870 45,600 54,140
Notice the model gives you a range, not just one number — that upper and lower bound tells the business how confident to be in the forecast, which matters a lot for planning decisions. 📊
🏭 Step 9 — Building a Real-World Enterprise Pipeline
Let's now combine everything into one production-style scenario, closer to what a real engineering team would ship. Imagine a bank wants to check every new transaction, flag fraud risk, log every decision for audit purposes, and alert a human team when something urgent appears. 🏦
This Python script:
- Connects to Oracle AI Database using OML4Py
- Scores new transactions using the fraud model we built earlier
- Logs every scoring decision, including a timestamp, for compliance audits
- Saves only the flagged, suspicious transactions into an alerts table for staff review
- Handles errors gracefully instead of silently crashing
import oml
import datetime
try:
# Connect to the database (the model already lives inside it)
oml.connect(user="ml_user", password=db_password, dsn="mydb_high")
# Point to the new transactions waiting to be checked
new_data = oml.sync(table="new_transactions")
# Run the fraud detection model against the new data
scored = oml.predict(model_name="FRAUD_DETECTOR", data=new_data)
# Add an audit timestamp column before saving anything
scored["scored_at"] = datetime.datetime.utcnow().isoformat()
# Log every single scoring decision for compliance, not just the flagged ones
scored.persist(table="fraud_scoring_audit_log", overwrite=False)
# Filter only the suspicious ones for the human review queue
flagged = scored[scored["IS_ANOMALY"] == 1]
flagged.persist(table="fraud_alerts", overwrite=True)
print(f"✅ {len(flagged)} suspicious transactions flagged and saved.")
print(f"📝 {len(scored)} total transactions logged for audit.")
except Exception as error:
# In production, this would also trigger a monitoring alert to the on-call engineer
print(f"❌ Pipeline failed: {error}")
Output:
✅ 2 suspicious transactions flagged and saved. 📝 350 total transactions logged for audit.
A complete, auditable fraud-detection loop — running entirely inside your database! 🏁
🏛️ Enterprise Architecture — Where In-Database ML Fits
A real enterprise AI system is a team effort, and Oracle AI Database is one of its strongest players. Let's meet the team:
- 🚪 Application Layer — the app your users actually click on
- 🗄️ Oracle AI Database — stores data, trains ML models, runs predictions, and holds vector embeddings, all in one place
- ✍️ OCI Generative AI — writes natural-sounding replies using context retrieved through vector search
- ⚡ OCI Functions / APEX — glue code and low-code apps that call SQL predictions directly
- 📊 Analytics Dashboards — visualize predictions and flagged anomalies for business teams
[ New Data Arrives ] → [ Oracle AI Database: Train / Score / Embed ]
→ [ Predictions + Vector Matches Stored In Same DB ]
→ [ OCI Generative AI drafts a smart, context-aware answer ]
→ [ Dashboard / App Shows Result To The Business User ]
This is exactly why in-database ML is trending — it removes the slow, risky step of copying sensitive data out to a separate ML platform, while still letting you plug in Generative AI for the parts that truly need natural language generation. ⚙️
🏢 A Named Enterprise Scenario: "MidCity Bank"
Let's make this concrete with a realistic (fictional) example. MidCity Bank processes around 2 million transactions per day. Before adopting in-database ML, their fraud detection pipeline worked like this:
- Transactions exported nightly to a separate data lake (a 6-hour delay before fraud could even be checked)
- A third-party ML platform scored the batch, taking another 2 hours
- Results were re-imported into the core banking database the next morning
- By the time fraud was flagged, the money was often already gone 😟
After moving fraud scoring in-database, their new pipeline looked like this:
- Every transaction is scored the instant it is written to the transactions table
- Suspicious transactions are flagged within roughly 200 milliseconds
- The fraud team receives alerts before the transaction even finishes settling
- Estimated fraud losses dropped by roughly 35% in the first quarter after launch
The improvement here wasn't a smarter algorithm — it was removing the multi-hour delay caused by moving data between systems. Sometimes the biggest ML win in an enterprise isn't a better model; it's simply running the same model closer to where the data already lives. ⏱️
🏆 Best Practices
- 🎯 Start with AutoML — let it suggest a strong baseline model before you hand-tune anything
- 📐 Keep data in the database — avoid exporting sensitive tables to external tools unless truly necessary
- 🧮 Pick the right vector size — smaller embedding models are faster and cheaper; only go bigger if accuracy demands it
- 🗂️ Index your vector columns — use vector indexes for large tables so similarity search stays fast
- 🔁 Retrain periodically — customer behavior changes over time, so refresh your models on a schedule
- 📝 Log every scoring decision — for regulated industries, an audit trail matters as much as the prediction itself
- 🔐 Use database roles and wallets — never hardcode passwords in notebooks or scripts
- 🧪 Always check a confusion matrix or error metric — before trusting a model in production
- Do NOT skip evaluating model accuracy before trusting it in production
- Do NOT forget that vectors take real storage space — plan capacity for large datasets
- Do NOT treat anomaly flags as final verdicts — always keep a human review step for high-stakes decisions
- Do NOT ignore data lineage — Oracle 23ai/26ai tracks the query used to build a model, so use it for audits
- Do NOT use accuracy as your only metric on imbalanced data — prefer F1, precision, or recall where appropriate
🌍 Real-World Use Cases
- 🏦 Banking & Finance: Real-time fraud detection and credit risk scoring, all inside the transactional database
- 🏥 Healthcare: Predicting patient readmission risk directly from clinical records
- 🛒 Retail & E-Commerce: Product recommendation and demand forecasting using time series models
- 📞 Customer Support: Vector search over old tickets to instantly find similar past issues and solutions
- 🏭 Manufacturing: Anomaly detection on sensor data to predict equipment failure before it happens
- 📄 Legal & Compliance: Semantic search across huge contract archives using AI Vector Search
🚀 What's New and Trending
- Vectors As First-Class Citizens — the native
VECTORdata type now works directly with in-database ML algorithms, so vectors can be predictors, not just search targets - Wide Tables For ML — tables now support up to 4096 columns, making room for far richer feature sets without complex workarounds
- ONNX As The Universal Bridge — Hugging Face transformer models can be converted and imported directly, blending "bring your own model" with in-database scoring
- Model Lineage & Governance — in-database models now track the exact data query used to build them, supporting audit and compliance needs
- RAG, Powered By The Database Itself — Retrieval-Augmented Generation pipelines increasingly use Oracle AI Vector Search as the retrieval layer feeding OCI Generative AI
📝 Quick Summary — What We Learned
- What Oracle AI Database ML is → Machine Learning that trains and runs directly inside the database engine
- OML4SQL → Build and use models with plain SQL through
DBMS_DATA_MINING, and evaluate them with confusion matrices - OML4Py → Use Python syntax while computation still happens inside the database, using
.pull()sparingly - AutoML → Automatically finds the best algorithm, tunes it, and even selects the best features
- AI Vector Search → Search by meaning using the native
VECTORdata type, sped up with vector indexes, combinable with normal filters - ONNX Import → Bring in models trained elsewhere, including automatically converted Hugging Face transformers
- Anomaly Detection → Automatically flag unusual, potentially risky patterns without needing labeled examples
- Time Series → Forecast future values with confidence ranges, not just single numbers
- Enterprise Pattern → Keep data and models together in the database, log every decision for audit, and call OCI Generative AI only for the natural-language layer on top
❓ Troubleshooting & Frequently Asked Questions
This usually means the row has missing values in a column the model relies on heavily, or the row falls outside the range of data the model was trained on. Check for NULLs in your input columns first.
Most core OML features, including OML4SQL and AI Vector Search, are available on Oracle Database Free, which is great for learning and small projects before moving to production.
Confirm you've actually created a vector index on that column, as shown in Step 5e. Without an index, every query does a full scan comparing against every row.
If your team is SQL-first, or the model will be called mostly from application queries, OML4SQL keeps everything simple. If your data science team already works in Python notebooks and wants pandas-style exploration before training, OML4Py is the more natural fit. Both ultimately train and store the same kind of in-database model.
AutoML is an excellent starting point and often matches or beats manual tuning. However, for regulated industries needing a specific, explainable algorithm, like GLM for certain lending decisions, you may still need to choose the algorithm manually to satisfy compliance requirements, even if AutoML would have picked something more accurate.
Happy building! 🗄️✨
Comments
Post a Comment