With python-oracledb what does 'DPY-4027: no configuration directory to search for tnsnames.ora' mean

user_4799035 0
Viewed 812

  1. With the python-oracledb driver the code:

    import oracledb

cs = "MYDB"
c = oracledb.connect(user='cj', password=mypw, dsn=cs)

    gives the error:

    oracledb.exceptions.DatabaseError: DPY-4027: no configuration directory to search for tnsnames.ora

  2. The same error also occurs in a second case:

    import oracledb

cs = "MYDB = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orclpdb1)))"
c = oracledb.connect(user='cj', password=mypw, dsn=cs)

    and with this:

    import oracledb

cs = "MYDB = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orclpdb1)))"

cp = oracledb.ConnectParams()
cp.parse_connect_string(cs)

What does this mean?

python-oracledb oracle python database
1 Answers

Database connection strings in python-oracledb can be one of:

  • An Oracle Easy Connect string
  • An Oracle Net Connect Descriptor string
  • A Net Service Name alias mapping to a connect descriptor

See the user documentation on connection strings

If the connection string is an alias, then you must have a tnsnames.ora configuration file that maps the alias to a connect descriptor.

The tnsnames.ora file might look like:

MYDB = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orclpdb1)))

  1. In case 1, you need to tell python-oracledb where to find the tnsnames.ora file that contains the mapping from the alias MYDB used by the app to the connect descriptor that really tells Oracle where the DB is located. (See this answer for why).

    If you have a file /opt/myconfigdir/tnsnames.ora, then in python-oracledb's default 'Thin' mode you can do this:

    import oracledb

cs = "MYDB"
c = oracledb.connect(user='cj', password=mypw, dsn=cs, config_dir='/opt/myconfigdir')

    In the Thick mode (which is the mode when the app calls init_oracle_client()), you can set it like:

    import oracledb

oracledb.init_oracle_client(config_dir='/opt/myconfigdir')

cs = "MYDB"
c = oracledb.connect(user='cj', password=mypw, dsn=cs)

    In both modes you can alternatively set the environment variable TNS_ADMIN to the directory containing the file, and then run Python. See the configuration file link above for more information.

  2. In case 2, what was passed as the connection string is a string containing both a net service name alias and the connect descriptor, which is the syntax used in tnsnames.ora configuration files, not in applications themselves.

    Python-oracledb didn't understand this syntax and assumed you were trying to pass a net service name alias. It needed to look this up in a tnsnames.ora file, but failed to locate such a file.

    A solution is to pass only the connect descriptor component without the MYDB = part. For example like:

    import oracledb

cs = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orclpdb1)))"
c = oracledb.connect(user='cj', password=mypw, dsn=cs)

    Or you can put the original, whole string in a tnsnames.ora file and then call:

    import oracledb

cs = "MYDB"
c = oracledb.connect(user='cj', password=mypw, dsn=cs)

    See the example above for where to locate the file.

    Another alternative is to use the Easy Connect syntax:

    import oracledb

cs = "localhost:1521/orclpdb1"
c = oracledb.connect(user='cj', password=mypw, dsn=cs)
user_47990350
Related