The contents of this blog are mostly about development tools; early postings are all about Oracle SQL Developer and SQL Developer Data Modeler written as product manager for these products. There are also the occasional forays into travel, conferences and a mention or two of the Delhi missions I have done.
From October 2011, the entries will still have a SQL, PL/SQL and application development flavor using new technologies, as I am no longer with Oracle. Why not stay and read awhile?

Followers

Subscribe To

Follow by Email

My Other Blogs

About Me

11 August 2006

I didn't know you could do that!

Have you ever used a tool for a while and then suddenly stumbled across something new? It's like opening a surprise gift or opening the window in the morning and seeing unexpected snow outside! Pure fun.

I started my day as normal by sorting out the bits of work I need to do and trashing unwanted mail and spam when I was distracted by a message about another product. I broke 2 'personal rules' one was to start playing with the piece of code and the other was to start working on something not on my list of things to do for the day! Well, it's Friday - that'll be my excuse.

The note was about handling external tables, so I thought I'd run the supplied code through SQL Developer, to see how well we handle it, and I found a whole new dialog! Maybe I shouldn't be admitting that, but I wondered if you'd like this little gem.

Creating an External Table and Loading Data

Here's some background first. Verbatim from the Oracle Documentation: "You create external tables using the ORGANIZATION EXTERNAL clause of the CREATE TABLE statement. You are not in fact creating a table; that is, an external table does not have any extents associated with it. Rather, you are creating metadata in the data dictionary that enables you to access external data."

In order for you to be able to replicate this exercise, I used the example offered by the Oracle database doc.

First you need these two files created and stored somewhere in a directory.

What we want to do is create a table that accesses this external data. i.e. an external table. Once created, the Oracle doc then moves the data from the external table into a table in the schema, to complete the exercise.

First we set up the directories. This syntax is not quite straight from the doc, but nearly. The only changes I made was to use my own directory structure and of course I'm using SQL Developer:

So now we need to create the external table for our HR schema. First, switch to the schema HR and then, using the context menu, invoke the Create Table dialog. This is the initial screen:

You can fill in the columns here, or just switch directly to the advanced dialog. Once you've switched you'll see the Table Types property. When I demo SQL Developer, I always highlight these options for users, but as I don't use external tables, I typically continue the demo by creating a regular table with constraints etc.

Once you select the Type External, your dialog changes. Now you can populate all the properties asrequired. (The full code is below)

Is the data there? Can you query it and access it as usual? Yes, yes and yes! Try this: Expand the Tables node in the Navigator and drag the ADMIN_EXT_EMPLOYEES table onto the SQL Worksheet and execute your query.

The Oracle doc finishes the exercise by copying the data from the external table into EMPLOYEES with the following command:

There's a rather weird UI for precision & scale. They are both presented in the same scrolling region. The arrow buttons to the right of the Precision control aren't for incrementing/decrementing Precision, they're the ends of the scroll bar for this region. Scrolling down will bring the Scale control into view. Or you can increase the size of the whole dialog.

Agree on the lack of options for the Access Parameters - they're the tricky bit. Still, nice to see early support for External Tables, which are a great feature.

Thanks for the feedback on this. It's useful to have the kind of feedback you have both given. I'll pass this on to the developers. While they might not be able to address the specific comments yet, knowing the sticking points is useful.

I'm never sure if you'll come back and read comments, as tme has passed, but in case you do. The scrolling region for the precision and scale happens in certain circumstances. If the real estate on your screen is limited then this area is squeezed a little and the scroll bar appears. All you need to do is resize the dialog slightly and that will go away. The reason this little region even has a scrolling bar, is becuase some complex datatypes have more vlaues and so you'd need to be able to move beyond the two.