7.1.Binding by Name or Position

Binding can be done “by name” or “by position”.

cursor.execute("""        insert into departments (department_id, department_name)        values (:dept_id, :dept_name)""",dept_id=280,dept_name="Facility")

data=dict(dept_id=280,dept_name="Facility")cursor.execute("""        insert into departments (department_id, department_name)        values (:dept_id, :dept_name)""",data)

cursor.execute("""        update departments set department_id = :dept_id + 10        where department_id = :dept_id""",dept_id=280)

cursor.execute("""        insert into departments (department_id, department_name)        values (:1, :2)""",[280,"Facility"])

cursor.execute("""        insert into departments (department_id, department_name)        values (:2, :1)""",[280,"Facility"])

cursor.execute("""        insert into departments (department_id, department_name)        values (:dept_id, :dept_name)""",[280,"Facility"])

cursor.execute("""        insert into departments (department_id, department_name)        values (:1, :2)""",(280,"Facility"))

7.2.Duplicate Bind Variable Placeholders

Binding by name is recommended when bind variableplaceholder names are repeated in statements.

In python-oracledb Thin mode, whenbinding by positionfor SQL statements, the order of the bind values must exactly match the orderof each bind variable placeholder and duplicated names must have their valuesrepeated:

cursor.execute("""        select dname from dept1 where deptno = :1        union all        select dname from dept2 where deptno = :1 = """,[30,30])

In some cases python-oracledb Thick mode may allow non-duplicated values forSQL statements, but this usage is not consistent and is not recommended. Itwill result in an error in python-oracledb Thin mode.

When binding by position for PL/SQL calls in python-oracledb Thin or Thickmodes, the order of the bind values must exactly match the order of eachunique placeholder found in the PL/SQL block and values should not berepeated.

Binding by name does not have these issues.

7.3.Bind Direction

The caller can supply data to the database (IN), the database can return datato the caller (OUT) or the caller can supply initial data to the database andthe database can supply the modified data back to the caller (IN/OUT). This isknown as the bind direction.

The examples shown above have all supplied data to the database and aretherefore classified as IN bind variables. In order to have the database returndata to the caller, a variable must be created. This is done by calling themethodCursor.var(), which identifies the type of data that will befound in that bind variable and its maximum size among other things.

Here is an example showing how to use OUT binds. It calculates the sum of theintegers 8 and 7 and stores the result in an OUT bind variable of type integer:

out_val=cursor.var(int)cursor.execute("""        begin            :out_val := :in_bind_var1 + :in_bind_var2;        end;""",out_val=out_val,in_bind_var1=8,in_bind_var2=7)print(out_val.getvalue())# will print 15

If instead of simply getting data back you wish to supply an initial value tothe database, you can set the variable’s initial value. This example is thesame as the previous one but it sets the initial value first:

in_out_var=cursor.var(int)in_out_var.setvalue(0,25)cursor.execute("""        begin            :in_out_bind_var := :in_out_bind_var + :in_bind_var1 +                    :in_bind_var2;        end;""",in_out_bind_var=in_out_var,in_bind_var1=8,in_bind_var2=7)print(in_out_var.getvalue())# will print 40

When binding data to parameters of PL/SQL procedures that are declared as OUTparameters, it is worth noting that any value that is set in the bind variablewill be ignored. In addition, any parameters declared as IN/OUT that do nothave a value set will start out with a value ofnull.

7.4.Binding Null Values

To insert a NULL into a character column you can bind the Python singletonNone. For example, with the table:

createtabletab(idnumber,valvarchar2(50));

You can use:

cursor.execute("insert into tab (id, val) values (:i, :v)",i=280,v=None)

Python-oracledb assumes the value will be a string (equivalent to a VARCHAR2column). If you need to use a different Oracle Database data type you will needto make a call toCursor.setinputsizes() or create a bind variable withthe correct type by callingCursor.var().

For example, if the table had been created using anOracle SpatialSDO_GEOMETRY object column:

createtabletab(idnumber,valsdo_geometry);

