Natural language to SQL (NL2SQL) is one of the most practical ways to bring generative AI into everyday data work. Instead of starting with table names, joins, filters, and GROUP BY clauses, you start with the business question you want answered. Oracle Select AI uses schema metadata and database context to generate SQL that is specific to your data model.

That context matters because plausible SQL is not enough. Generated SQL needs to reference the right objects, reflect the way your schema represents business concepts, choose valid join paths, and work within the security model you already depend on. Oracle Select AI supports this database-centered approach from Oracle AI Database and Oracle Autonomous AI Database, with capabilities that go well beyond basic text-to-SQL.

Earlier blog posts introduced Oracle Select AI for natural language querying, shared text-to-SQL guidance, and highlighted features such as feedback and automated object selection. This post brings those ideas together as a set of best practices for improving NL2SQL accuracy, from AI profile design and metadata enrichment to prebuilt agents and ready-made chatbot applications.

Start with the AI profile

The AI profile is the control plane for Oracle Select AI. It defines the AI provider, model-related attributes, credentials, eligible database objects, metadata options, and other behavior that shapes generated SQL. When you use the SQL command line SELECT AI SHOWSQL, RUNSQL, or EXPLAINSQL, Oracle Select AI uses the active profile to decide what metadata to include in the augmented prompt sent to the model.

This makes the profile the key control point for NL2SQL accuracy. You can start with a small, curated set of objects for a focused application, or use automated object selection when the schema is too large to maintain object lists manually. You can also enable table and column comments, annotations, and constraints so the model receives a richer description of the schema.

Note: The examples below use the SH sample schema and illustrative credential names. Adjust object owners, credentials, providers, and model attributes for your environment.

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name => 'SALES_NL2SQL_PROFILE',
    attributes   => '{
      "provider": "oci",
      "credential_name": "GENAI_CRED",
      "object_list": [
        {"owner": "SH", "name": "SALES"},
        {"owner": "SH", "name": "CUSTOMERS"},
        {"owner": "SH", "name": "PRODUCTS"},
        {"owner": "SH", "name": "TIMES"}
      ],
      "object_list_mode": "all",

      "enforce_object_list": true,
      "comments": true,
      "annotations": true,
      "constraints": true,
      "temperature": 0
    }',
    status      => 'enabled',
    description => 'Profile for sales NL2SQL examples'
  );
END;
/

EXEC DBMS_CLOUD_AI.SET_PROFILE('SALES_NL2SQL_PROFILE');

SELECT AI SHOWSQL
  what were total sales by product category last quarter;

In this example, the profile limits the initial NL2SQL scope to four SH objects and enables richer metadata. Because there are few tables, object_list_mode is set to “all”. If the schema has many tables, you can specify “automated” instead to have Oracle Select AI determine which to use dynamically. To enforce that only tables listed can be used, you can explicitly require this using enforce_object_list set to true. The comments, annotations, and constraints attributes influence what schema context Oracle Select AI can include in the augmented prompt sent to the model.

Give the model better schema meaning with comments

One of the simplest ways to improve NL2SQL accuracy is to describe what your tables and columns mean in business terms. This is especially useful when physical names are abbreviated, legacy-oriented, or understandable only to people who already know the application.

For example, a column named AMOUNT_SOLD may be clear enough to a data warehouse developer, but a business prompt is more likely to use words such as revenue, sales, or purchase amount. Comments give Oracle Select AI more natural language context to connect those terms to the right columns.

COMMENT ON TABLE sh.sales IS
  'Fact table containing sales transactions by customer, product, channel, promotion, and time.';

COMMENT ON COLUMN sh.sales.amount_sold IS
  'Revenue amount for each sales transaction.';

COMMENT ON COLUMN sh.sales.time_id IS
  'Date key used to analyze sales by day, month, quarter, and year.';

COMMENT ON TABLE sh.products IS
  'Product dimension containing product names, categories, and descriptions.';

