# Directory Structure
```
├── .gitignore
├── .python-version
├── LICENSE
├── package.json
├── pyproject.toml
├── README.md
├── src
│ └── mcp_server_oracle
│ ├── __init__.py
│ └── oracle_tools.py
└── uv.lock
```
# Files
--------------------------------------------------------------------------------
/.python-version:
--------------------------------------------------------------------------------
```
1 | 3.12
2 |
```
--------------------------------------------------------------------------------
/.gitignore:
--------------------------------------------------------------------------------
```
1 | # Byte-compiled / optimized / DLL files
2 | __pycache__/
3 | *.py[cod]
4 | *$py.class
5 |
6 | # C extensions
7 | *.so
8 |
9 | # Distribution / packaging
10 | .Python
11 | build/
12 | develop-eggs/
13 | dist/
14 | downloads/
15 | eggs/
16 | .eggs/
17 | lib/
18 | lib64/
19 | parts/
20 | sdist/
21 | var/
22 | wheels/
23 | share/python-wheels/
24 | *.egg-info/
25 | .installed.cfg
26 | *.egg
27 | MANIFEST
28 |
29 | # PyInstaller
30 | # Usually these files are written by a python script from a template
31 | # before PyInstaller builds the exe, so as to inject date/other infos into it.
32 | *.manifest
33 | *.spec
34 |
35 | # Installer logs
36 | pip-log.txt
37 | pip-delete-this-directory.txt
38 |
39 | # Unit test / coverage reports
40 | htmlcov/
41 | .tox/
42 | .nox/
43 | .coverage
44 | .coverage.*
45 | .cache
46 | nosetests.xml
47 | coverage.xml
48 | *.cover
49 | *.py,cover
50 | .hypothesis/
51 | .pytest_cache/
52 | cover/
53 |
54 | # Translations
55 | *.mo
56 | *.pot
57 |
58 | # Django stuff:
59 | *.log
60 | local_settings.py
61 | db.sqlite3
62 | db.sqlite3-journal
63 |
64 | # Flask stuff:
65 | instance/
66 | .webassets-cache
67 |
68 | # Scrapy stuff:
69 | .scrapy
70 |
71 | # Sphinx documentation
72 | docs/_build/
73 |
74 | # PyBuilder
75 | .pybuilder/
76 | target/
77 |
78 | # Jupyter Notebook
79 | .ipynb_checkpoints
80 |
81 | # IPython
82 | profile_default/
83 | ipython_config.py
84 |
85 | # pyenv
86 | # For a library or package, you might want to ignore these files since the code is
87 | # intended to run in multiple environments; otherwise, check them in:
88 | # .python-version
89 |
90 | # pipenv
91 | # According to pypa/pipenv#598, it is recommended to include Pipfile.lock in version control.
92 | # However, in case of collaboration, if having platform-specific dependencies or dependencies
93 | # having no cross-platform support, pipenv may install dependencies that don't work, or not
94 | # install all needed dependencies.
95 | #Pipfile.lock
96 |
97 | # UV
98 | # Similar to Pipfile.lock, it is generally recommended to include uv.lock in version control.
99 | # This is especially recommended for binary packages to ensure reproducibility, and is more
100 | # commonly ignored for libraries.
101 | #uv.lock
102 |
103 | # poetry
104 | # Similar to Pipfile.lock, it is generally recommended to include poetry.lock in version control.
105 | # This is especially recommended for binary packages to ensure reproducibility, and is more
106 | # commonly ignored for libraries.
107 | # https://python-poetry.org/docs/basic-usage/#commit-your-poetrylock-file-to-version-control
108 | #poetry.lock
109 |
110 | # pdm
111 | # Similar to Pipfile.lock, it is generally recommended to include pdm.lock in version control.
112 | #pdm.lock
113 | # pdm stores project-wide configurations in .pdm.toml, but it is recommended to not include it
114 | # in version control.
115 | # https://pdm.fming.dev/latest/usage/project/#working-with-version-control
116 | .pdm.toml
117 | .pdm-python
118 | .pdm-build/
119 |
120 | # PEP 582; used by e.g. github.com/David-OConnor/pyflow and github.com/pdm-project/pdm
121 | __pypackages__/
122 |
123 | # Celery stuff
124 | celerybeat-schedule
125 | celerybeat.pid
126 |
127 | # SageMath parsed files
128 | *.sage.py
129 |
130 | # Environments
131 | .env
132 | .venv
133 | env/
134 | venv/
135 | ENV/
136 | env.bak/
137 | venv.bak/
138 |
139 | # Spyder project settings
140 | .spyderproject
141 | .spyproject
142 |
143 | # Rope project settings
144 | .ropeproject
145 |
146 | # mkdocs documentation
147 | /site
148 |
149 | # mypy
150 | .mypy_cache/
151 | .dmypy.json
152 | dmypy.json
153 |
154 | # Pyre type checker
155 | .pyre/
156 |
157 | # pytype static type analyzer
158 | .pytype/
159 |
160 | # Cython debug symbols
161 | cython_debug/
162 |
163 | # PyCharm
164 | # JetBrains specific template is maintained in a separate JetBrains.gitignore that can
165 | # be found at https://github.com/github/gitignore/blob/main/Global/JetBrains.gitignore
166 | # and can be added to the global gitignore or merged into this file. For a more nuclear
167 | # option (not recommended) you can uncomment the following to ignore the entire idea folder.
168 | #.idea/
169 |
170 | # Ruff stuff:
171 | .ruff_cache/
172 |
173 | # PyPI configuration file
174 | .pypirc
175 |
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
1 | # mcp-server-oracle
2 | Model Context Protocol server to access oracle
3 |
4 | [](https://www.python.org/downloads/release/python-3120/)
5 | [](https://opensource.org/licenses/MIT)
6 |
7 | ## Demos
8 |
9 |
10 | https://github.com/user-attachments/assets/dc4e377b-4efb-43e6-85fa-93ed852fe21f
11 |
12 |
13 |
14 | ## Quickstart
15 |
16 | To try this in Claude Desktop app, add this to your claude config files:
17 |
18 | ```json
19 | {
20 | "mcpServers": {
21 | "mcp-server-oracle": {
22 | "command": "uvx",
23 | "args": [
24 | "mcp-server-oracle"
25 | ],
26 | "env": {
27 | "ORACLE_CONNECTION_STRING": "username/password@hostname:port/service_name"
28 | }
29 | }
30 | }
31 | }
32 | ```
33 |
34 | ### Prerequisites
35 |
36 | - UV (pacakge manager)
37 | - Python 3.12+
38 | - Claude Desktop
39 |
40 | ### Installation
41 |
42 | #### Claude Desktop Configuration
43 |
44 | Add the server configuration to your Claude Desktop config file:
45 |
46 | **MacOS**: `~/Library/Application\ Support/Claude/claude_desktop_config.json`
47 | **Windows**: `%APPDATA%/Claude/claude_desktop_config.json`
48 |
49 |
50 | ## Contributing
51 |
52 | 1. Fork the repository from [mcp-server-oracle](https://github.com/hdcola/mcp-server-oracle)
53 | 2. Create your feature branch
54 | 3. Commit your changes
55 | 4. Push to the branch
56 | 5. Open a Pull Request
57 |
58 | ## License
59 |
60 | This project is licensed under the MIT License - see the [LICENSE](LICENSE) file for details.
61 |
```
--------------------------------------------------------------------------------
/package.json:
--------------------------------------------------------------------------------
```json
1 | {
2 | "name": "mcp-server-oracle",
3 | "version": "1.0.1",
4 | "description": "",
5 | "main": "index.js",
6 | "scripts": {
7 | "inspector": "npx @modelcontextprotocol/inspector uv run main.py"
8 | },
9 | "keywords": [],
10 | "author": "hdcola",
11 | "license": "MIT",
12 | "packageManager": "[email protected]"
13 | }
14 |
```
--------------------------------------------------------------------------------
/pyproject.toml:
--------------------------------------------------------------------------------
```toml
1 | [project]
2 | name = "mcp-server-oracle"
3 | version = "1.0.2"
4 | description = "Model Context Protocol server to access oracle database"
5 | authors = [
6 | { name = "hdcola", email = "[email protected]" }
7 | ]
8 | readme = "README.md"
9 | requires-python = ">=3.12"
10 | dependencies = [
11 | "mcp[cli]>=1.5.0",
12 | "oracledb>=3.0.0",
13 | "python-dotenv>=1.0.1",
14 | ]
15 |
16 | [project.scripts]
17 | mcp-server-oracle = "mcp_server_oracle:main"
18 | dev = "mcp_server_oracle:dev"
19 |
20 | [build-system]
21 | requires = ["hatchling"]
22 | build-backend = "hatchling.build"
23 |
```
--------------------------------------------------------------------------------
/src/mcp_server_oracle/__init__.py:
--------------------------------------------------------------------------------
```python
1 | import os
2 | import sys
3 | import signal
4 | from typing import Any
5 | from mcp.server.fastmcp import FastMCP
6 | from . import oracle_tools
7 | from dotenv import load_dotenv
8 |
9 |
10 | # Load the environment variables
11 | load_dotenv()
12 |
13 | # Initialize the FastMCP server
14 | mcp = FastMCP("mcp-server-oracle")
15 |
16 | oracle_tools.connection_string = os.getenv("ORACLE_CONNECTION_STRING")
17 |
18 |
19 | @mcp.tool()
20 | async def list_tables() -> str:
21 | """Get a list of all tables in the oracle database
22 |
23 | Args:
24 | None
25 | """
26 | return await oracle_tools.list_tables()
27 |
28 |
29 | @mcp.tool()
30 | async def describe_table(table_name: str) -> str:
31 | """Get a description of a table in the oracle database"
32 |
33 | Args:
34 | table_name (string): The name of the table to describe
35 | """
36 | return await oracle_tools.describe_table(table_name)
37 |
38 |
39 | @mcp.tool()
40 | async def read_query(query: str) -> str:
41 | """Execute SELECT queries to read data from the oracle database
42 |
43 | Args:
44 | query (string): The SELECT query to execute
45 | """
46 | return await oracle_tools.read_query(query)
47 |
48 | @mcp.tool()
49 | async def exec_dml_sql(execsql: str) -> str:
50 | """Execute insert/update/delete/truncate to the oracle database
51 |
52 | Args:
53 | query (string): The sql to execute
54 | """
55 | return await oracle_tools.exec_dml_sql(execsql)
56 |
57 | @mcp.tool()
58 | async def exec_ddl_sql(execsql: str) -> str:
59 | """Execute create/drop/alter to the oracle database
60 |
61 | Args:
62 | query (string): The sql to execute
63 | """
64 | return await oracle_tools.exec_ddl_sql(execsql)
65 |
66 | @mcp.tool()
67 | async def exec_pro_sql(execsql: str) -> str:
68 | """Execute PL/SQL code blocks including stored procedures, functions and anonymous blocks
69 |
70 | Args:
71 | execsql (string): The PL/SQL code block to execute
72 | """
73 | return await oracle_tools.exec_pro_sql(execsql)
74 |
75 |
76 | def main() -> None:
77 | mcp.run(transport='stdio')
78 |
79 |
80 | def dev() -> None:
81 | """
82 | Development function that handles Ctrl+C gracefully.
83 | This function calls main() but catches KeyboardInterrupt to allow
84 | clean exit when user presses Ctrl+C.
85 | """
86 | print("mcp server starting", file=sys.stderr)
87 |
88 | # Define signal handler for cleaner exit
89 | def signal_handler(sig, frame):
90 | print("\nShutting down mcp server...", file=sys.stderr)
91 | sys.exit(0)
92 |
93 | # Register the signal handler for SIGINT (Ctrl+C)
94 | signal.signal(signal.SIGINT, signal_handler)
95 |
96 | try:
97 | # Run the server with proper exception handling
98 | main()
99 | except KeyboardInterrupt:
100 | print("\nShutting down mcp server...", file=sys.stderr)
101 | sys.exit(0)
102 | except Exception as e:
103 | print(f"\nError: {e}", file=sys.stderr)
104 | sys.exit(1)
105 |
```
--------------------------------------------------------------------------------
/src/mcp_server_oracle/oracle_tools.py:
--------------------------------------------------------------------------------
```python
1 | import oracledb
2 | import asyncio
3 |
4 | connection_string = ""
5 |
6 | # 初始化为 Thin 模式
7 | oracledb.init_oracle_client(lib_dir=None)
8 |
9 | async def list_tables() -> list:
10 | tables = []
11 | try:
12 | # Run database operations in a separate thread
13 | def db_operation():
14 | result_tables = []
15 | with oracledb.connect(connection_string) as conn:
16 | cursor = conn.cursor()
17 | cursor.execute(
18 | "SELECT table_name FROM user_tables ORDER BY table_name")
19 | for row in cursor:
20 | result_tables.append(row[0])
21 | return '\n'.join(result_tables)
22 |
23 | return await asyncio.to_thread(db_operation)
24 | except oracledb.DatabaseError as e:
25 | print('Error occurred:', e)
26 | return str(e)
27 |
28 |
29 | async def describe_table(table_name: str) -> str:
30 | try:
31 | # Run database operations in a separate thread
32 | def db_operation(table):
33 | with oracledb.connect(connection_string) as conn:
34 | cursor = conn.cursor()
35 |
36 | # Create CSV headers
37 | result = [
38 | "COLUMN_NAME,DATA_TYPE,NULLABLE,DATA_LENGTH,PRIMARY_KEY,FOREIGN_KEY"]
39 |
40 | # Get primary key columns
41 | pk_columns = []
42 | cursor.execute(
43 | """
44 | SELECT cols.column_name
45 | FROM all_constraints cons, all_cons_columns cols
46 | WHERE cons.constraint_type = 'P'
47 | AND cons.constraint_name = cols.constraint_name
48 | AND cons.owner = cols.owner
49 | AND cols.table_name = :table_name
50 | """,
51 | table_name=table.upper()
52 | )
53 | for row in cursor:
54 | pk_columns.append(row[0])
55 |
56 | # Get foreign key columns and references
57 | fk_info = {}
58 | cursor.execute(
59 | """
60 | SELECT a.column_name, c_pk.table_name as referenced_table, b.column_name as referenced_column
61 | FROM all_cons_columns a
62 | JOIN all_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name
63 | JOIN all_constraints c_pk ON c.r_owner = c_pk.owner AND c.r_constraint_name = c_pk.constraint_name
64 | JOIN all_cons_columns b ON c_pk.owner = b.owner AND c_pk.constraint_name = b.constraint_name
65 | WHERE c.constraint_type = 'R'
66 | AND a.table_name = :table_name
67 | """,
68 | table_name=table.upper()
69 | )
70 | for row in cursor:
71 | fk_info[row[0]] = f"{row[1]}.{row[2]}"
72 |
73 | # Get main column information
74 | cursor.execute(
75 | """
76 | SELECT column_name, data_type, nullable, data_length
77 | FROM user_tab_columns
78 | WHERE table_name = :table_name
79 | ORDER BY column_id
80 | """,
81 | table_name=table.upper()
82 | )
83 |
84 | rows_found = False
85 | for row in cursor:
86 | rows_found = True
87 | column_name = row[0]
88 | data_type = row[1]
89 | nullable = row[2]
90 | data_length = str(row[3])
91 | is_pk = "YES" if column_name in pk_columns else "NO"
92 | fk_ref = fk_info.get(column_name, "NO")
93 |
94 | # Format as CSV row
95 | result.append(
96 | f"{column_name},{data_type},{nullable},{data_length},{is_pk},{fk_ref}")
97 |
98 | if not rows_found:
99 | return f"Table {table} not found or has no columns."
100 |
101 | return '\n'.join(result)
102 |
103 | return await asyncio.to_thread(db_operation, table_name)
104 | except oracledb.DatabaseError as e:
105 | print('Error occurred:', e)
106 | return str(e)
107 |
108 |
109 | async def read_query(query: str) -> str:
110 | try:
111 | # Check if the query is a SELECT statement
112 | if not query.strip().upper().startswith('SELECT'):
113 | return "Error: Only SELECT statements are supported."
114 |
115 | # Run database operations in a separate thread
116 | def db_operation(query):
117 | with oracledb.connect(connection_string) as conn:
118 | cursor = conn.cursor()
119 | cursor.execute(query) # Execute query first
120 |
121 | # Get column names after executing the query
122 | columns = [col[0] for col in cursor.description]
123 | result = [','.join(columns)] # Add column headers
124 |
125 | # Process each row
126 | for row in cursor:
127 | # Convert each value in the tuple to string
128 | string_values = [
129 | str(val) if val is not None else "NULL" for val in row]
130 | result.append(','.join(string_values))
131 |
132 | return '\n'.join(result)
133 |
134 | return await asyncio.to_thread(db_operation, query)
135 | except oracledb.DatabaseError as e:
136 | print('Error occurred:', e)
137 | return str(e)
138 |
139 |
140 | async def exec_dml_sql(execsql: str) -> str:
141 | try:
142 | # 检查SQL语句是否包含DML关键字
143 | sql_upper = execsql.upper()
144 | if not any(keyword in sql_upper for keyword in ['INSERT', 'DELETE', 'TRUNCATE', 'UPDATE']):
145 | return "Error: Only INSERT, DELETE, TRUNCATE or UPDATE statements are supported."
146 |
147 | # Run database operations in a separate thread
148 | def db_operation(query):
149 | with oracledb.connect(connection_string) as conn:
150 | cursor = conn.cursor()
151 | # 执行DML语句
152 | cursor.execute(query)
153 | # 获取影响的行数
154 | rows_affected = cursor.rowcount
155 | # 提交事务
156 | conn.commit()
157 | # 返回执行结果
158 | return f"执行成功: 影响了 {rows_affected} 行数据"
159 |
160 | return await asyncio.to_thread(db_operation, execsql)
161 | except oracledb.DatabaseError as e:
162 | print('Error occurred:', e)
163 | return str(e)
164 |
165 | async def exec_ddl_sql(execsql: str) -> str:
166 | try:
167 | # 检查SQL语句是否包含ddl关键字
168 | sql_upper = execsql.upper()
169 | if not any(keyword in sql_upper for keyword in ['CREATE', 'ALTER', 'DROP']):
170 | return "Error: Only CREATE, ALTER, DROP statements are supported."
171 |
172 | def db_operation(query):
173 | with oracledb.connect(connection_string) as conn:
174 | cursor = conn.cursor()
175 | cursor.execute(query)
176 | return "DDL语句执行成功"
177 |
178 | return await asyncio.to_thread(db_operation, execsql)
179 | except oracledb.DatabaseError as e:
180 | print('Error occurred:', e)
181 | return str(e)
182 |
183 | async def exec_pro_sql(execsql: str) -> str:
184 | try:
185 | # Run database operations in a separate thread
186 | def db_operation(query):
187 | with oracledb.connect(connection_string) as conn:
188 | cursor = conn.cursor()
189 | # 执行PL/SQL代码块
190 | cursor.execute(query)
191 | # 如果有输出参数或返回值，尝试获取
192 | try:
193 | result = cursor.fetchall()
194 | if result:
195 | # 将结果格式化为字符串
196 | return '\n'.join(','.join(str(col) if col is not None else 'NULL' for col in row) for row in result)
197 | except oracledb.DatabaseError:
198 | # 如果没有结果集，说明是存储过程或无返回值的PL/SQL块
199 | pass
200 | # 提交事务
201 | conn.commit()
202 | return "PL/SQL代码块执行成功"
203 |
204 | return await asyncio.to_thread(db_operation, execsql)
205 | except oracledb.DatabaseError as e:
206 | print('Error occurred:', e)
207 | return str(e)
208 | except oracledb.DatabaseError as e:
209 | print('Error occurred:', e)
210 | return str(e)
211 |
212 | if __name__ == "__main__":
213 | # Create and run the async event loop
214 | async def main():
215 | # print(await list_tables())
216 | print(await describe_table('CONCAT'))
217 |
218 | asyncio.run(main())
219 |
```