SQLAlchemy Engine Fails to Fetch Records from Specific Oracle EBS Table but Talend Open Studio Succeeds

Question

I'm facing an issue with a specific scenario involving an Oracle EBS database. I'm attempting to fetch records from a table named ap_lookup_codes using SQLAlchemy in Python. However, the SQLAlchemy engine fails to retrieve any records from this table and produiung no error, despite successfully fetching records from other tables in the same database.

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# Replace the connection string with your actual database connection details
engine = create_engine('oracle+cx_oracle://user:password@host:port/dbname')
Session = sessionmaker(bind=engine)
session = Session()
    
results = session.execute("SELECT * FROM ap_lookup_codes").fetchall()
print(results)

Interestingly, when I execute a similar data fetching operation in Talend Open Studio, it successfully retrieves the records from the ap_lookup_codes table. This discrepancy suggests that the issue might be specific to how SQLAlchemy interacts with this table.

What could be the possible reasons for SQLAlchemy's failure to fetch records from the ap_lookup_codes table, especially considering that Talend Open Studio can fetch the records successfully? Are there any known limitations or configurations in SQLAlchemy when dealing with Oracle EBS databases that might cause this behavior?

Answer 1

UnCOMMITted data is only visible within the session that created it (and will ROLLBACK at the end of the session if it has not been COMMITted). If you can't see the data from another session (i.e. in SQLAlchemy) then make sure you have issued a COMMIT command in the SQL client where you INSERTed the data (i.e. Talend Open Studio).

Note: even if you connect as the same user, this will create a separate session and you will not be able to see the uncommitted data in the other session.

From the COMMIT documentation:

Until you commit a transaction:

• You can see any changes you have made during the transaction by querying the modified tables, but other users cannot see the changes. After you commit the transaction, the changes are visible to other users' statements that execute after the commit.
• You can roll back (undo) any changes made during the transaction with the ROLLBACK statement (see ROLLBACK).

---

If the data is committed then:

1. Make sure you are connecting to the correct database instance.

2. Make sure you are connecting to the correct user.

3. Make sure that the user you are connecting to has permissions to see the data if they are not the owner.

4. Make sure that you are querying the correct table.

   Note: Oracle is case-sensitive but, in many cases, will abstract that away from the user by converting unquoted identifiers to upper-case so:

   SELECT * FROM ap_lookup_codes
   

   is identical to any of:

   SELECT * FROM AP_LOOKUP_CODES
   SELECT * FROM Ap_Lookup_Codes
   SELECT * FROM Ap_LoOkUp_CoDeS
   SELECT * FROM "AP_LOOKUP_CODES"
   

   but:

   SELECT * FROM "ap_lookup_codes"
   

   would be querying a different table. If your table name is actually lower-case then you will need to use a quoted identifier. You may find that you have two tables one is lower-case and contains data and one is the same name but upper-case and contains no data (and that would one reason why using quoted identifiers is not considered good practice as you can get into the situation where you have multiple tables differing only be the case used in their names and it causes confusion).

Answer 2

while reading data from oracle db using sqlalchemy NLS_LANG was creating the issue. please note this is on level configurations, we can edit in regedit

SQL> select * from NLS_DATABASE_PARAMETERS;
Output: NLS_CHARACTERSET    US7ASCII

then i configure in python db connection manager

`os.environ["NLS_LANG"] = "AMERICAN_AMERICA.US7ASCII"`