Using Oracle SQLcl MCP Server with Oracle 19c: A Step-by-Step Guide for NLP-Based Database Queries

 

Introduction

With the rapid evolution of AI, databases are no longer limited to traditional SQL-only interactions. Oracle has taken a major step forward by introducing MCP (Model Context Protocol) support in SQLcl, allowing AI tools like Claude Desktop to interact directly with Oracle databases using natural language.

In this blog, I’ll walk you through a hands-on, end-to-end setup of Oracle SQLcl MCP Server with an on-prem / OCI-hosted Oracle 19c database, and show how conversational AI can query enterprise databases securely.

This guide is ideal for Oracle DBAs, Cloud Architects, and AI-curious professionals who want to explore NLP-driven database access.

   Image source:-https://blogs.oracle.com/database/introducing-mcp-server-for-oracle-database

---

Architecture Overview

AI Client (Claude Desktop)
⬇️ MCP Protocol
SQLcl MCP Server (Local Machine)
⬇️ JDBC
Oracle Database 19c (OCI / On-Prem)

The AI never connects to the database directly. SQLcl acts as a secure MCP bridge, translating natural language into database operations.

---

Prerequisites

Before starting, ensure you have:

• Oracle Database 19c (On-Prem or OCI Compute VM)

• Windows laptop or desktop

• Internet access to download tools

• Basic Oracle SQL knowledge

---

Step 1: Install JDK 17 (Required for SQLcl)

Oracle SQLcl requires Java 17.

• Download JDK 17 for Windows from Oracle

• Install using the .exe

• Set JAVA_HOME and update PATH

Verify:

java -version

---

Step 2: Install Oracle SQLcl

• Download SQLcl from Oracle

• Unzip it to a directory (example):

  C:\AI\sqlcli

SQLcl is portable—no installer required.

---

Step 3: Install Claude Desktop

Claude Desktop will act as the AI MCP client.

• Download Claude Desktop

• Install and launch once

• Close it before MCP configuration

---

Step 4: Prepare Oracle Database 19c

Verify PDBs

show pdbs;

Ensure your PDB (e.g., ORCLPDB) is in READ WRITE mode.

Listener and Network Setup

• Ensure port 1521 is open

• Disable firewall (lab use only):

systemctl stop firewalld

systemctl disable firewalld

• Confirm connectivity from Windows:

Test-NetConnection <DB_PUBLIC_IP> -Port 1521

---

Step 5: Create SQLcl Connection

Launch SQLcl:

sql /nolog

Create and save a connection:

conn -save oracle19c_mcptest -savepwd system/password@<IP>:1521/ORCLPDB

Validate:

CONNMGR test oracle19c_mcptest

---

Step 6: Start SQLcl MCP Server

sql -mcp -name oracle19c_mcptest

You should see:

MCP Server started successfully

This process must remain running.

---

Step 7: Configure Claude Desktop for MCP

Edit Claude configuration file:

{

"mcpServers": {

"oracle19c": {

"command": "C:/AI/sqlcli/sqlcl/sqlcl/bin/sql.exe",

"args": ["-mcp", "-name", "oracle19c_mcptest"]

}

}

}

Restart Claude Desktop and allow MCP access when prompted.

---

Step 8: Follow Least Privilege (Best Practice)

Instead of SYSTEM, create an application user:

CREATE USER app_user IDENTIFIED BY password;

GRANT CREATE SESSION, CREATE TABLE TO app_user;

Create sample data:

CREATE TABLE sales_orders (...);

INSERT INTO sales_orders VALUES (...);

COMMIT;

Create a separate SQLcl MCP connection for this user.

This ensures:

• AI only sees approved schemas

• SYS/SYSTEM access is avoided

---

Step 9: Test NLP Queries via Claude

Now the magic ✨

Ask Claude:

Claude:

• Understands intent

• Calls SQLcl MCP

• Executes SQL

• Returns results

No SQL typing required.

---

Security Considerations

✔ SQLcl connections are local-only ✔ Credentials stored in user profile ✔ Secure with OS file permissions ✔ Use separate DB users ✔ Optional: Oracle Wallet for credentials

