Configuring External Tools in SQLDeveloper

In order to distract myself from the lunacy that is the ongoing UK General Election campaign, I’ve been continuing to spend some quality time with the Swiss Army Knife of the Oracle world, SQLDeveloper.
I know, conventional wisdom says that when you have a hammer, everything is a nail.
On the other hand, conventional wisdom is rather less forthcoming if instead you have a multi-tool of Central European origin.
Fortunately, SQLDeveloper pays no heed to conventional wisdom but allows you to configure callouts to other useful software utilities by means of it’s External Tools functionality.
What we’re going to look at here is :

  • Adding a call to an external tool in SQLDeveloper
  • Using macros to control the behaviour of an External Tool
  • Configuration settings for common tools in Windows
  • Configuration settings for common tools in Ubuntu

Continue reading

Using Git in SQLDeveloper

As I write, South Africa have just emerged victorious from the 2019 version of the Rugby World Cup having vanquished England in the final.
This explains both the code used in the examples that follow and the fact that I’m writing this to escape from the “commiserations” pouring into my phone from my Welsh In-Laws. Tell me, what is the Welsh for Schadenfreude ?
Continuing my SQLDeveloper appreciation fest, I’m going to look at the latest version of SQLDeveloper’s (19.2) level of integration with Git.
Specifically, what I’ll be covering is :

  • Using the SQLDeveloper Files Tree to work with a local Git Repository
  • Creating a branch
  • Staging changes
  • Commiting changes
  • Comparing different versions of a file within a branch
  • Comparing branches
  • Merging branches
  • Deleting branches

We’re going to do all of this without leaving the comfort of our favourite Oracle IDE…
Continue reading

Making yourself at home in SQLDeveloper

I had to configure a new work laptop recently. This task was complicated somewhat by many of the usual issues us techies face when working for a large organization.
Admin privileges are out of the question. Pre-packaged applications are out-of-date and modern open-source development tools tend to fall foul of seemingly arbitrary security policies.
As a result, I’ve had to rely even more than usual on the Swiss Army Knife of the Oracle database.
Fortunately, there’s more to SQLDeveloper than getting stones out of horses hooves. So much so, in fact, that I’ve decided to arbitrarily declare that it’s now SQLDeveloper Appreciation Month. This is largely because, in exploring it’s capabilities, I’ve found myself with enough material to knock out a number of posts over the next few weeks.
It seems appropriate to begin this journey with a look at how you can make SQLDeveloper feel a bit more homely.
How can you encourage this most configurable of tools to do all the things you find so helpful whilst at the same time curbing some of it’s less endearing habits ?
Quite often, the answer can be found in the Preferences Tree.

About now, I’d normally give you a list of topics I’ll be covering. First of all though, for anyone who has found their way here in a desparate search for how to tweak something specific in this tool, here are links to the relevant sections of the post :

Note that the steps for navigating from the menu to the Preferences tree is included in each of these sections.
If you’ve the time and inclination to read the post from the top, I hope that you’ll forgive the repitition. Oh, and you’ll probably also still want to know what I’m going to cover :

  • Customising the Look and Feel of the Code Editor
  • Getting SQLDeveloper to Format your code just the way you like it
  • Saving yourself lots of typing with a code template
  • Customising SQLDeveloper Utilities defaults because life’s too short to be ticking boxes
  • Miscellaneous tweaks to make you feel more at home
  • If all else fails, ask Jeff Smith

One more thing before we get started. This post is intended to show you where these settings are. The values I set them to are according to my preferences which may not be shared by everyone (anyone ?) even though they’re absolutely the right way to do things because…well…they just are OK ?!

Right then, from the Tools menu select Preferences…

Continue reading

SQLcl ALIAS – because you can’t remember everything.

I want to find out which file is going to hold any trace information generated by my database session. Unfortunately, I keep forgetting the query that I need to run to find out.
Fortunately I’m using SQLcl, which includes the ALIAS command.
What follows is a quick run-through of this command including :

  • listing the aliases that are already set up in SQLcl
  • displaying the code that an alias will execute
  • creating your own alias interactively
  • deleting an alias
  • using files to manage custom aliases

Whilst I’m at it, I’ll create the alias for the code to find that pesky trace file too.
Continue reading

Easy Listening with Datapump in the SQLDeveloper DBA Module

There are a number of ways to transfer data between Oracle Databases, one of which is to use the PL/SQL Datapump API – DBMS_DATAPUMP.
If you wish to avail yourself of this utility but find the syntax a bit fiddly, you always have the option of getting SQLDeveloper to do (most of) it for you.
What we’re talking about here is how to persuade the SQLDeveloper DB module to :

  • Create and execute a custom Datapump export job
  • do most of the work creating an import of a subset of the exported data

Continue reading

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

Disclaimer
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

Automated Testing Frameworks and General Rule-Breaking in PL/SQL

If there’s one thing that 2016 has taught us is that rules (and in some cases, rulers) are made for breaking. Oh, and that it’s worth putting a fiver on when you see odds of 5000-1 on Leicester winning the League.

Having lacked the foresight to benefit from that last lesson, I’ve spent several months looking at Unit Testing frameworks for PL/SQL. In the course of this odyssey I’ve covered:

Update : utPLSQL version 3 is now with us. It is very different to version 2. If you want to know more then have a look here.

This post is a summary of what I’ve learned from this exercise, starting with the fact that many of the rules we follow about good programming practice are wrong…
Continue reading

(Almost) Everything you ever wanted to know about SQLDeveloper Unit Testing but were afraid to ask

The Political fallout from Brexit continues unabated.
In the immediate aftermath of the vote, David Cameron resigned as Prime Minister, triggering a power-struggle to succeed him.
Secret pacts, double-crosses and political “assassination” followed in short order.
It was like watching an episode of Game of Thrones although, mercifully, without the full-frontal nudity.
As for the Labour Party, they have captured the Zeitgeist…by descending into the sort of internal conflict for which the word “internecine” was invented.

Following the current trend that promises are made for breaking, this post has arrived a little bit later than advertised.
I write most of this stuff on the train to and from work, and, as they have been unusually punctual of late, my Sprint 1 velocity is somewhat lower than anticipated.
So, with apologies for my tardiness…
Continue reading