Exception binding variables with cx_Oracle in python

Question

Okay, so I'm connected to an oracle database in python 2.7 and cx_Oracle 5.1 compiled against the instant client 11.2. I've got a cursor to the database and running SQL is not an issue, except this:

    cursor.execute('ALTER TRIGGER :schema_trigger_name DISABLE',
                     schema_trigger_name='test.test_trigger')

or

    cursor.prepare('ALTER TRIGGER :schema_trigger_name DISABLE')
    cursor.execute(None,{'schema_trigger_name': 'test.test_trigger'})

both result in an error from oracle:

    Traceback (most recent call last):
      File "connect.py", line 257, in 
        cursor.execute('ALTER TRIGGER :schema_trigger_name DISABLE',
                    schema_trigger_name='test.test_trigger')
    cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number

While running:

    cursor.execute('ALTER TRIGGER test.test_trigger DISABLE')

works perfectly. What's the issue with binding that variable?

Vincent Malgrat · Accepted Answer · 2011-08-24 11:39:47Z

2

In your example test.test_trigger is not a variable but an object. You can only bind variables (that can be replaced by a value).

The query you are trying to run would be logically equivalent to:

ALTER TRIGGER 'test.test_trigger' DISABLE

Binding in this case won't work, you will have to build the query dynamically.

answered Aug 24, 2011 at 11:39

Vincent Malgrat

67.9k9 gold badges122 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ben · Accepted Answer · 2011-08-24 11:40:55Z

0

You normally can't bind an object name in Oracle. For variables it'll work but not for trigger_names, table_names etc.

answered Aug 24, 2011 at 11:40

Ben

53.1k36 gold badges133 silver badges156 bronze badges

Collectives™ on Stack Overflow

Exception binding variables with cx_Oracle in python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related