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

Introduction

Talking to your data is no longer a futuristic 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 toaistudio.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 permit it 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.