COMMENT ON COLUMN sh.products.prod_category IS
  'Business category used for product-level revenue analysis.';

BEGIN
  DBMS_CLOUD_AI.SET_ATTRIBUTE(
    profile_name    => 'SALES_NL2SQL_PROFILE',
    attribute_name  => 'comments',
    attribute_value => 'true'
  );
END;
/

SELECT AI SHOWSQL
  show revenue by product category for the most recent full quarter;

With comments enabled, Oracle Select AI can include table and column comments as metadata for natural language translation. This helps the model understand that revenue should map to AMOUNT_SOLD and product category should map to PROD_CATEGORY, without requiring the prompt to use exact column names.

Use annotations for richer, structured metadata

Comments are valuable, but annotations can add more structured meaning. You can use annotations to capture business labels, synonyms, usage guidance, or application-specific hints that should travel with the schema. For NL2SQL, annotations are helpful when you want to express how people talk about the data, not just how the database stores it.

Oracle Select AI includes annotations in the augmented prompt when the profile attribute annotations is true. This gives you another way to improve prompt grounding without hard-coding business vocabulary in the application layer.

ALTER TABLE sh.sales ANNOTATIONS (
  ADD Business_Description 'Fact table for revenue and unit sales analysis',
  ADD NL2SQL_Hint 'Use AMOUNT_SOLD for revenue and QUANTITY_SOLD for units sold'
);

ALTER TABLE sh.sales MODIFY amount_sold ANNOTATIONS (
  ADD Business_Term 'Revenue',
  ADD Synonyms 'sales amount, purchase amount, booked revenue'
);

ALTER TABLE sh.products MODIFY prod_category ANNOTATIONS (
  ADD Business_Term 'Product category',
  ADD Synonyms 'category, product line, merchandise category'
);

BEGIN
  DBMS_CLOUD_AI.SET_ATTRIBUTE(
    profile_name    => 'SALES_NL2SQL_PROFILE',
    attribute_name  => 'annotations',
    attribute_value => 'true'
  );
END;
/

SELECT AI SHOWSQL
  which product lines had the highest booked revenue this year;

In this example, the prompt uses product lines and booked revenue. The annotations provide additional signals that product line is a synonym for product category and booked revenue should use AMOUNT_SOLD. That type of metadata is especially useful when business users do not speak in table and column names.

Since annotations are a 26ai database feature, use this option when your Oracle AI Database or Oracle Autonomous AI Database environment supports annotations and your schema is annotated with the business terms you want Oracle Select AI to use.

Improve join accuracy with referential integrity

NL2SQL accuracy often depends on join quality. If the prompt asks for revenue by customer region and product category, the generated SQL must join the sales fact table to the correct dimensions. When primary keys and foreign keys are present and the profile attribute constraints is true, Oracle Select AI can include referential integrity constraints as metadata for the model.

This is a useful reminder for AI readiness: a well-modeled schema is easier for both people and AI to query. Constraints do more than enforce data quality. They can also provide relationship information that helps Oracle Select AI generate SQL.

ALTER TABLE sh.customers ADD CONSTRAINT customers_pk
  PRIMARY KEY (cust_id);

ALTER TABLE sh.products ADD CONSTRAINT products_pk
  PRIMARY KEY (prod_id);

ALTER TABLE sh.times ADD CONSTRAINT times_pk
  PRIMARY KEY (time_id);

ALTER TABLE sh.sales ADD CONSTRAINT sales_customer_fk
  FOREIGN KEY (cust_id) REFERENCES sh.customers(cust_id);

ALTER TABLE sh.sales ADD CONSTRAINT sales_product_fk
  FOREIGN KEY (prod_id) REFERENCES sh.products(prod_id);

ALTER TABLE sh.sales ADD CONSTRAINT sales_time_fk
  FOREIGN KEY (time_id) REFERENCES sh.times(time_id);