Then the previous code would fail with:

ORA-00932:expressionisofdatatypeCHAR,whichisincompatiblewithexpecteddatatypeMDSYS.SDO_GEOMETRY

To insert a NULL into the new table, use:

type_obj=connection.gettype("SDO_GEOMETRY")var=cursor.var(type_obj)cursor.execute("insert into tab (id, val) values (:i, :v)",i=280,v=var)

Alternatively use:

type_obj=connection.gettype("SDO_GEOMETRY")cursor.setinputsizes(i=None,v=type_obj)cursor.execute("insert into tab (id, val) values (:i, :v)",i=280,v=None)

7.5.Binding ROWID Values

The pseudo-column ROWID uniquely identifies a row in a table. Inpython-oracledb, ROWID values are represented as strings. The example below showsfetching a row and then updating that row by binding its rowid:

# fetch the rowcursor.execute("""        select rowid, manager_id        from departments        where department_id = :dept_id""",dept_id=280)rowid,manager_id=cursor.fetchone()# update the row by binding ROWIDcursor.execute("""        update departments set            manager_id = :manager_id        where rowid = :rid""",manager_id=205,rid=rowid)

7.6.Binding UROWID Values

Universal rowids (UROWID) are used to uniquely identify rows in indexorganized tables. In python-oracledb, UROWID values are represented as strings.The example below shows fetching a row from index organized tableuniversal_rowids and then updating that row by binding its urowid:

CREATETABLEuniversal_rowids(int_colnumber(9)notnull,str_colvarchar2(250)notnull,date_coldatenotnull,CONSTRAINTuniversal_rowids_pkPRIMARYKEY(int_col,str_col,date_col))ORGANIZATIONINDEX

ridvar=cursor.var(oracledb.DB_TYPE_UROWID)# fetch the rowcursor.execute("""        begin            select rowid into :rid from universal_rowids            where int_col = 3;        end;""",rid=ridvar)# update the row by binding UROWIDcursor.execute("""        update universal_rowids set            str_col = :str_val        where rowid = :rowid_val""",str_val="String #33",rowid_val=ridvar)

Note that the typeoracledb.DB_TYPE_UROWID is only supported inpython-oracledb Thin mode. For python-oracledb Thick mode, the database typeUROWID can be bound with typeoracledb.DB_TYPE_ROWID.SeeQuery Metadata in Thin and Thick Modes.

7.7.DML RETURNING Bind Variables

When a RETURNING clause is used with a DML statement like UPDATE,INSERT, or DELETE, the values are returned to the application throughthe use of OUT bind variables. Consider the following example:

# The RETURNING INTO bind variable is a stringdept_name=cursor.var(str)cursor.execute("""        update departments set            location_id = :loc_id        where department_id = :dept_id        returning department_name into :dept_name""",loc_id=1700,dept_id=50,dept_name=dept_name)print(dept_name.getvalue())# will print ['Shipping']

In the above example, since the WHERE clause matches only one row, the outputcontains a single item in the list. If the WHERE clause matched multiple rows,the output would contain as many items as there were rows that were updated.

The same bind variable placeholder name cannot be used both before and afterthe RETURNING clause. For example, if the:dept_name bind variable is usedboth before and after the RETURNING clause:

# a variable cannot be used for both input and output in a DML returning# statementdept_name_var=cursor.var(str)dept_name_var.setvalue(0,'Input Department')cursor.execute("""        update departments set            department_name = :dept_name || ' EXTRA TEXT'        returning department_name into :dept_name""",dept_name=dept_name_var)

The above example will not update the bind variable as expected, but no errorwill be raised if you are using python-oracledb Thick mode. Withpython-oracledb Thin mode, the above example returns the following error:

DPY-2048:thebindvariableplaceholder":dept_name"cannotbeusedbothbeforeandaftertheRETURNINGclauseinaDMLRETURNINGstatement

7.8.LOB Bind Variables

