# Oracle SQLcl MCP Server with Codex & Copilot #JoelKallmanDay # Introduction I have been using GitHub Copilot with Oracle’s SQLcl MCP server since its release in July 2025. The combination of Generative AI and databases is a powerful pairing that can help APEX developers build better products in less time. This statement comes with some caveats, which I will cover in this post. In this post, I will describe how to set up the SQLcl MCP server in SQL Developer for VS Code, utilizing both [OpenAI’s Codex](https://openai.com/codex/) and [GitHub Copilot](https://github.com/features/copilot). I will also review several use cases and provide example prompts, which will help you get more out of this technology. # Configuring the SQLcl MCP Server First, you will need to install the latest version of the SQL Developer Extension for VS Code and the latest version of SQLcl on your machine. You will also need to know the path to your SQLcl install. In the following sections, I will describe how to set up Codex and GitHub Copilot to utilize the SQLcl MCP Server. Which tool to use is up to you. I currently use both, but am leaning toward Codex as my go-to development assistant. ## Configuring GitHub Copilot See the ‘Appendix 1 - Configure & Test GitHub Copilot with SQLcl MCP Server’ for details on how to set up and test the SQLcl MCP Server with VS Code. ## Configuring OpenAI Codex ### Install the OpenAI Codex Extension for VS Code * Install the Codex Extension for VS Code. ![Codex Extension for VS Code](https://cdn.hashnode.com/res/hashnode/image/upload/v1760206643572/5f06c8b5-e441-43c9-a5e7-82679ff58d7c.png align="center") * Log in to your OpenAI / ChatGPT account * Open the Codex extension > Click the settings gear icon > Log in ## Configure Codex to use the SQLcl MCP Server * Open the Codex extension > Click ⚙️ > MCP Settings > Open config.toml ![Codes Settings](https://cdn.hashnode.com/res/hashnode/image/upload/v1760206131975/c1656423-e2cf-42e6-8102-46b543ff6e2a.png align="center") * Once the file opens, copy and paste the below text into it (adjust your SQLcl path accordingly). ```ini [mcp_servers.sqlcl] command = "/opt/homebrew/Caskroom/sqlcl/25.3.0.274.1210/sqlcl/bin/sql" args = ["-mcp"] startup_timeout_ms = 60000 ``` ## Testing the Setup For the rest of this post, I will be using a saved connection called ‘DEMO’ in SQLDeveloper for VS Code: ![DEMO SQL Developer Connection](https://cdn.hashnode.com/res/hashnode/image/upload/v1760208293698/88509c97-81e4-42ad-88d1-65b24d7e7e09.png align="center")
💡
SQLcl and the SQL Developer Extension for VS Code share the same DB connections. This is how SQLcl connects to your database in the examples below.
### Test from Codex with VS Code Open the Codex Extension and type the following in the chat Window:
➡
Connect to DEMO using the SQLcl MCP Server
Once connected, ask:
➡
How many tables are these in the schema
You should end up with something like this: ![Codex Connected to the DB](https://cdn.hashnode.com/res/hashnode/image/upload/v1760208546997/520c055b-abed-4fd3-a3cc-556589054818.png align="center") ### Test from the Codex CLI The SQLcl MCP Server also works with the [OpenAI Codex CLI.](https://developers.openai.com/codex/cli/) ![Codex CLI and SQLcl MCP Server 1](https://cdn.hashnode.com/res/hashnode/image/upload/v1760314788470/2af6882e-097e-416a-a5ce-0fb3832f1fc7.png align="center") ![Codex CLI and SQLcl MCP Server 1](https://cdn.hashnode.com/res/hashnode/image/upload/v1760314805992/f8b38895-0ae9-4b73-9139-a30843fdd548.png align="center") # How it Works Before reviewing the examples, it’s important to understand how the SQLcl MCP Server interacts with the Large Language Model (LLM). When you type a prompt in Copilot Chat or Codex, VS Code sends that prompt, along with prior context and a list of available tools, to the LLM. One of those tools can be the SQLcl MCP Server. The LLM analyzes the prompt and determines whether SQLcl is the appropriate tool to assist in fulfilling the request. If it is, the LLM instructs VS Code to run a command through SQLcl and return the output. The LLM then uses that output to decide the next step, continuing this exchange until the request is resolved.
💡
In short, the LLM is the driver; SQLcl is just an assistant.
⚠
The reason I call this out is that if the LLM decides that dropping all of your tables will answer the question and that SQLcl is the right tool for the job, then SQLcl will happily drop all the tables!
![It will drop tables!](https://cdn.hashnode.com/res/hashnode/image/upload/v1760212037485/ffe00adf-a5f9-493f-99ff-e6247f0d44f1.png align="center") # AGENTS.md ![AGENTS.md](https://cdn.hashnode.com/res/hashnode/image/upload/v1760210292198/9b39df34-09cd-4e0f-9206-3c22b5a8d22f.png align="center") Adding a file called [AGENTS.md](https://agents.md/) to the root of your GitHub repositories allows you to pass on guidelines to the LLM. Both Codex and [GitHub Copilot](https://github.blog/changelog/2025-08-28-copilot-coding-agent-now-supports-agents-md-custom-instructions/) recognize this file. You can include details such as: * APEX and DB Versions * Folder Structures of your Repo * Coding Standards and Conventions * Formatting Rules * Agent behaviors and guidelines (don’t drop my tables)
💡
I created the initial version of my AGENTS.md by asking ChatGPT to create it for me.
⚠
⚠️ Important: Do not rely on AGENTS.md to prevent destructive actions (DDL or DML). It only provides guidance — not enforcement.
# SQLcl MCP Use Cases Now that we are set up, it’s time to go through some use cases. I think this is the best way to understand why the SQLcl MCP server is so helpful.
💡
From now on, I will be using Codex for the demos. I will also assume that we are already connected to the DEMO database connection.
## Create a Query Let’s start by having the MCP Server create a query for us to use.
➡
Create a SQL query to get sales orders, customers, sales order lines, and products
Result: --- ```sql SELECT so.order_number , so.order_date , so.total_amount AS order_total , so.status AS order_status , c.name AS customer_name , c.email AS customer_email , sol.line_number , sol.quantity , sol.unit_price , sol.line_total , p.sku , p.name AS product_name FROM sales_orders so , customers c , sales_order_lines sol , products p WHERE so.customer_id = c.customer_id AND sol.order_id = so.order_id AND sol.product_id = p.product_id; ``` --- * The first thing you may notice is that the SQL is formatted. This is because of the formatting instructions in the AGENTS.md file. It also uses the old Oracle SQL syntax, again, because this is in my AGENTS.md (and because I am old 👴). * The LLM requested details of the tables in the database, SQLcl provided them, and the LLM generated the query. It correctly identified the tables based on my prompt, even though I did not use exact table names. ## Tune the Query Now that we have a query, let’s tune it for optimal performance when we want to view orders with an order date within the past month.
💡
Tune the SQL statement below. I want to improve performance when querying orders with an order date in the past month. Run the SQL query against the database, check the explain plan, and iterate through options until you find the best solution. SQL: SELECT so.order_number , so.order_date , so.total_amount AS order_total , so.status AS order_status , c.name AS customer_name , c.email AS customer_email , sol.line_number , sol.quantity , sol.unit_price , sol.line_total , p.sku , p.name AS product_name FROM sales_orders so , customers c , sales_order_lines sol , products p WHERE so.customer_id = c.customer_id AND sol.order_id = so.order_id AND sol.product_id = p.product_id AND so.order_date >= ADD_MONTHS(TRUNC(SYSDATE),-1)
Here is an excerpt from the LLM’s “model reasoning trace” and its back and forth with SQLcl: ![LLM Chain of Thought](https://cdn.hashnode.com/res/hashnode/image/upload/v1760211490603/8c2deb8f-526d-475f-9833-10c2e62b3c84.png align="center")
💡
As you can see, the LLM and SQLcl went through many iterations.
**Here is the final response from the LLM:** --- > **Tuning Results** > > * Captured the original plan: full scans on SALES\_ORDERS and SALES\_ORDER\_LINES, cost 12 (plan hash 1363380163), so the date filter wasn’t selective without supporting indexes. > > * Added index sales\_orders\_ord\_dt\_idx on (order\_date, order\_id) to allow the optimizer to apply the last-month filter via an index range scan. > > * **Added index** sales\_order\_lines\_ord\_idx on (order\_id, product\_id) so line lookups now use range scans instead of scanning the entire detail table. > > * **Regathered stats** on both tables (DBMS\_STATS.GATHER\_TABLE\_STATS) to make the new access paths visible to the optimizer. > > * New plan hash 1931885458: both SALES\_ORDERS and SALES\_ORDER\_LINES now use index range scans with batched rowid lookups; plan cost dropped to 9, and the query no longer performs storage full scans. > > * Executed the tuned SQL (ANSI JOIN form) and confirmed it returns no rows for the past month in this sample dataset, indicating the plan runs without error. > > > **Next Steps** > > 1. Validate the change against production-scale volumes (SQL Monitor/ASH) to quantify real runtime gains. > > 2. Keep table stats current after large data loads so the optimizer continues to choose the new indexes. > ---
💡
As you can see, it went ahead and created a new index and gathered stats. This is great, and it did reduce the cost of the SQL from 14 to 9, but I did not ask it to do anything that changed the database. More proof (if any was needed) that you should not be running this against PROD.
## Combining Context from Your DB and Your Codebase Sometimes it is helpful to ask questions about your codebase in GitHub and what is actually in your database. I have a folder open in VS Code with a GitHub Repo: ![Folder Open with Github Repo](https://cdn.hashnode.com/res/hashnode/image/upload/v1760212336403/14975e50-d061-4e48-b7b6-74c72d3c8623.png align="center") Now, I can ask questions that span the DB and my file system.
➡
Which tables in the database are referenced in the codebase? Use the SQLcl MCP server to inspect the DB.
Here is an excerpt from the “model reasoning trace”: ![DB and File System Chain of Thought](https://cdn.hashnode.com/res/hashnode/image/upload/v1760212653146/9d8ca341-ea8d-417a-bb72-7ff4cc18442c.png align="center") **Here is the Response:** --- > Connected to the DEMO database via SQLcl and cross-referenced every SQL/PL/SQL source in the repo. The query select distinct table\_name from all\_tables where table\_name in (...) returned twelve matches, and each has live references in the codebase: > > **GenAI Schema Tables** > > * GEN\_AI\_CONFIGS – defined in cn\_utils/ai/TABLES/GEN\_AI\_CONFIGS\_CRTB.sql:1, used in cn\_utils/ai/PLSQL/GEN\_AI\_UTL\_PKB.sql:18. > > * GEN\_AI\_LOGS – defined in cn\_utils/ai/TABLES/GEN\_AI\_LOGS\_CRTB.sql:2, logged through cn\_utils/ai/PLSQL/GEN\_AI\_UTL\_PKB.sql:8. > > * GEN\_AI\_TOOLS – created in cn\_utils/ai/TABLES/GEN\_AI\_TOOLS\_CRTB.sql:1, referenced by cn\_utils/ai/PLSQL/GEN\_AI\_TOOLS\_UTL\_PKB.sql:1. > > * GEN\_AI\_TOOL\_PARAMS – documented in cn\_utils/ai/TABLES/GEN\_AI\_TOOL\_PARAMS\_CRTB.sql:1, queried in cn\_utils/ai/PLSQL/GEN\_AI\_TOOLS\_UTL\_PKB.sql:66. > > * SALES\_ORDERS – DDL/comments in cn\_utils/ai/SELECTAI/SELECT\_AI\_DEMO.sql:19, accessed in cn\_utils/ai/PLSQL/GEN\_AI\_EXAMPLE\_TOOLS\_PKB.sql:26. > > * CUSTOMERS – table metadata in cn\_utils/ai/SELECTAI/SELECT\_AI\_DEMO.sql:28, queried in conference\_material/KSCOPE25/Built with LOV Using APEX/f777.sql:7176. > > > **Content & Reference Tables** > > * CNBA\_BLOG\_POSTS – synchronized in cn\_utils/ai\_demo\_app/APEX/f110.sql:334, selected in cn\_utils/ai\_demo\_app/PLSQL/CN\_AI\_UTL\_PKB.sql:36. > > * CNBA\_POST\_CHUNKS – inserted in cn\_utils/ai\_demo\_app/APEX/f110.sql:2765, joined in cn\_utils/ai\_demo\_app/PLSQL/CN\_AI\_UTL\_PKB.sql:88. > > * CN\_DOCUMENTS – queried in cn\_utils/ai\_demo\_app/APEX/f110.sql:1580, retrieved in cn\_utils/ai\_demo\_app/PLSQL/CN\_AI\_UTL\_PKB.sql:176. > > * CN\_DOCUMENT\_CHUNKS – read in cn\_utils/ai\_demo\_app/APEX/f110.sql:3860, maintained in cn\_utils/ai\_demo\_app/PLSQL/CN\_AI\_UTL\_PKB.sql:252. > > * PRODUCTS – LOV definition in cn\_utils/ai\_demo\_app/APEX/f110.sql:1572, included in cn\_utils/ai/SELECTAI/SELECT\_AI\_DEMO.sql:10. > > * COUNTRIES – created in conference\_material/KSCOPE25/Built with LOV Using APEX/countries.sql:2, queried in conference\_material/KSCOPE25/Built with LOV Using APEX/f777.sql:2462. > > > Other names appeared in the source scan but are not tables in the DEMO schema (e.g., views like CNDEMO\_ADDRESSES\_V or package calls such as TABLE\_FN\_PK.EVS\_SOLD\_BY\_YEAR). Let me know if you want to repeat the check against another connection or extend it to views/synonyms. --- ## Full Agent Mode Q&A questions are great, but can we create code in the DB? Let’s try!
➡
Create a TAPI package for the customers, orders, order_lines, and products tables in the DEMO db. Use coding standards laid out in the AGENT.md file. Use the prefix AIDEMO for the package name. Create the package directly in the DEMO DB using the sqlcl mcp server.
**Notice** the table names are not quite right. I have orders instead of sales\_orders and order\_lines instead of sales\_order\_lines. Here is an excerpt from the “model reasoning trace”: ![Chain of thought from Create Code Example.](https://cdn.hashnode.com/res/hashnode/image/upload/v1760213083700/3832a37c-5c71-41bd-85f2-b9d602f6f453.png align="center") Here is the response: --- > Created table APIs for customers, orders, order lines, and products in DEMO so each table now has consistent insert/update/delete/fetch routines with APEX debug logging and error propagation. > > * AIDEMO\_CUSTOMERS\_PK uses customers%ROWTYPE for CRUD, returning generated IDs and logging apex\_debug.error on failure. > > * AIDEMO\_PRODUCTS\_PK mirrors the same pattern for PRODUCTS, defaulting created\_at to SYSDATE when absent. > > * AIDEMO\_ORDERS\_PK targets SALES\_ORDERS (no plain ORDERS object exists) and fills defaults for order\_date, total\_amount, and status during inserts. > > * AIDEMO\_ORDER\_LINES\_PK targets SALES\_ORDER\_LINES, recalculating line\_total when not provided. > > * Verified all specs/bodies compile cleanly (USER\_OBJECTS shows each package and body VALID). > > > Next steps: > > 1. Exercise the new TAPIs from your APEX flows or unit harness to confirm business logic/defaults behave as expected. > > 2. If you need deployment artifacts, export these CREATE OR REPLACE statements into your /SCRIPTS folder for source control. > --- It figured out the correct tables and created a create/update/delete package for each table: ![Code Created](https://cdn.hashnode.com/res/hashnode/image/upload/v1760213252702/bcd3ba72-35f7-497c-bf6d-d05565e142db.png align="center") A few notes: * It included the `apex_debug` call in the WHEN OTHERS exception, which I had specified in my AGENTS.md # What about APEX? Because APEX lives in the database, it is an obvious candidate for interrogation by LLMs.
➡
Look at the Oracle APEX applications in the WKSP_WKSPDEMO schema and list the top 5 security concerns.
Example response (not my apps 😊): --- > * Application 103 has 45 pages where PAGE\_ACCESS\_PROTECTION = 'Unrestricted' (apex\_application\_pages), leaving session state open to tampering; switch these pages to checksum-protected or require re-authentication. 🫢 > > * Browser caching remains enabled for application 103 (BROWSER\_CACHE = 'Enabled'), risking sensitive data leakage on shared machines; disable caching for authenticated apps. > ---
➡
Which APEX Applications are not running the latest version of APEX and are not running the latest version of the universal theme?
Example response (again, not my apps 😊): --- > * apex\_release.version\_no reports 24.2.9 as the current APEX version; four applications (100, 101, 102, 110) already use compatibility\_mode = '24.2' and Universal Theme 24.2 (apex\_application\_themes). > > * Application 103 is still on compatibility\_mode = '4.2' 🫢 (apex\_applications), so its runtime features are constrained to an obsolete APEX release. > > * The same application 103 runs Universal Theme version 1.2 with file\_prefix = '#IMAGE\_PREFIX#themes/theme\_42/1.2/' (apex\_application\_themes), which predates the current 24.2 delivery. > --- # Other Prompts * Identify tables in the DEMO schema that are missing foreign keys. Add each missing foreign key to a script called MISSING\_FOREIGN\_KEYS.sql for my review. * Not only did the LLM do a good job of identifying missing foreign keys and creating the respective ALTER TABLE scripts, but it also identified orphaned records in one table! * Review database objects in the DEMO schema and files in my codebase to identify unused tables. Create a script called POTENTIAL\_TABLE\_DROPS.sql with DROP statements for each. Do not execute the drop statements. * Look at SQL queries that have run in the DEMO schema and list the top three poorly performing SQL statements. You may need to run this as a privileged user. * You are an expert technical author with specialist knowledge in Oracle APEX, Oracle Database, PL/SQL, and SQL. You have been assigned to the ‘XYZ project’. The goal of the project is to migrate from a legacy APEX version to the latest version (24.2) while enhancing security and adding the required business functionality. Connect to the 'XYZ' connection using the sqlcl mcp server. Review all of the database objects and APEX applications in the ‘XYX’ schema. Your goal is to create high-quality, easy-to-read, and concise technical documentation for the support team that will take over support for the application. Create a document called TECHICAL\_DESIGN.md in the DOCS folder. # Other Considerations * Ensure the LLM and SQLcl are connected to the correct instance and schema. This is especially important if, like me, you have numerous saved database connections in SQL Developer. Because the LLM is deciding what command to run in SQLcl, it can easily pick the wrong connection. It helps to have clear connection names that differ between clients and instances, e.g., ABCCORP-DEV and CLOUDNUEVA-DEV, as opposed to DEV1 and DEV2. * I recommend creating a read-only schema in non-development instances. * For complex questions, the number of iterations between the LLM and SQLcl can be large. Some questions can take multiple minutes to answer and require a significant number of tokens. 💲 * I find it interesting to review both the plan that the LLM generates at the start of the process and the “model reasoning trace” it emits as it goes through the process of answering your question. This helps me to build better prompts. * As models evolve (GPT3 > GPT4 > GPT5), the prompts you enter today may not yield the same results tomorrow. As with any AI technology, it is important to build a list of [Evals](https://blog.cloudnueva.com/why-evals-are-important-in-ai-development) that you can use to test new models and prompts against results from previous iterations. * If you do not already, add rich and informative table and column comments. I laid out why this is important in my [post about SELECT AI](https://blog.cloudnueva.com/select-ai-is-not-a-toy#heading-table-amp-column-comments). # Conclusion In the end, the SQLcl MCP server isn’t magic 🪄; it’s just a bridge between your database and an LLM. It saves time, reduces context switching, and helps you think through SQL and APEX problems more efficiently. It won’t replace your judgment or stop you from making a bad call, but it can make routine work faster and more consistent.
💡
Like any tool, it’s only as good as the care and thought that you put behind it.
# Appendix 1 - Configure & Test GitHub Copilot with SQLcl MCP Server Here is a guide to [get started with GitHub Copilot in VS Code](https://code.visualstudio.com/docs/copilot/getting-started). ## Install GitHub Copilot Extensions for VS Code * Install the GitHub Copilot and GitHub Copilot Chat Extensions ![GitHub Copilot and GitHub Copilot Chat Extensions](https://cdn.hashnode.com/res/hashnode/image/upload/v1760206602839/4fb22159-614b-4837-88a5-63811b65a24b.png align="center") * Log in using your GitHub account ## Configure Copilot to use the SQLcl MCP Server * Open the [VS Code Command Palette](https://code.visualstudio.com/docs/getstarted/userinterface#_command-palette). * Type MCP to see the MCP Options ![Copilot MCP Options](https://cdn.hashnode.com/res/hashnode/image/upload/v1760207088126/845efb6d-4e98-4f26-b857-5582a0274bac.png align="center") * Select MCP: Add Server ![Copilot Add MCP Server](https://cdn.hashnode.com/res/hashnode/image/upload/v1760207168597/0f69819d-0633-4d1e-aaed-4a1d494cd7c9.png align="center") * Select Command (stdio) ![Copilot Enter Command](https://cdn.hashnode.com/res/hashnode/image/upload/v1760207228168/02fcb70c-2283-4ae7-8e46-ae90c34416fc.png align="center") * Enter a name for the MCP Server ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1760207254783/185c0b38-a441-4018-ab4b-70508fb6810b.png align="center") * Select which scope you want it to run in: ![Copilot MCP Server Scope](https://cdn.hashnode.com/res/hashnode/image/upload/v1760207290592/ac9b697a-6dfa-426e-b9c0-1838dd813f41.png align="center") * You should now see the mcp.json file ![Copilot mcp.json](https://cdn.hashnode.com/res/hashnode/image/upload/v1760207331142/dceb6b93-92ab-4f62-89ca-a57324db11dd.png align="center") * Now that the SQLcl MCP Server is installed, you can start/stop/restart it by opening the menu and selecting > MCP: List Servers ![Start MCP Server 1](https://cdn.hashnode.com/res/hashnode/image/upload/v1760207605605/84496a0a-f068-4680-82a0-8e153f2188d3.png align="center") * Then select SQLcl ![Start MCP Server 2](https://cdn.hashnode.com/res/hashnode/image/upload/v1760207725912/2a5b6a0c-9a2f-4230-bb4e-c671eb2febe0.png align="center") * Select ‘Start Server’ to start the MCP Server. The same navigation will allow you to stop an already running MCP Server. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1760207744614/1d8af4ce-e725-4669-82b9-51ff9c504a1d.png align="center") * Click ‘Show Output’ to see a log of what it is doing. ![Start MCP Server 5](https://cdn.hashnode.com/res/hashnode/image/upload/v1760207822059/aba2e6eb-70dd-4fad-a9d4-e5a2fbd9b318.png align="center") **Note**: VS Code will start the SQLcl MCP Server for you if it is not already started. ## Test from VS Code Open the CoPilot Chat Extension and type the following in the chat Window: Connect to DEMO using the SQLcl MCP Server ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1760208892836/8fe34b15-dfa7-45df-97fc-e4363561aff6.png align="center")
🤨
Oops. What went wrong? Copilot did not route the prompt to the SQLcl MCP server, likely due to a VS Code workspace context or MCP scope issue. I have found that the VS Code extension becomes confused (regarding the SQLcl MCP Server) when a Workspace or any open files are in VS Code. If I close all folders and files and try again…
![SQLcl MCP Server from Copilot](https://cdn.hashnode.com/res/hashnode/image/upload/v1760209177914/c5afcabf-49ff-4a95-8035-a6cafe3c6550.png align="center")
💡
While it’s great that I am now connected, not being able to ask questions about my database and codebase simultaneously is not ideal. This is the main reason that I now use Codex instead of CoPilot when using the SQLcl MCP Server. You may also have noticed that Copilot is a lot more ‘chatty’, which I don’t like.