CX_ORACLE CONNECTION IN PYTHON WITH SELECT QUERY AND CASE OPTON

  case, cx-oracle, python, select

my fetch_sf function (written in python 3.7) raise error

cx_Oracle.DatabaseError: ORA-01008: not all variables bound

but when i reuse this select statement with on case it works fine. i.e

"SELECT C1, C2 FROM MYTABLE WHERE SCODE ="
"(SELECT CASE WHEN TO_NUMBER(TO_CHAR (:1)) = 10 THEN DAY10 END FROM SFDETAIL WHERE ENO = :2 AND RYEAR=:3 AND RMONTH=:4)"

def fetch_sf(p1, p2, p3, p4):
    dsn_tns = cx_Oracle.makedsn('myhost', '1521', service_name='ser')
    connection = cx_Oracle.connect(user='user', password='pas', dsn=dsn_tns)
    cursor = connection.cursor()
    sqlquery = "SELECT C1, C2 FROM MYTABLE WHERE SCODE =" 
               "(SELECT CASE WHEN TO_NUMBER(TO_CHAR (:1)) = 10 THEN DAY10 WHEN TO_NUMBER(TO_CHAR (:1)) = 11 THEN DAY11  END FROM SFDETAIL WHERE ENO = :2 AND RYEAR=:3 AND RMONTH=:4)"
    cursor.execute(sqlquery, (p1, p2, p3, p4))
    connection.close()
    print(res)
    return()
p1 =10
p2 = '0000000277'
p3 = 2021
p4 = 1
sf = fetch_sf(p1, p2, p3, p4)
print(sf)

Source: Python Questions

LEAVE A COMMENT