Database CLOBs, NCLOBS, BLOBs, and BFILEs can be bound with typesoracledb.DB_TYPE_CLOB,oracledb.DB_TYPE_NCLOB,oracledb.DB_TYPE_BLOB andoracledb.DB_TYPE_BFILErespectively. LOBs fetched from the database or created withConnection.createlob() can also be bound.

LOBs may represent Oracle Database persistent LOBs (those stored in tables) ortemporary LOBs (such as those created withConnection.createlob() orreturned by some SQL and PL/SQL operations).

LOBs can be used as IN, OUT, or IN/OUT bind variables.

SeeUsing CLOB, BLOB, NCLOB, and BFILE Data for examples.

7.9.REF CURSOR Bind Variables

Python-oracledb provides the ability to bind and define PL/SQL REF cursors. As anexample, consider the PL/SQL procedure:

CREATEORREPLACEPROCEDUREfind_employees(p_queryINVARCHAR2,p_resultsOUTSYS_REFCURSOR)ASBEGINOPENp_resultsFORSELECTemployee_id,first_name,last_nameFROMemployeesWHEREUPPER(first_name||' '||last_name||' '||email)LIKE'%'||UPPER(p_query)||'%';END;/

A newly opened cursor can be bound to the REF CURSOR parameter as shown in thefollowing Python code. After the PL/SQL procedure has been called withCursor.callproc(), the cursor can then be fetched just like any othercursor which had executed a SQL query:

ref_cursor=connection.cursor()cursor.callproc("find_employees",['Smith',ref_cursor])forrowinref_cursor:print(row)

With Oracle’ssample HR schema there are twoemployees with the last name ‘Smith’ so the result is:

(159,'Lindsey','Smith')(171,'William','Smith')

To return a REF CURSOR from a PL/SQL function, useoracledb.DB_TYPE_CURSOR for thereturn type ofCursor.callfunc():

ref_cursor=cursor.callfunc('example_package.f_get_cursor',oracledb.DB_TYPE_CURSOR)forrowinref_cursor:print(row)

SeeTuning python-oracledb for information on how to tune REF CURSORS.

Also seeImplicit Results which provides analternative way to return query results from PL/SQL procedures.

7.10.Binding PL/SQL Collections

PL/SQL Collections like Associative Arrays can be bound as IN, OUT, and IN/OUTvariables. When binding IN values, an array can be passed directly as shown inthis example, which sums up the lengths of all of the strings in the providedarray. First the PL/SQL package definition:

createorreplacepackagemypkgastypeudt_StringLististableofvarchar2(100)indexbybinary_integer;functionDemoCollectionIn(a_Valuesudt_StringList)returnnumber;end;/createorreplacepackagebodymypkgasfunctionDemoCollectionIn(a_Valuesudt_StringList)returnnumberist_ReturnValuenumber:=0;beginforiin1..a_Values.countloopt_ReturnValue:=t_ReturnValue+length(a_Values(i));endloop;returnt_ReturnValue;end;end;/

Then the Python code:

values=["String One","String Two","String Three"]return_val=cursor.callfunc("mypkg.DemoCollectionIn",int,[values])print(return_val)# will print 32

In order get values back from the database, a bind variable must be createdusingCursor.arrayvar(). The first parameter to this method is a Pythontype that python-oracledb knows how to handle or one of the oracledbDB API Types.The second parameter is the maximum number of elements that the array can holdor an array providing the value (and indirectly the maximum length). The finalparameter is optional and only used for strings and bytes. It identifies themaximum length of the strings and bytes that can be stored in the array. If notspecified, the length defaults to 4000 bytes.

Consider the following PL/SQL package:

createorreplacepackagemypkgastypeudt_StringLististableofvarchar2(100)indexbybinary_integer;procedureDemoCollectionOut(a_NumElementsnumber,a_Valuesoutnocopyudt_StringList);procedureDemoCollectionInOut(a_Valuesinoutnocopyudt_StringList);end;/createorreplacepackagebodymypkgasprocedureDemoCollectionOut(a_NumElementsnumber,a_Valuesoutnocopyudt_StringList)isbeginforiin1..a_NumElementsloopa_Values(i):='Demo out element #'||to_char(i);endloop;end;procedureDemoCollectionInOut(a_Valuesinoutnocopyudt_StringList)isbeginforiin1..a_Values.countloopa_Values(i):='Converted element #'||to_char(i)||' originally had length '||length(a_Values(i));endloop;end;end;/