BEGIN
  DBMS_CLOUD_AI.SET_ATTRIBUTE(
    profile_name    => ‘SALES_NL2SQL_PROFILE’,
    attribute_name  => ‘constraints’,
    attribute_value => ‘true’
  );
END;
/
SELECT AI SHOWSQL
  show revenue by customer region and product category for calendar year 2025;

The constraints attribute manages whether referential integrity constraints, including primary and foreign keys, can be included in the metadata sent to the LLM. For multi-table questions, this can reduce the chance that SQL is generated with the wrong join path or missing join conditions.

Simplify recurring questions with views

You can also improve NL2SQL accuracy by creating views that encode common joins, filters, and computed metrics directly in the database. Instead of asking the LLM to infer every relationship among base tables or reconstruct business calculations from column names, a view can present a cleaner semantic layer with purpose-built columns such as total_revenue, gross_margin, customer_region, or product_category. This reduces the reasoning burden on the model, limits the number of valid SQL patterns it needs to choose from, and makes prompts more likely to produce consistent results.

Views are especially useful for recurring business questions because they capture approved join paths and metric definitions once, making them easier to reuse across prompts, applications, and different AI-assisted query workflows. When included in an Oracle Select AI profile object list, these views help steer generation toward governed, repeatable SQL rather than asking the model to rediscover join paths and business logic for every prompt.

Capture feedback so similar prompts improve over time

Even with strong metadata, NL2SQL is not a one-and-done activity. Business definitions evolve, names are overloaded, and prompts can be ambiguous. Feedback gives Oracle Select AI a way to capture what worked and what should be corrected, then use semantically similar prior examples as hints for future SQL generation.

You can provide positive feedback when generated SQL is correct, or negative feedback with a corrected SQL response and optional natural language guidance. In 26ai, Oracle Select AI creates a feedback vector index the first time the capability is used for a profile. The stored examples are retrieved by vector similarity and included as hints in later augmented prompts.

-- First, inspect the generated SQL.
SELECT AI SHOWSQL
  which product categories had the highest revenue in Q4 2025;

-- If the SQL is correct, record positive feedback by SQL ID.
EXEC DBMS_CLOUD_AI.FEEDBACK(
  profile_name  => 'SALES_NL2SQL_PROFILE',
  sql_id        => '852w8u83gktc1',
  feedback_type => 'positive',
  operation     => 'add'
);

-- If you need to correct the generated SQL, provide negative feedback.
EXEC DBMS_CLOUD_AI.FEEDBACK(
  profile_name      => 'SALES_NL2SQL_PROFILE',
  sql_text          => 'select ai showsql which product categories had the highest revenue in Q4 2025',
  feedback_type     => 'negative',
  response          => q'[
                        SELECT p.prod_category, SUM(s.amount_sold) AS revenue
                        FROM sh.sales s
                        JOIN sh.products p ON p.prod_id = s.prod_id
                        JOIN sh.times t ON t.time_id = s.time_id
                        WHERE t.calendar_quarter_desc = '2025-Q4'
                        GROUP BY p.prod_category
                        ORDER BY revenue DESC
                       ]',
  feedback_content  => 'Use AMOUNT_SOLD for revenue, join PRODUCTS through PROD_ID, and use TIMES for quarter filtering.'
);

Note: Feedback and automated object list selection require 26ai because they rely on vector-based semantic similarity search. In 19c, you can still use core Oracle Select AI NL2SQL capabilities, but these vector-based enhancements are 26ai capabilities.

Treat feedback as shared profile-level knowledge. Before adding corrective feedback, confirm that the corrected SQL reflects the intended business definition for the profile’s audience, not just one user’s temporary interpretation of the question.

Use automated object list selection for large schemas

Object lists are one of the most important controls in an AI profile. They determine which objects are eligible for NL2SQL. In small applications, a curated object list is often the best choice because it narrows the search space and reduces ambiguity. In enterprise schemas with hundreds or thousands of tables, maintaining a perfect object list for every question can become difficult.

