"How many urgent tickets came from customers in India?" A manager who asks that needs a query, and the people who can write it have other work. A language model knows SQL, and given a good description of your tables, it can translate a question into a query. That is natural language to SQL, one of the most requested AI features in business applications.
This guide builds the generation half in Oracle AI Database 26ai: a function that describes the allowed tables to the model from the data dictionary, and a function that asks Gemini for one query as JSON. Running generated SQL safely is the other half, covered separately.
Code for This Guide
The examples are files 01 and 02 in the examples/ch13 folder of the Oracle AI code repository on GitHub, each with its output.
These examples come from AI Applications with Oracle Database 26ai and APEX 26.1, a book of 237 tested examples of AI in Oracle Database and APEX.
They describe the help desk tables of the sample schema from setup/atlas, and call Gemini through the GENERATE function from how to make LLM calls reliable in PL/SQL.
Select AI or Your Own
Oracle Autonomous Database has this feature built in, as Select AI (DBMS_CLOUD_AI). Oracle AI Database 26ai Free and on-premises databases do not have DBMS_CLOUD_AI, and building the feature yourself also shows what any implementation must take care of: a good schema description, a clear prompt, checks before running, and least privilege.
Select AI itself is described in Oracle Select AI: NL2SQL, RAG, and in-database agents across 26ai and 19c.
Describe the Schema
A model cannot write a query for tables it does not know. The prompt must describe them: names, columns, what they mean, how they join, and the values of coded columns. A model that does not know a status is 'Open' will write 'open' or 'OPEN' and find nothing.
The data dictionary has all of it. NL2SQL_TABLES lists the tables questions may use, and DESCRIBE_SCHEMA describes them from USER_TAB_COLUMNS, the table and column comments, the foreign keys, and, for each text column with at most eight short values, the values themselves.
Example:
-- the tables questions may use, and a function that describes them for the model
create table nl2sql_tables (
table_name varchar2(128) constraint nl2sql_tables_pk primary key
);
insert into nl2sql_tables values
('PRODUCTS'), ('CUSTOMERS'), ('AGENTS'), ('TICKETS'), ('TICKET_COMMENTS'),
('KB_ARTICLES');
commit;
create or replace function describe_schema return clob
is
l_text clob;
l_values varchar2(4000);
begin
for t in (select n.table_name, c.comments
from nl2sql_tables n
left join user_tab_comments c on c.table_name = n.table_name
order by n.table_name) loop
l_text := l_text || 'Table ' || t.table_name
|| case when t.comments is not null then ' -- ' || t.comments end || chr(10);
for c in (select col.column_name, col.data_type, cc.comments
from user_tab_columns col
left join user_col_comments cc
on cc.table_name = col.table_name
and cc.column_name = col.column_name
where col.table_name = t.table_name
and col.data_type not like 'VECTOR%'
and col.column_name not like 'AI\_%' escape '\'
order by col.column_id) loop
-- the values of a text column with few, short values: 'Open', 'Closed', ...
l_values := null;
if c.data_type = 'VARCHAR2' then
begin
execute immediate 'select listagg(distinct ''''''''|| ' || c.column_name
|| ' || '''''''', '', '') from ' || t.table_name
|| ' having count(distinct ' || c.column_name || ') <= 8'
|| ' and max(length(' || c.column_name || ')) <= 20'
into l_values;
exception
when no_data_found then null; -- many or long values: list none
end;
end if;
l_text := l_text || ' ' || c.column_name || ' ' || c.data_type
|| case when c.comments is not null then ' -- ' || c.comments end
|| case when l_values is not null then ' values: ' || l_values end
|| chr(10);
end loop;
end loop;
-- how the tables join
for f in (select c.table_name, cc.column_name, r.table_name as ref_table
from user_constraints c
join user_cons_columns cc on cc.constraint_name = c.constraint_name
join user_constraints r on r.constraint_name = c.r_constraint_name
where c.constraint_type = 'R'
and c.table_name in (select table_name from nl2sql_tables)
and r.table_name in (select table_name from nl2sql_tables)
order by 1, 2) loop
l_text := l_text || 'Join ' || f.table_name || '.' || f.column_name
|| ' to ' || f.ref_table || chr(10);
end loop;
return l_text;
end;
/
select describe_schema() as schema_description from dual;Output:
Table NL2SQL_TABLES created. 6 rows inserted. Commit complete. Function DESCRIBE_SCHEMA compiled SCHEMA_DESCRIPTION __________________________________________________________________________________________________ Table AGENTS -- The support agents of Atlas Support AGENT_ID NUMBER NAME VARCHAR2 values: 'Priya Raman', 'Daniel Okafor', 'Sofia Marquez', 'Liam Chen', 'Hannah Becker', 'Arjun Mehta', 'Emma Lindqvist', 'Yusuf Demir' TEAM VARCHAR2 values: 'Tier 1', 'Tier 2', 'Billing', 'Engineering' EMAIL VARCHAR2 Table CUSTOMERS -- Companies that use Atlas products, with their main contact CUSTOMER_ID NUMBER COMPANY VARCHAR2 CONTACT_NAME VARCHAR2 EMAIL VARCHAR2 COUNTRY VARCHAR2 PLAN VARCHAR2 values: 'Professional', 'Basic', 'Enterprise' CUSTOMER_SINCE DATE Table KB_ARTICLES -- Knowledge base: short help articles that answer common questions ARTICLE_ID VARCHAR2 PRODUCT_ID NUMBER -- The product the article is about; null for articles about the Atlas account TITLE VARCHAR2 BODY CLOB UPDATED_ON DATE Table PRODUCTS -- The six software products of Atlas Software PRODUCT_ID NUMBER NAME VARCHAR2 values: 'Atlas Analytics', 'Atlas Billing', 'Atlas CRM', 'Atlas Connect', 'Atlas Mobile', 'Atlas Sync' CATEGORY VARCHAR2 values: 'Sales', 'Finance', 'Mobile', 'Reporting', 'Integration', 'Desktop' CURRENT_VERSION VARCHAR2 values: '8.4', '5.2', '3.9', '6.1', '4.0', '2.7' DESCRIPTION VARCHAR2 Table TICKETS -- Support requests: what the customer wrote, priority, status, and outcome TICKET_ID NUMBER CUSTOMER_ID NUMBER PRODUCT_ID NUMBER AGENT_ID NUMBER SUBJECT VARCHAR2 DESCRIPTION CLOB PRIORITY VARCHAR2 values: 'Low', 'High', 'Normal', 'Urgent' STATUS VARCHAR2 values: 'Open', 'Closed', 'Resolved', 'In Progress', 'Waiting' CATEGORY VARCHAR2 values: 'Account', 'Billing', 'Bug', 'Question', 'Feature Request' CHANNEL VARCHAR2 values: 'Portal', 'Email', 'Chat', 'Phone' CREATED_AT TIMESTAMP(6) RESOLVED_AT TIMESTAMP(6) SATISFACTION NUMBER -- Customer rating from 1 (poor) to 5 (excellent), given when the ticket is closed Table TICKET_COMMENTS -- The conversation of each ticket after the first message COMMENT_ID NUMBER TICKET_ID NUMBER AUTHOR_TYPE VARCHAR2 values: 'Agent', 'Customer' AUTHOR_NAME VARCHAR2 BODY CLOB CREATED_AT TIMESTAMP(6) Join KB_ARTICLES.PRODUCT_ID to PRODUCTS Join TICKETS.AGENT_ID to AGENTS Join TICKETS.CUSTOMER_ID to CUSTOMERS Join TICKETS.PRODUCT_ID to PRODUCTS Join TICKET_COMMENTS.TICKET_ID to TICKETS
Three choices keep the description useful:
- Vector columns and the AI_ columns written by a model are left out: they mean nothing to a question.
- Long or numerous values are left out: listing every email address would only cost tokens.
- Comments are included: the comment on SATISFACTION tells the model what 1 and 5 mean.
Good comments on tables and columns are the cheapest improvement to generated SQL, and to every person who reads the schema.
Generate the Query
GENERATE_SQL sends the instructions, today's date, the description, and the question to Gemini, and asks for the query as JSON with a single field, so no Markdown or explanation comes with it.
Example:
create or replace function generate_sql (p_question in varchar2) return clob
is
l_answer clob;
begin
l_answer := generate(
'Write one Oracle SQL query that answers the question, using only the tables below. '
|| 'Return a single SELECT statement without a semicolon. Use the values exactly '
|| 'as listed. Today is ' || to_char(sysdate, 'DD Month YYYY') || '.' || chr(10)
|| describe_schema() || chr(10) || 'Question: ' || p_question,
'GEMINI',
json('{"generationConfig": {"temperature": 0,
"responseMimeType": "application/json",
"responseSchema": {"type": "OBJECT",
"properties": {"sql": {"type": "STRING"}},
"required": ["sql"]}}}'));
return json_value(l_answer, '$.sql' returning clob);
end;
/
select generate_sql('How many open tickets does each product have?') as generated_sql
from dual;Output:
Function GENERATE_SQL compiled GENERATED_SQL __________________________________________________________________________________________________ SELECT p.NAME, COUNT(t.TICKET_ID) AS OPEN_TICKETS FROM PRODUCTS p LEFT JOIN TICKETS t ON p.PRODUCT_ID = t.PRODUCT_ID AND t.STATUS = 'Open' GROUP BY p.NAME
A correct query, and a careful one: the LEFT JOIN with the status condition inside the join keeps products with no open tickets in the result, with a count of 0.
| Choice | Why |
|---|---|
| Thinking left on | A query is a small program, a task that benefits from reasoning. |
| Temperature 0 | The same question should give the same query. |
| Today's date in the prompt | Questions such as "this month" need it, and the model does not know what day it is. |
| JSON with one field | The answer is the statement only, ready to check and run. |
| "Use the values exactly as listed" | Status and plan values must match the data, case included. |
The Real Risk: Plausible Wrong Answers
A generated statement is code the database will run, and it must never run unchecked: a model asked to delete rows may write a DELETE despite its instructions. That is the obvious risk, and checks and privileges handle it.
The subtler risk is ambiguity. Asked which agents "resolved" the most tickets, the model may count STATUS = 'Resolved' and leave out closed tickets, which were also resolved. The query is valid and runs, and answers a slightly different question without saying so. Reduce it by describing the meaning of statuses in column comments, showing users the SQL or a description of what was counted, and testing with the questions your users really ask.
Conclusion
To generate SQL from natural language in Oracle, describe the allowed tables to the model from the data dictionary, with comments, joins, and the values of coded columns, then ask the model for one SELECT statement as JSON, with today's date in the prompt and thinking on. Comments are the cheapest way to improve the result. Treat every generated statement as untrusted input: check it, run it with read-only privileges, and show users what was counted.