The Python code to process an OUT collection will be as follows. Note thecall toCursor.arrayvar() which creates space for an array of strings.Each string permits up to 100 bytes and only 10 strings are permitted. If thePL/SQL block exceeds the maximum number of strings allowed the errorORA-06513:PL/SQL:indexforPL/SQLtableoutofrangeforhostlanguagearray will be raised.

out_array_var=cursor.arrayvar(str,10,100)cursor.callproc("mypkg.DemoCollectionOut",[5,out_array_var])forvalinout_array_var.getvalue():print(val)

This would produce the following output:

Demooutelement#1Demooutelement#2Demooutelement#3Demooutelement#4Demooutelement#5

The Python code to process IN/OUT collections is similar. Note the differentcall toCursor.arrayvar() which creates space for an array of strings,but uses an array to determine both the maximum length of the array and itsinitial value.

in_values=["String One","String Two","String Three","String Four"]in_out_array_var=cursor.arrayvar(str,in_values)cursor.callproc("mypkg.DemoCollectionInOut",[in_out_array_var])forvalinin_out_array_var.getvalue():print(val)

This will produce the following output:

Convertedelement#1 originally had length 10Convertedelement#2 originally had length 10Convertedelement#3 originally had length 12Convertedelement#4 originally had length 11

If an array variable needs to have an initial value but also needs to allowfor more elements than the initial value contains, the following code can beused instead:

in_out_array_var=cursor.arrayvar(str,10,100)in_out_array_var.setvalue(0,["String One","String Two"])

All of the collections that have been bound in preceding examples have usedcontiguous array elements. If an associative array with sparse array elementsis needed, a different approach is required. Consider the following PL/SQLcode:

createorreplacepackagemypkgastypeudt_StringLististableofvarchar2(100)indexbybinary_integer;procedureDemoCollectionOut(a_Valueoutnocopyudt_StringList);end;/createorreplacepackagebodymypkgasprocedureDemoCollectionOut(a_Valueoutnocopyudt_StringList)isbegina_Value(-1048576):='First element';a_Value(-576):='Second element';a_Value(284):='Third element';a_Value(8388608):='Fourth element';end;end;/

Note that the collection element indices are separated by large values. Thetechnique used above would fail with the exceptionORA-06513:PL/SQL:indexforPL/SQLtableoutofrangeforhostlanguagearray. The code required toprocess this collection looks like this instead:

collection_type=connection.gettype("MYPKG.UDT_STRINGLIST")collection=collection_type.newobject()cursor.callproc("mypkg.DemoCollectionOut",[collection])print(collection.aslist())

This produces the output:

['First element','Second element','Third element','Fourth element']

Note the use ofObject.aslist() which returns the collection elementvalues in index order as a simple Python list. The indices themselves are lostin this approach. The associative array can be turned into a Python dictionaryusingObject.asdict(). If that value was printed in the previousexample instead, the output would be:

{-1048576:'First element',-576:'Second element',284:'Third element',8388608:'Fourth element'}

If the elements need to be traversed in index order, the methodsObject.first() andObject.next() can be used. The methodObject.getelement() can be used to acquire the element at a particularindex. This is shown in the following code:

ix=collection.first()whileixisnotNone:print(ix,"->",collection.getelement(ix))ix=collection.next(ix)

This produces the output:

-1048576->Firstelement-576->Secondelement284->Thirdelement8388608->Fourthelement

Similarly, the elements can be traversed in reverse index order using themethodsObject.last() andObject.prev() as shown in thefollowing code:

ix=collection.last()whileixisnotNone:print(ix,"->",collection.getelement(ix))ix=collection.prev(ix)

