UnicodeDecodeError when using cx_Oracle.connect

[Heloisej]

I installed cx_Oracle via pip and successfully import it. However when I use the function cx_Oracle.connect, the error occured. The following is my code:

import os
os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.ZHS16GBK'
import cx_Oracle
connection = cx_Oracle.connect("uname/pwd@localhost/orcl")

The following is the error message:

UnicodeDecodeError: 'utf-8' codec can't decode bytes in position 82-83: invalid continuation byte

I almost have tried all the methods that can be found on Google, but the question remains unsolved.

Environment:

• Python: Python 3.6.2 (v3.6.2:5fd33b5, Jul 8 2017, 04:14:34) [MSC v.1900 32 bit (Intel)] on win32
• cx_Oracle: 6.0.1(32 bit)
• Oracle client version: Instant Client 11.2(32 bit)
• Oracle database version: 11.2
• Windows 7 Service Pack 1 (64 bit)

oracle defualt encode

my python default encode

[cjbj]

Thanks for the details:

Just checking: how exactly did you install it? Did you use the --upgrade option?

How do you know it's 6.0.1? What does cx_Oracle.version give?

[edited]

[anthony-tuininga]

In the command prompt where you are running Python, set the environment variable DPI_DEBUG_LEVEL to the value 31, as in: set DPI_DEBUG_LEVEL=31. Then let us know the results. Thanks!

[Heloisej]

When I first installed cx_Oracle， I just used

pip install cx_Oracle

I tried to add --upgrade then, it showed that

Requirement already up-to-date

Then I checked my cx_Oracle version by using cx_Oracle.version, and it is 6.0.1

---

I also tried to set the environment variable DPI_DEBUG_LEVEL to the value 31 in the command prompt. Unfortunately, it still didn't work. The same error message appeared when using the function cx_Oracle.connect.

[cjbj]

One more thing to check is that you have the correct VS Redistributable: you need Microsoft Visual Studio 2005 Redistributable for the 11.2 client. And make sure that the expected Oracle Client is first in your PATH.

It would be nice to know why a clearer error message isn't displayed.

[jmcp]

https://stackoverflow.com/questions/18266906/setting-query-results-encoding-in-cx-oracle-unicodedecodeerror-with-chinese-ch might also be useful with this problem. James

…

On 24/08/17 11:57 am, James C. McPherson wrote: This is an interesting issue. I came across https://stevecao.wordpress.com/2016/01/11/understanding-character-set/ There is a table towards the end of the post which lists the success/failure conversions for zhs16gbk. Perhaps if your python session specified us7ascii or gb2312 instead of utf-8 it might work better? hth, James On 23/08/17 08:36 pm, Heloisej wrote: > > For general questions: > > I installed cx_Oracle via pip and successfully import it. However when I > use the function cx_Oracle.connect, the error occured. The following is > my code: > > import os > os.environ['NLS_LANG']= 'AMERICAN_AMERICA.ZHS16GBK' > import cx_Oracle > connection= ***@***.***/orcl") > > The following is the error message: > > UnicodeDecodeError: 'utf-8' codec can't decode bytes in position > 82-83: invalid continuation byte > > I almost have tried all the methods that can be found on Google, but the > question remains unsolved. > > > Environment: > > * *Python:* Python 3.6.2 (v3.6.2:5fd33b5, Jul 8 2017, 04:14:34) [MSC > v.1900 32 bit (Intel)] on win32 > * *cx_Oracle:* 6.0.1(32 bit) > * *Oracle client version:* Instant Client 11.2(32 bit) > * *Oracle database version:* 11.2 > * Windows 7 Service Pack 1 (64 bit) > > *oracle defualt encode* > 1 > <https://user-images.githubusercontent.com/28823464/29611816-a557ddb4-8831-11e7-82b6-0f22e84cbce8.PNG> > > > *my python default encode* > 2 > <https://user-images.githubusercontent.com/28823464/29611819-a9f76aa6-8831-11e7-8c42-400fbd70e214.PNG> > > > — > You are receiving this because you are subscribed to this thread. > Reply to this email directly, view it on GitHub > <#71>, or mute the > thread > <https://github.com/notifications/unsubscribe-auth/AHNT8vU5l58kRuAZlf5Jmflt4eqgafJdks5sbACWgaJpZM4O_0MJ>. > >

-- James C. McPherson -- Oracle Systems / Solaris / Core https://www.jmcpdotcom.com/blog

[anthony-tuininga]

I also tried to set the environment variable DPI_DEBUG_LEVEL to the value 31 in the command prompt. Unfortunately, it still didn't work. The same error message appeared when using the function cx_Oracle.connect.

Did you not get any additional output?

Can you also try the following?

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

This will bypass the NLS_LANG environment variable completely.

[jmcp]

This is an interesting issue. I came across https://stevecao.wordpress.com/2016/01/11/understanding-character-set/ There is a table towards the end of the post which lists the success/failure conversions for zhs16gbk. Perhaps if your python session specified us7ascii or gb2312 instead of utf-8 it might work better? hth, James

