Oracle Cloud Infrastructure Documentation

SQL Search (NL2SQL) Quick Start

Use SQL Search (NL2SQL) to turn a natural-language question into validated SQL for enterprise data in OCI Generative AI.

NL2SQL uses a semantic store to map business terms to database fields, tables, and joins. It generates SQL only. The Database Tools MCP Server authorizes and runs the query against the source database by using the end user's permissions.

Before You Begin

Before using NL2SQL, ensure that you have a source database and configure the required database connections.

At minimum, you need:

  • A source Oracle Autonomous AI Database
  • A Database Tools service enrichment connection
  • A Database Tools service query connection

Get Started

The following steps provide an overview of how to connect to a database, prepare an NL2SQL semantic store, and submit natural-language questions through an MCP-compatible client.

  1. Create the required database connections.

    Create one connection for enrichment and a separate, lower-privileged connection for queries.

  2. Create and enrich a semantic store.

    Select the two connections, select the approved schemas, and wait for enrichment to finish.

  3. Configure the Database Tools MCP Server and MCP toolset.

    Configure the server and create a toolset that lets the client use NL2SQL to generate SQL and run approved SQL against the source database.

  4. Connect a client and ask a question.

    Use an Oracle client or your own MCP-compatible chat or agent client.

Tip

For instructions to configure the Database Tools MCP Server and integrate a client, see Steps for Creating a Database Tools MCP Server and Integrating with the Client.

At runtime: The client sends a question to the Database Tools MCP Server. NL2SQL generates SQL, and the MCP Server authorizes the request and runs the query by using the end user's database permissions.

Create a Semantic Store

To use NL2SQL, create a semantic store in OCI Generative AI.

A semantic store is backed by a vector store with structured data and includes two Database Tools service connections:

  • Enrichment Connection
  • Query Connection

In the Console

In the Console, create a vector store and select Structured data. In the semantic store options, select the enrichment connection, the query connection, and the schemas that NL2SQL can use.

By Using the OCI Generative AI API

Use the CreateSemanticStore operation in the OCI Generative AI API to create a semantic store.

Base URL Endpoint Path Authentication
https://generativeai.${region}.oci.oraclecloud.com/20231130 /semanticStores IAM session only

The CreateSemanticStore operation uses OCI IAM-based authentication.

Database Tools Connections

NL2SQL uses two database connections with different purposes.

Enrichment Connection

The Enrichment Connection is the higher-privileged connection used during enrichment. It requires privileges to:

  • Run queries.
  • Perform required Data Definition Language (DDL) operations.
  • Access permitted example values from the database.

OCI Generative AI uses this connection to read schema information and build the metadata needed to generate SQL.

Query Connection

The Query Connection is the lower-privileged connection used to run queries on behalf of the end user.

Keep the enrichment and query connections separate to distinguish enrichment from query execution and support safer access control.

Enrichment

The enrichment process reads schema metadata from the connected database. This metadata can include tables, columns, database comments, annotations, and synonyms. OCI Generative AI uses this information to map terms in a question to the appropriate database objects and generate SQL based on the available schema context.

Select when to run enrichment:

  • On create: Enrichment starts automatically after the semantic store is created.
  • Manual: Enrichment starts later through a separate API operation.

To start enrichment manually, call the GenerateEnrichmentJob operation.

Generate SQL from Natural Language

After enrichment completes, call the GenerateSqlFromNl operation to convert natural-language input into SQL.

This operation:

  • Accepts natural-language input
  • Uses the enriched semantic metadata
  • Returns generated SQL
Important

The GenerateSqlFromNl operation doesn't run the SQL against the database.

Query Execution

The Database Tools MCP Server manages the execution flow:

  1. Calls the NL2SQL service to generate SQL.
  2. Authorizes the request.
  3. Runs the query against the source database.
  4. Applies the appropriate guardrails.
  5. Uses the end user’s identity for execution.

This keeps query execution in the source database governed by existing database permissions.

Connect a Chat or Agent Client

Configure an Oracle client or an MCP-compatible chat or agent client to connect to the Database Tools MCP Server. The MCP Server calls NL2SQL to generate SQL and runs the query after authorizing the request.

A client can also use the OCI Responses API with MCP Calling to connect to the Database Tools MCP Server.

Integration note: Don't add NL2SQL directly as a Responses API tools entry. For an MCP-based flow, use the Database Tools MCP Server. For an API-based flow, call GenerateSqlFromNl directly.

For Long-Running Requests

Some workflows can take longer than a normal chat request. If the Responses API client supports background processing, store the returned response ID and check the status until the request finishes. Provide a way for the user to cancel the request when needed. Background processing prevents a client timeout from being treated as a failed query.

Try It

After setup, start with a short question that uses a known table. For example:

Which five products had the highest sales last month?

Confirm that the client returns generated SQL and, when execution is enabled, a result that follows the user's database permissions.

NL2SQL API Operations

The following OCI Generative AI API operations support NL2SQL:

Semantic stores
  • CreateSemanticStore
  • ListSemanticStores
  • GetSemanticStore
  • UpdateSemanticStore
  • ChangeSemanticStoreCompartment
  • DeleteSemanticStore
Enrichment Jobs
  • ListEnrichmentJobs
  • GetEnrichmentJob
  • GenerateEnrichmentJob
  • CancelEnrichmentJob
Generate SQL
GenerateSqlFromNl