Solved – The Mystery of SQLDeveloper and the Missing ocijdbc11

This is a follow up to my earlier post about SQLDeveloper being moody and suddenly refusing to connect to a database via TNS.
Having had a bit of a dig around, it would seem that this problem is not platform specific and affects Windows in the same way.
At this point, I’d like to say a big “thank you” to Grzegorz Wilczura, who referred me to this article by Sue Harper.
If you’re hitting this problem on Windows, then you may want to follow the instructions there to set up a TNS_ADMIN environment variable.

Remember, this problem had two primary symptoms :-

  1. Empty Network Alias list when defining a TNS connection
  2. When testing an existing TNS connection you get :
    Status: Failure – Test failed : no ocijdbc11 in java.library.path

In Sue’s article, it states that SQLDeveloper looks for a tnsnames.ora in the following places in this order :

  • $HOME/.tnsnames.ora
  • $TNS_ADMIN/tnsnames.ora
  • /etc/tnsnames.ora
  • $ORACLE_HOME/network/admin/tnsnames.ora

Only one of these places is has an absolute path. The rest all rely on environment variables being set. However, when I run SQLDeveloper from the Ubuntu desktop menu, I’m not starting a shell, so my .bashrc doesn’t get executed. Therefore, these variables are not set.
When I setup my first tns connection, I’d just installed sqldeveloper and ran it by executing the shell script ( sqldeveloper.sh) from a Terminal Window. Of course, the $ORACLE_HOME was set in this environment and SQLDeveloper could therefore see the tnsnames.ora in $ORACLE_HOME/network/admin.

All of this means that the cause of the problem is that SQLDeveloper cannot see, or can no longer see, the tnsnames.ora file.

Copying the tnsnames.ora to /etc will fix the problem. However, probably the best solution is to ensure that we’re only referencing one tnsnames.ora and don’t replicate it. That way, we only ever have to change it in one place, should the need arise.

So, the alternative I’ve chosen is to set the $ORACLE_HOME environment variable in sqldeveloper.sh – the script that gets called to start SQLDeveloper.
Start a terminal and go to the SQLDeveloper home directory ( in my case, I installed SQLDeveloper in /opt) :-

cd /opt/sqldeveloper
sudo gedit sqldeveloper.sh

Now amend the file so it looks something like this :

#!/bin/bash
ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
export ORACLE_HOME
cd "`dirname $0`"/sqldeveloper/bin && bash sqldeveloper $*

Now re-start SQLDeveloper from the Ubuntu menu. Remember, this menu item is simply executing the shell script we’ve just changed.
If you have an existing tns connection defined then you can test doing the following :

  1. Right-click the connection and select Properties from the pop-up menu.
  2. This will bring up the New/Select Database Connection window.
  3. Enter the password in the Password field
  4. Hit the test button.

The test should now succeed.

If you haven’t got a TNS connection defined currently, you should now be able to test by setting one up, with no problem.

SQLDeveloper doesn’t like Mondays – refusing to play with TNS defined connection

OK, so I didn’t find this until today ( Wednesday). Look it’s poetic license alright ? Give me a break here !
Anyway, it seems that SQLDeveloper has decided to stop playing nicely and when trying to connect to XE on my TNS defined connection.
Continue reading

Installing SQLDeveloper 1.5.5 on Ubuntu 9.10

Continuing my adventures in this strange and (mostly) wonderful new world of Ubuntu…

Now I’ve managed to get Oracle XE to behave itself on Ubuntu, it’s time to install SQLDeveloper.

The aim is to :-

1)Install SQLDeveloper so it’s working ( i.e. can connect to my XE database)
2)Setup a Menu item so I don’t have to mess about running a shell script to start it every time.

So, deep breath… Continue reading

Adding your own tabs in SQLDeveloper

As I said in my previous post, one of the really cool things about SQLDeveloper is that it is easy to add your own custom extensions.