Automated object list selection addresses that problem. When object_list_mode is set to automated, Oracle Select AI identifies the most relevant objects for the prompt and sends metadata only for those objects. If you provide an object_list, Oracle Select AI searches within that list. If you omit object_list in 26ai, Oracle Select AI can evaluate objects accessible to the profile owner and select the relevant ones.

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name => 'SALES_AUTO_NL2SQL_PROFILE',
    attributes   => '{
      "provider": "oci",
      "credential_name": "GENAI_CRED",
      "object_list_mode": "automated",
      "comments": true,
      "annotations": true,
      "constraints": true,
      "temperature": 0
    }',
    status      => 'enabled',
    description => 'Profile using automated object selection for NL2SQL'
  );
END;
/

EXEC DBMS_CLOUD_AI.SET_PROFILE('SALES_AUTO_NL2SQL_PROFILE');

SELECT AI SHOWSQL
  which customers increased revenue the most year over year;

-- You can also combine automated mode with a broad but bounded object list.
BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name => 'RETAIL_AUTO_NL2SQL_PROFILE',
    attributes   => '{
      "provider": "oci",
      "credential_name": "GENAI_CRED",
      "object_list": [
        {"owner": "SH"}
      ],
      "object_list_mode": "automated",
      "comments": true,
      "annotations": true,
      "constraints": true
    }',
    status      => 'enabled'
  );
END;
/

This profile-controlled approach gives you flexibility in balancing governance, prompt scope, and schema coverage. A department-level application can use a curated object list with object_list_mode set to “all”, while a broad analytics assistant can use automated object selection to reduce schema noise and prompt size. When automated mode is enabled, Oracle Select AI creates an object-list vector index for the profile.

Combine these controls for an accuracy workflow

The best results usually come from combining these controls rather than relying on only one. A practical workflow looks like this:

• Start with a focused AI profile and a curated object list for your first use case.
• Add table and column comments for business concepts that do not appear in object names.
• Add annotations for synonyms, labels, and usage guidance that should remain close to the schema.
• Define primary keys and foreign keys, then enable constraints in the profile.
• Use SHOWSQL and EXPLAINSQL during development so you can inspect generated SQL before running it.
• Capture positive feedback for correct SQL and negative feedback with corrected SQL or guidance when results need refinement.
• For large schemas on 26ai, use automated object list selection so Oracle Select AI can identify relevant objects through semantic similarity.

NL2SQL accuracy checklist

Use the NL2SQL Data Retrieval Agent for a more consistent data workflow

For use cases that need more than one-shot SQL generation, you can start from the NL2SQL Data Retrieval Agent, one of the Oracle Select AI prebuilt agents available in the Oracle Autonomous AI Database samples GitHub repository. The agent combines Oracle Select AI NL2SQL generation with database introspection, query correction, fail-safe retries, distinct-value checks, range checks, optional web intelligence, and chart generation. That matters because many real-world questions fail not because NL2SQL cannot generate SQL, but because the prompt contains ambiguous values, an invalid date range, a predicate that returns zero rows, or a request that needs a visualization or additional context.

The agent improves consistency and reliability by turning NL2SQL into a guided data retrieval workflow. It can inspect metadata, check valid categorical values before filtering, inspect numeric or date ranges, retry when a SQL statement fails or returns no rows, and return structured, explainable responses. Since agents bind to AI profiles, the same profile practices described earlier still apply: comments, annotations, constraints, object lists, automated object selection, feedback, and model settings continue to shape how the agent reasons about your data.

This agent also improves semantic understanding when a request is ambiguous. Rather than forcing the model to guess, the workflow can use human interaction as a tool: ask a clarifying question, confirm the intended metric or filter, and then continue with the data retrieval task. That is especially valuable in customer-facing or business-facing chat experiences where a confident but wrong answer is worse than a brief clarification request. By combining profile-grounded NL2SQL with tool-based checks and human clarification, the prebuilt NL2SQL Data Retrieval Agent helps produce more consistent, accurate, and reliable answers across prompts and model choices.

