Issue Querying Oracle Database with Natural Language - langchain SQLDatabaseChain

[dhtruong0407]

Checked other resources

• I added a very descriptive title to this question.
• I searched the LangChain documentation with the integrated search.
• I used the GitHub search to find a similar question and didn't find it.

Commit to Help

• I commit to help with one of those options 👆

Description

I'm trying to use langchain SQLDatabaseChain to interact with my oracle database with natural language (using gemini LLM). I always received the error: sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00911: invalid character while try to execute: db_chain.invoke(input_data) (with input_data as prompt):

import os
import json


from langchain_community.utilities import SQLDatabase
from langchain_google_genai import ChatGoogleGenerativeAI
from langchain_experimental.sql import SQLDatabaseChain


llm = ChatGoogleGenerativeAI(model="gemini-1.5-flash",temperature=0)

# Prepare Database
import cx_Oracle
lib_dir = r"D:\source\instantclient-basic-windows.x64-19.9.0.0.0dbru\instantclient_19_9"
cx_Oracle.init_oracle_client(lib_dir=lib_dir)
database_file_path = "my_oracle_db_path"

db = SQLDatabase.from_uri(f"oracle+cx_oracle://{database_file_path}")

# prompt = pre_prompt.format(question=question)
input_data = {"query": "I need you to answer the following question by querying the database: What was the average daily trading volume of HPG stock in 2022?"}


# Prepare SQLDatabaseChain
db_chain = SQLDatabaseChain.from_llm(llm=llm, db=db, verbose=True)

# Query with Natural Language
result = db_chain.invoke(input_data)

This will return in an error:

> Entering new SQLDatabaseChain chain...
I need you to answer the following question by querying the database: What was the average daily trading volume of HPG stock in 2022?
SQLQuery:```sql
SELECT
  AVG("Volume") AS average_volume
FROM twt_hpg_trading_data
WHERE
  EXTRACT(YEAR FROM "TradingDate") = 2022;
```Traceback (most recent call last):
  File "D:\source\.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "D:\source\.venv\Lib\site-packages\sqlalchemy\engine\default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
cx_Oracle.DatabaseError: ORA-00911: invalid character
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "C:\Users\duc.th4\Downloads\Duc Truong\IDE\PyCharm Community Edition 2024.1.1\plugins\python-ce\helpers\pydev\pydevconsole.py", line 364, in runcode
    coro = func()
           ^^^^^^
  File "<input>", line 1, in <module>
  File "D:\source\.venv\Lib\site-packages\langchain\chains\base.py", line 170, in invoke
    raise e
  File "D:\source\.venv\Lib\site-packages\langchain\chains\base.py", line 160, in invoke
    self._call(inputs, run_manager=run_manager)
  File "D:\source\.venv\Lib\site-packages\langchain_experimental\sql\base.py", line 201, in _call
    raise exc
  File "D:\source\.venv\Lib\site-packages\langchain_experimental\sql\base.py", line 146, in _call
    result = self.database.run(sql_cmd)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "D:\source\.venv\Lib\site-packages\langchain_community\utilities\sql_database.py", line 502, in run
    
    
  File "D:\source\.venv\Lib\site-packages\langchain_community\utilities\sql_database.py", line 467, in _execute
    elif isinstance(command, Executable):
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "D:\source\.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "D:\source\.venv\Lib\site-packages\sqlalchemy\sql\elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "D:\source\.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "D:\source\.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "D:\source\.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "D:\source\.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 2353, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "D:\source\.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "D:\source\.venv\Lib\site-packages\sqlalchemy\engine\default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00911: invalid character
[SQL: ```sql
SELECT
  AVG("Volume") AS average_volume
FROM twt_hpg_trading_data
WHERE
  EXTRACT(YEAR FROM "TradingDate") = 2022;
```]
(Background on this error at: https://sqlalche.me/e/20/4xp6)

This query:

SELECT
  AVG("Volume") AS average_volume
FROM twt_hpg_trading_data
WHERE
  EXTRACT(YEAR FROM "TradingDate") = 2022;

work completely well when I execute with my oracle db client.
Please help me with this problem.

System Info

System Information

OS: Windows
OS Version: 10.0.19045
Python Version: 3.12.2 (tags/v3.12.2:6abddd9, Feb 6 2024, 21:26:36) [MSC v.1937 64 bit (AMD64)]

Package Information

langchain_core: 0.3.29
langchain: 0.3.14
langchain_community: 0.3.14
langsmith: 0.2.10
langchain_experimental: 0.0.49
langchain_google_genai: 2.0.8
langchain_openai: 0.2.14
langchain_text_splitters: 0.3.5

Optional packages not installed

langserve

Other Dependencies

aiohttp: 3.11.11
async-timeout: Installed. No version info available.
dataclasses-json: 0.6.7
faker: Installed. No version info available.
filetype: 1.2.0
google-generativeai: 0.8.3
httpx: 0.28.1
httpx-sse: 0.4.0
jinja2: 3.1.5
jsonpatch: 1.33
langsmith-pyo3: Installed. No version info available.
numpy: 1.26.4
openai: 1.59.5
orjson: 3.10.14
packaging: 23.2
presidio-analyzer: Installed. No version info available.
presidio-anonymizer: Installed. No version info available.
pydantic: 2.10.4
pydantic-settings: 2.7.1
PyYAML: 6.0.2
requests: 2.32.3
requests-toolbelt: 1.0.0
sentence-transformers: Installed. No version info available.
SQLAlchemy: 2.0.30
tenacity: 8.5.0
tiktoken: 0.8.0
typing-extensions: 4.12.2
vowpal-wabbit-next: Installed. No version info available.
zstandard: Installed. No version info available.