Cursor Variable inside Another Cursor . CX_ORacle

[paulo79]

Hi all,

I have 3 cursors, want to pass values from first and second on third cursor query as below.

import cx_Oracle as oracledb
conn = oracledb.connect("/", mode=oracledb.SYSASM)
cursor_dgnames = conn.cursor()
cursor_dgnames.execute("select distinct name from myview1")
for v_dgnames in cursor_dgnames:
    print(str(v_dgnames[0]))
    v_dgname=str(v_dgnames[0])
    cursor_fgnmaes = conn.cursor()
    cursor_fgnmaes.execute("select distinct fg from myview2 where TYPE<>'XYZ'")
    for v_fgnames in cursor_fgnmaes:
        print(str(v_fgnames[0]))
        v_fgname=str(v_fgnames[0])
        cursormain = conn.cursor()
        cursormain.execute("select d.name from myview3 d, myview4 dg where d.N = dg.N and dg.name=%s and d.F=%s")(v_dgnames,v_fgnames)

I tried different ways but none worked. Anyone could help on this please?

[ibreeden]

I tried different ways but none worked

It is always difficult to judge what exactly did not work. Is the result not what you expected? Did you get an error message?

This does not seem to be correct:

cursormain.execute("select d.name from myview3 d, myview4 dg where d.N = dg.N and dg.name=%s and d.F=%s")(v_dgnames,v_fgnames)

Placehoders in cx_Oracle are a colon and an identifier or a number, as far as I know. And the values should be supplied as a list or a dictionary. Like this:

cursormain.execute("select d.name from myview3 d, myview4 dg where d.N = dg.N and dg.name=:dg and d.F=:df", [v_dgnames,v_fgnames])

Please let me know if this helps.

For reasons of efficiency it would be better to create the three cursors before the for loop. And also note cursors should be closed after use.