SQL Agent is unable to recognize the tables and its schema when connected to a Oracle DB.

madhubandru commented 9 months ago

Checked other resources

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

Example Code


###Oracle DB Connection - Enable for Oracle DB
DIALECT = 'oracle'
SQL_DRIVER = 'oracledb'
oracle_conn_str=DIALECT + '+' + SQL_DRIVER + '://' + USER_ID + ':' + PASSWORD +'@' + HOST_NAME + ':' + str(PORT) + '/?service_name=' + SERVICE_NAME 
db_engine = create_engine(oracle_conn_str)
db = SQLDatabase(db_engine)

###Agent Code
sql_toolkit = SQLDatabaseToolkit(db=self.db, llm=llm)
sql_toolkit.get_tools()

self.agent = create_sql_agent(
  llm=llm,
  toolkit=sql_toolkit,
  agent_type=AgentType.ZERO_SHOT_REACT_DESCRIPTION,
  verbose=True,
  prefix=zero_prompt,
  extra_tools=[llm_tool],
  handle_parsing_errors=True,
  agent_executor_kwargs= {
    "return_intermediate_steps" : True
  }
)```

### Description

When using the SQL agent on Azure SQL Database, I provide the Database name in the connection string. This allows the SQL agent to successfully retrieve the table names and their schema.

However, when working with the SQL agent on Oracle Database, I provide the UserID, Password, Server name, Host, and Port in the connection string (provided code in example). The connection is established successfully, but the SQL agent encounters an issue in recognizing the tables. I suspect that the problem may arise from my tables being located under a specific schema (let's call it ABC_SCHEMA).

Upon investigation, it seems that when the SQL agent enters the executor chain with the "**Action: sql_db_list_tables**," it fails to list the tables under ABC_SCHEMA. As a result, an error is generated, specifically: **ValueError: An output parsing error occurred. In order to pass this error back to the agent and have it try again, pass handle_parse LLM output: Observation needed.**

In simple terms, the SQL agent is facing difficulty in identifying tables under a particular schema in Oracle Database, leading to the mentioned error. Can someone please help to fix this issue.

### System Info

langchain==0.0.348
oracledb==2.0.1
SQLAlchemy==2.0.25

### Related Components

- [ ] LLMs/Chat Models
- [ ] Embedding Models
- [ ] Prompts / Prompt Templates / Prompt Selectors
- [ ] Output Parsers
- [ ] Document Loaders
- [ ] Vector Stores / Retrievers
- [ ] Memory
- [X] Agents / Agent Executors
- [X] Tools / Toolkits
- [ ] Chains
- [ ] Callbacks/Tracing
- [ ] Async

dosubot[bot] commented 9 months ago

🤖

Hey @madhubandru, good to see you here again! I hope you've been doing well. Let's dive into this issue you're experiencing.

Based on the information you've provided, it seems like the issue is related to how the SQL agent is retrieving the table names and their schema from the Oracle Database. The SQL agent uses the ListSQLDatabaseTool and InfoSQLDatabaseTool classes to retrieve this information.

The ListSQLDatabaseTool is used to list all the tables in the database, and the InfoSQLDatabaseTool is used to get the schema and sample rows for the tables. The input to the InfoSQLDatabaseTool is a comma-separated list of tables.

Here is the relevant code:

def get_tools(self) -> List[BaseTool]:
    """Get the tools in the toolkit."""
    list_sql_database_tool = ListSQLDatabaseTool(db=self.db)
    info_sql_database_tool_description = (
        "Input to this tool is a comma-separated list of tables, output is the "
        "schema and sample rows for those tables. "
        "Be sure that the tables actually exist by calling "
        f"{list_sql_database_tool.name} first! "
        "Example Input: table1, table2, table3"
    )
    info_sql_database_tool = InfoSQLDatabaseTool(
        db=self.db, description=info_sql_database_tool_description
    )
    ...
    return [
        query_sql_database_tool,
        info_sql_database_tool,
        list_sql_database_tool,
        query_sql_checker_tool,
    ]

In this code, ListSQLDatabaseTool(db=self.db) is used to create a tool for listing the tables in the database, and InfoSQLDatabaseTool(db=self.db, description=info_sql_database_tool_description) is used to create a tool for retrieving the schema and sample rows for the tables. The description for InfoSQLDatabaseTool suggests that the tables should exist in the database, which can be ensured by calling ListSQLDatabaseTool first.

It's possible that the ListSQLDatabaseTool and InfoSQLDatabaseTool classes are not able to correctly retrieve the table names and their schema from the Oracle Database due to the specific schema (ABC_SCHEMA) that your tables are located under.

One potential solution could be to modify the connection string to include the schema name. In Oracle, you can specify the schema in the connection string by appending it to the service name like this: SERVICE_NAME.SCHEMA_NAME.

Here is an example:

oracle_conn_str=DIALECT + '+' + SQL_DRIVER + '://' + USER_ID + ':' + PASSWORD +'@' + HOST_NAME + ':' + str(PORT) + '/?service_name=' + SERVICE_NAME + '.' + SCHEMA_NAME

Please try this and let me know if it resolves the issue. If not, we may need to investigate further.

Sources

libs/community/langchain_community/agent_toolkits/sql/toolkit.py

This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

If you want to continue the conversation, start your reply with @dosu-bot.

madhubandru commented 9 months ago

I tried to add schema in the connection string as suggested above but facing the below error.

sqlalchemy.exc.OperationalError: (oracledb.exceptions.OperationalError) DPY-6005: cannot connect to database (CONNECTION_ID=1MH+Ppcxxxx==). DPY-6001: Service "SERVICE_NAME.ABC_SCHEMA" is not registered with the listener at host "HOST_NAME" port PORT_NUM. (Similar to ORA-12514)

(Background on this error at: https://sqlalche.me/e/20/e3q8)

Please Note: Service name, host and port are masked.

langchain-ai / langchain