Thursday, May 14, 2009

PL/SQL Developer on Linux!

Finding a decent SQL IDE for linux is not easy.

I'm not a big toad fan, and even if I was, I don't think I could afford to be anyway... So what practical alternatives are there ? (aside from the Oracle Enterprise application suite itself of course). Tora seems to be the quick and easy answer, until you try to work using it ... Even Oracle's SQLDeveloper is cumbersome to use.

Well I've always been a fan of PL/SQL Developer, and finally getting it to work properly on Linux made my day. Over the years I've had limited success trying to get this working, mostly with wine. Today, I decided to try again, and extremely surprised to see that it is now a definite possibility.

  • What you need:

    1. Your favorite Linux distro... (I used Fedora Core 10)

    2. CrossOver Office (I used 7.1.0)

    3. Oracle instant client (for Linux and Windows) - I used 11.1

    4. PL/SQL Developer installation file (I used 7.1.5)

    note: Installation of Crossover Office will not be covered in this tutorial. I also assume that you are comfortable installing an unsupported application using CrossOver Office. If not, there are plenty of tutorials available if you exercise your Google-Fu.

    note: root privileges are required for this tutorial, to gain root access open a terminal and type su - and provide the root password.

    note: If you have selinux installed, you may encounter some hiccups. Monitor your selinux notifications if it starts to interfere.

  • Install Oracle Instant Clients

    1. Linux :

    2. # cd {directory containing instantclient RPM files}
      # yum --nogpgcheck localinstall oracle-instantclient*.rpm

      Now use a text editor to edit /etc/profile

      # gedit /etc/profile

      Add the following 2 lines to the bottom of the file and save

      export ORACLE_HOME=/usr/lib/oracle/11.1/client
      export TNS_ADMIN=$HOME

      Now we have to tell Linux where to find the oracle client shared objects.

      # cd /etc/
      # gedit oracle11.conf

      Insert the following line /usr/lib/oracle/11.1/client/lib. Save and exit your editor, and refresh your library cache with the following command

      # ldconfig

      Create or copy existing tnsnames.ora and sqlnet.orafiles to your home directory.

      Everything should be good to go at this point, you should have a fully functional instant client. Just to make sure, test your client with sqlplus.

      # sqlplus /nolog

      If you can connect to one of the databases listed in your tnsnames.ora, you're smiling at this point.

    3. Windows :

    4. Luckily the windows instant client is distributed as a zip archive. Simply use unzip or a gui-based archive manager such as file-roller to extract the contents of the installation file somewhere onto your disk.

      note: Because I'm only going to use the windows client for PL/SQL Developer, I put mine in my home directory under .cxoffice. Ensure that the user you plan on running PL/SQL Developer as has the necessary rights to read the files.

    5. Install PL/SQL Developer

    6. note: Ensure that you run CrossOver Office as the correct user when installing. This probably won't be root...

      $ cxinstallwizard

    7. Configure PL/SQL Developer

    8. Don't try log in the first time you fire up PL/SQL Developer, there's a bit more configuration to go first...

      When prompted to log in, click on cancel. Click the tools menu, and then the preferences sub-menu.

      You can leave the Oracle Home configuration as blank (you set your $ORACLE_HOME environment variable when you edited your /etc/profile file earlier).

      For the OCI Library setting, enter the path to the oci.dll file located in the windows instant client directory (eg: /home/claw/.cxoffice/Unsupported-3/drive_c/instantclient_11_1/oci.dll).

    9. Restart your X session to refresh your environment

    10. Log in and out of Linux to pick up the configuration changes made in /etc/profile earlier.

    11. You're all done!

No comments:

Post a Comment