6.2 Autonomous Database Select AI & Natural Language SQL Generation

6.2 Autonomous Database Select AI & Natural Language SQL Generation

Exam Tip: For the 1Z0-1122-26 examination, candidates must memorize the four Select AI action keywords: runsql, showsql, narrate, and chat. You will encounter direct scenario questions evaluating data governance: Select AI transmits only schema metadata (DDL) to the LLM, never the actual database rows or customer data. In addition, remember that all generated SQL queries run under the calling user's existing database security privileges.

---

The Concept of Autonomous Database Select AI

For decades, querying enterprise relational databases required proficiency in Structured Query Language (SQL). Business analysts, operational managers, and domain experts had to understand relational schemas, join constraints, primary-foreign key relationships, and aggregation syntax to extract business insights.

Oracle Autonomous Database Select AI eliminates this technical barrier by integrating generative AI capabilities directly into the database kernel. Select AI allows users to converse with relational databases using everyday natural language (e.g., "Show the top 5 sales representatives by revenue in the Midwest region for Q3"). The database engine intercepts the inquiry, collaborates with a configured Large Language Model to synthesize an optimized SQL query, executes the query against the relational data, and returns the result set—all within an existing SQL session.

+-----------------------------------------------------------------------------+
|                        SELECT AI CONCEPTUAL PARADIGM                        |
|                                                                             |
|   Traditional SQL:   "SELECT rep_name, SUM(revenue) FROM sales..."         |
|                      -> Requires deep SQL and schema knowledge              |
|                                                                             |
|   Select AI:         "SELECT AI Which reps had the highest sales in Q3?"    |
|                      -> Natural English translated to SQL in real time      |
+-----------------------------------------------------------------------------+

---

Execution Mechanics & Query Lifecycle

The operation of Select AI follows a structured, secure four-step execution lifecycle that ensures precise SQL generation while preserving enterprise security:

[User Prompt: SELECT AI ...] 
             │
             ▼ (1) Intercept Prompt
[Autonomous Database Kernel]
             │
             ▼ (2) Augment with Metadata (Tables, Columns, Data Types, Comments)
[Context-Enriched Payload: Prompt + DDL Metadata]
             │
             ▼ (3) HTTPS / REST Request (Metadata Only)
[Large Language Model (e.g., OCI GenAI Cohere / Meta)]
             │
             ▼ (4) Synthesized SQL Statement Returned
[Autonomous Database Engine]
             │
             ▼ (5) Execute SQL under Calling User's Privileges
[Tabular Result Set / Narrative Returned to User]

Step 1: User Prompt Submission

A user or application issues a query using the SELECT AI syntax within any standard database client (such as SQL Developer, SQLcl, Database Actions, or APEX):

SELECT AI runsql show total sales and average discount by product category for 2025;

Step 2: Metadata Retrieval and Context Packaging

The Autonomous Database does not blindly pass the natural language string to the model. Instead, it accesses the database data dictionary to retrieve relevant schema metadata for the tables enabled in the active AI profile. This metadata includes:

• Table and view names
• Column names and SQL data types
• Column and table comments (which provide critical semantic context)
• Primary key, unique, and foreign key constraints

Step 3: LLM Prompt Synthesis and Translation

The database packages the user's natural language question alongside the DDL schema descriptions into an instruction prompt and transmits it to the configured LLM (e.g., OCI Generative AI Cohere Command or Meta Llama) via a secure REST API. The LLM translates the semantic intent into an optimized, syntactically accurate Oracle SQL query.

Step 4: Autonomous Execution and Privilege Enforcement

The generated SQL statement is returned to the Autonomous Database engine. The database verifies that the syntax is valid and executes the query strictly within the security context of the user running the session. If the user lacks SELECT privileges on a referenced table, the query is rejected, preventing privilege escalation.

---

The Four Select AI Actions: Detailed Analysis

Select AI provides four operational action keywords that dictate how the database handles the prompt and processes the LLM output.

1. runsql (Default Execution Action)

• Behavior: Translates the natural language inquiry into SQL, executes the statement against the database, and displays the tabular result set directly to the caller.
• Operational Flow: Prompt $\rightarrow$ SQL Generation $\rightarrow$ Query Execution $\rightarrow$ Tabular Data Output.
• Example Syntax:

  SELECT AI runsql show me the top 10 customers by total invoice amount in 2025;
  

• Use Case: Everyday interactive analysis where business users want immediate quantitative answers without inspecting underlying SQL code.

2. showsql (Inspection and Validation Action)

• Behavior: Translates the natural language prompt into SQL and displays the exact SQL query text without executing it against the data.
• Operational Flow: Prompt $\rightarrow$ SQL Generation $\rightarrow$ SQL Text Output (Execution bypassed).
• Example Syntax:

  SELECT AI showsql list all employees hired after June 2024 with salaries above department average;
  