AI never gets raw database access.

---

Why This Matters

This setup demonstrates:

• Conversational AI for ad-hoc querying

• AI + Oracle DB without exposing credentials

• Perfect for DBAs, Support, and Architects

---

Final Thoughts

Oracle SQLcl MCP Server bridges the gap between enterprise databases and modern AI—securely, locally, and powerfully.

If you’re running Oracle 19c today, you can already start experimenting with conversational data access.

resize boot volume in oracle cloud infrastructure aka OCI for a Linux instance

 

The Oracle Cloud Infrastructure Block Volume service lets you scaleup the size of block volumes and boot volumes. Well, one fine day, my boot volume was short of space and i was looking for some vaccum in my  mount point. I had the challenge, where  /u01 was mounted on boot volume and it had free space of 30 GB only. My requirement was to have around 150 GB. Thanks to OCI, we can scale up the boot volume. 

P.S:-As per https://docs.oracle.com/en-us/iaas/Content/Block/Tasks/resizingavolume.htm, we cannot scale up IDE type boot volume. Also we cannot scale down the boot volume.

 

Open the navigation menu in Oracle Cloud Infrastructure console. Under Core Infrastructure, go to Compute and click Boot Volumes.

 

 

 

In the Boot Volumes list, click the boot volume you want to resize.Click Edit Size or Performance.Specify the new size in VOLUME SIZE (IN GB). You must specify a larger value than the boot volume's current size.

 

 

 

After the volume is provisioned, for the volume resize to take effect, Log on to your instance and then paste and run the rescan commands

 

1. Run the applicable rescan commands.
sudo dd iflag=direct if=/dev/<device_name> of=/dev/null count=1
echo "1" | sudo tee /sys/class/block/<device_name>/device/rescan
2. Extend the partition manually. 
[opc@apps ~]$ sudo su -
Last login: Mon Jan 11 14:17:16 GMT 2021 on pts/0
[root@apps ~]# df -kh
Filesystem      Size  Used Avail Use% Mounted on
devtmpfs        7.2G     0  7.2G   0% /dev
tmpfs           7.3G     0  7.3G   0% /dev/shm
tmpfs           7.3G   25M  7.2G   1% /run
tmpfs           7.3G     0  7.3G   0% /sys/fs/cgroup
/dev/sda3       367G  333G   35G  91% /
/dev/sda1       200M  8.6M  192M   5% /boot/efi
tmpfs           1.5G     0  1.5G   0% /run/user/994
tmpfs           1.5G     0  1.5G   0% /run/user/1000

 

Now, extend the partition

[root@apps ~]# sudo /usr/libexec/oci-growfs
CHANGE: partition=3 start=17188864 old: size=769243102 end=786431966 new: size=1031387102 end=1048575966
Confirm? [y/n]y
CHANGED: partition=3 start=17188864 old: size=769243102 end=786431966 new: size=1031387102 end=1048575966
meta-data=/dev/sda3              isize=256    agcount=39, agsize=2515200 blks
         =                       sectsz=4096  attr=2, projid32bit=1
         =                       crc=0        finobt=0 spinodes=0 rmapbt=0
         =                       reflink=0
data     =                       bsize=4096   blocks=96155387, imaxpct=25
         =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0 ftype=1
log      =internal               bsize=4096   blocks=4912, version=2
         =                       sectsz=4096  sunit=1 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0
data blocks changed from 96155387 to 128923387

 

 

Confirm the changes

 

[root@apps ~]# df -kh
Filesystem      Size  Used Avail Use% Mounted on
devtmpfs        7.2G     0  7.2G   0% /dev
tmpfs           7.3G     0  7.3G   0% /dev/shm
tmpfs           7.3G   25M  7.2G   1% /run
tmpfs           7.3G     0  7.3G   0% /sys/fs/cgroup
/dev/sda3       492G  333G  160G  68% /
/dev/sda1       200M  8.6M  192M   5% /boot/efi
tmpfs           1.5G     0  1.5G   0% /run/user/994
tmpfs           1.5G     0  1.5G   0% /run/user/1000
[root@apps ~]#

 

 In this post, i have tried to demonstrate on how we can scale up the boot volume online in Oracle Cloud Infrastructure. Hope it helps someone. Happy learning Cloud.

 

 

