Oracle Database 23ai ships with a feature that sounds almost too good to be true: type a natural-language question directly in a SQL prompt, and Oracle translates it to valid SQL, executes it, and returns structured results — all without any middleware, Python, or external service call from your application. That feature is Select AI, powered by DBMS_CLOUD_AI. Here is a complete technical walkthrough for enterprise DBAs and developers.

1. How Select AI Works Under the Hood

Select AI does not send your data to an LLM. The flow is:

1. Oracle extracts your schema metadata (table names, column names, comments, sample values if you allow it)
2. It constructs a prompt containing that metadata + your natural-language question
3. It calls the configured LLM (OpenAI, Cohere, OCI GenAI) with that prompt
4. The LLM returns a SQL string — Oracle validates it, then executes it against your live data
5. Results come back as a normal SQL result set

Your actual row data never leaves the database. Only the schema shape is shared with the LLM.

2. Prerequisites

• Oracle Database 23ai (on-premises or OCI)
• ORDS 23.3+ (for REST-based AI endpoints)
• Network ACL allowing the DB to reach the LLM endpoint
• A credential object for the LLM API key

3. Step-by-Step Setup with OpenAI GPT-4o

Step 1 — Create a Credential

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OAI_CRED',
    username        => 'OPENAI',
    password        => 'sk-proj-YOUR_API_KEY_HERE'
  );
END;
/

Step 2 — Create an AI Profile

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name => 'GPT4O_PROFILE',
    attributes   => '{
      "provider"        : "openai",
      "model"           : "gpt-4o",
      "credential_name" : "OAI_CRED",
      "object_list"     : [
        {"owner": "SALES", "name": "ORDERS"},
        {"owner": "SALES", "name": "CUSTOMERS"},
        {"owner": "SALES", "name": "PRODUCTS"}
      ]
    }'
  );
END;
/

The object_list controls which tables the LLM can see — a critical security boundary. Never include tables with PII or credentials in this list.

Step 3 — Enable the Profile for Your Session

EXEC DBMS_CLOUD_AI.SET_PROFILE('GPT4O_PROFILE');

Step 4 — Query in Plain English

-- Natural language query
SELECT AI What were the top 10 products by revenue in Q1 2025, grouped by category?

-- Equivalent SQL that Oracle generates and executes:
SELECT   p.category,
         p.product_name,
         SUM(o.quantity * o.unit_price) AS revenue
FROM     sales.orders o
JOIN     sales.products p ON p.id = o.product_id
WHERE    o.order_date BETWEEN DATE '2025-01-01' AND DATE '2025-03-31'
GROUP BY p.category, p.product_name
ORDER BY revenue DESC
FETCH FIRST 10 ROWS ONLY;

4. Using DBMS_CLOUD_AI.GENERATE for Custom LLM Calls

Beyond Select AI, you can call any LLM from PL/SQL for text generation, classification, or summarisation:

DECLARE
  l_response CLOB;
BEGIN
  l_response := DBMS_CLOUD_AI.GENERATE(
    prompt       => 'Summarise this customer complaint in one sentence: ' || :complaint_text,
    profile_name => 'GPT4O_PROFILE',
    action       => 'narrate'
  );
  DBMS_OUTPUT.PUT_LINE(l_response);
END;
/

Available action values: chat, narrate, summarize, runsql, showsql. The showsql action is invaluable for debugging — it shows the generated SQL without executing it.

5. Using OCI Generative AI (No API Key Cost)

If your Oracle DB is on OCI, you can use OCI GenAI (Cohere Command R+, Llama 3) at no additional LLM cost:

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name => 'OCI_GENAI_PROFILE',
    attributes   => '{
      "provider"        : "oci",
      "model"           : "cohere.command-r-plus",
      "credential_name" : "OCI_CRED",
      "oci_compartment_id": "ocid1.compartment.oc1..xxxxx"
    }'
  );
END;
/

6. Adding Rich Column Comments to Improve Accuracy

The single most effective way to improve Select AI accuracy is to add COMMENT ON COLUMN statements. The LLM uses these as metadata:

COMMENT ON COLUMN orders.status IS
  'Order lifecycle status. Values: PENDING, CONFIRMED, SHIPPED, DELIVERED, CANCELLED, RETURNED. Use DELIVERED for revenue reporting.';

COMMENT ON COLUMN orders.unit_price IS
  'Price in INR at time of purchase. Does not include GST. Always use SUM(quantity * unit_price) for revenue calculations.';

With good column comments, Select AI accuracy on complex analytical queries reaches 85–95% without any fine-tuning.

7. Enterprise Security Considerations

• Schema isolation: Limit object_list to reporting tables only. Never expose USERS, CREDENTIALS, or audit tables.
• Row-level security: VPD (Virtual Private Database) policies are enforced even on AI-generated SQL — the LLM cannot bypass them.
• Audit trail: Enable AUDIT SELECT AI to log every natural-language query and the SQL it generated.
• No training on your data: Oracle's contract with OpenAI and OCI GenAI providers explicitly prohibits using API call content for model training.

Key Takeaways

• Select AI turns Oracle 23ai into a natural-language analytics interface with zero middleware
• Schema metadata — not row data — is sent to the LLM
• OCI GenAI integration removes per-token API cost for OCI-hosted databases
• Column comments are the highest-ROI investment for accuracy improvement
• VPD row security is enforced on all AI-generated SQL

ROSTAN Technologies implements Oracle 23ai Select AI for Oracle EBS and Oracle Fusion Cloud customers, enabling business analysts to query ERP data in plain English without SQL knowledge. Contact our Oracle team for a live demo.