• Sample Output:

  SELECT e.employee_id, e.first_name, e.last_name, e.salary, e.hire_date
  FROM employees e
  WHERE e.hire_date > DATE '2024-06-01'
    AND e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);
  

• Use Case: Essential for developers, database administrators (DBAs), and compliance auditors to inspect, validate, debug, and optimize generated SQL queries prior to production execution.

3. narrate (Conversational Summarization Action)

• Behavior: Translates the prompt to SQL, executes the query against the database, and feeds the resulting data records back to the LLM to synthesize a natural-language conversational summary of the findings.
• Operational Flow: Prompt $\rightarrow$ SQL Generation $\rightarrow$ Query Execution $\rightarrow$ Result Ingestion $\rightarrow$ Natural Language Narrative Output.
• Example Syntax:

  SELECT AI narrate summarize quarterly customer attrition trends across European branches;
  

• Sample Output:

  "In 2025, customer attrition across European branches averaged 4.2%, with the Frankfurt branch experiencing the lowest attrition at 1.8% and the Madrid branch recording the highest at 6.1%, primarily concentrated in retail banking accounts during Q2."

• Use Case: Executive briefings, dashboard narrative summaries, and automated reporting where business stakeholders require qualitative insights rather than raw data tables.

4. chat (Conversational Dialogue Action)

• Behavior: Routes the natural language prompt directly to the LLM as a general conversational exchange, bypassing the database schema and generating no SQL.
• Operational Flow: Prompt $\rightarrow$ LLM Dialogue $\rightarrow$ Conversational Response.
• Example Syntax:

  SELECT AI chat what are the regulatory differences between IFRS 16 and GAAP lease accounting?;
  

• Use Case: In-session domain explanations, business terminology clarification, or technical definitions where the user does not require database records.

---

Security, Privacy & Enterprise Data Governance

When connecting relational enterprise databases to generative AI services, organizations must maintain rigorous security and regulatory compliance. Autonomous Database Select AI is engineered around strict data governance principles:

Metadata-Only Transmission: No Data Egress

The most critical security guarantee of Select AI is that underlying database records and customer rows never leave the database environment during SQL generation:

• Only structural DDL metadata (table names, column names, data types, comments) and the user's prompt are sent to the external or internal LLM.
• Customer names, credit card numbers, healthcare records, financial amounts, and proprietary rows remain sealed inside the Autonomous Database instance.
• When using the narrate action, only the specific aggregate query output required for summarization is processed, and sensitive fields can be restricted through column masking or view boundaries.

Preserving Existing Database Security Controls

Select AI operates within the standard Oracle Database security framework:

• Least Privilege Enforcement: Queries execute under the authenticated user's database role and privileges. A user cannot query tables or views they do not already have permission to access.
• Virtual Private Database (VPD) & Row-Level Security: Row-level access policies remain fully active. If a regional manager queries the database, VPD policies automatically restrict the generated SQL to records within that manager's authorized region.
• Oracle Database Vault: Protects critical operational schemas against unauthorized DBA or privileged user inspection.

---

Configuring Select AI Profiles with DBMS_CLOUD_AI

Database administrators configure and manage Select AI by creating AI Profiles using the built-in DBMS_CLOUD_AI PL/SQL package. An AI profile specifies the AI provider, authentication credentials, model selection, and the specific database objects made accessible to the LLM.

BEGIN
  -- Create an AI Profile connecting Autonomous Database to OCI Generative AI
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name => 'OCI_GENAI_PROFILE',
    attributes   => '{
      "provider": "oci",
      "credential_name": "OCI_CRED_NAME",
      "model": "cohere.command-r-plus-08-2024",
      "object_list": [
        {"owner": "SH", "name": "CUSTOMERS"},
        {"owner": "SH", "name": "SALES"},
        {"owner": "SH", "name": "PRODUCTS"}
      ],
      "temperature": 0.1
    }'
  );

  -- Set the active AI profile for the current database session
  DBMS_CLOUD_AI.SET_PROFILE(profile_name => 'OCI_GENAI_PROFILE');
END;
/

Key Configuration Attributes

• provider: Specifies the generative AI backend. Supported options include oci (OCI Generative AI service), azure (Azure OpenAI Service), and openai (OpenAI).
• credential_name: References an OCI credential object storing the required API signing key, OCI Resource Principal, or bearer token.
• model: Identifies the specific foundation model, such as Cohere Command R+ or Meta Llama 3 on OCI.
• object_list: Explicitly defines which tables, views, and schemas are exposed to the LLM. Exposing only relevant business tables reduces prompt token size and prevents the LLM from hallucinating queries across unrelated system tables.
• temperature: Set to low values (e.g., 0.0 or 0.1) to ensure deterministic, syntactically correct SQL output.

---

Action Comparison Reference