This produces the output:

8388608->Fourthelement284->Thirdelement-576->Secondelement-1048576->Firstelement

7.11.Binding PL/SQL Records

PL/SQL record type objects can also be bound for IN, OUT, and IN/OUTbind variables. For example:

createorreplacepackagemypkgastypeudt_DemoRecordisrecord(NumberValuenumber,StringValuevarchar2(30),DateValuedate,BooleanValueboolean);procedureDemoRecordsInOut(a_Valueinoutnocopyudt_DemoRecord);end;/createorreplacepackagebodymypkgasprocedureDemoRecordsInOut(a_Valueinoutnocopyudt_DemoRecord)isbegina_Value.NumberValue:=a_Value.NumberValue*2;a_Value.StringValue:=a_Value.StringValue||' (Modified)';a_Value.DateValue:=a_Value.DateValue+5;a_Value.BooleanValue:=nota_Value.BooleanValue;end;end;/

Then this Python code can be used to call the stored procedure which willupdate the record:

# create and populate a recordrecord_type=connection.gettype("MYPKG.UDT_DEMORECORD")record=record_type.newobject()record.NUMBERVALUE=6record.STRINGVALUE="Test String"record.DATEVALUE=datetime.datetime(2016,5,28)record.BOOLEANVALUE=False# show the original valuesprint("NUMBERVALUE ->",record.NUMBERVALUE)print("STRINGVALUE ->",record.STRINGVALUE)print("DATEVALUE ->",record.DATEVALUE)print("BOOLEANVALUE ->",record.BOOLEANVALUE)print()# call the stored procedure which will modify the recordcursor.callproc("mypkg.DemoRecordsInOut",[record])# show the modified valuesprint("NUMBERVALUE ->",record.NUMBERVALUE)print("STRINGVALUE ->",record.STRINGVALUE)print("DATEVALUE ->",record.DATEVALUE)print("BOOLEANVALUE ->",record.BOOLEANVALUE)

This will produce the following output:

NUMBERVALUE->6STRINGVALUE->TestStringDATEVALUE->2016-05-2800:00:00BOOLEANVALUE->FalseNUMBERVALUE->12STRINGVALUE->TestString(Modified)DATEVALUE->2016-06-0200:00:00BOOLEANVALUE->True

Note that when manipulating records, all of the attributes must be set by thePython program in order to avoid an Oracle Client bug which will result inunexpected values or the Python application segfaulting.

7.12.Binding Spatial Data Types

Oracle Spatial data types objects can be represented by Python objects and theirattribute values can be read and updated. The objects can further be bound andcommitted to database. This is similar to the examples above.

An example of fetching SDO_GEOMETRY is inOracle Database Objects andCollections.

7.13.Reducing the SQL Version Count

When repeated calls toCursor.execute() orCursor.executemany()bind different string data lengths, then usingCursor.setinputsizes()can help reduce Oracle Database’s SQL “version count” for thestatement. The version count is the number of child cursors used for the samestatement text. The database will have a parent cursor representing the text ofa statement, and a number of child cursors for differing executions of thestatement, for example when different bind variable types or lengths are used.

For example, with a table created as:

createtablemytab(c1varchar2(25),c2varchar2(100),c3number);

You can usesetinputsizes() to help reduce the number of childcursors:

sql="insert into mytab (c1, c2) values (:1, :2)"cursor.setinputsizes(25,15)s1="abc"s2="def"cursor.execute(sql,[s1,s2])s1="aaaaaaaaaaaaaaaaaaaaaaaaa"s2="z"cursor.execute(sql,[s1,s2])

Thesetinputsizes() call indicates that the first value boundwill be a Python string of no more than 25 characters and the second valuebound will be a string of no more than 15 characters. If the data stringlengths exceed thesetinputsizes() values, then python-oracledbwill accept them but there will be no processing benefit.

It is not uncommon for SQL statements to have low hundreds ofversions. Sometimes this is expected and not a result of any issue. Todetermine the reason, find the SQL identifier of the statement and then querythe Oracle Database viewV$SQL_SHARED_CURSOR.