step by step configure EBS cloud manager

In this post, i will try to give the step by step on how to configure EBS cloud manager. It is already assumed that by going through the link Oracle® E-Business Suite Cloud Manager Guide https://docs.oracle.com/cd/E26401_01/doc.122/f35809/toc.htm, all the prerequisite tasks and EBS Cloud manager topology have been have been completed. If you need an overview of EBS Cloud manager, you can go through my earlier blog https://samappsdba.blogspot.com/2021/01/oracle-e-business-suite-cloud-manager-compute-instance-configuration-issues.html

In the EBS Cloud manager compute instance, Run Oracle E-Business Suite Cloud Manager Configure Script for the First Time

-bash-4.2$ perl configure.pl

Created log file: /u01/install/APPS/apps-unlimited-ebs/out/configure_2021-01-09_09_33_15.log

===========================================================

Configuring Oracle E-Business Suite Cloud Manager VM 20.2.1

===========================================================

Enter Oracle E-Business Suite Cloud Manager Admin Password                       : *************

Enter Oracle E-Business Suite Cloud Manager Admin User OCID (Non-Federated)      : ocid1.user.oc1..********************

Enter Full path to API Private Signing Key                                       : /u01/install/APPS/.oci/oci_api_key.pem

Enter Tenancy OCID                                                               : ocid1.tenancy.oc1..*******************

Validating OCI credentials. Please wait.

Do you wish to use an existing Load Balancer??

1: yes

2: no

Enter your choice: 1

Getting list of available Load Balancers. Please wait.

Available Load Balancers:

1: Cloud_manager

Choose Load Balancer from above list: 1

Enter Load Balancer Listener Port                                                : 443

Validating Port. Please wait.

Enter CIDR Block (Range) from which Client can Access Load Balancer Listener Port: 192.0***/24

----------------------------------------------------------------------------------------------------------

Summary of Inputs

----------------------------------------------------------------------------------------------------------

Oracle E-Business Suite Cloud Manager User Name ( Non Federated )           : *****

Oracle E-Business Suite Cloud Manager User OCID ( Non Federated )           : ocid1.user.oc******************

Fingerprint of API Public Key                                               : 1d:**************

Path to Private PEM key file                                                : /u01/install/APPS/.oci/oci_api_key.pem

Tenancy OCID                                                                : ocid1.tenancy.*************

Region                                                                      : us-ashburn-1

Oracle E-Business Suite Cloud Manager VM Compartment Name                   : ebscm-compartment

Oracle E-Business Suite Cloud Manager VM Compartment OCID                   : ocid1.com****************

Network Compartment Name                                                    : ebscm-compartment

Network Compartment OCID                                                    : ocid1.compartment.*********************

Network VCN Name                                                            : ebscm-vcn

Network VCN OCID                                                            : ocid1.vcn.oc1.***************

Use an existing Load Balancer                                               : true

Load Balancer Listener Port                                                 : 443

CIDR Block (Range) from which Client can Access Load Balancer Listener Port : 192*****

Load Balancer Name                                                          : Cloud_manager

Load Balancer OCID                                                          : ocid1.*****************************

Load Balancer Visibility Type                                               : Public

Load Balancer Shape                                                         : flexible

Load Balancer Subnet Name                                                   : lbaas-subnet-phx

Load Balancer Subnet OCID                                                   : ocid1.subnet*******************

Load Balancer Subnet CIDR                                                   : 10.******

----------------------------------------------------------------------------------------------------------

Do you wish to continue??

1: yes

2: no

Enter your choice: 1

Database schema is already configured. No action needed.

Setting up Load Balancer for Oracle E-Business Suite Cloud Manager VM. This may take a few minutes. Please wait.

Checking Security Lists of LBaaS Subnet, lbaas-subnet-phx

Checking Security Lists of Oracle E-Business Suite Cloud Manager VM Subnet, ebscm-subnet-phx with source CIDR 10.0**********

