Oracle DB AI: Select AI & natural-language SQL

Select AI lets you type a question in plain English and get SQL back, generated by an AI provider you choose and limited to the schema objects you allow. This guide walks through the setup on Autonomous AI Database, shows how to inspect the SQL before you trust it, and explains how Select AI relates to AI Vector Search and JSON Relational Duality.

1. Grant access (as ADMIN)

The user who runs Select AI needs EXECUTE on DBMS_CLOUD_AI and network access to the provider's API host. This example uses OpenAI and the HR sample schema; swap in your own user and provider host.

GRANT EXECUTE ON DBMS_CLOUD_AI TO HR;

BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => 'api.openai.com',
    ace  => xs$ace_type(privilege_list => xs$name_list('http'),
                        principal_name => 'HR',
                        principal_type => xs_acl.ptype_db));
END;
/

2. Store the provider credential

Store the API key as a database credential with DBMS_CLOUD.CREATE_CREDENTIAL. For OpenAI, password is your secret API key. Other providers (OCI Generative AI, Azure OpenAI, AWS…) use different credential types — check Oracle's provider examples.

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'AI_CRED',
    username        => 'OPENAI',
    password        => '<your_api_key>');
END;
/

3. Create an AI profile

An *AI profile* names the provider, the credential and, through object_list, the tables and views whose metadata Select AI may use. provider and credential_name are required. Set model explicitly to a model from Oracle's *Select your AI Provider and LLMs* list for your provider (Oracle documents no default chat model for OpenAI; Azure OpenAI ignores model and uses your deployment). "comments": true adds your table and column comments to the metadata sent to the model.

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name => 'HR_AI',
    attributes   => '{"provider": "openai",
                      "credential_name": "AI_CRED",
                      "model": "gpt-5",
                      "comments": true,
                      "object_list": [{"owner": "HR", "name": "EMPLOYEES"},
                                      {"owner": "HR", "name": "DEPARTMENTS"}]}');
END;
/

4. Ask in natural language

Set the profile once per session (connection), then use SELECT AI <action> <prompt>. With no action, the default is runsql: the SQL is generated and executed.

EXEC DBMS_CLOUD_AI.SET_PROFILE('HR_AI');

SELECT AI how many employees per department earn above average;

-- see the generated SQL instead of running it
SELECT AI showsql how many employees per department earn above average;

• runsql (default) — generate and run the SQL.
• showsql — show the generated SQL without running it.
• explainsql — explain the generated SQL in plain language.
• narrate — run the SQL and return a prose answer (sends the result rows to the provider).
• chat — send the prompt straight to the LLM, no SQL generation.

Stateless connections and APEX

SET_PROFILE only lasts for the session. For stateless or pooled connections — and in APEX and Database Actions, where the SELECT AI keyword is not supported — call DBMS_CLOUD_AI.GENERATE and pass the profile on every call.

SELECT DBMS_CLOUD_AI.GENERATE(
         prompt       => 'how many employees per department earn above average',
         profile_name => 'HR_AI',
         action       => 'showsql')
FROM dual;

What leaves the database

To generate SQL, Oracle sends your prompt plus schema metadata only — table and column definitions, plus comments, annotations and constraints when you enable them in the profile (comments, annotations, constraints) — not row values. narrate is the exception: it sends the query result to the provider. Leave sensitive tables out of object_list, and add "enforce_object_list": true if generated SQL must stay within the listed objects.

Where the rest of DB AI fits

• AI Vector Search — semantic similarity search over embeddings stored in the database; Select AI can use a vector index for RAG (see the Vector Search guide).
• JSON Relational Duality views — the same data exposed as relational tables and as JSON documents, convenient for app backends.
• Select AI narrate — a prose answer instead of rows, for chat-style apps.

Check your understanding