CODEWITHSUNDEEP
pythonoracle-databasecx-oracle
alex998
alex998

Reputation: 1

Oracle SQL Subquery seems to not work when ececuted with cx_Oracle

I have an sql statement that gets executed from a python script using cx_Oracle. This statement includes two subqueries like this:

select distinct
 us.firstname as "Vorname",
 us.lastname as "Nachname",
 us.email as "Email",
 us.loginname as "Q-Nummer",
 us.status,
 us.lastlogintime
from 
 user_table us
where
 us.lastlogintime >= add_months(trunc(sysdate), -7)
 and us.lastlogintime < add_months(trunc(sysdate), -6)
 and not exists (select distinct sp.user_id from specificstatus sp where sp.user_id = us.id)
 and not exists (select distinct ec.owner_id from electriccomponent ec where ec.owner_id = us.id)

I get a different number of results, depending on whether I run this sql statement in my python script or directly in sql developper. It seems the python script does not execute the two subqueries, for when I comment those out in sql developper, the result is identical with the one from the python script. What could be the reason for this?

Edit: the python code is nothing special:

dsn_tns = cx_Oracle.makedsn(cfg.db_hostname, cfg.db_port, sid=cfg.db_servicename)
conn = cx_Oracle.connect(user=cfg.db_username, password=cfg.db_password, dsn=dsn_tns, encoding="UTF-8", nencoding="UTF-8")
conn.autocommit = True
c = conn.cursor()
c.execute(cfg.sql_query)

Upvotes: 0

Views: 300

Answers (1)

Anthony Tuininga
Anthony Tuininga

Reputation: 7096

As mentioned in the comments, you may be connected to a different database or as a different user, or there may be uncommitted data in one or the other environment. You need to verify that -- by executing a query that fetches data that you have just added in SQLDeveloper in a Python script (for example). You can also perform these queries as an initial check in both environments:

select sys_context('userenv', 'service_name') from dual

select sys_context('userenv', 'current_user') from dual

It is highly unlikely that a query executed in one environment will simply "fail" to execute in another environment -- barring a serious bug, of course! The SQL is simply sent to the database without any manipulation.

You can also set the environemnt variable DPI_DEBUG_LEVEL to the value 16 and make sure that the SQL that you are executing is in fact the SQL that you think you are executing!

Upvotes: 1

Related Questions