First Steps in SQLDeveloper Data Modeler

It’s true, Oracle are giving away free stuff. “Oracle ?”, I hear you say, “as in Larry’s Database Emporium and Cloud base ?” The very same.
It’s been going on for quite a while and includes relatively hidden gems such as SQLDeveloper Data Modeler.

There is some confusion around this particular tool for a couple of reasons.
When it was first released (sometime around 2009 as I recall), Data Modeler was an additional cost option. However, that didn’t last long.
At present (and for a number of years now), it is available either as a completely standalone tool, or as a fully integrated component of the SQLDeveloper IDE.
Either way, it costs exactly the same as the SQLDeveloper IDE – i.e. nothing.

I can tell you like the price, want to take it for a spin ?

I’m going to focus here on using the integrated version of Data Modeler. This is because

  • I want to use it for small-scale modelling of the type you might expect to find when using an Agile Methodology
  • I’m a developer and don’t want to leave the comfort of my IDE if I don’t need to

What I’m going to cover is :

  • Viewing a Table Relationship Diagram (TRD) for an existing database table
  • Creating a Logical Data Model and Entity Relationship Diagram (ERD)
  • Generating a physical model from a logical model
  • Generating DDL from a Physical Model (including some scripting tweaks to suit your needs)
  • Using a Reporting Schema and pre-canned SQLDeveloper Reports to explore your models

This post is about introducing the features of Data Modeler in the hope that you may find them useful.
It’s not intended as a paragon of data modelling virtue.
Come to that, it’s not intended as a definitive guide on how to use this tool. I’m no expert with Data Modeler (as you are about to find out). Fortunately, there are people out there who are.
If, after reading this, you want to explore further, then you could do worse than checking out words of Data Modeler wisdom from :

Let’s get started…

Continue reading

Importing an Excel Spreadsheet into an Oracle database with SQLDeveloper

It was late. In a corner of the Treasury however, a light burned brightly.
Phillip Hammond, Chancellor of the Exchequer, was still working.
“Spreadsheet Phil” was trying to formulate a plan to rectify the nation’s finances in the event that the Prime Minister was won over by the ebullient Mr Johnson and decided upon a “No Deal” Brexit.
Boris Johnson was currently based at the F.O. which, Phil considered darkly, was somewhat appropriate.
If only his predecessor had had the foresight to stick a couple of billion on Liecester City to win the league at 5000 to 1, The Country’s finances would be in much better shape.
In the midst of this gloomy reflection, he was struck by a sudden inspiration. The crowded half-hour that followed resulted in – what else – a spreadsheet.
The information therein could save The Economy. “Hmmm”, thought Phil, “this stuff is far too important to leave in Excel, I really need to put it into my Oracle database. I wonder if SQLDeveloper could help”… Continue reading

Installing SQLDeveloper and SQLCL on CentOS

As is becoming usual in the UK, the nation has been left somewhat confused in the aftermath of yet another “epoch-defining” vote.
In this case, we’ve just had a General Election campaign in which Brexit – Britain’s Exit from the EU – played a vanishingly small part. However, the result is now being interpreted as a judgement on the sort of Brexit that is demanded by the Great British Public.
It doesn’t help that, beyond prefixing the word “Brexit” with an adjective, there’s not much detail on the options that each term represents.
Up until now, we’ve had “Soft Brexit” and “Hard Brexit”, which could describe the future relationship with the EU but equally could be how you prefer your pillows.
Suddenly we’re getting Open Brexit and even Red-White-and-Blue Brexit.
It looks like the latest craze sweeping the nation is Brexit Bingo.
This involves drawing up a list of adjectives and ticking them off as they get used as a prefix for the word “Brexit”.
As an example, we could use the names of the Seven Dwarfs. After all, no-one wants a Dopey Brexit, ideally we’d like a Happy Brexit but realistically, we’re likely to end up with a Grumpy Brexit.

To take my mind off all of this wacky word-play, I’ve been playing around with CentOS again. What I’m going to cover here is how to install Oracle’s database development tools and persuade them to talk to a locally installed Express Edition database.

Specifically, I’ll be looking at :

  • Installing the appropriate Java Developer Kit (JDK)
  • Installing and configuring SQLDeveloper
  • Installing SQLCL

Sound like a Chocolate Brexit with sprinkles ? OK then… Continue reading

Turning off Code Completion in SQLDeveloper – a Grumpy Old Man Fights Back

“You’re problem is you’re BBC”, a colleague remarked recently.
On further investigation, I found this not to be some reference to the British Broadcasting Corporation but rather that I had been “Born Before Computers”. Yes, I grew up – in computing terms at least – on the Command Line.
A number of things have changed since those dim and distant days.
Recently, I had a comment from Darryl claiming that chocolate bars had also been gradually reducing in size.
Damning photographic evidence of this scandal has recently come into my possession…

