Oracle Select AI: Natural Language Queries Against Your Data

Oracle Select AI is the feature that lets you write SELECT AI 'show me last quarter's top customers' and get a real SQL answer back. It’s one of the more genuinely useful AI features in Oracle’s stack — and also one of the easiest to misuse.

This post walks through what Select AI actually is, how to set it up, and where it’s the right tool versus where it’ll get you in trouble.

What it actually is

Select AI is a PL/SQL package (DBMS_CLOUD_AI) that ships with Oracle Autonomous Database. When you submit a natural-language prompt, the package:

1. Reads your schema metadata (table names, column names, comments, constraints)
2. Constructs a prompt that includes both your natural-language question and the relevant schema context
3. Sends it to a configured LLM provider
4. Parses the LLM’s SQL response
5. Optionally executes it against your data and returns the rows

The LLM never sees your row data — only your schema. That’s an important design choice for both security and compliance.

Setting it up

Select AI needs three things: credentials for an LLM provider, a profile that ties those credentials to a model, and an authenticated session that points at the profile.

-- 1. Credentials for the LLM provider (one-time)
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OCI_GENAI_CRED',
    user_ocid       => 'ocid1.user.oc1..xxx',
    tenancy_ocid    => 'ocid1.tenancy.oc1..xxx',
    private_key     => '...',
    fingerprint     => '...'
  );
END;

-- 2. Profile: tie credentials to a model and scope it to a schema
BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name => 'SALES_PROFILE',
    attributes   => '{
      "provider": "oci",
      "credential_name": "OCI_GENAI_CRED",
      "object_list": [
        {"owner": "SALES", "name": "ORDERS"},
        {"owner": "SALES", "name": "CUSTOMERS"},
        {"owner": "SALES", "name": "PRODUCTS"}
      ],
      "model": "cohere.command-r-plus"
    }'
  );
END;

-- 3. Activate the profile in this session
BEGIN
  DBMS_CLOUD_AI.SET_PROFILE('SALES_PROFILE');
END;

The provider can be oci (OCI Generative AI), openai, cohere, azure, anthropic, or a few others. Object scoping matters — Select AI only “sees” tables you list, which means you can grant analysts access to question one slice of the warehouse without exposing the whole schema.

The four actions

Once a profile is active, you can call Select AI in four ways:

-- Run the generated SQL and return rows
SELECT AI runsql 'top 5 customers by revenue last quarter';

-- Show the SQL without running it (great for code review)
SELECT AI showsql 'top 5 customers by revenue last quarter';

-- Run and narrate the result in prose
SELECT AI narrate 'top 5 customers by revenue last quarter';

-- Free-form chat (no SQL generation)
SELECT AI chat 'how should I model a customer hierarchy?';

The shortest form (SELECT AI 'prompt') defaults to runsql. The showsql variant is the one to use first — it lets you sanity-check what the LLM thinks you meant before any rows leave the database.

Where it shines

• Ad-hoc data exploration by analysts. An analyst who knows the business but not SQL can answer their own questions instead of queuing up a ticket.
• Quickly drafting complex queries. Even seasoned engineers benefit from showsql to scaffold a starting point for a multi-join query.
• Internal dashboards with a natural-language search box. A simple wrapper around runsql turns “show me X” prompts into live charts.
• Schema discovery. chat against a profile that includes the schema is a fast way to ask “what’s in this database?”

Where it’s the wrong tool

• Production code paths. Select AI is non-deterministic. The same prompt can produce slightly different SQL on different runs. Never embed SELECT AI 'live prompt' in an application’s hot path.
• Poorly named schemas. If your tables are T_A1 and your columns are C_001, the LLM has nothing to work with and will hallucinate joins. Select AI rewards good naming and meaningful column comments.
• Sensitive cross-row analysis. Aggregations are fine, but Select AI isn’t a substitute for purpose-built BI tools when accuracy is regulated.
• High-volume querying. Each prompt is an LLM call, which is slower and more expensive than a cached SQL query. Don’t build hot paths around it.

A few practical habits

• Always use showsql first when developing a new prompt. Validate the SQL before running it.
• Add column comments. COMMENT ON COLUMN sales.orders.amt IS 'order amount in USD'; materially improves the LLM’s accuracy.
• Scope profiles tightly. Don’t grant a profile access to your whole schema — give analysts a profile that covers only the tables they’re meant to query.
• Log prompts. Capture every SELECT AI call to a separate audit table for review.

The honest summary

Select AI is a real, useful feature — not a marketing demo. It works best as a productivity tool for humans (analysts, developers exploring data, internal users) rather than as an autonomous component in production systems.

The mistake is treating it as a replacement for SQL fluency. It isn’t. It’s a fast way to draft, explore, and discover. The SQL it produces still needs the same review you’d give SQL written by anyone else.