Confession – I’ve knocked this together using SQLDeveloper 1.5.4 running against Oracle XE on…er…Windows Vista.

I know, after all that stuff about how great Ubuntu is. Look, I just haven’t got round to installing Oracle yet. All these annoying little things like going to work keep getting in the way. Look It’s on the list OK ?

Anyway, back to SQLDeveloper.

Take table information as an example. By default SQLDeveloper will show constraint information for a table, but it won’t display the relationship between columns in the child and parent tables of a Foreign Key relationship.

Standard SQLDeveloper Table view

Getting this information from the data dictionary can be done with the following query :-

SELECT cons.constraint_name, fkcols.column_name,
    pkcols.table_name as "Referenced Table",
    pkcols.column_name as "Referenced Column"
FROM all_constraints cons, all_cons_columns fkcols,
    all_cons_columns pkcols
WHERE cons.owner = fkcols.owner
AND cons.table_name = fkcols.table_name
AND cons.constraint_name = fkcols.constraint_name
AND cons.r_owner = pkcols.owner
AND cons.r_constraint_name = pkcols.constraint_name
AND fkcols.position = pkcols.position
AND cons.constraint_type = 'R'
AND cons.owner = :OBJECT_OWNER
AND cons.table_name = :OBJECT_NAME
ORDER BY fkcols.column_name, fkcols.position

Simple enough, but it would be nice to see this for every table without having to type in and run this query every time.
To add this query as a permanent tab in SQLDeveloper, all you need to do is create an XML file containing the text of the query.

<items>
    <item type="editor" node="TableNode" vertical="true">
    <title><![CDATA[FK Columns]]></title>
    <query>
        <sql>
            <![CDATA[SELECT cons.constraint_name, fkcols.column_name,
                    pkcols.table_name as "Referenced Table",
                    pkcols.column_name as "Referenced Column"
                    FROM all_constraints cons, all_cons_columns fkcols,
                    all_cons_columns pkcols
                    WHERE cons.owner = fkcols.owner
                    AND cons.table_name = fkcols.table_name
                    AND cons.constraint_name = fkcols.constraint_name
                    AND cons.r_owner = pkcols.owner
                    AND cons.r_constraint_name = pkcols.constraint_name
                    AND fkcols.position = pkcols.position
                    AND cons.constraint_type = 'R'
                    AND cons.owner = :OBJECT_OWNER
                    AND cons.table_name = :OBJECT_NAME
                    ORDER BY fkcols.column_name, fkcols.position]]>
        </sql>
    </query>
    </item>
</items>

The are only two bits of this file you need to change :-

<title><![CDATA[FK Columns]]></title>

FK Columns will be the name of the tab displayed in SQLDeveloper.

The other bit obviously, is the SQL statement.

On the subject of the SQL, we’re using two standard SQLDeveloper placeholders – :OBJECT_OWNER and :OBJECT_NAME.

To add this extension into SQLDeveloper

1) Go to the Tools Menu and select Preferences

You will then be presented with the Preferences Dialog

2) From the Menu tree in the left-hand pane, select Database and then User Defined Extensions - as shown below

Preferences Dialog

Preferences Dialog

3) Click on the Add Row button.

4) Click on the row under the Type column and select EDITOR from the drop-down list.

5) Click on the row under the Location column and click the Browse button that appears.

6) Navigate to the XML file you’ve created and click OK.

You should now be looking at something like this

New Tab definition

New Tab definition

7) Now shutdown and re-start SQLDeveloper.

Now if you view a table, you should see your new tab ( FK Columns in this case).

New Table Tab

New Table Tab ( FK Columns)

And that’s it.

If you need any further information, there’s a fairly good ( and rather more comprehensive) tutorial at http://www.oracle.com/technology/obe/sqldev_obe/extension/extensions.htm
Update – a big thanks to Adrian Dillon ( see comment below), for pointing out my omission in the SQL on this one, which I’ve now corrected.