ThatJeffSmith

New and Improved Create Table Dialogs in Oracle SQL Developer 4

Creating a new table in Oracle SQL Developer

Creating a new table in Oracle SQL Developer

You may not spend a lot of time creating tables in your database, but if you are performing that task, you don’t want your tool to slow you down. And while many of you may design your tables in a modeling solution or even code them by hand in a worksheet, there are still some of you out there that are using our Create and Alter dialogs.

And that’s what I’m here to talk about today.

The What’s New goodies for this area in the product boil down to two categories:

  1. 12c Stuff
  2. UI and Productivity Enhancements

Let’s look at the UI stuff first.

UI and Productivity Enhancements

So before we look at what’s what in version 4, let’s take a quick look at how it is in today’s production copy of SQL Developer v3.2.

The Advanced Create Table Dialog in Oracle SQL Developer v3.2

The Advanced Create Table Dialog in Oracle SQL Developer v3.2

As you define columns, they go into a ‘box’, and then you set the column properties. Creating many columns in a short amount of time was a bit challenging. Having to take your fingers off the keyboard to reach for the mouse is something I like to keep at a minimum, and we felt like we were failing this test.

The screen itself just has a lot going on. The radio buttons to choose the table type are kind of squeezed in there.

Now, here’s a look at the same dialog in version 4.0:

Less going on, breathe and relax.

Less going on, breathe and relax.

Now the table type is in a drop-down control. The columns are all listed in a table-like panel. You can quickly tab through the column properties and set the basic bits.

For the more advance stuff you can click on the panels on the bottom – which are collapsed by default.

A Tweak for Complex Data Types
When you’re going out of the ‘normal’ data type territory, you need to browse the database’s set of user defined types. I’ve found that most of the time, this is to create columns defined as SYS.XMLTYPE.

So in version 4.0, this is the default selection.

The goodie box from the XMLDB team in no way influenced this change.

The goodie box from the XMLDB team in no way influenced this change.

You can of course always set the schema context back to your schema, or browse the data dictionary until you find what you need.

Ok, I have bills to pay, let’s talk about the Database 12c enhancements you’ll see here.

12c Stuff

The biggest thing you’ll notice is that when dealing with numeric columns, you can now take advantage of the new IDENTITY support.

No compromises from leaving the old sequence-trigger paradigm!

No compromises from leaving the old sequence-trigger paradigm!

Additionally, you can define VARCHAR2, NVARCHAR2, and RAW data type columns as having a new maximum length of 32767 characters – that’s 4k if you’re wondering why it’s a really weird number to pick.