A Bounty Bar from 1980 together with it's modern counterpart

There’s more. My phone keeps insisting that I can’t spell and arbitrarily replacing words when I’m writing an SMS.
“You’re just the breast” took a bit of explaining to a rather skeptical Deb.
Having finally persuaded my phone that I really don’t require it’s assistance when composing a short missive, I have now turned my attention to SQLDeveloper. Continue reading

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 ( 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 – 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

Now amend the file so it looks something like this :

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.

Note – if you’ve stumbled across this post whilst looking for instructions for installing the latest version of SQLDeveloper ( currently version 4), you can find instructions here.

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.

    <item type="editor" node="TableNode" vertical="true">
    <title><![CDATA[FK Columns]]></title>
            <![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]]>

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
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.

SQLDeveloper or TOAD

Judging by the title I suppose your expecting some exhaustive ( exhausting ?) comparison between the giants of the Oracle IDE world ( or if you’ve been here before, another ramble through the remnants of my sanity).

So, before I launch into my treatise on why SQL*Plus is better than both, it’s only fair that I share with you the fruits of my in depth, impartial research.

Only one test was required – put them into Googlefight.

In case your not aware of it, is the URL – simply put in two search terms and watch them fight it out !

For the record, “TOAD” beats “SQLDeveloper”, “SQLDeveloper” beats “Quest TOAD” and, most gratifyingly, “SQL*Plus” knocks seven bells out of the pair of them !

That’s the pretence of impartiality out of the way, now for the bias and invective. Are you sitting comfortably ?

When I’m writing anything more complex than a simple, one time SQL statement, I’ll use Textpad. Syntax highlighting, no annoying code auto-completion popping up and inserting an option or keyword I don’t want, and when I want to compile/test, I just shell out and run SQL*Plus. Simple.

If I want to do any admin on the database, I’ll always use the command line – whether it’s running object creation scripts or general housekeeping tasks, I want to know that the database is doing exactly what I’ve asked it to do and the IDE isn’t trying to do anything clever behind my back.

One major reason for my reluctance to use an IDE for DDL is the propensity for it to crash partway through the statement, leaving the target object locked by a zombie session that you then have to hunt down and kill in the OS. Toad was particularly renowned for this at one point.

Another amusing “quirk” of both tools is that they tend to let you get away with some funky stuff where escaped quotes inside strings are concerned. Whereas SQL*Plus will give you an ORA-01756 “Quoted string not properly terminated”, the IDEs will just chug on. The results are likely to be other than what was intended.

In SQLDeveloper especially, when you do get an error message, it seems to be a bit hazy on the line number. Sometimes it’s a bit reluctant to divulge the actual error message.

At this point I usually just cut and paste the offending statement into SQL*Plus and magically…it still doesn’t run. However, I do get a better idea of what’s going on and where.

I do use an IDE to browse database program units and other schema objects, and maybe take advantage of the explain plan or session monitor features from time to time.

The one utility I did find invaluable at some point is TOAD’s schema comparison tool, so much help when trying to synchronize code between environments without being able to just replicate the database.

Having said all of that, I must confess a preference for SQLDeveloper over it’s amphibian rival ( what is it about animals and software – toads, penguins, koalas – it’s a veritable menagerie).

OK, it’s not a smooth as Toad ( a by-product of being a Java Application), but it has a couple of things going for it that really appeal.

First, it’s cheap ( i.e. free). In the current climate, it’s worth considering that, if your shelling out tens of thousands of pounds for any software site license, that more or less equates to someone’s job.

Secondly, it’s easily extensible. You can write your own reports and save them for easy reference. More impressively, you can add your own tabs to an object view window.

Also, I’ve now found the setting that turns of the automatic code completion feature unless I specifically invoke it.

I’ve already posted about creating a report in SQLDeveloper and I’ll probably write something on adding a tab in the future.

In the meantime, hello Textpad, my old friend.

You Don’t Have to be Posh to be Privileged

Working in multiple environments, I sometimes get tripped up by the RDBMS telling me a program unit doesn’t exist when I know it does.
What with system privileges either being granted directly, or via roles, I find the following query really useful in working out just what privileges I’ve got on an environment.

SELECT distinct privilege
FROM dba_sys_privs
WHERE grantee = USER
OR grantee IN (
    SELECT granted_role
    FROM dba_role_privs
    START WITH grantee = USER
    CONNECT BY PRIOR granted_role = grantee)

As a SQL Developer user, I drop this into my User Defined Reports folder so it’s always handy.