What Is Select AI in Oracle 26ai? Natural Language Database Queries Explained

Select AI in Oracle 26ai is an inner large-language model (LLM) embedded inside the Autonomous Database that converts plain English questions into executable SQL using the DBMS_CLOUD_AI PL/SQL package, keeping all data processing local to the database.

Oracle 26ai introduces Select AI as a built-in capability that enables developers to query database tables using natural language without writing SQL. According to the oracle-devrel/oracle-ai-developer-hub reference implementation, this feature leverages an internal LLM that resides within the database kernel, allowing applications to send English prompts and receive either narrated summaries or raw result sets while ensuring data never leaves the Autonomous Database environment.

How Select AI Works

Select AI operates as an "inner" LLM that sits inside the Oracle Autonomous Database, distinct from external models like GPT-4o or Claude that might handle initial user interactions. The architecture follows a strict security model where the LLM processing happens entirely within database memory.

The Inner LLM Architecture

When an application submits a natural language query, Select AI processes the request through DBMS_CLOUD_AI.GENERATE. This function accepts three critical parameters: the prompt (the English question), the profile_name (configuration defining allowed tables and LLM provider), and the action (determining output format). The LLM generates appropriate SQL syntax based on the database schema metadata and executes it immediately, returning results without exposing the underlying query logic to external networks.

Prompt Engineering via Schema Comments

Select AI uses table- and column-level comments as contextual prompts to guide accurate SQL generation. In apps/tanstack-shoe-store/seed.sql, descriptive comments attached to the PRODUCTS, CUSTOMERS, and TRANSACTIONS tables inform the model about data relationships, data types, and business context. These comments act as the primary mechanism for grounding the LLM, ensuring generated queries reference correct columns and understand semantic meaning (such as distinguishing between "price" and "cost").

Configuring Select AI with DBMS_CLOUD_AI

Implementation requires two distinct setup phases: creating secure credentials for external LLM providers and defining a Select AI profile that scopes which tables the model may access.

Creating Credentials and Profiles

First, establish a credential object using DBMS_CLOUD.CREATE_CREDENTIAL to securely store the API key for your LLM provider (Anthropic, in this reference implementation). Then invoke DBMS_CLOUD_AI.CREATE_PROFILE to define the schema objects available for querying.

Execute the following as the SHOESTORE user in apps/tanstack-shoe-store/setup-selectai.sql:

-- Create Anthropic credential
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'ANTHROPIC_CRED',
    username        => 'ANTHROPIC',
    password        => '<your-anthropic-api-key>'
  );
END;
/

-- Create the Select AI profile pointing at the three tables
BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name => 'SHOESTORE_AI',
    attributes   => '{
      "provider": "anthropic",
      "credential_name": "ANTHROPIC_CRED",
      "object_list": [
        {"owner": "SHOESTORE", "name": "PRODUCTS"},
        {"owner": "SHOESTORE", "name": "CUSTOMERS"},
        {"owner": "SHOESTORE", "name": "TRANSACTIONS"}
      ],
      "model": "claude-sonnet-4-20250514"
    }'
  );
END;
/

This profile restricts the LLM to only generate queries against explicitly listed tables, preventing unauthorized access to sensitive schemas.

Generating SQL from Natural Language

Once configured, applications invoke DBMS_CLOUD_AI.GENERATE with different action modes to control output behavior. The function supports four distinct actions: narrate (human-readable summary), runsql (execute and return rows), showsql (display generated SQL only), and chat (conversational context).

Narrating Results

Use the narrate action to receive conversational summaries ideal for chat interfaces:

SELECT DBMS_CLOUD_AI.GENERATE(
  prompt       => 'how many products do we have',
  profile_name => 'SHOESTORE_AI',
  action       => 'narrate'
) FROM DUAL;

This returns a natural language response such as "We have 20 products in the catalog," generated by the inner LLM after executing the underlying SELECT COUNT(*) query.

Executing Raw SQL

For application processing or reporting tools, use runsql to return the actual result set:

SELECT DBMS_CLOUD_AI.GENERATE(
  prompt       => 'list the top-5 best-selling shoe brands',
  profile_name => 'SHOESTORE_AI',
  action       => 'runsql'
) FROM DUAL;

Debugging Generated Queries

During development, validate SQL generation without execution using showsql:

SELECT DBMS_CLOUD_AI.GENERATE(
  prompt       => 'show me all shoes priced above $150',
  profile_name => 'SHOESTORE_AI',
  action       => 'showsql'
) FROM DUAL;

This returns the constructed SQL statement (e.g., SELECT * FROM SHOESTORE.PRODUCTS WHERE price > 150) allowing developers to verify query logic before production deployment.

Data Security and Residency Benefits

Select AI's architecture ensures complete data residency—the LLM processes metadata and executes queries entirely within the Autonomous Database compute layer. Unlike external AI services that require shipping data to third-party APIs, Select AI keeps sensitive information inside Oracle's secured infrastructure. The outer LLM (Claude, GPT-4o, or Gemini) in the client application merely decides when a database query is necessary and forwards the English question; the actual data retrieval and processing happen inside the database kernel, as implemented in apps/tanstack-shoe-store/src/lib/oracle-tools.ts.

Summary

• Select AI in Oracle 26ai embeds an LLM directly inside the Autonomous Database to enable natural language SQL generation.
• DBMS_CLOUD_AI.GENERATE is the core function that accepts English prompts and returns results via actions: narrate, runsql, showsql, or chat.
• Schema comments serve as prompt engineering context, allowing the model to understand table structures without exposing data dictionaries externally.
• Select AI profiles restrict LLM access to specific tables and link to provider credentials created via DBMS_CLOUD.CREATE_CREDENTIAL.
• Data residency is maintained because the LLM executes SQL inside the database; no data transmits to external AI services during query processing.

Frequently Asked Questions

How does Select AI differ from external LLM integrations?

Select AI operates as an "inner" LLM that lives within the Autonomous Database kernel, whereas external integrations require sending data to third-party APIs. When using Select AI, as shown in apps/tanstack-shoe-store/README.md, the LLM generates and executes SQL entirely inside the database boundary, ensuring sensitive data never leaves Oracle's infrastructure while external models (like Claude or GPT-4o) only handle the initial natural language understanding in the application layer.

What are Select AI profiles and why are they required?

A Select AI profile, created via DBMS_CLOUD_AI.CREATE_PROFILE, defines the security boundary for natural language queries by explicitly listing which tables and schemas the LLM may access, along with the LLM provider credentials and model version (such as claude-sonnet-4-20250514). This profile acts as a governance layer ensuring the inner LLM cannot generate SQL against unauthorized tables or sensitive system catalogs.

Can Select AI handle complex joins and aggregations?

Yes, Select AI can generate sophisticated SQL including joins, aggregations, and window functions, provided the schema comments in tables like PRODUCTS, CUSTOMERS, and TRANSACTIONS offer sufficient context about relationships and business logic. The model uses these comments as semantic grounding to understand that "customer purchases" likely requires joining the CUSTOMERS and TRANSACTIONS tables on a foreign key relationship.

Is training data required to use Select AI?

No fine-tuning or custom model training is required. Select AI utilizes the existing schema metadata (table names, column names, and comments) combined with the foundation model's pre-trained SQL generation capabilities. As demonstrated in apps/tanstack-shoe-store/seed.sql, simply adding descriptive comments to your tables is sufficient preparation for accurate natural language querying.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}