…

On 23/08/17 08:36 pm, Heloisej wrote: For general questions: I installed cx_Oracle via pip and successfully import it. However when I use the function cx_Oracle.connect, the error occured. The following is my code: import os os.environ['NLS_LANG']= 'AMERICAN_AMERICA.ZHS16GBK' import cx_Oracle connection= ***@***.***/orcl") The following is the error message: UnicodeDecodeError: 'utf-8' codec can't decode bytes in position 82-83: invalid continuation byte I almost have tried all the methods that can be found on Google, but the question remains unsolved. Environment: * *Python:* Python 3.6.2 (v3.6.2:5fd33b5, Jul 8 2017, 04:14:34) [MSC v.1900 32 bit (Intel)] on win32 * *cx_Oracle:* 6.0.1(32 bit) * *Oracle client version:* Instant Client 11.2(32 bit) * *Oracle database version:* 11.2 * Windows 7 Service Pack 1 (64 bit) *oracle defualt encode* 1 <https://user-images.githubusercontent.com/28823464/29611816-a557ddb4-8831-11e7-82b6-0f22e84cbce8.PNG> *my python default encode* 2 <https://user-images.githubusercontent.com/28823464/29611819-a9f76aa6-8831-11e7-8c42-400fbd70e214.PNG> — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#71>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AHNT8vU5l58kRuAZlf5Jmflt4eqgafJdks5sbACWgaJpZM4O_0MJ>.

-- James C. McPherson -- Oracle Systems / Solaris / Core https://www.jmcpdotcom.com/blog

[wanrenjerry]

have you solved the above questions? I encountered the same problem

[anthony-tuininga]

Did you try the suggestions made above? If so, please report on your results. Thanks!

[Heloisej]

Sorry for the late response.

I successfully solved the problem!! What I did is as below:

1. Download and install Microsoft Visual C++ 2005 Redistributable Package (x86)
2. Set the expected Oracle Client first in my PATH.
3. Using cx_Oracle.connect("user/password@server/service", encoding = "UTF-8", nencoding = "UTF-8")

Then it works!!

Thank you for all the suggestions and solutions provided!

[cjbj]

@Heloisej do you still get the error if you don't set the encoding & nencoding parameters?
We'd like to be able to fix what is causing the UnicodeDecodeError and display the proper underlying OS error message.

[Heloisej]

I deleted the encoding & nencoding parameters. It still works. So I think the key issue is to install the right Microsoft Visual C++ Redistributable Package

[anthony-tuininga]

I'm glad you got it resolved. When you set the environment variable DPI_DEBUG_LEVEL to the value 31, did you not get any output at all? We would definitely like to resolve this for everyone!

Perhaps you can also let us know what language/regional parameters you have set in your OS?

[Heloisej]

1. When I set DPI_DEBUG_LEVEL=31, there is no output.
2. The language/regional parameters I set is Simplified Chinese, China.

[anthony-tuininga]

Ok. Thanks. You should see some output. Are you running Python from the command line? Not something like IPython, Anaconda, etc.? If you run this in Python do you see the expected output of '31'?

os.environ["DPI_DEBUG_LEVEL"]

Of course you'd have to introduce the problem again. So if you don't want to do that, I understand. Hopefully someone else who has this problem as well will follow these instructions and provide the information we are looking for in that case!

[shaojianwei]

I follow the suggestions above, but the problem remains.

[anthony-tuininga]

Can you show the output where you set the environment variable DPI_DEBUG_LEVEL to the value 31, the output you get when you run the Python statement os.environ["DPI_DEBUG_LEVEL"] and the output when you run the Python statements to import cx_Oracle and attempt to make the connection? Please also show the Python version and cx_Oracle version that you are using.

[fengxing11111]

i also don't know how to deal with the problem ,still UnicodeDecodeError.
and i meet the problem when use pandas data.to_sql('data',engine)

[anthony-tuininga]

Can you show the output when you set the environment variable DPI_DEBUG_LEVEL to the value 31?

[cjbj]

@fengxing11111 Are you asking about an error when you execute a particular statement with specific data? This current issue is about the error at initial connection. Can you confirm? If you are asking about execution, then please create a new issue.

[fengxing11111]

Environment:
Python 3.5.1 (v3.5.1:37a07cee5969, Dec 6 2015, 01:54:25) [MSC v.1900 64 bit (AMD64)] on win32
cx_Oracle: 6.0.2
Oracle client version: Instant Client 11.2(32 bit)
Oracle database version: 11.2

[anthony-tuininga]

If you are running a 64-bit Python and cx_Oracle, you also need to have 64-bit Instant Client and from what you have stated above you have 32-bit Instant Client!

Also, can you set DPI_DEBUG_LEVEL=31 before you import cx_Oracle. Preferably before you even start Python? Thanks.

[fengxing11111]

Thanks for your reply!Maybe the wrong environment construction.I change the python version to 32-bit and i can run the codes.I will change the environments to 64-bit to test whether the codes can run in the next few days.Thanks a lot.This is my first time asking question online by English,hope you can understand it.