============================================================================================================================================

Load Balancer Cloud_manager configuration completed. It is not ready to use until you add required rules as per any errors mentioned above.

============================================================================================================================================

==============================================================================================================================================

Register confidential application in IDCS with URL: https://193.********:443 and then re-run this script to update your IDCS configuration.

==============================================================================================================================================

============================================================================================

Finished Configuring Oracle E-Business Suite Cloud Manager VM.

Login URL: https://193.*********:443

Ensure the confidential application is correctly configured in IDCS as per the documentation.

============================================================================================

Register Oracle E-Business Suite Cloud Manager as a Confidential Application

Login to the IDCS console using the non federated user credentials which you have created during the initial setup.

Select confidential application

Under grant, click on add and select authenticator and me

Note down the following

Client ID, Client Secret and Oracle identity service tenant value.

Run Oracle E-Business Suite Cloud Manager Configure Script for the Second Time

-bash-4.2$ perl configure.pl

Created log file: /u01/install/APPS/apps-unlimited-ebs/out/configure_2021-01-09_10_27_12.log

===========================================================

Configuring Oracle E-Business Suite Cloud Manager VM 20.2.1

===========================================================

Enter Oracle E-Business Suite Cloud Manager Admin Password                       : *************

Enter IDCS Client ID                                                             : d*********************

Enter IDCS Client Secret                                                         : 6********************************

Enter IDCS Client Tenant                                                         : idcs-*****************

----------------------------------------------------------------------------------------------------------

Summary of Inputs

----------------------------------------------------------------------------------------------------------

IDCS Client ID                                                              : d*******************

IDCS Client Secret                                                          : 6********************

IDCS Client Tenant                                                          : idcs-*****************

IDCS Host                                                                   : https://idc******************

----------------------------------------------------------------------------------------------------------

Do you wish to continue??

1: yes

2: no

Enter your choice: 1

Database schema is already configured. No action needed.

Load Balancer Cloud_manager is already configured. No action needed.

Configuring IDCS

Installing Chef Server. Please wait.

Starting ChefServer.

Uploading cookbooks

Stopping Node and Job Server if running.

Starting Node and Job Server.

============================================================================================

Finished Configuring Oracle E-Business Suite Cloud Manager VM.

Login URL: https://19**********:443

Ensure the confidential application is correctly configured in IDCS as per the documentation.

============================================================================================

Update local machine etc host file.

load balancer IP        public IP for EBS Compute instance

Create Network Profile

bash-4.2$ cd /u01/install/APPS/apps-unlimited-ebs/bin

-bash-4.2$ perl ProvisionOCINetwork.pl

Program: ProvisionOCINetwork.pl started at Sun Jan 10 08:58:23 2021

Log File : /u01/install/APPS/apps-unlimited-ebs/out/ProvisionOCINetwork_Sun_Jan_10_08_58_23_2021.log

This script will be creating following subnets

EBS subnet 1: This subnet will be used in One-Click Provisioning to deploy Single Image EBS VM. For Advanced Provisioning this subnet will be used to deploy EBS apps tier VM.

EBS subnet 2: This subnet will be used in Advanced Provisioning to deploy EBS db tier VM.

LBaaS subnet: This subnet will be used for Advanced Provisioning and One-Click Provisioning to deploy Load Balancer.

Enter Y to proceed or N to exit: Y

Enter user ocid: *******************

Enter absolute path of private key of API signing key: /u01/install/APPS/.oci/oci_api_key.pem

Enter tenancy ocid: *********************************************

Validating user and fetching OCI metadata...

Enter unique identifier for the EBS network: ebscmnet

Enter EBS subnet 1 CIDR (E.g. 10.0.1.0/24): 

Enter EBS subnet 2 CIDR (E.g. 10.0.2.0/24): 

Enter LBaaS subnet CIDR (E.g. 10.0.3.0/24): 

Are you sure you want to proceed with the above inputs? [Y/N]: Y

Creating network for EBS with unique identifier ebscmnet

Executing command: /u01/install/APPS/bin/terraform init

