According to PowerCenter design, when a Pre-SQL is configured in a session, PowerCenter first executes the Pre-SQL command using the source database connection. Then, it opens a new connection for the source and executes the Source Qualifier query.
If the requirement is to use the same source database connection (which is working as expected) and the same Oracle session, then you can use the Environment SQL option in the source relational database connection. The Integration Service runs the connection environment SQL each time it connects to the database. You can configure a SQL which will keep the Oracle session in sync each time it connects to the source database. The necessary SQL command to give in Environment SQL will retain the same Oracle session.
What can we do to improve this information (2000 or fewer characters)