search

oracle / python-oracledb

Python driver for Oracle Database conforming to the Python DB API 2.0 specification. This is the renamed, new major release of cx_Oracle
https://oracle.github.io/python-oracledb
Other
328 stars 66 forks source link

ValueError: buffer too small for requested bytes #224

Closed Spliting closed 5 months ago

Spliting commented 1 year ago

platform.platform: Windows-10 -10.0.18363 sys.maxsize > 2**32: True platform.python_version: 3.8.8 oracledb.version: 1.4.0 oracle EE: 19.0.0.0.0

import oracledb

connection = oracledb.connect(dsn=dsn)

I can connect with xshell but get an error with oracledb

ValueError: buffer too small for requested bytes

The above exception was the direct cause of the following exception:

OperationalError: DPY-6005: cannot connect to database (CONNECTION_ID=JR9tbNKMKxaCKcfxlgOcWQ==).
buffer too small for requested bytes
cjbj commented 1 year ago

What's the exact version of the database? If you connect via SQL*Plus it would be the second version shown. It will have a non-zero second value like 19.20.

What OS is the database running on?

Is the database running on Oracle Cloud?

If you connect (in SQL*Plus) and run SELECT network_service_banner FROM v$session_connect_info; does it mention anything about encryption or checksumming?

As a workaround, you could almost certainly connect in Thick mode which can be enabled by adding a call to init_oracle_client() after your import oracledb, see the doc.

Spliting commented 1 year ago

Thank you very much!!!! I forgot to install the client. After installing the client, I successfully connected.

cjbj commented 1 year ago

@Spliting I think there is a buglet in python-oracledb and would like to be able to reproduce it so we can give a better error message, or connect successfully. Can you share more details about your setup (see my previous update)?

Spliting commented 1 year ago

Of course, I am glad to provide the details and hope these will be of some help to you.

  1. The exact version is 19.3.0.0

  2. I installed the database by visual box so it runs on linux. I usually connect it with Xshell/MobaXterm by SSH.

  3. When I run SELECT network_service_banner FROM v$session_connect_info; the result is as following:

    Oracle Bequeath NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
Authentication service for Linux: Version 19.0.0.0.0 - Production
Encryption service for Linux: Version 19.0.0.0.0 - Production
Crypto-checksumming service for Linux: Version 19.0.0.0.0 - Production

  4. When I connected the database directly by the following code,

    
import oracledb

host = "192.168.56.19" port = 22 service_name = "ORCL" user = "baozi" password = "baozi666" dsn = f"{user}/{password}@{host}:{port}/{service_name}" connection = oracledb.connect(dsn=dsn)

I got the error blow,

ValueError: buffer too small for requested bytes

The above exception was the direct cause of the following exception:

OperationalError: DPY-6005: cannot connect to database (CONNECTION_ID=JR9tbNKMKxaCKcfxlgOcWQ==). buffer too small for requested bytes

5. Then I downloaded the instantclient_19_20, but I got another error by adding `oracledb.init_oracle_client(lib_dir=r"D:\Oracle\instantclient_19_20")` after `import oracledb`

ORA-12569: TNS:packet checksum failure

6. I checked the file tnsnames.ora under the path $ORACLE_HOME/network/admin, and copied it to the same folder as the instantclient_19_20, and the lsnrctl status is as following:

before connection:

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 04-SEP-2023 18:47:38

Copyright (c) 1991, 2019, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19c)(PORT=1521))) STATUS of the LISTENER

Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 04-SEP-2023 18:47:07 Uptime 0 days 0 hr. 0 min. 45 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19c/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/oracle19c/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle19c)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) The listener supports no services The command completed successfully


after connection:

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 04-SEP-2023 18:48:50

Copyright (c) 1991, 2019, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19c)(PORT=1521))) STATUS of the LISTENER

Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 04-SEP-2023 18:47:07 Uptime 0 days 0 hr. 1 min. 57 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19c/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/oracle19c/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle19c)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "orcl" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "orclXDB" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... The command completed successfully



This was very strange because I had successfully connected on other computers before, but this time it always failed
anthony-tuininga commented 1 year ago

Can you supply the full traceback, please? Thank you!

Spliting commented 1 year ago

Sure, I can't tell which step is wrong, so I will provide all the details of my installation and the problems encountered.

  1. I got the following three files from my teacher instead of official website: VirtualBox-7.0.10-158379-Win.exe, MobaXterm_portable_v20.3.zip, oracle19c_full.ova.

I installed the VBox and oracle by running the .exe file and .ova file., respectively. But I can't connect to the oracle database at first. Then I tried to solve the problem, I checked /etc/oratab, env | grep i oracle, cat .bash_profile and lsnrctl status. I found that these files are already configured, but I haven't changed any files so far. After modifying the /etc/sysconfig/network-scripts-ifcfg-enp0s3 file, I successfully connected to the oracle database through SSH. Is that the key question?

  1. After successful SSH connection, I want to connect the database by oracledb in python. But I meet the first problem ValueError: buffer too small for requested bytes when I tried to connect directly. 
    
import oracledb

host = "192.168.56.19" port = 22 service_name = "ORCL" user = "baozi" password = "baozi666" dsn = f"{user}/{password}@{host}:{port}/{service_name}" connection = oracledb.connect(dsn=dsn)

3. After I download the instantclient_19_20, and added the path to the system envs. I got a new error `ORA-12569: TNS:packet checksum failure` by the following code.

import oracledb

oracledb.init_oracle_client(lib_dir=r"D:\Oracle\instantclient_19_20") host = "192.168.56.19" port = 22 service_name = "ORCL" user = "baozi" password = "baozi666" dsn = f"{user}/{password}@{host}:{port}/{service_name}" connection = oracledb.connect(dsn=dsn)

Then I checked the file `tnsnames.ora` under `$ORACLE_HOME/network/admin`, and copied this file to the same directory as instantclient_19_20, but it didn't work. After I checked the listener and find no exception, I didn't know what to do. The `lsnrctl status` is as following:

before connection:

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 04-SEP-2023 18:47:38

Copyright (c) 1991, 2019, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19c)(PORT=1521))) STATUS of the LISTENER

Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 04-SEP-2023 18:47:07 Uptime 0 days 0 hr. 0 min. 45 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19c/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/oracle19c/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle19c)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) The listener supports no services The command completed successfully


after connection:

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 04-SEP-2023 18:48:50

Copyright (c) 1991, 2019, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19c)(PORT=1521))) STATUS of the LISTENER

Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 04-SEP-2023 18:47:07 Uptime 0 days 0 hr. 1 min. 57 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19c/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/oracle19c/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle19c)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "orcl" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "orclXDB" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... The command completed successfully


I used docker to install an oracle image on linux, and then connected with oracledb, and it worked very well. This is my first time trying VBox on windows. It really stumps me. I tried every solution I could find on Google, but it didn't work.
cjbj commented 1 year ago

The "ORA-12569: TNS:packet checksum failure" sounds either like a misconfiguration, or a network problem not related to Oracle.

anthony-tuininga commented 10 months ago

What I was looking for was the Python traceback you received when you get the ValueError exception. I have not seen this error before. Although it is likely a misconfiguration issue and not high priority, it would be nice to squash this, if possible. Can you supply that traceback?

cjbj commented 5 months ago

Closing - no activity.