I am changing the schema in the main function
db_dwh.cursor.execute("alter session set current_schema = SCHEMA_NAME")
But when I am passing this db_dwh object to a function and trying to execute a query on a table I am getting table not found error, for this again I have to set schema using:
db_dwh.cursor.execute("alter session set current_schema = SCHEMA_NAME")
Is there any way to set schema at only one place globally?
PS The job running in Hadoop environment.
I expect your function has a different connection - I hope you're using a connection pool.
The comment has one solution.
Here are some other tools that are available. They may be useful, depending on your (or other readers) application architecture:
Connection.current_schema instead of an explicit ALTER SESSION. This saves the round-trip overhead of an execute().
Connection Pool callbacks to efficiently set state: https://cx-oracle.readthedocs.io/en/latest/user_guide/connection_handling.html#session-callbacks-for-setting-pooled-connection-state You say you are trying to set the schema globally. If it always has the same value, then I would use this solution in conjunction with the Connection.current_schema attribute.
def init_session(connection, requested_tag):
connection.current_schema = 'ALISON'
# Create the pool with a session callback
pool = cx_Oracle.SessionPool(user="whoever", password=userpwd, dsn="orclpdb1", session_callback=init_session)
# Get a connection from the pool. It will always have the current schema
# set to ALISON
connection = pool.acquire()
. . . # Use the connection