After you install the prebuilt agent and bind it to an AI profile, you can expose the agent team to applications or chatbot interfaces. Example prompts include questions about tables associated with the selected profile, requests for charts, and data retrieval questions that benefit from metadata inspection or value validation.

Use the same profile from SQL command line experiments to deployed applications

Oracle Select AI is accessible directly from the SQL command line for interactive experimentation and validation using your AI profile. Application developers can use the same validated profile through PL/SQL or Python. Applications can call Oracle Select AI with a profile that already controls the provider, eligible objects, metadata, and accuracy features.

For stateless environments, DBMS_CLOUD_AI.GENERATE enables you to pass the profile name directly. This approach is useful in application code where you cannot depend on session state.

DECLARE
  l_sql CLOB;
BEGIN
  l_sql := DBMS_CLOUD_AI.GENERATE(
             prompt       => 'show total revenue by product category for the last quarter',
             profile_name => 'SALES_NL2SQL_PROFILE',
             action       => 'showsql'
           );

  DBMS_OUTPUT.PUT_LINE(l_sql);
END;
/

# Conceptual Python pattern using Oracle Select AI for Python.
# Use your installed Oracle Select AI for Python version's API names for production code.

from select_ai import SelectAI

sai = SelectAI(connection=conn)
sai.set_profile("SALES_NL2SQL_PROFILE")

sql_text = sai.generate(
    prompt="show total revenue by product category for the last quarter",
    action="showsql"
)

print(sql_text)

This reinforces the profile as the governance and behavior boundary. Whether the request comes from a SQL worksheet, APEX application, chatbot, Python application, or Oracle Select AI Agent Framework workflow, the profile determines what Oracle Select AI can use for SQL generation.

Give users a ready-made chatbot with Ask Oracle Select AI

You do not have to build a chatbot from scratch to give business users a natural language interface to Oracle Select AI. The Ask Oracle Select AI APEX application provides a ready-made chatbot experience for NL2SQL and RAG using your AI profiles and Oracle Select AI Agent framework agent teams, like the NL2SQL Data Retrieval Agent. This chatbot application makes it easy to validate a profile, compare NL2SQL behavior across prompts, view generated SQL and explanations, generate charts from NL2SQL results, and switch between profiles or agents from an application UI.

Ask Oracle Select AI is useful during both development and deployment. During development, it gives data owners and application teams a fast way to test whether comments, annotations, constraints, object lists, feedback, and automated object selection are improving answers. During deployment, it gives coders and non-coders a familiar chat interface over the governed profiles and agents you already configured. For an NL2SQL accuracy program, this closes the loop: design the AI profile, enrich the schema, add feedback, optionally use the NL2SQL Data Retrieval Agent for tool-driven validation, and expose the result through a ready-made APEX chatbot.

Keep security and governance close to the data

NL2SQL should not bypass your database controls. Oracle Select AI works with database users, privileges, profile configuration, and database security features. You decide which objects are eligible through the profile, which metadata options are enabled, and which users can execute the relevant packages and access the underlying data.

This matters because NL2SQL accuracy and governance are related. A smaller, well-governed object set can produce more accurate SQL and reduce the risk of exposing unnecessary data and metadata. When row-level security, Virtual Private Database policies, redaction, or other database controls apply to the underlying SQL execution, those controls remain part of the application architecture.

Oracle Select AI NL2SQL versus MCP tools

