Oracle 26ai: Integrating Google Gemini via PL/SQL and UTL_HTTP

 

Introduction

Talking to your data is no longer a future idea. With Oracle AI Database 26ai, Oracle has made it possible to use natural language instead of writing long SQL queries. You can ask questions in simple English and get answers directly from the database.

However, there is a problem.

In the Oracle 26ai Free VirtualBox Appliance, Select AI does not work as expected. Some required AI packages are missing, and even after creating and compiling them manually, DBMS_CLOUD_AI.CREATE_PROFILE still does not work.

The reason is simple: the Free VirtualBox Appliance does not currently support Google Gemini or OpenAI through native Select AI. The feature is present, but external AI providers are not yet enabled in this environment.

This is not a setup mistake. It is a limitation of the current Free release.

In this blog, I will show how to solve this by building our own AI connection using PL/SQL and UTL_HTTP. This method gives full control, works without Select AI, and allows us to use Google Gemini directly from Oracle 26ai.

Why Use a Custom Procedure (ASK_AI) Instead of Select AI?

In the Oracle 26ai Free VirtualBox Appliance, Select AI cannot connect to external AI providers like Google Gemini. Even though the feature exists, native support is not available yet.

Creating a custom PL/SQL procedure allows us to bypass this limitation, control the request format, and use AI models directly through REST APIs. This gives us a reliable and flexible solution that works today.

Prerequisites

1. Oracle AI Database 26ai (or 23ai)

You can download it from the following link: Oracle AIDatabase 26ai Free VirtualBox Appliance

2. Google AI Studio API Key

To connect Oracle to Google Gemini, you need an API key from Google AI Studio. You can get one in just a few minutes:

1. Visit Google AI Studio: Go to aistudio.google.com.

2. Sign In: Use your standard Google/Gmail account.

3. Accept Terms: On your first visit, you'll need to accept the Generative AI terms of service.

4. Click "Get API Key": Look for the "Get API key" button in the top-left sidebar menu.

5. Create Your Key: Click "Create API key". You can choose to create it in a new project (easiest) or an existing Google Cloud project.

6. Copy and Save: Your key (usually starting with AIza...) will appear. Copy it immediately and keep it safe.

Important:
Treat this key like a password. Do not share it or commit it to public repositories.

Implementing the ASK_AI Procedure Using UTL_HTTP

Step 1: Network Security (ACL Configuration)

Oracle 23ai and 26ai follow a secure-by-default design. This means all outbound network access is blocked unless you explicitly allow it.

Before the HR schema can call the Google Gemini API, we must give it permission to connect to Google’s servers. This is done by configuring a network ACL.

Run the following code as the SYS user to allow the HR user to access the Google AI endpoint:

SET SERVEROUTPUT ON;
SET SQLBLANKLINES ON;

BEGIN
  -- 1. Grant 'connect' to the Gemini API host
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => 'generativelanguage.googleapis.com',
    lower_port => 443,
    upper_port => 443,
    ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                       principal_name => 'HR',
                       principal_type => xs_acl.ptype_db));

  -- 2. Grant 'resolve' (DNS) to the Gemini API host
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => 'generativelanguage.googleapis.com',
    ace => xs$ace_type(privilege_list => xs$name_list('resolve'),
                       principal_name => 'HR',
                       principal_type => xs_acl.ptype_db));
END;
/
COMMIT;

Step 2: Give HR Permission to Use Required Packages

Before HR can call Google Gemini or run dynamic SQL, we need to give it permission to use the necessary Oracle packages.

Run the following commands as the SYS user:

GRANT EXECUTE ON UTL_HTTP TO HR;
GRANT EXECUTE ON DBMS_SQL TO HR;

Step 3: Handle SSL Certificates (The “System” Wallet)

Older Oracle AI versions required manually managing wallet files and certificates for HTTPS calls. With 23ai and 26ai, you can use the OS certificate store directly.

In the code, this is done with "UTL_HTTP.SET_WALLET('system:', NULL);" This line tells Oracle to use the system’s trusted certificates. It means you don’t need to create or manage any .p12 or .sso wallet files, the HTTPS call to Google Gemini will work out of the box.

Step 4: Create the ASK_AI Procedure

The ASK_AI procedure is like a mini AI engine inside your database. It does three main things:

1. Send your question to Google Gemini
• It builds a JSON message with your question.
• It calls the Gemini API over HTTPS using UTL_HTTP.
• Important: On the second line of the procedure (l_key := 'YOUR_GEMINI_KEY';) replace 'YOUR_GEMINI_KEY' with your actual Gemini API key.
• The response contains a SQL query generated by Gemini.
2. Extract the SQL from the response
• The procedure reads the JSON response.
• Using JSON_VALUE, it pulls out the SQL string that Gemini returned.
• It also cleans up any extra formatting, like Markdown code blocks or semicolons.
3. Run the SQL and show the results
• It uses DBMS_SQL to run the SQL dynamically.
• It detects the columns, prints headers, and loops through the rows to display results in DBMS_OUTPUT.

