Using Oracle Select AI to Query Your Database With Natural Language

Ask Questions, Get SQL

Oracle Select AI, introduced in Oracle 23ai, lets users query the database using natural language. Instead of writing SQL, users type questions like “show me the top 10 customers by revenue this quarter” and Oracle translates this into SQL, executes it, and returns the results. For APEX developers, this opens the door to conversational data exploration interfaces.

How Select AI Works

Select AI uses a large language model (LLM) to translate natural language into SQL. It sends the user’s question along with your database schema metadata to the LLM, which generates a SQL query. Oracle then executes the SQL and returns the results. The schema metadata is derived from table and column names, comments, and an optional profile that provides additional business context.

Setting Up Select AI

-- Create an AI profile that tells the LLM about your schema
BEGIN
    DBMS_CLOUD_AI.CREATE_PROFILE(
        profile_name => 'SALES_PROFILE',
        attributes   => '{"provider":"OCI",
                          "model":"cohere.command-r-plus",
                          "credential_name":"OCI_AI_CRED",
                          "object_list":[
                              {"owner":"SALES","name":"CUSTOMERS"},
                              {"owner":"SALES","name":"ORDERS"},
                              {"owner":"SALES","name":"ORDER_ITEMS"},
                              {"owner":"SALES","name":"PRODUCTS"}
                          ]}'
    );
END;

-- Set the profile for the session
EXEC DBMS_CLOUD_AI.SET_PROFILE('SALES_PROFILE');

-- Now use natural language in SELECT AI
SELECT AI "show me the top 5 customers by total order amount this year";

Improving Accuracy

The quality of generated SQL depends heavily on how well the LLM understands your schema. Add meaningful column comments to your tables, as these are included in the metadata sent to the LLM:

COMMENT ON COLUMN orders.order_date IS 'Date when the order was placed by the customer';
COMMENT ON COLUMN orders.ship_date IS 'Date when the order was shipped from the warehouse';
COMMENT ON COLUMN customers.ltv IS 'Customer lifetime value in USD';

You can also provide sample questions and their correct SQL in the AI profile to guide the LLM toward the patterns that work for your schema.

Integrating With APEX

Build an APEX page with a text input for the natural language query and a Classic Report that displays the results. The page process calls DBMS_CLOUD_AI to generate the SQL, then the report uses the generated SQL as its source. Add a “Show SQL” toggle that displays the generated query for transparency and debugging.

Guardrails

Select AI generates read-only SELECT queries by default. It does not generate DML (INSERT, UPDATE, DELETE) from natural language to prevent accidental data modification. The object_list in the profile restricts which tables the LLM can reference, preventing access to sensitive tables. Always run generated SQL under a restricted database user with only SELECT privileges on the intended tables.