Oracle Select AI NL2SQL and an MCP client and tools-based approach handle natural-language-to-SQL generation differently. With Oracle Select AI, your prompt is translated to SQL through Oracle AI Database using an AI profile that controls the AI provider, model, accessible objects, metadata, feedback, and object-selection behavior. The selected LLM may run within a trusted Oracle environment, such as OCI Generative AI Service, outside the Oracle environment, or in a privately hosted environment. In each case, Oracle Select AI prepares the augmented prompt in the database, sends that prompt to the selected LLM, and receives SQL back, providing a database-centered NL2SQL workflow.

By contrast, an MCP client and tools-based approach usually requires the MCP client or agent runtime to make several round trips before it can answer a question. It may need one tool call to inspect available schemas, another to list relevant tables, additional calls to inspect columns, comments, keys, or sample values, then an LLM call to generate SQL, followed by a tool call to execute the SQL and possibly more calls to correct errors. That orchestration can be useful for broad agent workflows across many systems, but for NL2SQL it shifts much of the grounding, validation, and retry logic into the application layer. It can also introduce additional data-governance considerations when agentic AI memory, including short- and long-term memory, vector stores, conversation history, and trace logs, is maintained outside the database. Those stores can become shadow data repositories if they are not governed with the same rigor as the source data. It’s worth noting that Oracle Select AI Agent Framework addresses this pattern by keeping agentic AI memory in the database, however, a deeper discussion of agent memory belongs in a separate post.

Oracle Select AI reduces the overhead typically associated with MCP-based NL2SQL by producing the augmented prompt in the database and making a single call to the LLM to generate a SQL query for a given prompt. At the same time, it keeps profile controls, schema metadata, feedback, and object-selection behavior close to the data and managed through the database.

26ai unlocks the vector-based accuracy enhancements

Core Oracle Select AI NL2SQL capabilities are available across supported releases, but some of the newest accuracy enhancements depend on vector-based semantic similarity search. Feedback uses vector search to find prior prompts and corrections that are similar to the current prompt. Automated object list selection uses vector search to identify schema objects that are most relevant to the prompt.

For that reason, capabilities such as feedback and automated object list selection require 26ai. If you are using 19c, you can still improve NL2SQL accuracy by carefully designing profiles, curating object lists, adding comments, and defining constraints. If you want feedback-driven prompt tuning and automated object selection, plan for 26ai.

Conclusion

Oracle Select AI NL2SQL accuracy improves when the database gives the model the right context and the AI profile controls that context intentionally. Start with the objects that matter. Add comments so business terms map to schema elements. Use annotations to capture richer vocabulary and usage hints. Define referential integrity so join paths are clear. Capture feedback so similar prompts improve over time. For large schemas on 26ai, use automated object list selection to find the right objects without overwhelming the model.

You can then extend that foundation with prebuilt agents. The NL2SQL Data Retrieval Agent adds tool-driven validation, retries, clarifying interactions, and chart generation for more reliable data retrieval workflows. Ask Oracle Select AI gives you a customizable, ready-made APEX-based chatbot for using those profiles and agents with business users and developers alike. The result is a more reliable natural language query experience that stays close to your data, your metadata, and your database security model. Instead of treating NL2SQL as a generic prompt engineering exercise, you can make it a governed, database-centered workflow with Oracle Select AI.

Resources

• Introducing Oracle Select AI – Natural Language to SQL Generation on Autonomous Database
• 6 Simple Tips for Better Text-to-SQL Generation using Oracle Autonomous Database Select AI
• Oracle Select AI Enhances Text-to-SQL Support
• Announcing Oracle Autonomous Database Select AI Feedback for Enhanced SQL Query Generation
• Oracle Select AI by Release: A Quick Guide to 26ai and 19c Capabilities
• Building AI Apps with Oracle Select AI and Virtual Private Database
• Announcing Oracle Autonomous AI Database Select AI for Python
• NL2SQL Data Retrieval Agent – GitHub
• Ask Oracle Select AI APEX application – GitHub
• Autonomous AI Agents sample repository – GitHub
• DBMS_CLOUD_AI Package
• Oracle Select AI Feedback
• Examples of Using Oracle Select AI