cxOracle: ORA-01036: illegal variable name/number

Question

I have problem with simple variable binding in my code:

conn_str = (f"oracle+cx_oracle://{self.params.user}:{urllib.parse.quote_plus(self.params.password)}"
            f"@{self.params.host}:{self.params.port}/?service_name={self.params.service}")

self.engine = create_engine(conn_str)

with self.engine.connect() as conn:
    s = text("""TRUNCATE TABLE :t1""")
    conn.execute(s, t1=table_name)

Error:

sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-01036: illegal variable    name/number
[SQL: TRUNCATE TABLE :t1]
[parameters: {'t1': 'hss_prod'}]
(Background on this error at: https://sqlalche.me/e/14/4xp6)

I've revieved documentation:

• https://cx-oracle.readthedocs.io/en/latest/user_guide/bind.html

and simmilar issues:

• https://github.com/oracle/python-cx_Oracle/issues/433
• DatabaseError: ORA-01036: illegal variable name/number

and can't get my head around it.

Answer 1

Oracle does not allow bind variables in DDL statements. This is mentioned in the linked cx_Oracle documentation on binding and reads as follows:

Bind variables also cannot be used in Data Definition Language (DDL) 
statements, such as CREATE TABLE or ALTER statements.

And yes, a truncate table statement is a DDL statement! So your only option is to do something like this:

sql = f"truncate table {table_name}"
cursor.execute(sql)

If the value for table_name comes from an unknown source, make sure it is validated in order to avoid SQL injection!