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.
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.
You can download it from the following link: Oracle AIDatabase 26ai Free VirtualBox Appliance
To connect Oracle to Google Gemini, you need an API key from Google AI Studio. You can get one in just a few minutes:
Important:
Treat this key like a password. Do not share it or commit it to public repositories.
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;
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;
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.
The ASK_AI procedure is like a mini AI engine inside your database. It does three main things:
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:
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?');
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.