Loadingge9

Since I started a new job I also wanted to try out MacBook as my new laptop. I’ve never been a Mac user, but there seems to be a large group of people who think Macs are the best, so I needed to find out the goodness of Macs myself 🙂 A quick tip: Don’t upgrade to Yosemite if you laptop is bound to Active Directory.

One part of setting up my new laptop was also setting up SQL Developer WITH Oracle Driver, because a year or two ago I rolled out radius authentication for all users who need to access the production databases directly and radius (and other non-default-password authentication schemes) do not work with thin JDBC driver. Being new to Macworld and not finding any guides for this task online, it was quite a struggle in the beginning. Here I’m trying to share my experiences if anybody else find themselves in the similar situation. If you find any mistakes here, please let me know – I’m new to the world of MacOS 🙂

• How to install Oracle Instant Client Version 12.1.0.2 (64-bit) on a Mac (OS X 10.11.5) I have downloaded version 12.1.0.2 (64-bit) version and unzipped the file. I now have the following files under /downloads/instantclient.
• SQuirreL SQL Client for Mac is a very popular graphical SQL client, written in Java, which allows you to view the structure of a JDBC compliant database, browse the data in tables, and, amongst other things, issue SQL commands. The great thing about using SQuirreL SQL Client for Mac is that the functionality can be extended with the use of plugins!
• Check for updates. To check for latest updates, click the gear icon on the bottom left of the window and click Check for Updates. In an offline environment updates can be applied by installing the latest version directly over a previously installed version. Uninstalling prior versions of Azure Data Studio is not necessary as the installer updates a currently installed application, if present.

Jan Karremans just today published a similar article about Mac and Instant client, but the goal of my blog post is to get SQL Developer connected through Instant Client.

JDK

First you need Java installed, I installed 64-bit JDK8, but it would be better to install 64-bit JDK7, since SQL Developer 4.0 is not supported on JDK8.

SQL Developer

When I’m writing this, SQL Developer 4.1 is in Early Adopter release and 4.0 is in production. I installed SQL Developer 4.0.3.

Oracle Instant Client

There is another reason to choose SQL Developer 4.0 over 4.1 – 4.1 requires version 12.1 of the Oracle Client and that is not yet released for MacOS. SQL Developer 4.0 requires Oracle Client 11.2.0.3 and that is available for MacOS. I downloaded Instant Client 11.2.0.3 (64-bit – since I installed 64-bit JDK): instantclient-basic-macos.x64-11.2.0.3.0.zip and instantclient-sqlplus-macos.x64-11.2.0.3.0.zip to be precise. I unzipped both these files under /opt so my Instant Client installation directory is /opt/instantclient_11_2. After unzipping, Instant Client installation instructions require creating the following symlinks:

Configuring SQL Developer

Open SQL Developer, open Preferences and go to Database > Advanced. Configure Oracle Client paths and check Use Oracle Client and Use OCI/Thick driver boxes. Here is the sample screenshot from my settings.
When you press the Configure button to set the Oracle client path, there is also a button to Test if the Client was loaded correctly. Currently this test should fail.

Setting the environment variables

We need to set two environment variables: DYLD_LIBRARY_PATH to make sure applications can find and load the instant client libraries and TNS_ADMIN so instant client could find sqlnet.ora and tnsnames.ora files. In my case sqlnet.ora is essential, since I need to use Radius authentication.

First I added these environment variables to ~/.profile:

After restarting terminal and verifying that these environment variables are indeed set, execute SQL Developer app directly from terminal by executing open /Applications/SQLDeveloper.app/.

Open preferences and press the Oracle Client Test button again, now the test should succeed and SQL Developer is connected through Instant Client.
All good! Great success! Guess again… Close SQL Developer and launch it from Docker/Launchpad and you will see the Oracle Client test failing again. This is because the environment variables under ~/.profile or ~/.bash_profile are loaded only inside Terminal.

Setting environment variables on login

This seems to be the most complex part of this story since it seems to change with every MacOS release. The method that is working for me under Yosemite (and in no way am I declaring that it is the best method, I am a Mac newbie after all), is this:

Open application Script Editor and paste the following AppleScript there:

Yes, it is also necessary to set TNS_ADMIN, since setting the TNS admin directory path under SQL Developer preferences does not work if you need to use specific settings in sqlnet.ora, for example authentication.

Save the script and in the save dialog set File format: Application. And finally System settings -> Users & Groups -> Login items press + sign and select the application you just saved. Now, log out from MacOS and log back in. Now SQL Developer should work with Instant Client even when you launch it from Launchpad.

I got this method from a Stackoverflow thread.

What is the best SQL client for Mac OS X?

Apple Macintosh boxes are excellent for programming use, as many an experienced developer who prefers working on a Mac will essentially tell you. If you’re an aspiring programmer who doesn’t want to use Linux or Windows in place of your Mac, you will find this assertion heartening.

There might be a problem, though, when you’re working on various database platforms. For example, if you have a program running on SQL Server and another running on MySQL, you’ll often have one tool that supports Mac for performing SQL programming on one platform and another tool for the other database.

Mac Mysql Client

The above situation is far from ideal since it might require shifting from one tool to another. Worse, substantial licensing costs may be required for both tools.
The problem is compounded when you have a mix of SQL and NoSQL databases, or when you have a standalone database for one program, then another program running on a cloud-based database, such as Amazon Redshift. In this case, you’ll have to contend with having many tools for different databases.

DBHawk – SQL client for Mac OS X

The ideal solution, in this case, is to have a single Mac SQL client that can connect to different database platforms. This is where a web-based SQL tool, such as DBHawk, from Datasparc, comes in. You only have to run your favorite web browser, log on to DBHawk, then connect to your various databases, after which you can create tables, load data, and build SQL queries.

Mac Database Client

Aside from managing your different databases, you can also visualize your data using reports and charts and create a dashboard since DBHawk is not only a Mac SQL client and a database management platform but a business intelligence tool as well. All these operations are done using mere point-and-click. DBHawk is a complete tool, so to speak.

To run DBHawk on your own web server, all you have to do is install it on the server, a step that doesn’t take more than 10 minutes. The web server must have Apache Tomcat and Java 1.8 installed. Hardware requirements are minimal, too – recommended specs for a server that can be accessed by 25 DBHawk users is a minimum 8GB of RAM and 25GB disk space.

DBHawk offers support for various databases, including Amazon Redshift, Greenplum, MongoDB, MySQL, Netezza, Oracle, PostgreSQL, SAP Hana, SQL Server, and Teradata. In general, you should be able to connect to any JDBC-compliant database.

DBHawk is the jack-of-all-trades, Mac SQL client solution that is required to solve your problem when working on diverse databases.

Try DBHawk Demo or request a Free trial today!