🚀 How to Integrate Gemini AI with Oracle APEX (Step-by-Step Guide)

In today’s world, users don’t want to write complex SQL queries—they want instant answers in plain English.

What if your Oracle APEX application could understand natural language and automatically generate SQL queries, reports, and insights?

In this blog, I’ll show you how to connect Google Gemini AI with Oracle APEX and build a powerful AI-powered search engine 🔥

💡 What We Are Building

👉 A smart APEX application where users can:

• Ask questions in plain English
• Automatically generate Oracle SQL
• View results in reports/charts
• No SQL knowledge required

🧠 Architecture Overview

User Input → Gemini AI API → SQL Query → Oracle APEX Report

🚀 Step 1: Get Gemini API Key (Free)

1. Go to Google AI Studio
2. Login with your Google account
3. Click Create API Key
4. Copy the key

👉 No credit card required ✅

⚙️ Step 2: Create APEX Page Items

Create these items:

• P1_SEARCH → User input
• P1_RESPONSE → Store API response
• P1_SQL → Extracted SQL

🔗 Step 3: Call Gemini API using PL/SQL

Use APEX_WEB_SERVICE to call the API:

DECLARE
    l_response CLOB;
BEGIN
    apex_web_service.g_request_headers.delete;

    apex_web_service.g_request_headers(1).name  := 'x-goog-api-key';
    apex_web_service.g_request_headers(1).value := 'YOUR_API_KEY';

    apex_web_service.g_request_headers(2).name  := 'Content-Type';
    apex_web_service.g_request_headers(2).value := 'application/json';

    l_response := apex_web_service.make_rest_request(
        p_url => 'https://generativelanguage.googleapis.com/v1beta/models/gemini-3-flash-preview:generateContent',
        p_http_method => 'POST',
        p_body => '{
          "contents": [
            {
              "parts": [
                {
                  "text": "You are an Oracle SQL expert. Return ONLY SQL query. Table: XXQST_INVENTORY (ITEM_NAME, PRICE, QUANTITY, CATEGORY). User Query: ' || :P1_SEARCH || '"
                }
              ]
            }
          ]
        }'
    );

    :P1_RESPONSE := l_response;
END;

🔍 Step 4: Extract SQL from Response

Gemini returns JSON, so we extract SQL:

DECLARE
    l_sql VARCHAR2(4000);
BEGIN
    l_sql := REGEXP_SUBSTR(:P1_RESPONSE, 'SELECT.*', 1, 1);
    :P1_SQL := l_sql;
END;

📊 Step 5: Display Data in APEX Report

Create an Interactive Report

Source:

RETURN :P1_SQL;

🎯 Example

User Input:

Show products where price > 5000

AI Output:

SELECT * FROM XXQST_INVENTORY WHERE PRICE > 5000;

👉 Report auto-generates instantly 🚀

💡 Pro Tips (Very Important)

🔥 1. Use Strong Prompt

Return ONLY SQL query.
Do NOT explain.
No markdown.

🔥 2. Define Table Structure

Always pass table + columns to AI

🔥 3. Handle Errors

• Invalid SQL
• Empty responses
• API limits

⚠️ Common Issues

🔥 Final Result

You now have:

✅ AI-powered search
✅ Natural language → SQL
✅ Auto reports
✅ Zero SQL dependency

🚀 What’s Next?

You can extend this further:

• 📊 Auto charts & dashboards
• 🎤 Voice-based search
• 🌐 Multi-language queries
• 📈 KPI cards

💬 Final Thoughts

Integrating AI with Oracle APEX opens a new world of possibilities.
You’re no longer building just apps—you’re building intelligent systems.

👉 This is how modern Business Intelligence should feel.

🔥 If you found this helpful...

Follow for more Oracle APEX + AI content 🚀
And feel free to connect!

#OracleAPEX #AI #Gemini #SQL #LowCode #Automation #DataAnalytics