The SQL identifier of a statement can be found in Oracle Database views likeV$SQLAREA after you have run a statement, or youcan find itbefore you execute the statement by using theDBMS_SQL_TRANSLATOR.SQL_ID() function. Make sureto pass in exactly the same SQL text, including the same whitespace:

sql="insert into mytab (c1, c2) values (:1, :2)"# statement to examinecursor.execute("select dbms_sql_translator.sql_id(:1) from dual",[sql])(sqlid,)=cursor.fetchone();print(sqlid)

This might print a value like:

6h6gj3ztw2wd8

Then, to find the SQL versions, run a query to see the child cursors. Forexample:

cursor.execute("""select child_number, reason                  from v$sql_shared_cursor                  where sql_id = :1 order by 1""",[sqlid])col_names=[c.nameforcincursor.description]forrowincursor.fetchall():r=[dict(zip(col_names,row))]print(r)

With the earlier code that usedsetinputsizes() and inserteddifferent data lengths you might see:

[{'CHILD_NUMBER':0,'REASON':' '}]

However ifsetinputsizes() had not been used, you would see tworows and REASON would include the text “Bind mismatch”, for example:

[{'CHILD_NUMBER':0,'REASON':'<ChildNode><ChildNumber>0</ChildNumber><ID>39</ID><reason>Bind mismatch(22)</reason><size>4x8</size><bind_position>0</bind_position><original_oacflg>1</original_oacflg><original_oacmxl>32</original_oacmxl><upgradeable_new_oacmxl>128</upgradeable_new_oacmxl></ChildNode> '}][{'CHILD_NUMBER':1,'REASON':'<ChildNode><ChildNumber>1</ChildNumber><ID>39</ID><reason>Bind mismatch(22)</reason><size>4x8</size><bind_position>0</bind_position><original_oacflg>1</original_oacflg><original_oacmxl>128</original_oacmxl><upgradeable_new_oacmxl>32</upgradeable_new_oacmxl></ChildNode> '}]

7.14.Changing Bind Data Types using an Input Type Handler

Input Type Handlers allow applications to change how data is bound tostatements, or even to enable new types to be bound directly.

An input type handler is enabled by setting the attributeCursor.inputtypehandler orConnection.inputtypehandler.

Inserting NaN values as NULL in NUMBER columns

To insert NaN values as NULLs in a NUMBER column, use an input type handlerwith an inconverter:

definput_type_handler(cursor,value,arraysize):ifisinstance(value,float):returncursor.var(oracledb.DB_TYPE_NUMBER,arraysize=arraysize,inconverter=lambdax:Noneifmath.isnan(x)elsex)connection.inputtypehandler=input_type_handler

Note that this is not needed for BINARY_FLOAT or BINARY_DOUBLE columns.

Binding Python Objects

Input type handlers can be combined with variable converters to bind Pythonobjects seamlessly:

# A standard Python objectclassBuilding:def__init__(self,build_id,description,num_floors,date_built):self.building_id=build_idself.description=descriptionself.num_floors=num_floorsself.date_built=date_builtbuilding=Building(1,"Skyscraper 1",5,datetime.date(2001,5,24))# Get Python representation of the Oracle user defined type UDT_BUILDINGobj_type=con.gettype("UDT_BUILDING")# convert a Python Building object to the Oracle user defined type# UDT_BUILDINGdefbuilding_in_converter(value):obj=obj_type.newobject()obj.BUILDINGID=value.building_idobj.DESCRIPTION=value.descriptionobj.NUMFLOORS=value.num_floorsobj.DATEBUILT=value.date_builtreturnobjdefinput_type_handler(cursor,value,num_elements):ifisinstance(value,Building):returncursor.var(obj_type,arraysize=num_elements,inconverter=building_in_converter)# With the input type handler, the bound Python object is converted# to the required Oracle object before being insertedcur.inputtypehandler=input_type_handlercur.execute("insert into myTable values (:1, :2)",(1,building))

