Oracle data insertion raising 'ascii' codec can't encode character '\xea' in position 87: ordinal not in range(128) error

Question

I am trying to insert Portuguese text into my table. But, it is giving 'ascii' codec can't encode character '\xea' error.

Here is what I am doing :

   os.environ["NLS_LANG"] = ".AL32UTF8"

   query = "INSERT INTO MESSAGE (MESSAGE,LANGUAGE) VALUES (:MESSAGE,:LANGUAGE)"
   data = {'MESSAGE': '..... assistência para ajuda responda AJUDA Sua', 'LANGUAGE': 'Portuguese'}
   cursor = conn.cursor()
   cursor.execute(query, data)
   .....

My table structure:

CREATE TABLE MESSAGE  (   
    language  VARCHAR2(12) NOT NULL, 
    message  NVARCHAR2(350) NOT NULL
);

I am not sure if I need to set anything to insert other characters into the database.

Your Query part is ok. seems like python Encoding issue. check out this similar issue : https://stackoverflow.com/questions/9942594/unicodeencodeerror-ascii-codec-cant-encode-character-u-xa0-in-position-20 — Anoop J, Jun 01 '18 at 09:03
Which editor do you use to write your python script? Check character settings in your editor - is it set to UTF-8? — Wernfried Domscheit, Jun 01 '18 at 09:31
@WernfriedDomscheit, I am using sublime and have set `# -*- coding: utf-8 -*-` — Sijan Bhandari, Jun 01 '18 at 11:21
@WernfriedDomscheit , following the comment : https://github.com/oracle/python-cx_Oracle/issues/5#issuecomment-288268711, I set the value of `NLS_LANG` — Sijan Bhandari, Jun 01 '18 at 11:30

score 7 · Accepted Answer · answered Jun 03 '18 at 02:20

7

If you are going to set the environment variable NLS_LANG you must do it before you create any connection; otherwise, it will have no effect. Better yet, however, is to use the following to create the connection as this does not depend on environment variables:

connection = cx_Oracle.connect("user/password@connectString",
        encoding="UTF-8", nencoding="UTF-8")

answered Jun 03 '18 at 02:20

Anthony Tuininga

6,388
2
14
23

this saving my life! – sprite Jan 20 '20 at 11:24

Oracle data insertion raising 'ascii' codec can't encode character '\xea' in position 87: ordinal not in range(128) error

1 Answers1