SQLcl MCP Demo Guide

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

# Install Oracle JDK (latest version - currently Java 24)
brew install --cask oracle-jdk

# Find the Java installation path
/usr/libexec/java_home -V

# The output will show available Java versions. Look for the Oracle JDK path.
# For Java 24, it should be something like:
# /Library/Java/JavaVirtualMachines/jdk-24.jdk/Contents/Home

# Set JAVA_HOME using the dynamic path (recommended)
echo 'export JAVA_HOME="$(/usr/libexec/java_home)"' >> ~/.zshrc
echo 'export PATH="$JAVA_HOME/bin:$PATH"' >> ~/.zshrc

# OR set it to the specific Java 24 path if you see it:
# echo 'export JAVA_HOME="/Library/Java/JavaVirtualMachines/jdk-24.jdk/Contents/Home"' >> ~/.zshrc
# echo 'export PATH="$JAVA_HOME/bin:$PATH"' >> ~/.zshrc

# Reload your shell
source ~/.zshrc

# Verify installation
java -version

# 1. Download Oracle JDK from: https://www.oracle.com/java/technologies/downloads/
# 2. Choose macOS x64 Installer (.dmg) for Java 17 or later
# 3. Install the .dmg file
# 4. Find the installed version
/usr/libexec/java_home -V

# 5. Set JAVA_HOME (using dynamic path)
echo 'export JAVA_HOME="$(/usr/libexec/java_home)"' >> ~/.zshrc
echo 'export PATH="$JAVA_HOME/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

# Verify installation
java -version

java version "24.0.x" 2024-xx-xx
Java(TM) SE Runtime Environment (build 24.0.x+xx-xxx)
Java HotSpot(TM) 64-Bit Server VM (build 24.0.x+xx-xxx, mixed mode, sharing)

docker --version

# Pull the Oracle Database 23ai Free image (latest version)
docker pull gvenzl/oracle-free:latest

# Alternative: Use the slim version for faster download (recommended)
docker pull gvenzl/oracle-free:slim

# Create a Docker network for Oracle
docker network create oracle-network

# Run Oracle Database 23ai container (using slim version)
docker run -d \
  --name oracle23ai \
  --network oracle-network \
  -p 1521:1521 \
  -p 5500:5500 \
  -e ORACLE_PASSWORD=Welcome123 \
  -e APP_USER=demo \
  -e APP_USER_PASSWORD=demo123 \
  -v oracle-data:/opt/oracle/oradata \
  gvenzl/oracle-free:slim

# Check container status
docker ps

# Wait for database to be ready (may take 2-3 minutes)
docker logs -f oracle23ai

# Look for: "DATABASE IS READY TO USE!"

# Connect to the database container (suppress Java warnings)
export JAVA_TOOL_OPTIONS="--enable-native-access=ALL-UNNAMED"
docker exec -it oracle23ai sqlplus sys/Welcome123@//localhost:1521/FREE as sysdba

# Or connect with SQLcl
sql sys/Welcome123@//localhost:1521/FREE as sysdba

# In SQLPlus/SQLcl, run:
SELECT banner FROM v$version;
EXIT;

# Create directory for SQLcl
mkdir -p ~/oracle-tools
cd ~/oracle-tools

# Download latest SQLcl
curl -L -o sqlcl-latest.zip https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip

# Extract SQLcl
unzip sqlcl-latest.zip

# Add to your shell profile (~/.zshrc)
echo 'export SQLCL_HOME="$HOME/oracle-tools/sqlcl"' >> ~/.zshrc
echo 'export PATH="$SQLCL_HOME/bin:$PATH"' >> ~/.zshrc

# Add Java options to suppress warnings with Java 24
echo 'export JAVA_TOOL_OPTIONS="--enable-native-access=ALL-UNNAMED"' >> ~/.zshrc

# Reload shell
source ~/.zshrc

# Verify SQLcl installation
sql -version

# Connect as SYSDBA
sql sys/Welcome123@//localhost:1521/FREE as sysdba

# In SQLcl, create MCP user:

-- Create MCP user with limited privileges
CREATE USER mcp_user IDENTIFIED BY mcp123;

-- Grant necessary privileges
GRANT CONNECT, RESOURCE TO mcp_user;
GRANT CREATE SESSION TO mcp_user;
GRANT SELECT ANY TABLE TO mcp_user;  -- For demo only, be more restrictive in production

-- Grant quota on USERS tablespace (essential for creating tables)
ALTER USER mcp_user QUOTA UNLIMITED ON USERS;

-- Grant the new 23ai developer role
GRANT DB_DEVELOPER_ROLE TO mcp_user;

-- Create some sample data
CONNECT mcp_user/mcp123@//localhost:1521/FREE;

-- Create sample tables
CREATE TABLE employees (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    department VARCHAR2(50),
    salary NUMBER,
    hire_date DATE
);