Executing command: /u01/install/APPS/bin/terraform apply -auto-approve

Creating network profile /u01/install/APPS/apps-unlimited-ebs/ebscmnet_networkprofile.txt

Creating ingress Security Rule with source 10.****** and port 443 for Security List ebscmvm-seclist.

Creating ingress Security Rule with source 10.0.******** and port 443 for Security List ebscmvm-seclist.

Oracle EBS Cloud related network created successfully.

List of resources created:

ebscmnet_lbaas_subnet

ebscmnet_lbaas_seclist

ebscmnet_lbaas_routetable

ebscmnet_db_subnet

ebscmnet_db_seclist

ebscmnet_db_routetable

ebscmnet_apps_subnet

ebscmnet_apps_seclist

ebscmnet_apps_routetable

Program: ProvisionOCINetwork.pl completed at Sun Jan 10 09:04:03 2021

Advanced Network Profile JSON Path: /u01/install/APPS/apps-unlimited-ebs/build/ebscmnet/ebscmnet_DEFAULT_PROFILE_ADVANCED.json

OneClick Network Profile JSON Path: /u01/install/APPS/apps-unlimited-ebs/build/ebscmnet/ebscmnet_DEFAULT_PROFILE_ONECLICK.json

Execute /u01/install/APPS/apps-unlimited-ebs/bin/UploadOCINetworkProfile.pl to Upload JSON into DB

-bash-4.2$

Now run the upload script. The script needs to be uploaded twice, the first time for the One-Click Provisioning default network profile and the second time for the Advanced Provisioning default network profile.

-bash-4.2$ cd /u01/install/APPS/apps-unlimited-ebs/bin

-bash-4.2$ perl UploadOCINetworkProfile.pl

Log File : /u01/install/APPS/apps-unlimited-ebs/out/UploadOCINetworkProfile_Sun_Jan_10_09_04_56_2021.log

Enter Network profile JSON file absolute path: /u01/install/APPS/apps-unlimited-ebs/build/ebscmnet/ebscmnet_DEFAULT_PROFILE_ADVANCED.json

Enter User OCID: oci**************************

Enter Absolute path of private key of API signing key: /u01/install/APPS/.oci/oci_api_key.pem

Enter Tenancy OCID: ocid1.*************

Enter Oracle E-Business Suite Cloud Manager Admin Password:

Executing: ebscm_add_default_network_profile API for DEFAULT_PROFILE_ADVANCE

Executing Stored Procedure: ebscm_add_default_network_profile

RetCode: 0

Row count: 0

ADVANCED Network Profile uploaded successfully.

-bash-4.2$ perl UploadOCINetworkProfile.pl

Log File : /u01/install/APPS/apps-unlimited-ebs/out/UploadOCINetworkProfile_Sun_Jan_10_09_07_15_2021.log

Enter Network profile JSON file absolute path: /u01/install/APPS/apps-unlimited-ebs/build/ebscmnet/ebscmnet_DEFAULT_PROFILE_ONECLICK.json

Enter User OCID: ocid******************

Enter Absolute path of private key of API signing key: /u01/install/APPS/.oci/oci_api_key.pem

Enter Tenancy OCID: ocid1****************

Enter Oracle E-Business Suite Cloud Manager Admin Password:

Executing: ebscm_add_default_network_profile API for DEFAULT_PROFILE_ONECLICK

Executing Stored Procedure: ebscm_add_default_network_profile

RetCode: 0

Row count: 0

ONECLICK Network Profile uploaded successfully.

-bash-4.2$

Access your EBS Cloud Manager URL now

This completes the configuration of EBS Cloud Manager and on how we can launch the GUI based interface. Lots of improvisation have been done which i will be exploring now and hopefully i will be able to blog them as well. Till then happy learning Oracle Cloud and off course EBS :)

References:-

https://docs.oracle.com/cd/E26401_01/doc.122/f35809/toc.htm

                https://docs.oracle.com/cd/E26401_01/doc.122/f35809/T679330T679340.htm#cmg_prepare_createnetrsrcs