7.15.Binding Multiple Values to a SQL WHERE IN Clause

To use a SQL IN list with multiple values, use one bind variable placeholderper value. You cannot directly bind a Python list or dictionary to a singlebind variable. For example, to use two values in an IN clause your code shouldbe like:

items=["Smith","Taylor"]cursor.execute("""    select employee_id, first_name, last_name    from employees    where last_name in (:1, :2)""",items)forrowincursor:print(row)

This gives the output:

(159,'Lindsey','Smith')(171,'William','Smith')(176,'Jonathon','Taylor')(180,'Winston','Taylor')

If the query is executed multiple times with differing numbers of values, abind variable placeholder should be included in the SQL statement for each ofthe maximum possible number of values. If the statement is executed with alesser number of data values, then bindNone for missing values. Forexample, if a query is used for up to five values, but only two values are usedin a particular execution, the code could be:

items=["Smith","Taylor",None,None,None]cursor.execute("""    select employee_id, first_name, last_name    from employees    where last_name in (:1, :2, :3, :4, :5)""",items)forrowincursor:print(row)

This will produce the same output as the original example.

Reusing the same SQL statement like this for a variable number of values,instead of constructing a unique statement per set of values, allows best reuseof Oracle Database resources. Additionally, if a statement with a large numberof bind variable placeholders is executed many times with varying stringlengths for each execution, then consider usingCursor.setinputsizes()to reduce Oracle Database’s SQL “version count” forthe statement. For example, if the columns are VARCHAR2(25), then add thisbefore theCursor.execute() call:

cursor.setinputsizes(25,25,25,25,25)

If other bind variables are required in the statement, adjust the bind variableplaceholder numbers appropriately:

binds=[120]# employee idbinds+=["Smith","Taylor",None,None,None]# IN listcursor.execute("""    select employee_id, first_name, last_name    from employees    where employee_id > :1    and last_name in (:2, :3, :4, :5, :6)""",binds)forrowincursor:print(row)

If a statement containing WHERE IN is not going to be re-executed, or thenumber of values is only going to be known at runtime, then a SQL statement canbe dynamically built:

bind_values=["Gates","Marvin","Fay"]bind_names=",".join(":"+str(i+1)foriinrange(len(bind_values)))sql=f"select first_name, last_name from employees where last_name in ({bind_names})"cursor.execute(sql,bind_values)forrowincursor:print(row)

sql="""select . . .         where key in (:0,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,...)            or key in (:50,:51,:52,:53,:54,:55,:56,:57,:58,:59,...)            or key in (:100,:101,:102,:103,:104,:105,:106,:107,:108,:109,...)"""

SELECT...WHEREcolIN(<somethingthatreturnsalistofvalues>)

SQL>CREATEORREPLACETYPEname_arrayASTABLEOFVARCHAR2(25);2/

type_obj=connection.gettype("NAME_ARRAY")obj=type_obj.newobject()obj.extend(["Smith","Taylor"])cursor.execute("""select employee_id, first_name, last_name                  from employees                  where last_name in (select * from table(:1))""",[obj])forrowincursor:print(row)

7.16.Binding Column and Table Names

Table names cannot be bound in SQL queries. You can concatenate text to buildup a SQL statement, but ensure that you use an Allow List or other means tovalidate the data in order to avoid SQL Injection security issues:

table_allow_list=['employees','departments']table_name=get_table_name()#  get the table name from user inputiftable_name.lower()notintable_allow_list:raiseException('Invalid table name')sql=f'select * from{table_name}'

Binding column names can be done either by using a similar method, or by usinga CASE statement. The example below demonstrates binding a column name in anORDER BY clause:

sql="""    select *    from departments    order by        case :bindvar            when 'DEPARTMENT_ID' then                department_id            else                manager_id        end"""col_name=get_column_name()# Obtain a column name from the usercursor.execute(sql,[col_name])

Depending on the name provided by the user, the query results will beordered either by the column DEPARTMENT_ID or the column MANAGER_ID.