CREATE OR REPLACE PROCEDURE ASK_AI (p_question IN VARCHAR2) AS
    l_key      VARCHAR2(200) := 'YOUR_GEMINI_KEY';
    l_url      VARCHAR2(1000);
    l_body     CLOB;
    l_json     CLOB := '';
    l_sql      VARCHAR2(4000);
    l_req      UTL_HTTP.req;
    l_resp     UTL_HTTP.resp;
    l_text     VARCHAR2(32767);
   
    -- Variables for dynamic execution
    l_cur      NUMBER;
    l_col_cnt  NUMBER;
    l_desc     DBMS_SQL.DESC_TAB;
    l_val      VARCHAR2(4000);
    l_status   NUMBER;
BEGIN
    UTL_HTTP.SET_WALLET('system:', NULL);
    l_url := 'https://generativelanguage.googleapis.com/v1beta/models/gemini-2.5-flash:generateContent?key=' || l_key;
    l_body := '{"contents": [{"parts":[{"text": "Return only the Oracle SQL for: ' || p_question || ' on table HR.EMPLOYEES. No markdown."}]}]}';

    -- 1. Get the SQL from Gemini
    l_req := UTL_HTTP.BEGIN_REQUEST(l_url, 'POST', 'HTTP/1.1');
    UTL_HTTP.SET_HEADER(l_req, 'Content-Type', 'application/json');
    UTL_HTTP.SET_HEADER(l_req, 'Content-Length', LENGTH(l_body));
    UTL_HTTP.WRITE_TEXT(l_req, l_body);
    l_resp := UTL_HTTP.GET_RESPONSE(l_req);

    BEGIN
        LOOP
            UTL_HTTP.READ_TEXT(l_resp, l_text, 32767);
            l_json := l_json || l_text;
        END LOOP;
    EXCEPTION WHEN UTL_HTTP.END_OF_BODY THEN UTL_HTTP.END_RESPONSE(l_resp);
    END;

    IF l_resp.status_code = 200 THEN
        l_sql := JSON_VALUE(l_json, '$.candidates[0].content.parts[0].text');
        l_sql := REPLACE(REPLACE(l_sql, '```sql', ''), '```', '');
        l_sql := RTRIM(TRIM(l_sql), ';');
       
        DBMS_OUTPUT.PUT_LINE('SQL: ' || l_sql);
        DBMS_OUTPUT.PUT_LINE(RPAD('-', 60, '-'));

        -- 2. DYNAMICALLY EXECUTE AND SHOW RESULTS
        l_cur := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(l_cur, l_sql, DBMS_SQL.NATIVE);
        DBMS_SQL.DESCRIBE_COLUMNS(l_cur, l_col_cnt, l_desc);

        -- Define columns (we treat everything as strings for simple display)
        FOR i IN 1..l_col_cnt LOOP
            DBMS_SQL.DEFINE_COLUMN(l_cur, i, l_val, 4000);
            DBMS_OUTPUT.PUT(RPAD(l_desc(i).col_name, 20)); -- Print Header
        END LOOP;
        DBMS_OUTPUT.NEW_LINE;
        DBMS_OUTPUT.PUT_LINE(RPAD('-', 60, '-'));

        l_status := DBMS_SQL.EXECUTE(l_cur);

        -- Fetch and print each row
        WHILE DBMS_SQL.FETCH_ROWS(l_cur) > 0 LOOP
            FOR i IN 1..l_col_cnt LOOP
                DBMS_SQL.COLUMN_VALUE(l_cur, i, l_val);
                DBMS_OUTPUT.PUT(RPAD(NVL(l_val, ' '), 20));
            END LOOP;
            DBMS_OUTPUT.NEW_LINE;
        END LOOP;
       
        DBMS_SQL.CLOSE_CURSOR(l_cur);
    ELSE
        DBMS_OUTPUT.PUT_LINE('API Error: ' || l_json);
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        IF DBMS_SQL.IS_OPEN(l_cur) THEN DBMS_SQL.CLOSE_CURSOR(l_cur); END IF;
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

I connected using the HR user and ran the code in SQLcl, which is installed by default on the desktop of the Oracle AI Database 26ai (or 23ai).

Step 5: Execute and Verify

Now it’s time to see your ASK_AI procedure in action. To view the results in our terminal (SQLcl, SQL*Plus, or SQL Developer), I first need to enable the output buffer. This allows DBMS_OUTPUT messages to be displayed.

As the HR user, run the following commands:

SET SERVEROUTPUT ON SIZE UNLIMITED;
EXEC ASK_AI('Who are the top 3 earners?');

In the above image, we can see:

• The SQL query was generated by Gemini.
• A neatly formatted table with the top 3 highest-paid employees from HR.EMPLOYEES table.

To demonstrate the power of the ASK_AI procedure, I ran the following commands and included the output below:

EXEC ASK_AI('What is the average salary by department?');
EXEC ASK_AI('Show employees along with their department names');
EXEC ASK_AI('How many employees earn above 5000 in each department?');

Conclusion

Oracle AI Database 26ai makes it possible to work with AI models directly in SQL. Free versions or early builds may have limits, but using PL/SQL and UTL_HTTP lets you connect to powerful models like Google Gemini.

This gives you flexibility and control, letting you explore data, run reports, or build AI-driven apps—all straight from your SQL console.