0

Unable to debug the issue for invalid character. The query runs fine on the database editor but not able to run via python. The code is as follows:

            print(finalSql)
            print(data)
            cursor.executemany(finalSql, data)

Output:

insert into TEST_MAN (MANUFACTURER,REGISTRY_ID,MFR_DESCRIPTION,STATUS,PARTY_TYPE,CITY,STATE,POSTAL_CODE,PRIMARY_PHONE_NUMBER,PRIMARY_PHONE_EXTENSION,WEBSITE,CONTACT_NAME,GENERAL_INQUIRY_EMAIL,MANUFACTURER_NAME_LONG,MANUFACTURER_ADDRESS,COUNTRY,ADDRESS1,SUPPLIER_NAME,SUPPLIER_EMAIL,SUPPLIER_PHONE_NUMBER,ADDRESS2) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21)
[('gxsx-01', '2009', 'gxsx-01 test', 'Active', 'Organization', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '')]
Traceback (most recent call last):
  File "/home/dev/main.py", line 53, in <module>
    cursor.executemany(finalSql, data)
cx_Oracle.DatabaseError: ORA-00911: invalid character
Denver
  • 21
  • 2
  • Can you print the `data` as well ? – Barbaros Özhan Jun 10 '22 at 12:39
  • The common cause of this error is a trailing semi-colon at the end of the SQL statement text. – Christopher Jones Jun 10 '22 at 22:47
  • I have gone through the probable causes and am unable to see what might be causing it. There are no issues with a trailing semicolon; if I copy and run it over the DB, it runs just fine. – Denver Jun 11 '22 at 11:23
  • Can you print the repr() of the SQL? It is possible there is some "interesting" character in the SQL that is causing the problem. For example, copy/paste from certain document editors make use of "smart quotes". – Anthony Tuininga Jun 11 '22 at 21:43
  • Thanks @AnthonyTuininga, did not know about repr() and it helped debug the issue. Since I was reading this by opening a file without specifying the encoding, the BOM was being added to the string, which was causing the issue – Denver Jun 13 '22 at 13:12
  • 1
    That's good to know! We can adjust the documentation and also see if there might be a way for the database to handle the BOM (or for the driver to remove it before it gets sent to the database). – Anthony Tuininga Jun 13 '22 at 13:41

1 Answers1

0

It was due to BOM being inserted at the start of the string. I had to specify the correct encoding character set to solve this. For details check u'\ufeff' in Python string

print(repr(finalSql))

Output:

insert into OTBI_MANUFACTURERS (\ufeffMANUFACTURER,REGISTRY_ID,MFR_DESCRIPTION,STATUS,PARTY_TYPE,CITY,STATE,POSTAL_CODE,PRIMARY_PHONE_NUMBER,PRIMARY_PHONE_EXTENSION,WEBSITE,CONTACT_NAME,GENERAL_INQUIRY_EMAIL,MANUFACTURER_NAME_LONG,MANUFACTURER_ADDRESS,COUNTRY,ADDRESS1,SUPPLIER_NAME,SUPPLIER_EMAIL,SUPPLIER_PHONE_NUMBER,ADDRESS2) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21)'
Denver
  • 21
  • 2