OpenLink MCP + Claude LLM: A Data Journey

STEP 1

Initial Connection Request

The journey began when a human requested to execute a SQL query against an Oracle database using JDBC.

Using JDBC URL: jdbc:oracle:thin:@oracle-host:1521:XE, execute.
SELECT * from JOBS.
                    

Human Request

→

Claude LLM

→

OpenLink MCP

STEP 2

Authentication Challenge

Claude attempted multiple database connections but encountered ORA-01017 authentication errors. The OpenLink MCP JDBC driver correctly reported these security violations, demonstrating proper error handling.

Failed to execute_query_md: ORA-01017: invalid username/password; logon denied

STEP 3

Successful Authentication

Once proper credentials (UID=hr, PWD=openlink) were provided, the OpenLink MCP JDBC Driver established a secure connection to the Oracle database and successfully executed the query.

Result: Retrieved 19 job records from HR.JOBS table with complete metadata including job titles, salary ranges, and job codes.

STEP 4

Schema Discovery

Claude leveraged the OpenLink MCP's describe_table and get_tables functions to understand the database structure, revealing 7 interconnected HR tables.

Tables discovered: COUNTRIES, DEPARTMENTS, EMPLOYEES, JOBS, JOB_HISTORY, LOCATIONS, REGIONS
                    

STEP 5

Complex Query Construction

Claude intelligently crafted a sophisticated multi-table JOIN query to create a comprehensive employee job history dashboard, showcasing the power of AI-driven SQL generation.

SELECT e.employee_id, e.first_name || ' ' || e.last_name AS employee_name,
       d.department_name, j.job_title,
       (SELECT COUNT(*) FROM job_history jh WHERE jh.employee_id = e.employee_id) AS job_changes,
       (SELECT LISTAGG(j_hist.job_title, ' → ') WITHIN GROUP (ORDER BY jh.start_date)
        FROM job_history jh JOIN jobs j_hist ON jh.job_id = j_hist.job_id
        WHERE jh.employee_id = e.employee_id) AS career_path
FROM employees e JOIN departments d ON e.department_id = d.department_id...
                    

STEP 6

Data Quality Issues Identified

During dashboard creation, Claude identified empty department names in visualizations. Rather than just fixing the presentation layer, Claude traced the issue back to the source query.

Problem: Bar charts showing departments with blank labels
Root Cause: NULL values in department joins
Solution: SQL-level data cleansing with COALESCE

STEP 7

Query Optimization & Data Cleansing

Claude refined the SQL query using COALESCE functions and LEFT JOINs to handle NULL values properly, ensuring clean data at the source rather than post-processing fixes.

COALESCE(d.department_name, 'Unassigned') AS current_department,
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE (SELECT COUNT(*) FROM job_history jh WHERE jh.employee_id = e.employee_id) > 0
                    

STEP 8

Interactive Dashboard Creation

The culmination: Claude generated a React-based interactive dashboard with multiple visualization components, including pie charts, bar charts, and detailed employee tables—all powered by clean data from the OpenLink MCP JDBC connection.

Features Created:

📊 Employee mobility pie chart
📈 Job changes by employee bar chart
🏢 Department-wise tenure analysis
📋 Detailed employee career path table
🔢 Key performance metrics cards

Key Insights from the Integration

🔄 Seamless Protocol

OpenLink MCP provided a standardized interface between Claude and the Oracle database, enabling natural language to SQL translation.

🛡️ Security First

Proper authentication handling and error reporting ensured secure database access with clear feedback on access attempts.

🧠 Intelligent Analysis

Claude's ability to understand data relationships and generate complex analytical queries showcased AI-powered database intelligence.

🔧 Problem Solving

When data quality issues arose, Claude traced problems to their source and implemented proper solutions rather than quick fixes.