CREATE TABLE departments (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    manager VARCHAR2(100),
    budget NUMBER
);

-- Insert sample data
INSERT INTO employees VALUES (1, 'John Smith', 'Engineering', 75000, DATE '2023-01-15');
INSERT INTO employees VALUES (2, 'Jane Doe', 'Marketing', 65000, DATE '2023-02-20');
INSERT INTO employees VALUES (3, 'Bob Wilson', 'Engineering', 80000, DATE '2022-11-10');
INSERT INTO employees VALUES (4, 'Alice Brown', 'Sales', 70000, DATE '2023-03-05');

INSERT INTO departments VALUES (1, 'Engineering', 'Tech Lead', 500000);
INSERT INTO departments VALUES (2, 'Marketing', 'Marketing Director', 300000);
INSERT INTO departments VALUES (3, 'Sales', 'Sales Manager', 400000);

COMMIT;

EXIT;

# Suppress Java warnings
export JAVA_TOOL_OPTIONS="--enable-native-access=ALL-UNNAMED"

# Start SQLcl without connecting
sql /nolog

-- Create and save named connections WITH PASSWORDS (essential for MCP)
CONN -save oracle23ai -replace -savepwd mcp_user/mcp123@//localhost:1521/FREE

-- Also create a SYS connection for admin tasks  
CONN -save sys_admin -replace -savepwd sys/Welcome123@//localhost:1521/FREE as sysdba

-- List saved connections to verify (correct SQLcl command)
CONNMGR LIST

-- Show details of a specific connection
CONNMGR SHOW oracle23ai

-- Test the saved connection
CONN oracle23ai
SELECT 'Connection test successful' as status FROM dual;

EXIT;

-- List all saved connections
CONNMGR LIST

-- Show details of a specific connection
CONNMGR SHOW connection_name

-- Test a connection
CONNMGR TEST connection_name

-- Delete a saved connection if needed
-- (Currently not available in SQLcl - you need to re-save to overwrite)

-- Update a saved connection (with password)
CONN -save oracle23ai -replace -savepwd new_user/new_password@//localhost:1521/FREE

-- Create connections for different environments (all with saved passwords)
CONN -save dev_env -replace -savepwd user/pass@//dev-server:1521/DEV
CONN -save test_env -replace -savepwd user/pass@//test-server:1521/TEST
CONN -save prod_readonly -replace -savepwd readonly_user/pass@//prod-server:1521/PROD

# Start SQLcl in MCP mode
sql -mcp

---------- MCP SERVER STARTUP ----------
MCP Server started successfully on [timestamp]
Press Ctrl+C to stop the server
----------------------------------------

{
  "mcpServers": {
    "oracle-sqlcl": {
      "command": "/Users/[your-username]/oracle-tools/sqlcl/bin/sql",
      "args": ["-mcp"],
      "env": {
        "JAVA_HOME": "/Library/Java/JavaVirtualMachines/jdk-24.jdk/Contents/Home"
      }
    }
  }
}

{
  "mcpServers": {
    "oracle-sqlcl": {
      "command": "/Users/[your-username]/oracle-tools/sqlcl/bin/sql",
      "args": ["-mcp"],
      "env": {
        "JAVA_HOME": "/Library/Java/JavaVirtualMachines/jdk-24.jdk/Contents/Home"
      }
    }
  }
}

# Suppress Java warnings
export JAVA_TOOL_OPTIONS="--enable-native-access=ALL-UNNAMED"

# Test saved connections
sql /nolog

-- List all saved connections
CONNMGR LIST

-- Connect using saved connection name
CONN oracle23ai

-- Test the tables we created
SELECT table_name FROM user_tables;
SELECT * FROM employees;

-- Test switching connections
CONN sys_admin
SELECT username FROM dba_users WHERE username = 'MCP_USER';

EXIT;

# Start the MCP server
sql -mcp

---------- MCP SERVER STARTUP ----------
MCP Server started successfully on [timestamp]
Press Ctrl+C to stop the server
----------------------------------------

List all available database connections, then connect to oracle23ai and show me all available tables in the schema.

Connect to oracle23ai and show me the highest paid employees and their departments.

Using the oracle23ai connection, create a report showing the average salary by department, including the department budget and the percentage of budget spent on salaries.

Connect to oracle23ai and describe the structure of the employees table and show me some sample data.

Using oracle23ai connection, add a new employee: Sarah Connor, Engineering department, salary $85000, hired today.

Connect to oracle23ai and analyze employee salary trends. Show me the salary distribution by department and identify any salary outliers.

Using oracle23ai connection, show me the current database version, available tablespaces, and total number of objects in the mcp_user schema.

# In a new terminal, connect to database
export JAVA_TOOL_OPTIONS="--enable-native-access=ALL-UNNAMED"
sql oracle23ai

