Forum Stats

  • 3,874,164 Users
  • 2,266,675 Discussions


Python-OracleDB connectivity issues from python/jupyter notebook on Win10 WSL2/Ubuntu.

User_4NH2K Member Posts: 27 Blue Ribbon

Hi - hope this is the correct discussion group for this topic.


I am not able to connect to a RAC Oracle database. I am connecting as myself being a proxy into another schema. Connection is all fine with usual SQLPlus or SQL Developer on windows or within the WSL2/Ubuntu linux. BUT when I do use Python-OracleDB module from within python3 or a jupyter-notebook on a WSL2/Ubuntu linux within Windows 10 client, I encountered some errors that I can fully shake off.

First I have been using thin mode connection. I have TNS_ADMIN and LD_LIBRARY_PATH set to some directory where the tnsnames.ora and where the Oracle Instant Client Lib directory are. Note I can only use what is avail in this company ie some Oracle Instant Client v12.2 which has this structure oracle/12.2/client64/lib (for the library) and oracle/12.2/client64/bin (with some of the binaries like sqlplus etc. This work fine when I point my sqlplus to them and connect ok.

Note as mentioned i am a proxying as another schema, so I use this connection string:

my_username[schema_user_I_am_connecting_as]/[email protected]_alias (from tnsnames.ora)

This works fine in sqlplus but not in python as:

import oracle_db

import db_config

conn = oracledb.connect(user=db_config.user, password, dsn=db_config.db_alias)

(I have verified the variables by print to check they ok - all fine as it is)

In this instance I get this error exceptions.OperationalError: DPY-6001: cannot connect to database. Service " ....." not registered with listener t host "....scan....:: port 99999 (Similar to ORA12514)

so my tnsnames.ora looks fine as I can connect also with these same db_alias, my username and my password. Note as mention i am proxying into another user schema to do some work of the schema's behalf. like this ( my_username[schema_user_I_am_connecting_as]/[email protected]_alias )

so the tnsnames.ora has an address_list with 2 separate host scan addresses - one of them is a standby in another site - so only one is up . Could that pose a problem in python connection?

Because i also sometimes see the warning message ORA-12514 for that standby address on my SQL Developer connection in the navigation connection tree when I hover my cursor over that connection. But it is fine in SQLDeveloper when I connect as it knows to pick the other address when using the db_alias in the tnsnames.

So I have tried some other combinations in python with not much success :

When i do :

conn = oracledb.connect("my_username[schema_user_I_am_connecting_as]/[email protected]_alias")

Get same error as above DPY-6001:

When I do:

oracledb.connect("my_username[schema_user_I_am_connecting_as]/[email protected]//host-scan-name:port/servicename")

I get ORA-01017: invalid username/password: logon denied.

hum - close . I am still using the same user and password that work fine in sqlplus. now i have use an easyconnect method with the full address pass in the connection string. That works fine from sqlplus or sqlcli but not here. Has to do with python! I even put a raw string like r"connection str") - no still same login denied

I have also tried without a proxy user login and still gettin logon denied.

What am I doing wrong? or is the way using proxy to connect is nor correct or has a bug? password also obviously has non alpha char like # etc and the [ ] for proxy connection not causing some python grief here. or may be I just not reading the correct doc or missing some important stuff. It is definitely using Thin mode as i can see the error trace showing :

File .... "src/oracldb/impl/thin/connection.pyx or protocol .....

I have also tried without the LD_LIBRARY_PATH as I was using only thin mode.

Thanks in advance,


  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,656 Employee
    edited Jun 8, 2022 9:55PM
    • The best place to discuss this is in but we can continue here if you like
    • Can you post a careful copy of what's in your tnsnames.ora file (please change names & domains to fake strings)? This would be quickest for us to check. Make sure to keep all the elements used e.g. multiple names, failover options etc.
    • Since you are using Thin mode, setting LD_LIBRARY_PATH and having Instant Client are not relevant, as you noted
    • But why not try Thick mode to see if there is a difference? The connection string management is handled by python-oracledb directly in Thin mode and is handled in the Oracle Client libraries in Thick mode. Add a call to init_oracle_client() and try that.

  • User_4NH2K
    User_4NH2K Member Posts: 27 Blue Ribbon

    Chris, Thank you very much for all the tips. The github issues link was very good and gave me a few things to think and try.

    In the end, I have only managed to get Thick Mode working by running the oracledb.init_oracle_client() first. This works with my tnsnames.ora intact as follows:
























    I have tried testing with Thin mode again with no luck. I have some environments where there is no Oracle client libraries installed nor can i install a simple instant client. As would be good to get working for these situations.

    I have tried these variations of tnsnames.ora :

    1) with the 2 lines FAILOVER=on and LOAD_BALANCE=on

    2) removed those lines one at a time

    3) removed both lines

    No difference whatsoever when in Thin Mode, they all give error DPY-6001 complaining about the the listener which is currently down as standby.

    I tried also

    1) removing the address with the host which is not up.

    and leaving only the other one whose listener is up but now this time I do not get error DPY-6001. I get an ORA-01017 - logon denied. ORA-01017 is a pain when testing as 3 strikes and you are locked out.

    So seems there are some issues when one is using a tnsnames.ora with Thin Mode - either it is due to oracledb driver not being able to failover when there is multiple scan addresses with some host not running.

    SQLPLus works fine with all these tnsnames.ora variations.

    SQLDeveloper also fine. SQLDeveloper also detects which listener does not know of the service requested when hovering over the connection entry in the connections tree but will still give me a connection using the other address.

    What I have not tested is try with an EasyConnect string directly ie not using any tnsnames.ora file to get the tns alias. May be I'll try that when i get some time soon. For now I'll just use the Thick Mode on where I can . Where I can't then I'll wait for some fix or solution or may be EasyConnect string could work,

    Thanks again.

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,656 Employee

    We released version 1.0.1 with some changes in this area. Can you update and try Thin mode again for us?: python -m pip install oracledb --upgrade

  • User_4NH2K
    User_4NH2K Member Posts: 27 Blue Ribbon

    Thanks Chris for your response. sorry i did get a chance earlier to come back here. so far the thick mode works ok. But at some time i will give the thin mode a try again as per your recommendation above. Thank you.

  • User_4NH2K
    User_4NH2K Member Posts: 27 Blue Ribbon

    Regarding Python Jupyter Notebook magic function %%sql (via sqlalchemy) and the OracleDB driver, how does it work. Do they work together?

    I want to be able to run sqlplus commands in %sql magic function directly in jupyter notebook. Can that be done with the OracleDB driver and sqlAlchemy. because I have tried things like spool filename.log which the sql magic does not understand and basically how can i use sqlplus commands inside sql magic on notebook. Is it possible? That would be awesome if it can as this would be very valuable to document the whole process when running sql jobs in a pipeline/worklflow and possibly automate/schedule a notebook sql run with that. Or is there a better alternative you can suggest?

    Thanks in advance.

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,656 Employee
    edited Aug 22, 2022 4:52AM

    SQL*Plus commands like 'SPOOL' are not SQL commands - the DB doesn't understand them. They are understood and acted on only by the SQL*Plus program. So I don't think iPython understands them because they are vendor-specific.

    Fundamentally you want to move on from running SQL*Plus scripts in Python, because the error handling and flow control for executing each statement in Python is a lot better. If you insist, then look at and