Connecting to Oracle DB using ODBC on Ubuntu

[Jon Baynham]

I'm having trouble getting Stata14 to connect to an Oracle DB over ODBC.

I'm running Ubuntu 14.04 in a virtual machine. Oracle Instant Client is installed and I can connect to my DB in SQLPLUS. UnixODBC is installed and the ODBC driver and DSN is set up, and I can succesfully query the DB using iSql.

When I start Stata and do "odbc list" I get
"The function SQLDataSources could not be found in libiodbc.so, therefor some ODBC services may be unavailable".
r(680)

I've switched the odbcmgr to unixodbc, but I still get an error - r(680) - but not error message.

If I go to File -> Import -> ODBC data source there is nothing displayed in the ODBC data sources box.

Can anyone point me in the right direction to solve this?

[wbuchanan]

Are you changing the ODBC driver manager before using odbc list? Your Stata appears to be defaulting to looking for iODBC rather than unixODBC. I've had issues with preferences being saved across sessions, but had luck once I switched the ODBC manager manually. Also, bear in mind that SQLPLUS is not the same as ODBC, it's a command line client built around Oracle's native interface.

[Jon Baynham]

I had changed the ODBC driver manager to unixodbc using the "set odbcmgr unixodbc" command, but still got the error. I mentioned SQLPLUS simply to confirm that the connection details stored in my TNSNAMES.ORA were correct. My /etc/ODBC.ini file referred to the named connection from TNSNAME.ora in the Servername parameter. I confirmed that the ODBC configuration was working by using the isql utility to connect to my DB using the DSN name defined in ODBC.ini. It was only Stata that had the problem with the ODBC connection.

I have actually solved the issue by uninstalling unixODBC and reinstalled using the latest version from www.unixodbc.org. The version that is in the Ubuntu package repository for Ubuntu 14.04LTS is 2.2.14, while the latest version is 2.3.4. It seems that Stata14 and unixODBC 2.2.14 don't work together. Once I had installed 2.3.4 everything just worked!!

[wbuchanan]

That's good to know.