Imagine if your company's entire Oracle database could answer questions like:
"Which products sold the most last quarter?" — just by you typing that sentence.
No SQL. No coding. No technical team needed. Just plain English. 🗣️
💡 Did You Know?
According to Oracle's enterprise surveys, over 70% of business users who need data insights do not know SQL — they rely entirely on technical teams.
Select AI is Oracle's answer to eliminating that bottleneck forever.
According to Oracle's enterprise surveys, over 70% of business users who need data insights do not know SQL — they rely entirely on technical teams.
Select AI is Oracle's answer to eliminating that bottleneck forever.
What Is OCI Select AI? (The Burger Shop Analogy)
Let's start with a story. 🍔
Imagine you walk into a burger shop and say: "Give me something spicy, not too heavy, under 300 rupees."
The cashier understands you perfectly and brings exactly what you want — no form to fill, no menu code to enter.
Now imagine doing the same thing with your company's Oracle database.
You type: "Show me the top 5 customers by revenue this month" — and the database
just understands you and returns the answer. 🎯
OCI Select AI is that cashier for your Oracle Autonomous Database.
It is a feature built directly inside Oracle's Autonomous Database that lets
you query your data using plain, everyday language.
✅ Official Simple Definition:
OCI Select AI = A built-in feature of Oracle Autonomous Database that uses Large Language Models (LLMs) to convert your natural language questions into SQL queries — and returns real answers from your actual database. 🗄️
OCI Select AI = A built-in feature of Oracle Autonomous Database that uses Large Language Models (LLMs) to convert your natural language questions into SQL queries — and returns real answers from your actual database. 🗄️
Why Did Oracle Build Select AI? (The Context)
Let's understand the real problem Oracle was solving. 🤔
In most companies, there are two types of people:
Business people — who have all the questions ("Why are our sales dropping?")
Technical people — who know SQL ("SELECT SUM(revenue) FROM sales...")
Getting answers used to take days. Business people submitted requests. Technical people wrote SQL. Results came back. Business people had more questions. Repeat. 🔄
❌ The Old World Problem:
A business manager waits 3 days for a "simple" data question to be answered.
A technical team spends hours writing SQL for questions that change every day.
Decisions get delayed. Opportunities are missed. Costs go up. 📉
A business manager waits 3 days for a "simple" data question to be answered.
A technical team spends hours writing SQL for questions that change every day.
Decisions get delayed. Opportunities are missed. Costs go up. 📉
Oracle's answer : remove the middleman entirely.
Let the AI translate human language into SQL — instantly, accurately, safely.
OLD WAY (Without Select AI):
┌──────────────┐ 3 days ┌──────────────┐ Query runs ┌──────────┐
│ Business Mgr │ ─────────────────► │ Dev / DBA │ ───────────────────► │ Database │
│ "Why sales │ Ticket raised │ Writes SQL │ SELECT SUM(...) │ │
│ dropped?" │ │ │ │ │
└──────────────┘ └──────────────┘ └──────────┘
NEW WAY (With Select AI):
┌──────────────┐ Seconds! ┌──────────────────────────────────────┐
│ Business Mgr │ ────────────────► │ Select AI (LLM + Autonomous DB) │
│ "Why sales │ Natural English │ → Understands question │
│ dropped?" │ │ → Generates SQL automatically │
└──────────────┘ │ → Runs on your real data │
│ → Returns human-readable answer │
└──────────────────────────────────────┘
📊 SQL vs Select AI — Side by Side
Let's see the same question answered both ways, so you can feel the difference.
QUESTION: "Which sales rep brought in the most revenue this quarter?"
──────────────────────────────────────────────────────────
❌ TRADITIONAL SQL (you need to know the database schema):
──────────────────────────────────────────────────────────
SELECT
e.employee_name,
SUM(o.order_amount) AS total_revenue
FROM
employees e
JOIN orders o ON e.employee_id = o.sales_rep_id
JOIN order_dates d ON o.order_id = d.order_id
WHERE
d.order_date BETWEEN TRUNC(SYSDATE, 'Q') AND SYSDATE
GROUP BY
e.employee_name
ORDER BY
total_revenue DESC
FETCH FIRST 1 ROW ONLY;
──────────────────────────────────────────────────────────
✅ SELECT AI (just ask in plain English):
──────────────────────────────────────────────────────────
SELECT AI
Which sales rep brought in the most revenue this quarter?
→ Oracle AI figures out the tables, joins, dates, and grouping FOR YOU.
→ Returns: "Rahul Sharma — ₹4.2 Crores this quarter" ✅
💡 Key Insight:
Select AI does not replace SQL — it generates SQL for you behind the scenes.
The AI is basically a very smart SQL writer that has read your database's entire structure.
You ask in English. It writes SQL. Your database runs the SQL. You get answers. 🎯
Select AI does not replace SQL — it generates SQL for you behind the scenes.
The AI is basically a very smart SQL writer that has read your database's entire structure.
You ask in English. It writes SQL. Your database runs the SQL. You get answers. 🎯
🏗️ How Select AI Works Internally (The Full Architecture)
Now let's peek inside the engine. 🔧
Think of Select AI like a postal system — your question goes through several checkpoints
before the answer comes back to you.
┌─────────────────────────────────────────────────────────────────────────────────┐
│ OCI SELECT AI — INTERNAL ARCHITECTURE │
└─────────────────────────────────────────────────────────────────────────────────┘
STEP 1: You type your question
┌───────────────────────────────────────────┐
│ User: "Show top 10 orders from Mumbai" │
└─────────────────────┬─────────────────────┘
│
▼
STEP 2: Autonomous Database receives the SELECT AI statement
┌───────────────────────────────────────────────────────────────────┐
│ Oracle Autonomous Database (ADB) │
│ → Intercepts the SELECT AI keyword │
│ → Retrieves your AI Profile settings │
│ → Knows which LLM provider to call (OCI GenAI / OpenAI / etc.) │
└───────────────────────────────┬───────────────────────────────────┘
│
▼
STEP 3: Schema metadata is collected (the "context")
┌───────────────────────────────────────────────────────────────────┐
│ ADB collects metadata about your tables: │
│ → Table names: ORDERS, CUSTOMERS, PRODUCTS │
│ → Column names: ORDER_ID, CITY, AMOUNT, ORDER_DATE │
│ → Data types, relationships, sample values │
│ → Any custom table/column descriptions you added │
└───────────────────────────────┬───────────────────────────────────┘
│
▼
STEP 4: A rich prompt is sent to the LLM
┌───────────────────────────────────────────────────────────────────┐
│ OCI Generative AI Service (or other configured LLM) │
│ Receives a carefully built prompt containing: │
│ → Your natural language question │
│ → Your database schema information │
│ → Oracle SQL dialect instructions │
│ → Any special rules you configured │
└───────────────────────────────┬───────────────────────────────────┘
│
▼
STEP 5: LLM generates SQL
┌───────────────────────────────────────────────────────────────────┐
│ LLM Response: │
│ SELECT c.city, COUNT(o.order_id) as order_count, │
│ SUM(o.amount) as total_amount │
│ FROM orders o JOIN customers c ON o.cust_id = c.cust_id │
│ WHERE c.city = 'Mumbai' │
│ ORDER BY total_amount DESC FETCH FIRST 10 ROWS ONLY │
└───────────────────────────────┬───────────────────────────────────┘
│
▼
STEP 6: ADB runs the SQL on your actual data
STEP 7: Results returned to user in the chosen mode (table / narrative / chat)
🔵 Architecture Key Point:
Your actual data NEVER leaves your Autonomous Database.
Only the schema structure (table/column names) is sent to the LLM — not your real records.
The LLM just writes the SQL. The SQL runs inside your secure ADB. Data stays safe. 🔐
Your actual data NEVER leaves your Autonomous Database.
Only the schema structure (table/column names) is sent to the LLM — not your real records.
The LLM just writes the SQL. The SQL runs inside your secure ADB. Data stays safe. 🔐
OCI Generative AI Services Overview
Before we go deeper into Select AI, let's understand one of its key power sources: OCI Generative AI Service. 🧠
Think of OCI Generative AI Service as Oracle's "AI brain rental service".
Oracle has partnered with and built their own Large Language Models —
powerful AI systems that understand and generate human language.
OCI Generative AI — Models Available: ┌─────────────────────────────┬───────────────────────────────────────────────┐ │ Model │ Best Used For │ ├─────────────────────────────┼───────────────────────────────────────────────┤ │ cohere.command-r-plus │ Complex reasoning, long documents, SQL gen │ │ cohere.command-r │ Fast responses, everyday queries, chatbots │ │ meta.llama-3-70b-instruct │ Open-source, high accuracy, coding tasks │ │ meta.llama-3-8b-instruct │ Lightweight, fast, low cost │ │ cohere.embed-english-v3 │ Converting text to vectors (semantic search) │ │ cohere.embed-multilingual │ Multi-language vector embeddings │ └─────────────────────────────┴───────────────────────────────────────────────┘ Select AI can use ANY of these models — you choose based on your needs!
💡 Recommendation:
For Select AI SQL generation, use cohere.command-r-plus — it has the best understanding of structured data, complex joins, and Oracle SQL syntax.
For cost optimization with simpler queries, meta.llama-3-8b-instruct is excellent.
For Select AI SQL generation, use cohere.command-r-plus — it has the best understanding of structured data, complex joins, and Oracle SQL syntax.
For cost optimization with simpler queries, meta.llama-3-8b-instruct is excellent.
👤 AI Profiles (Your AI's Identity Card)
Before Select AI can do anything, you need to create an AI Profile.
Think of an AI Profile as an identity card for your AI setup — it tells
Select AI everything it needs to know: which AI model to use, which tables to access,
and any special rules to follow. 🪪
Without a profile, Select AI has no idea who you are, which database to look at, or which AI model to talk to. The profile is the central configuration hub.
AI PROFILE = A named configuration object stored inside Autonomous Database ┌─────────────────────────────────────────────────┐ │ AI Profile: "SALES_ANALYTICS_PROFILE" │ │ │ │ provider = 'oci' │ │ credential = 'OCI_GENAI_CREDENTIAL' │ │ model = 'cohere.command-r-plus' │ │ object_list = [ORDERS, CUSTOMERS, PRODUCTS] │ ← which tables to use │ temperature = 0 │ ← 0 = precise, 1 = creative │ max_tokens = 4000 │ └─────────────────────────────────────────────────┘ One database can have MULTIPLE AI Profiles: → SALES_ANALYTICS_PROFILE (for sales team) → HR_REPORTS_PROFILE (for HR department) → FINANCE_DASHBOARD_PROFILE (for CFO office)
💻 DBMS_CLOUD_AI Package (The Control Center)
Now let's look at the actual Oracle package you use to set everything up.
DBMS_CLOUD_AI is a built-in Oracle PL/SQL package — think of it as
a "remote control" for all Select AI operations. 📺
You use it to create profiles, set your active profile, and generate responses.
Let's walk through each operation step by step.
🗒️ What the code below does — Read this first!
This first piece of code creates a credential — a secure "password container" that stores your OCI API key so Oracle can call the OCI Generative AI Service on your behalf.
Think of it like giving Oracle a key to enter the AI building. 🔑
You do this once, and Oracle safely stores it inside the database vault.
This first piece of code creates a credential — a secure "password container" that stores your OCI API key so Oracle can call the OCI Generative AI Service on your behalf.
Think of it like giving Oracle a key to enter the AI building. 🔑
You do this once, and Oracle safely stores it inside the database vault.
-- ============================================================
-- STEP 1: Create a Credential to connect to OCI GenAI Service
-- ============================================================
-- This stores your OCI API access details securely inside ADB.
-- Replace the values with your actual OCI tenancy details.
-- ============================================================
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'OCI_GENAI_CREDENTIAL', -- a name you choose
user_ocid => 'ocid1.user.oc1..aaaaaa...your-user-ocid',
tenancy_ocid => 'ocid1.tenancy.oc1..aaaaa...your-tenancy-ocid',
private_key => '-----BEGIN RSA PRIVATE KEY-----
MIIEowIBAAKCAQEA...your-private-key-here...
-----END RSA PRIVATE KEY-----',
fingerprint => 'aa:bb:cc:dd:ee:ff:00:11:22:33:44:55:66:77:88:99'
);
END;
/
🗒️ What the code below does — Read this first!
Now we create the actual AI Profile — the identity card we talked about.
We tell Oracle: "Use THIS AI model, look at THESE tables, follow THESE rules."
The
Now we create the actual AI Profile — the identity card we talked about.
We tell Oracle: "Use THIS AI model, look at THESE tables, follow THESE rules."
The
object_list is critical — it tells the AI which tables exist in your database.
This is what the LLM uses to understand your data structure. 📋
-- ============================================================
-- STEP 2: Create an AI Profile
-- ============================================================
-- This registers your AI configuration inside the database.
-- Think of it as creating an "AI worker" and telling it:
-- which AI model to use, and which tables it's allowed to see.
-- ============================================================
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'SALES_AI_PROFILE',
attributes => '{
"provider" : "oci",
"credential_name" : "OCI_GENAI_CREDENTIAL",
"model" : "cohere.command-r-plus",
"oci_apiformat" : "COHERE",
"region" : "us-chicago-1",
"object_list" : [
{"owner": "SALES_SCHEMA", "name": "ORDERS"},
{"owner": "SALES_SCHEMA", "name": "CUSTOMERS"},
{"owner": "SALES_SCHEMA", "name": "PRODUCTS"},
{"owner": "SALES_SCHEMA", "name": "ORDER_ITEMS"}
],
"temperature" : 0,
"max_tokens" : 4000,
"comments" : true
}'
);
END;
/
-- ============================================================
-- STEP 3: Set this as your ACTIVE profile for this session
-- ============================================================
-- This tells Oracle: "When I use SELECT AI, use SALES_AI_PROFILE"
-- ============================================================
BEGIN
DBMS_CLOUD_AI.SET_PROFILE(
profile_name => 'SALES_AI_PROFILE'
);
END;
/
✅ Profile Setup Checklist:
☑️ Credential created with correct OCI API keys
☑️ Profile created with the right model and region
☑️ object_list includes ALL tables the AI needs to know about
☑️ temperature set to 0 for predictable SQL (not creative writing!)
☑️ Active profile set for your database session
☑️ Credential created with correct OCI API keys
☑️ Profile created with the right model and region
☑️ object_list includes ALL tables the AI needs to know about
☑️ temperature set to 0 for predictable SQL (not creative writing!)
☑️ Active profile set for your database session
🎭 The 4 Modes of Select AI
Select AI is not just one thing — it has 4 different modes,
each for a different purpose.
Think of it like a TV remote with 4 different buttons. 📺
┌──────────────┬────────────────────────────────────────────────────────┐ │ Mode │ What it does │ ├──────────────┼────────────────────────────────────────────────────────┤ │ RUNSQL │ Converts your question to SQL and RUNS it. │ │ │ Returns actual data rows from your database. 📊 │ ├──────────────┼────────────────────────────────────────────────────────┤ │ EXPLAIN │ Shows you the SQL that would be generated. │ │ │ Does NOT run it. Great for learning/debugging. 🔍 │ ├──────────────┼────────────────────────────────────────────────────────┤ │ NARRATE │ Runs the query AND explains the results │ │ │ in plain English. Like a data analyst narrating. 📖 │ ├──────────────┼────────────────────────────────────────────────────────┤ │ CHAT │ General AI conversation — not tied to SQL. │ │ │ Use for explanations, definitions, guidance. 💬 │ └──────────────┴────────────────────────────────────────────────────────┘
🔵 Mode 1: RUNSQL — Get Real Data Back
🗒️ What the code below does — Read this first!
RUNSQL mode takes your English question, converts it to SQL automatically, runs it against your real database, and returns actual data rows — just like a regular SQL query.
This is the mode you use for dashboards, reports, and data extraction. 📊
RUNSQL mode takes your English question, converts it to SQL automatically, runs it against your real database, and returns actual data rows — just like a regular SQL query.
This is the mode you use for dashboards, reports, and data extraction. 📊
-- Ask in plain English → get real data rows back
-- Default mode is RUNSQL, so you can use it like this:
SELECT AI
What are the top 5 customers by total order value this year?
-- OR explicitly use RUNSQL:
SELECT AI RUNSQL
Show me all orders placed in Mumbai last month with value above 50000
-- RESULT: Returns actual rows from your database!
-- ┌─────────────────┬───────────────┬──────────────┐
-- │ CUSTOMER_NAME │ ORDER_DATE │ ORDER_AMOUNT │
-- ├─────────────────┼───────────────┼──────────────┤
-- │ Rahul Sharma │ 15-JAN-2026 │ 75,000.00 │
-- │ Priya Patel │ 22-JAN-2026 │ 63,500.00 │
-- │ Anil Kumar │ 28-JAN-2026 │ 58,200.00 │
-- └─────────────────┴───────────────┴──────────────┘
🔵 Mode 2: EXPLAIN — See the SQL First
🗒️ What the code below does — Read this first!
EXPLAIN mode is your "show me what you're going to do before you do it" mode.
It generates the SQL query but does NOT run it.
This is incredibly useful for: learning SQL, checking AI accuracy, debugging wrong results, or showing to a DBA before executing on production. 🔍
EXPLAIN mode is your "show me what you're going to do before you do it" mode.
It generates the SQL query but does NOT run it.
This is incredibly useful for: learning SQL, checking AI accuracy, debugging wrong results, or showing to a DBA before executing on production. 🔍
-- See what SQL would be generated — WITHOUT running it
-- Perfect for: debugging, learning SQL, safety checking
SELECT AI EXPLAIN
Which product categories have declining sales compared to last year?
-- RESULT: Returns the SQL text only:
-- SELECT
-- p.category_name,
-- SUM(CASE WHEN EXTRACT(YEAR FROM o.order_date) = 2025 THEN oi.amount ELSE 0 END) AS sales_2025,
-- SUM(CASE WHEN EXTRACT(YEAR FROM o.order_date) = 2026 THEN oi.amount ELSE 0 END) AS sales_2026,
-- ROUND(
-- (SUM(CASE WHEN ... 2026 ...) - SUM(CASE WHEN ... 2025 ...))
-- / NULLIF(SUM(CASE WHEN ... 2025 ...), 0) * 100, 2
-- ) AS pct_change
-- FROM products p
-- JOIN order_items oi ON p.product_id = oi.product_id
-- JOIN orders o ON oi.order_id = o.order_id
-- GROUP BY p.category_name
-- HAVING pct_change < 0
-- ORDER BY pct_change ASC;
🔵 Mode 3: NARRATE — AI Tells You the Story
🗒️ What the code below does — Read this first!
NARRATE mode is like having a personal data analyst sitting next to you.
It runs the SQL, looks at the results, and then writes a human-friendly paragraph explaining what the numbers mean — in plain English.
This is perfect for executive summaries, business reports, and non-technical audiences. 📖
NARRATE mode is like having a personal data analyst sitting next to you.
It runs the SQL, looks at the results, and then writes a human-friendly paragraph explaining what the numbers mean — in plain English.
This is perfect for executive summaries, business reports, and non-technical audiences. 📖
-- Ask a question → AI runs query → AI explains what it found in plain English
SELECT AI NARRATE
Summarize our sales performance this quarter vs last quarter
-- RESULT: A human-readable narrative (not raw data rows):
--
-- "This quarter, your total sales reached ₹2.4 Crores, representing a
-- 12.3% increase compared to last quarter's ₹2.14 Crores.
-- The strongest growth came from the Electronics category (+28%), while
-- Clothing saw a slight decline of 5%.
-- Your top-performing region continues to be Maharashtra, contributing
-- 34% of total revenue.
-- Overall, Q1 shows strong growth momentum across most categories."
🔵 Mode 4: CHAT — General AI Conversation
🗒️ What the code below does — Read this first!
CHAT mode lets you have a free-form conversation with the AI — not for data queries, but for explanations, definitions, and guidance.
It's like asking your AI a general question: "What is a good database index strategy?"
No SQL is generated or run. Just AI knowledge. 💬
CHAT mode lets you have a free-form conversation with the AI — not for data queries, but for explanations, definitions, and guidance.
It's like asking your AI a general question: "What is a good database index strategy?"
No SQL is generated or run. Just AI knowledge. 💬
-- General AI conversation — no SQL, no data query involved
SELECT AI CHAT
Explain what a database partition is in simple terms
-- RESULT:
-- "A database partition is like dividing a very large filing cabinet into smaller
-- sections — each section (partition) holds data for a specific range or category.
-- For example, you might store January orders in one partition, February in another.
-- This makes searching much faster because Oracle only needs to open the relevant
-- section instead of the entire cabinet..."
-- Also useful for:
SELECT AI CHAT
What Oracle SQL function should I use to calculate running totals?
How the LLM Understands Your Database (Semantic Understanding)
Here is one of the most important concepts to understand: how does the AI know your tables? 🤔
You haven't shown it any data. How does it write accurate SQL?
The answer lies in schema metadata and semantic context.
When you create an AI Profile, Oracle automatically collects:
- All table names from your object_list
- All column names and their data types
- Any primary/foreign key relationships
- Any comments you added to tables and columns
- A small number of sample values for context
This metadata is bundled into a large "context document" and sent to the LLM along with your question.
The LLM uses this to understand your database structure without ever seeing your actual data.
🗒️ What the code below does — Read this first!
Adding comments to your tables and columns is one of the most powerful things you can do to improve Select AI accuracy.
Think of it as writing a "guide for the AI" — telling it what each column means in business terms.
For example, column name "ORD_AMT" is confusing, but with a comment "Total order value in Indian Rupees" — the AI instantly understands it. 🏷️
Adding comments to your tables and columns is one of the most powerful things you can do to improve Select AI accuracy.
Think of it as writing a "guide for the AI" — telling it what each column means in business terms.
For example, column name "ORD_AMT" is confusing, but with a comment "Total order value in Indian Rupees" — the AI instantly understands it. 🏷️
-- ============================================================
-- Add business-friendly comments to help the AI understand
-- your tables and columns better
-- ============================================================
-- This is like writing labels on every drawer in a filing cabinet
-- so anyone (or any AI) knows what's inside without opening it.
-- ============================================================
-- Comment on the TABLE itself
COMMENT ON TABLE orders IS
'Contains all customer purchase orders. One row per order.
Orders include online, in-store, and phone orders.
Status values: PENDING, CONFIRMED, SHIPPED, DELIVERED, CANCELLED';
-- Comments on COLUMNS
COMMENT ON COLUMN orders.ord_amt IS
'Total order value in Indian Rupees (INR). Includes all items but excludes GST.';
COMMENT ON COLUMN orders.ord_status IS
'Current status of the order. Use DELIVERED for completed orders.';
COMMENT ON COLUMN orders.region_code IS
'Geographic region: MH=Maharashtra, DL=Delhi, KA=Karnataka, TN=Tamil Nadu';
COMMENT ON COLUMN customers.tier IS
'Customer loyalty tier. Values: GOLD (top 10%), SILVER (next 20%), STANDARD (rest).
Use this to filter premium customers in revenue analysis.';
✅ Golden Rule for Select AI Accuracy:
Better comments = Better AI understanding = More accurate SQL = Better answers.
Spend time writing clear, descriptive comments on all your tables and columns.
This is the single most impactful thing you can do to improve Select AI quality. 🏆
Better comments = Better AI understanding = More accurate SQL = Better answers.
Spend time writing clear, descriptive comments on all your tables and columns.
This is the single most impactful thing you can do to improve Select AI quality. 🏆
Prompt Engineering for Select AI
Here's a secret that most people don't know: how you ask the question matters enormously. 🎭
The same database, the same AI, but a better-worded question gets a much better answer.
POOR PROMPT (vague):
SELECT AI sales data
RESULT: AI doesn't know what you want — might return anything. ❌
─────────────────────────────────────────────────────────────
GOOD PROMPT (specific):
SELECT AI Show me total sales amount grouped by product category
for the year 2026, ordered from highest to lowest
RESULT: Clean, accurate SQL with GROUP BY and ORDER BY. ✅
─────────────────────────────────────────────────────────────
GREAT PROMPT (specific + context + filter):
SELECT AI Show me total confirmed sales amount in Indian Rupees
grouped by product category for orders placed in Q1 2026
(January to March), only for DELIVERED orders,
ordered by total from highest to lowest,
show top 5 categories only
RESULT: Highly precise SQL with all the right filters. 🏆
✅ Prompt Engineering Rules for Select AI:
✅ Be specific — mention table concepts, time ranges, and filters explicitly
✅ Use business terms that match your column comments
✅ Mention the output format — "top 10", "grouped by", "ordered by"
✅ Specify filters — status, region, date range, tier
✅ Include aggregation hints — "total", "average", "count", "percentage"
✅ Be specific — mention table concepts, time ranges, and filters explicitly
✅ Use business terms that match your column comments
✅ Mention the output format — "top 10", "grouped by", "ordered by"
✅ Specify filters — status, region, date range, tier
✅ Include aggregation hints — "total", "average", "count", "percentage"
❌ DON'T: Ask ambiguous questions without context
❌ "Show me the data" — too vague, AI may return wrong tables
❌ "What happened?" — no time range, no subject, no context
❌ "Sales report" — this is a topic, not a question — specify exactly what you want
❌ Use internal jargon column names — use business terms that match your comments
❌ "Show me the data" — too vague, AI may return wrong tables
❌ "What happened?" — no time range, no subject, no context
❌ "Sales report" — this is a topic, not a question — specify exactly what you want
❌ Use internal jargon column names — use business terms that match your comments
AI Hallucination Risks and How to Manage Them
Here is an important warning. 🚨
AI models are incredibly powerful — but they are not perfect.
Sometimes an AI can be confidently wrong. This is called hallucination.
In the context of Select AI, hallucination means: the AI generates SQL that looks correct but actually produces wrong results. This can happen when:
- Table names or column names are ambiguous (e.g., multiple "amount" columns in different tables)
- The question is vague or uses terms not matching your schema comments
- The LLM makes incorrect assumptions about your business rules
- Complex multi-table joins are needed and the relationships are not clearly defined
❌ Never Trust Critical Decisions Blindly to Select AI Output
For financial reports, compliance data, or critical business decisions: always use EXPLAIN mode first — review the generated SQL before running it.
Treat Select AI like a very smart intern — useful, fast, but needs supervision for important work.
For financial reports, compliance data, or critical business decisions: always use EXPLAIN mode first — review the generated SQL before running it.
Treat Select AI like a very smart intern — useful, fast, but needs supervision for important work.
Hallucination Prevention Strategy: ┌────────────────────────────────────────────────────────┐ │ 1. Always use EXPLAIN first for critical queries │ │ SELECT AI EXPLAIN [your question] │ │ → Review the SQL before running │ ├────────────────────────────────────────────────────────┤ │ 2. Add rich column/table COMMENTS to your schema │ │ Better context = less hallucination risk │ ├────────────────────────────────────────────────────────┤ │ 3. Restrict object_list to only relevant tables │ │ Fewer tables = less confusion for the AI │ ├────────────────────────────────────────────────────────┤ │ 4. Set temperature = 0 in your AI Profile │ │ Zero temperature = deterministic, predictable output │ ├────────────────────────────────────────────────────────┤ │ 5. Cross-validate results for critical data │ │ Run the same question twice and compare │ └────────────────────────────────────────────────────────┘
🔐 Security and Governance
Security is not optional — it's the foundation. 🏛️
Let's understand all the security layers that Select AI operates within.
SELECT AI SECURITY LAYERS:
Layer 1: OCI IAM (Identity and Access Management)
─────────────────────────────────────────────────
→ Controls who can call OCI Generative AI APIs
→ Uses OCI Policies like:
Allow group AI_Users to use generative-ai-family in compartment AI_Compartment
→ API Keys + tenancy_ocid + user_ocid stored in ADB credentials
Layer 2: Oracle Database Privileges
─────────────────────────────────────────────────
→ Only users with EXECUTE on DBMS_CLOUD_AI can create profiles
→ Only users with SELECT on specific tables in object_list can query them
→ Even if you ask "show me HR salary data" — if you don't have SELECT on HR tables,
the generated SQL will FAIL with an ORA-insufficient-privileges error ✅
Layer 3: Data Stays Inside ADB
─────────────────────────────────────────────────
→ Only schema metadata goes to the LLM (never actual row data)
→ All query execution happens inside your Autonomous Database
→ Results never pass through OCI GenAI Service
Layer 4: Network Security
─────────────────────────────────────────────────
→ OCI Private Endpoints can be used to keep AI traffic inside your VCN
→ No public internet exposure required for ADB-to-GenAI communication
→ TLS encryption for all API calls
✅ Enterprise Security Best Practices:
✅ Create separate AI Profiles per department — HR profile only sees HR tables
✅ Grant minimum necessary database privileges to Select AI users
✅ Use OCI Private Endpoints to keep all traffic within your network
✅ Enable Oracle Audit Vault to log all SELECT AI queries
✅ Rotate API keys every 90 days using OCI Vault
✅ Never put real production API keys in development environments
✅ Create separate AI Profiles per department — HR profile only sees HR tables
✅ Grant minimum necessary database privileges to Select AI users
✅ Use OCI Private Endpoints to keep all traffic within your network
✅ Enable Oracle Audit Vault to log all SELECT AI queries
✅ Rotate API keys every 90 days using OCI Vault
✅ Never put real production API keys in development environments
🔌 REST API with Select AI (Calling from Applications)
So far we have used Select AI from inside the database (SQL*Plus / SQL Developer).
But in real enterprise projects, you need to call Select AI from your application —
whether it's a web app, mobile app, or an integration platform. 🌐
Oracle Autonomous Database has built-in ORDS (Oracle REST Data Services) that lets you call Select AI through HTTP REST APIs.
🗒️ What the code below does — Read this first!
This code shows how to call Select AI from any application using a simple HTTP POST request.
Think of it like calling a waiter (the REST API) who then talks to the kitchen (Select AI) and brings back your food (data).
This is how you would connect a React web app, a Python backend, or an OIC integration to Select AI. 🍽️
This code shows how to call Select AI from any application using a simple HTTP POST request.
Think of it like calling a waiter (the REST API) who then talks to the kitchen (Select AI) and brings back your food (data).
This is how you would connect a React web app, a Python backend, or an OIC integration to Select AI. 🍽️
-- STEP 1: Enable ORDS and create a REST-enabled module in ADB
-- (Run this in SQL Developer or Database Actions)
BEGIN
ORDS.ENABLE_SCHEMA(
p_enabled => TRUE,
p_schema => 'SALES_SCHEMA',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'sales',
p_auto_rest_auth => TRUE
);
COMMIT;
END;
/
-- STEP 2: Create a REST endpoint that wraps Select AI
-- This creates a URL that your app can call
BEGIN
ORDS.DEFINE_MODULE(
p_module_name => 'select_ai_module',
p_base_path => '/ai/',
p_is_published => TRUE
);
ORDS.DEFINE_TEMPLATE(
p_module_name => 'select_ai_module',
p_pattern => 'query'
);
ORDS.DEFINE_HANDLER(
p_module_name => 'select_ai_module',
p_pattern => 'query',
p_method => 'POST',
p_source_type => ORDS.source_type_plsql,
p_source => '
DECLARE
v_question VARCHAR2(4000) := :question;
v_mode VARCHAR2(50) := NVL(:mode, ''runsql'');
v_result CLOB;
BEGIN
-- Set the AI Profile for this query
DBMS_CLOUD_AI.SET_PROFILE(''SALES_AI_PROFILE'');
-- Generate the AI response
v_result := DBMS_CLOUD_AI.GENERATE(
prompt => v_question,
profile_name => ''SALES_AI_PROFILE'',
action => v_mode
);
-- Return result as JSON
:result := v_result;
:status := 200;
EXCEPTION
WHEN OTHERS THEN
:result := SQLERRM;
:status := 500;
END;
'
);
COMMIT;
END;
/
-- STEP 3: Call the REST API from any application or tool
-- Using curl (command line / Postman):
curl -X POST https://your-adb-hostname/ords/sales/ai/query \
-H "Content-Type: application/json" \
-H "Authorization: Bearer YOUR_TOKEN_HERE" \
-d '{
"question": "Show me top 10 customers by revenue this month",
"mode": "narrate"
}'
-- Response from Select AI:
{
"result": "This month, your top 10 customers generated a combined revenue
of ₹1.24 Crores. Rahul Sharma leads with ₹18.5 Lakhs,
followed by Priya Enterprises at ₹15.2 Lakhs..."
}
🔗 OIC + Select AI Integration Architecture
Now we get to the enterprise integration gold. 🏅
Oracle Integration Cloud (OIC) is the enterprise middleware platform
that connects all your Oracle and non-Oracle applications.
When you combine OIC with Select AI, you unlock incredible scenarios:
✅ A sales manager asks a question in Microsoft Teams → OIC sends it to Select AI → answer comes back
✅ An ERP system detects a business event → OIC triggers an AI query → result goes to a dashboard
✅ A mobile app user types a question → OIC calls Select AI → formatted answer displayed
OIC + SELECT AI — FULL INTEGRATION ARCHITECTURE:
┌──────────────────────────────────────────────────────────────────────────────┐
│ TRIGGER LAYER │
│ MS Teams / Slack / Web App / Oracle Fusion / Mobile → sends user question │
└───────────────────────────────┬──────────────────────────────────────────────┘
│ HTTP POST (user question as JSON)
▼
┌──────────────────────────────────────────────────────────────────────────────┐
│ ORACLE INTEGRATION CLOUD (OIC) │
│ │
│ Integration Flow: │
│ ┌────────────┐ ┌─────────────┐ ┌─────────────┐ ┌─────────────────┐ │
│ │ REST Trigger│──►│ Validate │──►│ Enrich │──►│ Call ADB Select │ │
│ │ (receive │ │ & Transform │ │ Question │ │ AI REST API │ │
│ │ question) │ │ Input │ │ with Context│ │ │ │
│ └────────────┘ └─────────────┘ └─────────────┘ └────────┬────────┘ │
│ │ │
│ ┌──────────────────────┐ ┌──────────────────────────────────┘ │
│ │ Format & Return │◄──│ Parse AI Response │
│ │ Result to Caller │ │ & Apply Business Rules │
│ └──────────────────────┘ └──────────────────────────────────────────────┘
└──────────────────────────────────────────────────────────────────────────────┘
│
▼ (OIC calls Select AI REST endpoint)
┌──────────────────────────────────────────────────────────────────────────────┐
│ ORACLE AUTONOMOUS DATABASE │
│ │
│ SELECT AI (NARRATE mode) → OCI GenAI → SQL Generated → Run on Data │
│ → Human-readable answer returned to OIC │
└──────────────────────────────────────────────────────────────────────────────┘
🗒️ What the code below does — Read this first!
This PL/SQL procedure is the "bridge module" you deploy in ADB — it receives a question from OIC via REST, calls DBMS_CLOUD_AI to process it, and returns the answer.
Think of it as the "kitchen" in our restaurant analogy — OIC is the waiter who brings the order, this procedure is the chef who cooks the answer, and the response goes back to the customer. 👨🍳
This PL/SQL procedure is the "bridge module" you deploy in ADB — it receives a question from OIC via REST, calls DBMS_CLOUD_AI to process it, and returns the answer.
Think of it as the "kitchen" in our restaurant analogy — OIC is the waiter who brings the order, this procedure is the chef who cooks the answer, and the response goes back to the customer. 👨🍳
-- ============================================================
-- OIC Bridge Procedure in Autonomous Database
-- ============================================================
-- OIC will call this via REST API.
-- It takes: a question string + a mode (runsql/narrate/explain)
-- It returns: the AI-generated answer as a JSON CLOB
-- ============================================================
CREATE OR REPLACE PROCEDURE oic_select_ai_bridge(
p_question IN VARCHAR2, -- The natural language question from OIC
p_mode IN VARCHAR2, -- 'runsql', 'narrate', 'explain', 'chat'
p_profile IN VARCHAR2, -- Which AI profile to use
p_result OUT CLOB, -- The answer goes back here
p_error_msg OUT VARCHAR2 -- Error details if something goes wrong
) AS
v_ai_response CLOB;
BEGIN
-- Step 1: Set the active AI Profile
DBMS_CLOUD_AI.SET_PROFILE(p_profile);
-- Step 2: Call Select AI to generate the answer
-- DBMS_CLOUD_AI.GENERATE is the core function that does everything:
-- → Sends question + schema to the LLM
-- → Gets SQL back
-- → Runs SQL on your data
-- → Returns result in the format you chose (table / narrative / SQL text)
v_ai_response := DBMS_CLOUD_AI.GENERATE(
prompt => p_question,
profile_name => p_profile,
action => LOWER(p_mode) -- 'runsql', 'narrate', 'explain', or 'chat'
);
-- Step 3: Wrap result in JSON format for OIC to parse easily
p_result := JSON_OBJECT(
'status' VALUE 'SUCCESS',
'mode' VALUE p_mode,
'question' VALUE p_question,
'answer' VALUE v_ai_response,
'timestamp' VALUE TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')
);
p_error_msg := NULL;
EXCEPTION
WHEN OTHERS THEN
-- Always handle errors gracefully — never let the application crash
p_result := NULL;
p_error_msg := 'Select AI Error: ' || SUBSTR(SQLERRM, 1, 500);
END oic_select_ai_bridge;
/
Building an AI Chatbot with Select AI
Let's build something real and exciting — a full enterprise chatbot powered by Select AI that business users can talk to. 💬
The chatbot takes a question in plain English, queries your Oracle database, and gives a human-friendly answer. No SQL knowledge required from the user.
CHATBOT CONVERSATION FLOW: ┌─────────────────────────────────────────────────────────────────┐ │ User: "What's my region's performance this month?" │ │ │ │ │ ▼ │ │ [OIC Integration] → [Select AI NARRATE mode] → [ADB Query] │ │ │ │ │ ▼ │ │ Bot: "Your region (Maharashtra) generated ₹87.3 Lakhs this │ │ month — a 14% improvement over last month (₹76.6 Lakhs). │ │ Electronics led with ₹31.2 Lakhs, followed by Clothing │ │ at ₹22.4 Lakhs. You are on track to meet your │ │ quarterly target. 🎉" │ │ │ │ │ User: "Which customers need follow-up calls?" │ │ │ │ │ ▼ │ │ Bot: "There are 12 customers with pending orders over 5 days │ │ old. The most urgent is Tech Solutions Ltd (₹4.2 Lakhs │ │ order placed 8 days ago). Should I show you the full │ │ list?" │ └─────────────────────────────────────────────────────────────────┘
🗒️ What the code below does — Read this first!
This Python script simulates a chatbot that calls your ADB Select AI REST endpoint.
In a real project, this logic would be inside your web application, Slack bot, or Microsoft Teams bot.
It sends the user's question to Select AI and displays the AI's narrated response. 🤖
This Python script simulates a chatbot that calls your ADB Select AI REST endpoint.
In a real project, this logic would be inside your web application, Slack bot, or Microsoft Teams bot.
It sends the user's question to Select AI and displays the AI's narrated response. 🤖
# ============================================================
# Select AI Chatbot — Python Example
# ============================================================
# This script takes a user question, sends it to your ADB
# Select AI REST endpoint, and prints the AI's response.
# In a real app: replace print() with your chat UI display code.
# ============================================================
import requests
import json
# Configuration — replace with your actual ADB ORDS URL
ADB_BASE_URL = "https://your-adb-hostname.adb.us-chicago-1.oraclecloud.com"
ORDS_PATH = "/ords/sales/ai/query"
ADB_USERNAME = "SALES_APP_USER"
ADB_PASSWORD = "YourSecurePassword123!"
def ask_select_ai(question, mode="narrate"):
"""
Sends a natural language question to Select AI via ORDS REST API.
Returns a human-readable answer string.
"""
url = ADB_BASE_URL + ORDS_PATH
# Build the request payload
payload = {
"question" : question, # The user's natural language question
"mode" : mode, # How should AI respond? narrate = human text
"profile" : "SALES_AI_PROFILE"
}
# Send POST request to ADB Select AI endpoint
response = requests.post(
url,
json = payload,
auth = (ADB_USERNAME, ADB_PASSWORD), # Basic auth for ORDS
headers = {"Content-Type": "application/json"},
timeout = 30 # Wait max 30 seconds for AI response
)
if response.status_code == 200:
result = response.json()
return result.get("answer", "No answer returned")
else:
return f"Error: {response.status_code} — {response.text}"
# ============================================================
# Simple chatbot loop — keeps asking questions until "exit"
# ============================================================
print("🤖 Sales AI Assistant — Ask me anything about your data!")
print(" Type 'exit' to quit.\n")
while True:
user_question = input("You: ").strip()
if user_question.lower() == "exit":
print("Goodbye! 👋")
break
if not user_question:
continue
print("\n AI is thinking...\n")
answer = ask_select_ai(user_question, mode="narrate")
print(f"Bot: {answer}\n")
print("-" * 60)
🔍 RAG and Vector Search with Select AI
Let's go to the advanced level.
Oracle has extended Select AI beyond just tables and SQL.
Now it can also search your unstructured documents —
PDF files, Word documents, emails, policy manuals — using a technique called
RAG (Retrieval Augmented Generation).
Imagine asking: "What does our refund policy say about electronics?"
Instead of querying a database table, Select AI searches through your actual policy PDF,
finds the relevant paragraph, and gives you the answer. 📄
🔵 What is RAG? (Simple Explanation)
RAG = Retrieval + AI Generation
Step 1: Retrieval — Search your documents to find the relevant pieces
Step 2: Augmentation — Give those pieces to the AI as context
Step 3: Generation — AI writes a clear answer based on what it found
Think of it like an open-book exam — the AI gets to look at your documents before answering, instead of relying only on what it already knows. 📚
RAG = Retrieval + AI Generation
Step 1: Retrieval — Search your documents to find the relevant pieces
Step 2: Augmentation — Give those pieces to the AI as context
Step 3: Generation — AI writes a clear answer based on what it found
Think of it like an open-book exam — the AI gets to look at your documents before answering, instead of relying only on what it already knows. 📚
RAG WITH SELECT AI — HOW IT WORKS:
STEP 1: Document Ingestion (done once)
──────────────────────────────────────
Policy PDF → Oracle converts each paragraph into a "vector"
→ Vectors stored in Oracle AI Vector Search table in ADB
→ Think of vectors as "fingerprints" of meaning 🧬
STEP 2: User asks a question
──────────────────────────────────────
User: "What is our return window for electronics?"
→ Question is also converted to a vector
STEP 3: Vector similarity search
──────────────────────────────────────
Oracle AI Vector Search compares the question vector
to all document paragraph vectors
→ Finds the 3 most similar paragraphs (nearest neighbors)
→ Paragraphs about "return policy" and "electronics" float to top ✅
STEP 4: AI generates the answer
──────────────────────────────────────
Found paragraphs + User question → sent to LLM
LLM reads the paragraphs and writes a clear answer:
"According to our policy, electronics can be returned
within 14 days of purchase with original packaging..."
🗒️ What the code below does — Read this first!
This creates a vector table — a special Oracle table that stores your documents as mathematical "fingerprints" (vectors) so the AI can search them by meaning.
This is the foundation of RAG in Oracle Autonomous Database. 🧬
This creates a vector table — a special Oracle table that stores your documents as mathematical "fingerprints" (vectors) so the AI can search them by meaning.
This is the foundation of RAG in Oracle Autonomous Database. 🧬
-- ============================================================
-- Create a Vector Store for document-based RAG
-- ============================================================
-- This table stores your documents along with their "meaning"
-- encoded as mathematical vectors (arrays of numbers).
-- Oracle's AI Vector Search can then find similar documents
-- in milliseconds — like a super-smart Google for your docs!
-- ============================================================
CREATE TABLE company_knowledge_base (
doc_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
doc_name VARCHAR2(500), -- e.g., 'Refund Policy v3.2.pdf'
doc_type VARCHAR2(100), -- e.g., 'POLICY', 'MANUAL', 'FAQ'
chunk_text CLOB, -- A paragraph/chunk of the document
chunk_seq NUMBER, -- Which paragraph in the document
embed_vector VECTOR(1024, FLOAT32), -- The "meaning fingerprint" — 1024 numbers
created_date DATE DEFAULT SYSDATE
);
-- Create a vector index for lightning-fast similarity search
-- This index makes semantic search 100x faster on large document sets
CREATE VECTOR INDEX knowledge_vector_idx
ON company_knowledge_base(embed_vector)
ORGANIZATION NEIGHBOR PARTITIONS
WITH DISTANCE COSINE; -- COSINE = measure meaning similarity
-- ============================================================
-- Ingest a document and generate its embeddings
-- ============================================================
DECLARE
v_text VARCHAR2(32000);
v_embedding VECTOR(1024, FLOAT32);
v_chunk_num NUMBER := 1;
BEGIN
-- For each chunk/paragraph of your document:
v_text := 'Electronics purchased from our store can be returned
within 14 days of the original purchase date.
Items must be in original packaging with all accessories.
Screen protectors and earphones are non-returnable once opened.';
-- Generate vector embedding using OCI GenAI embedding model
-- This converts the text into 1024 numbers representing its "meaning"
v_embedding := DBMS_VECTOR.UTL_TO_EMBEDDING(
data => v_text,
params => JSON_OBJECT(
'provider' VALUE 'ocigenai',
'credential_name' VALUE 'OCI_GENAI_CREDENTIAL',
'model' VALUE 'cohere.embed-english-v3'
)
);
-- Store the text + its vector together
INSERT INTO company_knowledge_base
(doc_name, doc_type, chunk_text, chunk_seq, embed_vector)
VALUES
('Refund_Policy_2026.pdf', 'POLICY', v_text, v_chunk_num, v_embedding);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Document chunk ingested and vectorized ✅');
END;
/
🏢 Real Enterprise Use Cases
Let's see Select AI working in the real world across different industries. 🌍
USE CASE 1 — RETAIL & E-COMMERCE 🛒
────────────────────────────────────────────────────────
Business User: "Which products are running low on stock in Mumbai warehouses?"
Select AI: Queries inventory + warehouse tables
Result: "17 products are below reorder threshold in Mumbai.
Most urgent: 'Samsung Galaxy A35' — only 3 units left
against average daily sales of 12 units."
USE CASE 2 — BANKING & FINANCE 🏦
────────────────────────────────────────────────────────
CFO asks: "Show me all transactions above ₹10 Lakhs in the last 7 days
with accounts that were opened less than 6 months ago"
Select AI: Generates complex fraud-detection SQL query
Result: Returns 4 flagged transactions for review
USE CASE 3 — HEALTHCARE 🏥
────────────────────────────────────────────────────────
Admin asks: "How many patients missed follow-up appointments this month
by department?"
Select AI: Queries appointments + patients + departments tables
Result: "Cardiology: 23 missed, Orthopedics: 15 missed..."
USE CASE 4 — HUMAN RESOURCES 👥
────────────────────────────────────────────────────────
HR Manager: "Which employees have completed all mandatory training
but not yet received a performance review this year?"
Select AI: Multi-table join across training + reviews + employees
Result: List of 41 employees needing review scheduling
USE CASE 5 — SUPPLY CHAIN 🚛
────────────────────────────────────────────────────────
Ops Manager: "Which suppliers have delayed deliveries more than twice
in the last quarter and what is their average delay?"
Select AI: Queries supplier + delivery + SLA tables
Result: "3 suppliers flagged: Sharma Logistics (avg 4.2 days late)..."
📊 Monitoring and Troubleshooting
In production, you need to know: Is Select AI working? Is it slow? Are queries failing? 🔭
Oracle provides built-in views to monitor everything.
🗒️ What the code below does — Read this first!
These SQL queries help you monitor your Select AI usage — see which queries ran, how long they took, which mode was used, and whether any failed.
Think of it like checking the "call logs" of your AI system. 📱
These SQL queries help you monitor your Select AI usage — see which queries ran, how long they took, which mode was used, and whether any failed.
Think of it like checking the "call logs" of your AI system. 📱
-- ============================================================
-- MONITORING: View recent Select AI activity
-- ============================================================
-- USER_CLOUD_AI_ACTIVITY logs every Select AI call made in ADB.
-- Use this to audit usage, spot slow queries, catch errors.
-- ============================================================
SELECT
request_id,
profile_name,
action, -- runsql / narrate / explain / chat
SUBSTR(prompt, 1, 100) AS question_preview, -- first 100 chars of question
status, -- SUCCESS or ERROR
error_message, -- what went wrong (if anything)
tokens_used, -- AI tokens consumed (affects cost!)
elapsed_time_ms, -- how long it took in milliseconds
TO_CHAR(created_on, 'DD-MON-YY HH24:MI') AS asked_at
FROM
user_cloud_ai_activity
ORDER BY
created_on DESC
FETCH FIRST 20 ROWS ONLY;
-- ============================================================
-- TROUBLESHOOTING: Find the slowest Select AI queries
-- ============================================================
SELECT
SUBSTR(prompt, 1, 80) AS question,
elapsed_time_ms,
tokens_used,
status
FROM
user_cloud_ai_activity
WHERE
elapsed_time_ms > 5000 -- queries that took more than 5 seconds
ORDER BY
elapsed_time_ms DESC;
-- ============================================================
-- COST MONITORING: Track token usage (tokens = money!)
-- ============================================================
SELECT
profile_name,
action,
COUNT(*) AS total_queries,
SUM(tokens_used) AS total_tokens,
AVG(tokens_used) AS avg_tokens_per_query,
MAX(tokens_used) AS max_tokens_single_query
FROM
user_cloud_ai_activity
WHERE
created_on >= TRUNC(SYSDATE, 'MM') -- this month only
GROUP BY
profile_name, action
ORDER BY
total_tokens DESC;
💰 Cost Optimization
OCI Generative AI Service charges based on tokens —
every word in your question and every word in the AI's response costs tokens. 💸
For enterprise-scale usage, cost management is critical.
TOKEN COST BREAKDOWN: ────────────────────────────────────────────────────────── Your schema metadata (sent with every request): ~500–2000 tokens Your question: ~20–100 tokens AI's generated SQL: ~100–500 tokens AI's narrated response: ~100–300 tokens Total per query (approx): ~800–3000 tokens At Cohere command-r-plus pricing: Input tokens: $0.003 per 1000 tokens Output tokens: $0.015 per 1000 tokens 1000 queries/day × 2000 tokens each = 2M tokens = ~$36/day = ~$1100/month ──────────────────────────────────────────────────────────
✅ Cost Optimization Strategies:
✅ Use RUNSQL over NARRATE when users just need data, not explanations
✅ Reduce object_list — include ONLY the tables relevant to each profile
✅ Use smaller models (meta.llama-3-8b) for simple queries; save large models for complex ones
✅ Cache frequent queries — if "weekly sales report" is asked daily, cache the result for hours
✅ Set max_tokens in profile to cap runaway responses
✅ Monitor token usage monthly via user_cloud_ai_activity and set OCI budget alerts
✅ Use RUNSQL over NARRATE when users just need data, not explanations
✅ Reduce object_list — include ONLY the tables relevant to each profile
✅ Use smaller models (meta.llama-3-8b) for simple queries; save large models for complex ones
✅ Cache frequent queries — if "weekly sales report" is asked daily, cache the result for hours
✅ Set max_tokens in profile to cap runaway responses
✅ Monitor token usage monthly via user_cloud_ai_activity and set OCI budget alerts
🚫 Common Beginner Mistakes
MISTAKE 1: Including ALL tables in object_list ────────────────────────────────────────────── ❌ Wrong: Adding 200 tables to object_list ✅ Right: Create multiple focused profiles — each with 5-15 relevant tables Why: More tables = longer schema context = more tokens + more AI confusion MISTAKE 2: Not adding column comments ────────────────────────────────────────────── ❌ Wrong: Column named "ORD_AMT_NR" with no comment ✅ Right: Add comment: "Net order amount in INR after discounts, before GST" Why: Without comments, AI guesses wrong → incorrect SQL → wrong results MISTAKE 3: Using Select AI for real-time data ────────────────────────────────────────────── ❌ Wrong: Expecting sub-second response for live trading data ✅ Right: Select AI takes 2-10 seconds (LLM call + SQL execution) Why: It's not a cache — it calls an LLM every single time MISTAKE 4: Trusting results without verification for critical data ────────────────────────────────────────────── ❌ Wrong: Running NARRATE mode directly for a financial audit report ✅ Right: Use EXPLAIN first → review SQL → then run for final report Why: AI can generate plausible-looking but logically wrong SQL MISTAKE 5: Temperature > 0 for SQL generation ────────────────────────────────────────────── ❌ Wrong: temperature = 0.7 (makes AI "creative" — terrible for SQL!) ✅ Right: temperature = 0 always for SQL generation (predictable output) Why: High temperature causes random SQL variations — same query gives different results MISTAKE 6: Forgetting to handle errors in applications ────────────────────────────────────────────── ❌ Wrong: No try-catch around Select AI API calls in your app ✅ Right: Always catch OCI service timeouts, rate limits, token limit errors Why: LLM APIs can fail or time out — your app must handle this gracefully
🔮 Future Trends: Select AI and Beyond
Oracle's AI roadmap for Select AI is expanding rapidly.
Here is what is happening and what is coming next:
CURRENT:
────────────────────────────────────────────────────────────
✅ Text-to-SQL using OCI GenAI (Cohere, Llama 3)
✅ RUNSQL / EXPLAIN / NARRATE / CHAT modes
✅ Vector Search + RAG for document querying
✅ OIC integration via ORDS REST APIs
✅ Multi-table schema understanding with VNodes
✅ Autonomous Database 23ai — AI-native version
NEAR FUTURE :
────────────────────────────────────────────────────────────
🔜 Select AI for JSON and NoSQL data collections
🔜 Multi-modal Select AI (ask questions about images + data together)
🔜 Agentic AI — AI that not only answers but takes actions
(e.g., "reorder this product" → AI writes + executes the INSERT)
🔜 Select AI with Oracle Analytics Cloud — voice-powered dashboards
🔜 Fine-tuned Oracle-specific LLMs for better SQL accuracy
🔜 Automated hallucination detection + correction loops
STRATEGIC DIRECTION:
────────────────────────────────────────────────────────────
Oracle's vision: Every business user becomes a data analyst.
No SQL. No dashboards to learn. Just ask questions.
The database becomes a conversation partner. 🗣️💾
🔵 The Big Picture — Why This Matters:
Oracle Select AI represents a fundamental shift in how humans interact with data.
For the first time in computing history, the database speaks your language — not the other way around.
As an Oracle developer or architect, mastering Select AI is not optional — it is the single most important new skill you can add to your toolkit. 🎯
Oracle Select AI represents a fundamental shift in how humans interact with data.
For the first time in computing history, the database speaks your language — not the other way around.
As an Oracle developer or architect, mastering Select AI is not optional — it is the single most important new skill you can add to your toolkit. 🎯
📋 Production Implementation Checklist
INFRASTRUCTURE: ☑️ Oracle Autonomous Database 23ai provisioned ☑️ OCI Generative AI Service enabled in your tenancy and region ☑️ OCI IAM policies set for GenAI access ☑️ Private endpoint configured (no public internet for AI traffic) ☑️ OCI Budget Alert set for GenAI spending DATABASE SETUP: ☑️ DBMS_CLOUD_AI privileges granted to application schema ☑️ OCI credential created and tested (DBMS_CLOUD.CREATE_CREDENTIAL) ☑️ AI Profile created with correct model, region, object_list ☑️ temperature = 0 in all SQL-generating profiles ☑️ Table and column COMMENTS added to all objects in object_list SECURITY: ☑️ Separate AI Profiles per department with restricted object_list ☑️ Database privileges: app user only has SELECT on needed tables ☑️ OCI Vault used for credential storage (not hardcoded keys) ☑️ Oracle Audit enabled to log all SELECT AI activity ☑️ API key rotation schedule set (every 90 days) APPLICATION / OIC: ☑️ ORDS enabled and REST endpoints tested ☑️ OIC integration flow built with proper error handling ☑️ EXPLAIN mode review built into approval workflow for critical reports ☑️ Response time SLA tested (expect 3-10 seconds per query) ☑️ Retry logic implemented for OCI GenAI service timeouts MONITORING: ☑️ Daily token usage dashboard from user_cloud_ai_activity ☑️ Error rate alerting (>5% errors triggers notification) ☑️ Slow query alerting (>8 seconds triggers review) ☑️ Monthly cost report automated to finance team
🎓 The Complete Journey — Zero to Hero in One Page
WHAT YOU LEARNED TODAY:
✅ OCI Select AI = natural language → SQL → real database answers
✅ Old way: Business people wait days for SQL queries
New way: Ask in English, get answers in seconds
✅ Architecture: ADB → schema metadata → OCI GenAI LLM → SQL → run → answer
✅ Your data NEVER leaves ADB. Only schema structure goes to the LLM. 🔐
✅ 4 Modes: RUNSQL (data) / EXPLAIN (see SQL) / NARRATE (story) / CHAT (general)
✅ AI Profile = identity card: which model, which tables, which rules
✅ DBMS_CLOUD_AI = the control center PL/SQL package for everything
✅ Column COMMENTS = your most powerful accuracy tool
✅ OIC + Select AI = enterprise chatbots, event-driven AI queries
✅ RAG + Vector Search = AI that answers from your documents too
✅ Always use EXPLAIN first for critical financial/compliance queries
✅ temperature = 0 always for SQL generation
✅ Monitor tokens = control costs
✅ Separate profiles per department = better security + accuracy
✅ The 5 Commands to Remember:
1️⃣
2️⃣
3️⃣
4️⃣
5️⃣
Master these 5 commands and you are ready for enterprise production deployment. 🏆
1️⃣
DBMS_CLOUD.CREATE_CREDENTIAL — connect to OCI GenAI2️⃣
DBMS_CLOUD_AI.CREATE_PROFILE — set up your AI identity card3️⃣
SELECT AI EXPLAIN ... — see what SQL will be generated4️⃣
SELECT AI NARRATE ... — get human-friendly answers5️⃣
DBMS_CLOUD_AI.GENERATE(...) — call Select AI from PL/SQL/RESTMaster these 5 commands and you are ready for enterprise production deployment. 🏆
Happy Building with Oracle Select AI!
Comments
Post a Comment