Building NL2SQL Pipelines With Oracle DBMS_CLOUD_AI and Oracle Integration Cloud

The paradigm of database interaction is shifting from rigid, syntax-bound queries to fluid, intent-driven natural language interfaces. Historically, extracting insights from relational databases required specialized knowledge of Structured Query Language (SQL) and an intimate understanding of the underlying schema. The advent of Large Language Models (LLMs) has catalyzed the development of Natural Language to SQL (NL2SQL) systems. However, deploying these systems in enterprise environments introduces significant challenges regarding accuracy, hallucination mitigation, and data security. Oracle addresses these enterprise constraints natively within its Autonomous Database through the DBMS CLOUD AI package. This built-in capability, commonly referred to as “Select AI,” enables developers to configure AI profiles that connect database objects directly to external LLM providers. Rather than relying on external middleware to orchestrate the NL2SQL pipeline, the database itself handles prompt augmentation, schema metadata injection, and query execution. This paper provides an imperative technical analysis of the DBMS CLOUD AI architecture, covering implementation methodology, execution modalities, OIC integration, and data governance. \ BACKGROUND AND RELATED WORK The transition from natural language to structured queries involves a complex pipeline. Modern NL2SQL systems must perform schema linking—mapping natural language entities to database tables and columns—before generating syntactic SQL [4]. A persistent challenge in this domain is LLM hallucination, where the model generates syntactically valid but semantically incorrect queries, often referencing non-existent columns or misunderstanding table relationships [5]. To mitigate these risks, enterprise NL2SQL architectures employ prompt augmentation. By injecting precise Data Definition Language (DDL) statements and schema metadata into the LLM prompt, the context window is constrained to the actual database structure [6]. Oracle’s DBMS CLOUD AI automates this augmentation process. When a user issues a natural language prompt, the database retrieves the metadata for the tables specified in the AI profile and constructs an augmented prompt, significantly reducing the probability of schema hallucinations [3]. DATA SCHEMA The implementation examples in this paper use two relational tables residing in an Oracle ATP instance: INB INVOICE HEADER and INB INVOICE LINES. The header table stores supplier-level invoice metadata, while the lines table captures individual line-item details. Fig. 1 and Fig. 2 illustrate representative query results from these tables, establishing the data context for all subsequent NL2SQL. \ \ IMPLEMENTATION METHODOLOGY Deploying DBMS CLOUD AI requires a systematic five-step configuration of network access, credentials, and AI profiles. The following imperative steps outline the implementation using Cohere as the designated LLM provider. Table I summarizes each step and its purpose. IMPLEMENTATION STEPS FOR DBMS CLOUD AI DEPLOYMENT | Step | Action | Purpose | |----|----|----| | 1 | Obtain LLM Provider API Key | Authenticate against the external LLM endpoint (e.g., Cohere) | | 2 | GRANT EXECUTE on DBMS CLOUD AI | Authorize the database user to invoke the package | | 3 | DBMS NETWORK ACL ADMIN.APPEND HOST ACE | Allow outbound HTTPS traffic to the LLM API host | | 4 | DBMS CLOUD.CREATE CREDENTIAL | Store the API key securely within the Oracle Wallet | | 5 | DBMS CLOUD AI.CREATE PROFILE | Bind the LLM provider, credential, and object list | \ \ A. Step 1 — Obtain LLM Provider API Key The first prerequisite is obtaining a valid API key from the chosen LLM provider. For this implementation, a Cohere trial account was provisioned and an API key generated from the Cohere dashboard. Fig. 3 illustrates the Cohere API key management interface. The key must be treated as a sensitive credential and must never be embedded in source code or exposed in application logs. \ B. Step 2 — Grant Execute Privilege The database user executing DBMS CLOUD AI must be explicitly granted the EXECUTE privilege on the package by a DBA. This follows the Oracle principle of least privilege, ensuring that only authorized schemas can invoke the AI translation functions. GRANT EXECUTE ON DBMS CLOUD AI TO ADMIN; \ C. Step 3 — Network Access Control Entry The Autonomous Database must be explicitly authorized to establish outbound HTTPS connections to the external LLM API endpoint. This is achieved by appending a host Access Control Entry (ACE) using the DBMS NETWORK ACL_ADMIN package, as shown below. \ BEGIN \n &nbsp;&nbsp;&nbsp; DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( \n &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; host => 'api.cohere.ai', \n &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ace&nbsp; => xs$ace_type( \n &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; privilege_list => xs$name_list('http'), \n &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; principal_name => 'ADMIN', \n &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; principal_type => xs_acl.ptype_db \n &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) \n &nbsp;&nbsp;&nbsp; ); \n END; \n D. Step 4 — Credential Storage The API key must be securely stored within the Oracle Wallet using DBMS CLOUD.CREATE CREDENTIAL. This abstraction ensures that sensitive tokens are not hardcoded into application logic or visible in system views. Fig. 5 confirms successful credential creation. BEGIN \n &nbsp;&nbsp;&nbsp; DBMS_CLOUD.CREATE_CREDENTIAL( \n &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; credential_name => 'BHARATHCOHERE', \n &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; username&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; => 'BHARATHCOHERE', \n &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; password&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; => '<api_key_redacted>' \n &nbsp;&nbsp;&nbsp; ); \n END; \n E. Step 5 — AI Profile Definition The core of the architecture is the AI profile, instantiated via DBMS CLOUD AI.CREATE PROFILE. The profile acts as the binding contract between the LLM provider, the authentication credential, and the specific database objects (tables and views) that the LLM is permitted to reason about. By explicitly defining the object list, the system enforces a strict boundary on the metadata exposed to the LLM, adhering to the principle of least privilege and optimizing the token payload [3]. \ BEGIN \n &nbsp;&nbsp;&nbsp; DBMS_CLOUD_AI.CREATE_PROFILE( \n &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; profile_name => 'BHARATHCOHEREPROFILE', \n &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; attributes&nbsp;&nbsp; => '{ \n &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "provider": "cohere", \n &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "credential_name": "BHARATHCOHERE", \n &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "object_list": [ \n &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {"owner": "ADMIN", "name": "INB_INVOICE_HEADER"}, \n &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {"owner": "ADMIN", "name": "INB_INVOICE_LINES"} \n &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ] \n &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }' \n &nbsp;&nbsp;&nbsp; ); \n END; \n EXECUTION MODALITIES AND ANALYSIS Once the profile is established, the DBMS CLOUD AI.GENERATE function facilitates interaction through four distinct action modalities. Each modality serves a specific phase of the data extraction lifecycle. Table II provides a comparative summary. TABLE II: COMPARATIVE ANALYSIS OF DBMS CLOUD AI.GENERATE ACTION MODALITIES | Action | Executes SQL? | LLM Invocations | Primary Use Case | |----|----|----|----| | showsql | No | 1 | Query validation and audit before execution | | runsql | Yes | 1 | Single-round-trip data retrieval for applications | | narrate | Yes | 2 | Conversational agents and executive dashboards | | explainsql | No | 1 | Query transparency and user trust | \ A. SQL Generation (showsql) The showsql action generates the SQL statement without executing it. This is critical for query validation and auditing. When prompted with “how many invoices exist,” the LLM, guided by the augmented metadata, constructs a deterministic aggregation query. This imperative step allows developers to verify schema linking accuracy before execution. SELECT DBMS_CLOUD_AI.GENERATE( \n &nbsp;&nbsp;&nbsp; prompt&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; => 'how many invoices exist', \n &nbsp;&nbsp;&nbsp; profile_name => 'BHARATHCOHEREPROFILE', \n &nbsp;&nbsp;&nbsp; action&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; => 'showsql' \n ) FROM DUAL; \ B. Direct Execution (runsql) The runsql action bridges the generation and execution phases, returning the resultant dataset directly to the caller. This modality is optimized for application integration, where the NL2SQL translation and data retrieval must occur within a single synchronous round-trip. \ SELECT DBMS_CLOUD_AI.GENERATE( \n &nbsp;&nbsp;&nbsp; prompt&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; => 'how many Invoices exist', \n &nbsp;&nbsp;&nbsp; profile_name => 'BHARATHCOHEREPROFILE', \n &nbsp;&nbsp;&nbsp; action&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; => 'runsql' \n ) FROM DUAL; \ C. Narrative Synthesis (narrate) The narrated action introduces a secondary LLM invocation. After generating and executing the SQL query, the database passes the structured result set back to the LLM to synthesize a natural language summary. For instance, querying “how many invoices belong to CAPITAL ONE” yields a human-readable response. This modality is highly effective for conversational agents and executive dashboards. SELECT DBMS_CLOUD_AI.GENERATE( \n &nbsp;&nbsp;&nbsp; prompt&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; => 'how many invoices belong to CAPITAL ONE', \n &nbsp;&nbsp;&nbsp; profile_name => 'BHARATHCOHEREPROFILE', \n &nbsp;&nbsp;&nbsp; action&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; => 'narrate' \n ) FROM DUAL; D. Query Explanation (explainsql) The explainsql action reverse-engineers the generated SQL into a plain-English explanation. This serves as an invaluable tool for query transparency and user trust, addressing the “black box” nature of LLM outputs by detailing the join conditions and filtering logic the model applies. SELECT DBMS_CLOUD_AI.GENERATE( \n &nbsp;&nbsp;&nbsp; prompt&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; => 'how many invoices belong to CAPITAL ONE', \n &nbsp;&nbsp;&nbsp; profile_name => 'BHARATHCOHEREPROFILE', \n &nbsp;&nbsp;&nbsp; action&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; => 'explainsql' \n ) FROM DUAL; E. Shorthand SELECT AI Syntax In addition to the DBMS CLOUD AI.GENERATE function, Oracle provides a shorthand SQL syntax that allows users to issue natural language prompts directly from the SQL command line. This syntax is particularly useful for interactive exploration and rapid prototyping. -- Shorthand syntax examples \n SELECT AI how many invoices exist; \n SELECT AI showsql how many invoices belong to CAPITAL ONE; \n SELECT AI narrate list invoices for supplier CAPITAL ONE; \ \ \ \ ARCHITECTURAL EXTENSION VIA ORACLE INTEGRATION CLOUD While DBMS CLOUD AI provides native database capabilities, enterprise architectures frequently require these insights to be distributed across disparate systems. Oracle Integration Cloud (OIC) serves as the orchestration layer for this distribution [7]. By leveraging the OIC ATP Adapter, developers can expose the DBMS CLOUD AI.GENERATE function as a secure RESTful API. This architectural pattern decouples the client application from the database tier. A downstream system—such as a CRM or custom web application—can issue a natural language HTTP POST request to OIC. OIC authenticates the request, invokes the ATP adapter to execute the runsql or narrate action, and returns a JSON-formatted response. This integration enables enterprise-wide access to NL2SQL capabilities without provisioning direct database access to edge applications. \ SECURITY AND DATA GOVERNANCE The imperative analysis of this architecture must conclude with a rigorous evaluation of data governance. When utilizing external LLM providers (e.g., Cohere, OpenAI), the database transmits schema metadata—and in the case of the narrate action, actual result set data—across the public internet to third-party infrastructure [8]. Enterprises must obtain explicit client approval and conduct a data-governance assessment before enabling this integration. Enterprises must conduct a Zero Trust assessment of this data flow [9]. If regulatory frameworks (e.g., GDPR, HIPAA) prohibit the external transmission of specific metadata or payload data, the architecture must be adapted. Oracle mitigates this risk by offering internal Generative AI capabilities through the OCI Generative AI Service, which ensures that LLM inference occurs within the tenant’s secure cloud boundary, preventing data exfiltration while maintaining the full NL2SQL functionality [3]. \ CONCLUSION Oracle’s DBMS CLOUD AI represents a robust, enterprise-grade solution for Natural Language to SQL translation. By embedding prompt augmentation and schema linking directly within the Autonomous Database, it reduces architectural complexity and mitigates LLM hallucination risks. The four execution modalities—showsql, runsql, narrate, and explainsql—provide a comprehensive toolkit for query validation, data retrieval, narrative synthesis, and transparency. When combined with Oracle Integration Cloud, the architecture provides a scalable mechanism for distributing AI-driven insights via RESTful services. However, the implementation must be governed by strict data security policies, ensuring that the selection of the LLM provider aligns with the organization’s compliance mandates. Future work will explore the use of OCI Generative AI as the internal LLM provider to eliminate external data transmission entirely. \ REFERENCES [1] N. Floratou et al., “NL2SQL is a solved problem… Not!,” CIDR 2024, Jan. 2024. [2] AWS Machine Learning Blog, “Enterprise-grade natural language to SQL generation using LLMs,” Apr. 2025. [3] Oracle Help Center, “DBMS CLOUD AI Package,” Oracle Documentation, 2026. [4] HKUST Dial, “From Natural Language to SQL: Review of LLM-based Text-to-SQL Systems,” arXiv preprint arXiv:2410.01066, 2024. [5] Z. Wang et al., “Hallucination Detection for LLM-based Text-to-SQL Generation via Two-Stage Metamorphic Testing,” arXiv preprint arXiv:2512.22250, Dec. 2025. [6] Wren AI, “Reducing Hallucinations in Text-to-SQL: Building Trust and Accuracy,” Jan. 2025. [7] Oracle A-Team, “Using OCI Generative AI with Integration,” Oracle Blog, Nov. 2024. [8] S. Veera, “Securing Enterprise Data for LLM-Powered Applications: A Reference Architecture for Inference-Time Data Protection,” J. Computer Science and Technology, 2026. [9] Cloud Security Alliance, “Using Zero Trust to Secure Data in LLM Environments,” CSA Research, 2024. \ \ \ \