0

I am currently getting a couple errors with my insert using cx_Oracle for python 3.6

ORA-01036: illegal variable name/number

and

ORA-01745: invalid host/bind variable name

Here is my code:

    def load_wp_sql(self, wp_dict):
        self.db.begin()
        cursor = self.db.cursor()
        sql = """INSERT INTO work_package_work_table
                 (CONTROL_ACCOUNT, CONTROL_MANAGER, WP_MANAGER,
                  TITLE, WORK_STATEMENT, TYPE,
                  RISK_MITIGATION_SCOPE)
                 VALUES
                 (:control_account, :cam, :wp_manager,
                  :wp_title, :statement_of_work,
                  :wp_type, risk_mitigation_scope)
              """ 
        try:
            for wp in wp_dict:
                cursor.prepare(sql)
                cursor.execute(None, wp)
            self.db.commit()
        except cx_Oracle.DatabaseError as e:
            self.db.rollback()

This is the dictionary I am trying to bind, it is passed into the function as a generator object.

{
  'cam': 'CA Manager', 
  'control_account': 'W80.11.11.01.LL', 
  'risk_mitigation_scope': 'No', 
  'statement_of_work': 'For setup and maintainance of of the W80 EVMS Tools', 
  'wp_manager': 'cohagan1', 
  'wp_title': 'EVMS Tools', 
  'wp_type': 'Discrete'
}

I've looked a quite a few stackoverflow posts: Python cx_Oracle bind variables

DatabaseError: ORA-01036: illegal variable name/number

As well as their documentation:https://www.oracle.com/technetwork/articles/dsl/prez-python-queries-101587.html

I'm pretty sure I am just missing something simple but can't seem to find it.

3
  • I would check what is the actual command (string) you try to execute, I have the feeling you are not building the string you think you do. Commented Apr 29, 2019 at 17:16
  • 2
    You seem to be missing a : on the risk_mitigation_scope variable. Commented Apr 29, 2019 at 17:18
  • @Blorgbeard yes that was it, thank you Commented Apr 29, 2019 at 17:21

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.