Oracle E-Business Suite Cloud Manager Compute Instance configuration issues

Oracle E-Business Suite Cloud Manager - Oracle E-Business Suite Cloud Manager is a graphical user interface used for creating, managing, and configuring Oracle E-Business Suite environments on Oracle Cloud Infrastructure. Oracle E-Business Suite Cloud Manager can be used with the Oracle E-Business Suite Cloud Backup Module to lift and shift or clone environments from onpremises to Oracle Cloud Infrastructure

Issues Encountered:-

Recently, i was trying to configure Oracle E-Business Suite Cloud Manager and as part of which when i tried to run the perl configure.pl script, encountered the below error

[2021/01/02 14:37:20]:BMCUtils::printOutErrorUtil: INFO:
---- Err: START ----
ServiceError:
{
"code": "NotAuthorizedOrNotFound",
"message": "Authorization failed or requested resource not found",
"opc-request-id": "DAA8DCE74C3C4F6291B2E0A8F4011B72/8C171D153E97B1ECBB5D8A5553B8EFBD/CAC67E1B04F8787E886355B92D0FA89D",
"status": 404
}

To avoid the errors, we can crosscheck the below points

1. make sure .pem files are owned by Oracle user
2. check the permissions of the .oci directory and the .pem files inside it
3. Ensure that the user who will do the api calling are non federated user
4. Check if the non federated user has been assigned to any group in OCI

 Another issue which i encountered is:-

Utils::executeConfigCmd: INFO: ^[[1m^[[31mError: ^[[0m^[[0m^[[1mService error:InvalidParameter. shapeDetails cannot be null when shape is flexible. http status code: 4

To get rid of this error, i had to create a flexible load balancer first and then deleted the listener which got created as part of load balancer creation.

Then choose the option of

1. Use an existing load balancer when you run the perl configure.pl script.

Issue:-log4j:WARN Please initialize the log4j system properly.log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.BMCS credential validation failed. Detailed error: (404, NamespaceNotFound, false) You do not have authorization to perform this request

Make sure the user for which you are passing the ocid have the authorization to create resources in the following compartment in which you want the backups and new provisioned environment to be.

Issue:-Trying to establish SSH connection using custom private key file /home/oracle/.ssh/id_rsacom.jcraft.jsch.JSchException: Auth failat com.jcraft.jsch.Session.connect(Session.java:512)at com.jcraft.jsch.Session.connect(Session.java:183)at oracle.apps.liftNshift.secure.util.STAGE.EBSLiftNShiftJSCHSessionManager.createJSCHSession(EBSLiftNShiftJSCHSessionManager.java:268)

Created a id_rsa and id_rsa.pub keys. Copied .pub keys to >>authorized_keys. And then using puttygen created a new ppk file from id_rsa file and copied it to server

In my next post https://samappsdba.blogspot.com/2021/01/step-by-step-configure-ebs-cloud-manager.html, i have demonstrated on how we can configure EBS Cloud Manager.

Where can i get  EBS Cloud Manager:-

As of now, the latest Oracle E-Business Suite Cloud Manager version is 20.2.1 which we can get it easily from Oracle Cloud Marketplace and configure it using https://docs.oracle.com/cd/E26401_01/doc.122/f35809/toc.htm

There are considerable changes done to this version and i can't wait to get my hands dirty and play with it.

What is new in this version:-

Advanced Provisioning

Multi-zone support

Enhanced logical hostname support

Public SSH keys deployment

Automated Standby Deployment (EBS 12.2 with DB 12.1.0.2 on Compute)

On-premise to cloud standby deployment

Reduced downtime lift and shift

Cloning on Compute

Option to choose different shapes for target

Extensibility Framework

Capability to add post-execution tasks to provisioning and cloning flows

Support for seeded and custom tasks

Capability to pause and resume execution flows

Lifecycle Management

Horizontal scaling

Backup scheduling

References:-

https://blogs.oracle.com/ebsandoraclecloud/get-ebs-cloud-manager-2021

https://docs.oracle.com/cd/E26401_01/doc.122/f35809/T679330T679337.htm#8732305