-- View MCP activity logs
DESC DBTOOLS$MCP_LOG;

-- See recent MCP operations
SELECT 
    id,
    mcp_client,
    model,
    end_point_type,
    end_point_name,
    log_message,
    created_on
FROM DBTOOLS$MCP_LOG
ORDER BY created_on DESC;

-- Check active MCP sessions
SELECT 
    sid,
    serial#,
    username,
    module,
    action,
    status,
    logon_time
FROM v$session 
WHERE module LIKE '%MCP%' 
   OR username = 'MCP_USER';

-- Connect to the database first
-- sql oracle23ai (or use Claude Desktop with "Connect to oracle23ai")

-- Add more realistic data
CREATE TABLE sales_data (
    id NUMBER PRIMARY KEY,
    employee_id NUMBER,
    sale_date DATE,
    amount NUMBER,
    product VARCHAR2(50),
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

INSERT INTO sales_data VALUES (1, 4, DATE '2024-01-15', 15000, 'Enterprise Software');
INSERT INTO sales_data VALUES (2, 4, DATE '2024-01-20', 25000, 'Cloud Services');
INSERT INTO sales_data VALUES (3, 2, DATE '2024-01-18', 5000, 'Marketing Package');

COMMIT;

Connect to oracle23ai and analyze the sales performance by employee. Create a summary report showing each salesperson's total sales, average deal size, and commission (assuming 5% commission rate).

Using oracle23ai connection, create a new table to track customer information and insert sample customer data for the sales we have recorded.

Connect to oracle23ai and generate a comprehensive business intelligence report showing employee performance, departmental budgets, and sales metrics with recommendations for improvement.

# Stop MCP server (Ctrl+C in the MCP terminal)
# Stop Docker container
docker stop oracle23ai

# Start Oracle container
docker start oracle23ai

# Wait for database to be ready
docker logs -f oracle23ai

# Start MCP server
export JAVA_TOOL_OPTIONS="--enable-native-access=ALL-UNNAMED"
sql -mcp

# If you get "getCurrentConnection() is null" error:

# 1. MOST COMMON CAUSE: Missing -savepwd flag
# Re-save connections with passwords:
sql /nolog
CONN -save oracle23ai -replace -savepwd mcp_user/mcp123@//localhost:1521/FREE
CONNMGR LIST
EXIT;

# 2. Stop the current MCP server (Ctrl+C) and restart
export JAVA_TOOL_OPTIONS="--enable-native-access=ALL-UNNAMED"
sql -mcp

# 3. If still issues, try starting with explicit connection
sql mcp_user/mcp123@//localhost:1521/FREE -mcp

# 4. Verify saved connections work
sql /nolog
CONN oracle23ai
SELECT 'Test successful' FROM dual;
EXIT;

# Method 1: Start with explicit connection
sql mcp_user/mcp123@//localhost:1521/FREE -mcp

# Method 2: Start and then connect via MCP tools
sql -mcp
# Then use Claude to connect

# Method 3: Use SQLcl with connection file
echo "mcp_user/mcp123@//localhost:1521/FREE" > connection.txt
sql @connection.txt -mcp

# Check if MCP server is responding
# In another terminal while MCP server is running:
ps aux | grep sql
netstat -an | grep LISTEN

# Test basic MCP functionality
echo '{"jsonrpc": "2.0", "method": "initialize", "params": {"capabilities": {}}, "id": 1}' | nc -l 3000

# Check Claude Desktop logs (macOS)
tail -f ~/Library/Logs/Claude/claude_desktop.log

# Check MCP server configuration
cat ~/Library/Application\ Support/Claude/claude_desktop_config.json

# Restart Claude Desktop completely
# Close app, then restart

# Test database connectivity
sql mcp_user/mcp123@//localhost:1521/FREE

# Check Docker container
docker ps
docker logs oracle23ai

# Verify SQLcl version (should be 25.2+)
sql -version

# Check MCP server startup
sql -mcp -verbose

-- Production-ready user setup
CREATE USER ai_readonly IDENTIFIED BY complex_password;
GRANT CONNECT TO ai_readonly;
GRANT SELECT ON specific_schema.specific_table TO ai_readonly;
-- Grant quota if the user needs to create temporary tables
-- ALTER USER ai_readonly QUOTA 100M ON USERS;
-- Grant only necessary permissions

# Check running containers
docker ps

# View Oracle logs
docker logs oracle23ai

# Connect to database using saved connection
sql oracle23ai

# Connect to database directly
sql mcp_user/mcp123@//localhost:1521/FREE

# Start MCP server
sql -mcp

# Check SQLcl version
sql -version

# Save new connection with password
sql /nolog
CONN -save oracle23ai -replace -savepwd mcp_user/mcp123@//localhost:1521/FREE

# List saved connections
sql /nolog
CONNMGR LIST

# Show connection details
CONNMGR SHOW oracle23ai