[GIS] How to connect QGIS to an Oracle DB (SDE)

enterprise-geodatabaseoracle-dbmsqgis

I want to access an Oracle Database (with SDE) from Qgis 2.4.

I have tried many different options from within the window Create a new Oracle Connection but I always get an error message (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor or similar messages). I know however that the DB is working and listening. I know the correct host, the correct port and I have valid username / password.

The IT support here gave me 2 (independent) pieces of advice but I'm not sure how to implement them with Qgis:

  • "Does your client (Qgis) know about the correct tns_admin directory?"
  • "Make sure that LDAP is listed first: NAMES.DIRECTORY_PATH = (LDAP, TNSNAMES,ONAMES)"

Anyone has an idea?

Thanks a lot in advance!

PS: sorry if my title is not very explicit. I'm not sure how to best formulate it.

EDIT [30.09.2014]

I have moved a little bit forward and I have edited the file tnsnames.ora located under C:\OraClient11g\product\11.2.0\client_1\NETWORK\ADMIN:

name_of_my_db =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = *********)(PORT = 1568))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = name_of_my_db)
 )
)

Will Qgis read this file or does it require its own copy of it? If yes, where should it be located? If no, how does it know where tnsnames.ora is stored? Should there be an entry in the registry?

By the way, my sqlnet.ora file (also located in C:\OraClient11g\product\11.2.0\client_1\NETWORK\ADMIN) looks like this:

# sqlnet.ora Network Configuration File: C:\oracle\product\10.2.0\NETWORK\ADMIN\sqlnet.ora
# Generated by Oracle configuration tools.

################
# Filename......: sqlnet.ora
# Name..........: $Id: sqlnet.ora,v 1.3 2004/11/22 13:47:17 oracle Exp $
# Date..........: $Source: /data/repository/cvsroot/ORACLE/env_sqlnet/indus/sqlnet.ora,v $
#
################

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (LDAP,TNSNAMES,ONAMES)

SQLNET.CRYPTO_SEED = "************************"

NAMES.PREFERRED_SERVERS =
  (ADDRESS_LIST =
    (ADDRESS = (COMMUNITY = ************************)(PROTOCOL = TCP)(Host = ************************)(Port = 1568))
    (ADDRESS = (COMMUNITY = ************************)(PROTOCOL = TCP)(Host = ************************)(Port = 1568))
    (ADDRESS = (COMMUNITY = ************************)(PROTOCOL = TCP)(Host = ************************)(Port = 1568))
    (ADDRESS = (COMMUNITY = ************************)(PROTOCOL = TCP)(Host = ************************)(Port = 1568))
    (ADDRESS = (COMMUNITY = ************************)(PROTOCOL = TCP)(Host = ************************)(Port = 1529))
    (ADDRESS = (COMMUNITY = ************************)(PROTOCOL = TCP)(Host = ************************)(Port = 1527))
    (ADDRESS = (COMMUNITY = ************************)(PROTOCOL = TCP)(Host = ************************)(Port = 1528))
  )

NAMES.DEFAULT_DOMAIN = ************************

SQLNET.EXPIRE_TIME = 0

EDIT 2 [30.09.2014]

According to my DB Admin, I shouldn't even need the tnsnames.ora, because the connection should work through LDAP… Not sure whether or not this information brings anything relevant for my problem?

Best Answer

It looks like EZConnect can't connect for whatever reason (3a works/3b doesn't). You can try adding "ezconnect" to NAMES.DIRECTORY_PATH in the sqlnet.ora file. Hopefully that will make 3b from @Albert Godfrind's answer work.

The LDAP connection may fail from QGIS because you have something like the Oracle Instant Client somewhere in the path before your C:\OraClient11g\ client. In that case, sqlplus and tnsping would get loaded from the C:\OraClient11g\ client, while QGIS would load the other client that is missing the sqlnet.ora file. You can set the the variables ORACLE_HOME and TNS_ADMIN to tell the Oracle client to load the admin directory that it specifies, rather than the default relative path. Try setting these variable to force Oracle to use the same admin dir that sqlplus is using. Start QGIS from the command line after setting:

SET ORACLE_HOME=C:\OraClient11g\product\11.2.0\client_1
SET TNS_ADMIN=C:\OraClient11g\product\11.2.0\client_1\NETWORK\ADMIN

NOTE: I'm not sure if TNS_ADMIN is going to be just for TNS Names looks up, or if the client will load the sqlnet.ora file and use LDAP. Technically, I think ORACLE_HOME will be enough, and you don't actually need the TNS_ADMIN variable or tns_names.ora file.

I got this to work with TNS_NAMES by setting the TNS_ADMIN variable, then starting QGIS 2.4.0. I created the connection by only setting the Database, Username and Password values in the "Create a New OGR Data connection" dialog.

The version of QGIS that I have does have the instant client included in the bin folder, so you wither need to set TNS_ADMIN to use tns name conenctions, or create the Network/admin/ folders in the QGIS bin folders and copy your tnsnames.ora and sqlnet.ora file into it.

It looks like if you set the Host values in the connection dialog, QGIS tries to use an ezconnect connection.

Related Question