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.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

2 Comments

  1. Hi Jeff
    One thing I do not like about the new dialog box on the bottom is that there is no flexibility over how much space it takes up. It can either be minimized or it takes up 60% of the vertical height of the panel on the right.
    99% of the column definitions (and I presume the same for most people) that I create are simple data types, which do not require space XML schemas or identity info. I just need enough space to select a column type, size or precision/scale.
    I would like to have more space for the list of column names, as many of my tables will have more than 10 columns and I prefer not to do a lot of scrolling.
    It would be great to be able to control the bottom panel – rather than just a binary expand or collapse, that the developer could drag the panel to fit 10%, 20% or whatever he/she finds most suitable for the task at hand
    Thanks !

    • I can see what you’re saying with the 60% or nothing, but I’m confused.

      If 99% of your column definitions don’t require it, what’s wrong with leaving it collapsed except for the 1% of the time you need it?

Reply To Alan Lawlor Cancel Reply