SQL Developer 2.1: Early Adopter introduces PL/SQL Unit Testing

SQL Developer 2.1 early adopter is now available for download from OTN. Go to the main SQL Developer pages on OTN for more details and the links to the download site. The announcement includes links to a number of documents that you should skim through before you start.

An early adopter is your opportunity to take a look at a build and feedback your findings to the development team before the product is released as production software.

SQL Developer 2.1 includes lots of new bits of code, like the updated data grids. With these you'll be able to sort data based on drop lists and highlight records based on the data. To name just two of the many new features.
In addition to this the release includes a free Data Modeler Viewer. This extension to SQL Developer allows you to open any model created in the stand alone SQL Developer Data Modeler. You can also open the Data Model Viewer and then drag tables from the Connections navigator to the Relational model to create a model of your objects. These models can't be saved, but you can drag the tables around on the diagram to see how the relate to each other and to see the tables and foreign key constraints on a model.

Perhaps the most significant feature of all is the new PL/SQL Unit Testing Feature. Using this, you can build unit tests for your PL/SQL code stored in the database. The unit tests are saved in a unit test repository and can be rerun when needed. Ideally you can build up a suite of tests and rerun them when you code is changed to test and trap regressions. Walk through this Oracle By Example tutorial, available on OTN, to learn a little more about this new feature.

If you have any feedback on the new early adopter, then please use the SQL Developer forum on OTN.

SQL Developer Data Modeler: Working with Domains

I hope you found the little demo about creating update scripts with the Data Modeler useful. Do you ever work with Domains? If this is a familiar topic for you, then here is a quick demo to show you how to work with domains in the Data Modeler. It does not tell you about why you'd use domains in great detail. I'm really trying to keep these demos to under 6 minutes. When I do the demos for OTN, I think I might even cut this in two. Anyhow, there is a quick demo on Domains:

Screencasts and videos online

Please note I added "Numeric" for one of the domains I added and should have entered the Precision and Scale. In this example, the DDL will only generate NUMBER, not NUMBER(8).

Updating the Database Using Detail From your Data Model

One of the predominant questions that has come our of the forum is how to update the database with the changes you have made on your relational model in the Oracle SQL Developer Data Modeler. I have created this brief demonstration to illustrate what we have tried to explain on the forum.

Free online screencasting tool

If you roll your mouse over the screen of this demonstration you'll notice a icon to the bottom right-hand corner. This will allow you to watch the demo in full screen, which may be easier. The demo is 5mins 27.

Listen to Customer feedback on the Data Modeler

Maggie Tompkins of the US Marine Corps, a long time member of ODTUG and well known advocate of Oracle Designer, has been speaking to Kimberly Billings, from Oracle, about her experiences with Oracle SQL Developer Data Modeler. Maggie talks about they felt about the new product and how they were able to move their data models from Designer to the Data Modeler.
The MP3 is available to listen to, from the www.oracle.com/podcasts pages, or directly from here.

Data Modeler connects to the Database...(another online demonstration)

The last few demos I have done have been about creating models without the need for a database connection. In this next one you do need a connection to the database, as the demo is about connecting to and importing the detail from the data dictionary.

The demo talks about the diagram and the subviews created. It shows the logical model and details and then looks at the physical models that are created.

I do assume you have already created a connection and so if you're struggling with that, here's a screen shot of the connection dialog. Like SQL Developer, you can just make use of the thin JDBC driver to connect to the database, as I have in the example. You probably won't see the extra DB2 and SQL Server tabs I have, unless you've added the required drivers for those. If you want to connect to those databases, then you can add the drivers by using the Tools > General Options settings.

The dialog also supports connecting to your entries in the TNS file and you can add your own custom JDBC URL using the Advanced connection type.

Once you have your connection set up, watch the online demonstration to learn more about what you can import.

The Data Modeler supports Partitions

So the snag with a new product is to try to show all the features in the product to everyone as quickly as possible. I'm working on the online demos and training internal teams and getting the message out in general, but it's not fast enough. I'm looking forward to the day when you all start blogging about the product. Until that happens I'm afraid we're going to be dipping and diving all over the place. Just wait until SQL Developer 2.1 comes out and this blog becomes a mix and match!

We've recently had the question asking when we'll start support for partitions. The answer is right now. The product already does.

You need to understand the difference between the relational table definition, and the physical definition.

When you start creating the relational model, you create the tables that make up the diagram. The details are listed under tables under the relational node, as shown in this first image. (Click the image to see a full screen shot)

Invoking the properties here for the table, provides a database independent view of the table. So you can specify columns and datatypes and make use of domains at this point. What you can't do is specify the schema details or the other physical properties, like the tablespaces or partitions. This is because not all databases support the same set of physical properties.

The second screen shot shows the property dialog for the table in the relational model. Here you can add columns, set primary or foreign key properties, the datatypes and domains. There are entry point for indexes and a variety of additional features. But you do not set any database specific implementation details.

If you expand the physical model under the main relational model, (select Open from the context menu and select the database you want to support) and then expand the node. This is also shown in the first image above. Now you can view or add partitioning details.

Below are two illustrations, one showing the partition order in the Sales table, which is partitioned by range.

The last of the screen shots displays another partitioned table. The illustration here shows the drop list to show the types of partitioning supported.



The mantra is: The logical model is central, if you like is the core of the product. Each logical model supports one or more relational models. These visual models are database independent. Each relational model supports one or more physical models.

What the next demo to come out later this week, where we import for the data dictionary. That demonstration shows the multiple physical database support.

New Customer Article on OTN: Getting Started

So there are users articles and there are Oracle product manager articles... sometimes it's handy to get the perspective from someone outside Oracle. Here's one of those opportunities.

If you're just starting out with the Data Modeler, take a look at this latest article just published on OTN. Written by an Oracle DBA, it's a very brief review of how you can connect to an XE database and quickly create a data model using Oracle SQL Developer Data Modeler. His initial experience was with the early adopters and then the production release. It follows on nicely from the last two demos I talked about where you do not need to have a database to create the model. Here you do and is the next demo I'm working on.

Still no Database? Build a Data Model from a DDL script

In my last post I said that you can use the SQL Developer Data Modeler to build a data model without needing a database, all you need to do is start drawing. Well, you can also create a data model, without a database, but with a DDL script. The Data Modeler page on OTN has a number of the DDL scripts that you can use for this purpose. On OTN they're in zip files for easy download; all you need to do is extract the DDL file and then using the Data Modeler, import the DDL file.

This quick online demo shows you how you can import one DDL script to create an initial relational model and then use the Data Modeler to add the contents of a second file.

Next week we'll start work with a database, so if you haven't got access to one, now is the time to download and install Oracle Express Edition, to use as your sandbox. The next two demos I plan are to connect to the database and import a model and of course the one that follows has to be how to create those update scripts.

Data Modeler: Getting Started - You don't need a database!

If you are new to the latest tool from the SQL Developer team, then a short live demo might be just the thing you need. The great thing about the Data Modeler is that once you have downloaded and unzipped the product, you can just get going. No need for a database or a connection, just unzip and click the datamodeler.exe to start the product.

Now once the product is started, there is no need to create a project, you can start drawing. Click the tool bar button to create a table and you're off and creating.

Watch this 5 minute online demo to see a few extra tidbits of detail, such as controlling the naming of primary and foreign keys and a brief look at interacting with Design rules.

Extensions to SQL Developer

I'm pleased to say that there are a few folk who are starting to write about their extensions to SQL Developer. I know there are people who are extending - indeed some customers are building quite extensive extensions , but not a lot of folk are telling us about their experiences.

John Flack recently did a talk about his experiences at the ODTUG conference in Monterey Bay and he has now posted his work. He talks about it on the ITToolbox site, and invites you to keep an eye on that as he plans to add more in time. He's posted the paper on his own site here. If you're building extensions to SQL Developer, or thinking about it, I'd encourage you to take a look, as John had some good examples in his talk and you might be able to pick up an extra tip or two. When I do the demo at events, I tend to add extensions for sub partitions or an extra node in the navigator for dimensions or similar. John has added a node for his instance data. This has the useful advantage that he can drill down through the instance data, using the navigator.

A Piece of SQL Developer News: 1.5.5

We released SQL Developer 1.5.5 on OTN yesterday.

You may be wondering why we have had so many minor releases of SQL Developer. The main release with all the new functionality is SQL Developer 1.5 and we published a list of the new functionality that went into 1.5. Following a main release we've typically had a significant patch release, which in this case was 1.5.1. The next three releases were minor and while they have each had a number of additional bug fixes, each had a specific role.

1.5.3 - our first Japanese translation
1.5.4 - we added the rest of the languages we'll be supporting Japanese, Spanish, Italian, German, French, Brazilian Portuguese, Simplified Chinese and Korean
1.5.5 - this is the release that is scheduled for 11gR2 and is also on the media pack with the new Oracle SQL Developer Data Modeler.

...and each pulled in a few issues that had raised their heads on the forum, along with few others. Methinks it pays to be involved and active on the forum.

The Data Modeler is drawing lots of interest, which is great news. We've just published a Pricing FAQ on OTN as there has been some confusion on that.

Oracle SQL Developer Data Modeler is Production

Well,well, well... it's July and in the UK the temperatures have reached the 30's. This might be normal for many of you, but for us, the gardens, the houses and the dogs are not quite used to it. Now for those of you thinking that this has become a biannual weather blog, you might not be far off. Since the last was in January and it was talk of snow...

So the reason for the lack of "blogginess" is multi-fold, some is that I have been working on our new product in addition to SQL Developer and some that I am writing a SQL Developer book. So it's not so much lack of material as lack of hours to write some more...

Now I do have so much to share with you, that I'm really keen to break this silence and get more words to paper (screen). Also there are many lovely little features in SQL Developer 1.5.4 that I find many of you don't know about, when I show them at events, that I should be doing short features more often. Mind you here's a blog on REAL-TIME SQL Monitoring, a feature that I wanted to write about for ages and Doug Burns has done it instead. (Thanks Doug) I've also been sent a list of blog topic suggestions from an attendee at one of the recent events I attended, so there really is no excuse.

Now that new tool: Oracle SQL Data Modeler. It's production! We published all the related material yesterday and hopefully the news won't be overwhelmed by all the Oracle Fusion Middleware 11g annoucements happening this week. Still it's all good and all good news.

You can download it from the main OTN site, as it's a featured download there, or you can go to our Data Modeler homepage for links to the download and the FAQ and other supporting technical docs and examples. This page will be updated fairly regularly as we add more bits of collateral. Remember too to use the SQL Developer forum if you have questions, and when I return from my blogging holiday, I'll mix SQL Developer and Data Modeler news and details here.

Frost on the Ground and New Year Ahead

Happy New Year to you all! May 2009 be a great year. I know our newspapers are full of gloom, but I'm looking forward to the year and some of the promise it holds.

2008 was quite a year for us in the SQL Developer team. I took a month off in March to work with children in a Delhi slum and then hit the ground running on my return as we were working on bringing a new product on board and the challenges that brings. After that I was back on the road talking at as many conferences and events as we could fit in. I'm really lucky in that our development managers, Kris and Barry, will travel and talk at these events too and so I'm not so stretched and SQl Developer gets to more events. I'm hoping to get more folk involved from other teams this year and so we can settle down to working on ensuring the features you request are in the product.

Conferences I talked at in 2008 were RMOUG, in Denver, ODTUG, in New Orleans, OOW, in San Francisco, SOUG, in Glasgow, Scotland, SAOUG, in Pilanesberg, South Africa, SOUG, in Switzerland, Oracle Develop, in China, UKOUG, in Birmingham, England, DOAG, in Nuremberg, Germany. While the list seems long, it is missing a few obvious spots! South America, Australia, New Zealand, Canada and great chunks of Europe and Asia. Perhaps if you live in an area where there is a user group and you know a bit about SQL Developer, you should consider doing a presentation. You'd be amazed how much you learn and how little things you show folk can be really useful.

Of all the talks I do, the one on creating user defined extensions is the most fun. There are always folk in the audience who suddenly have an idea and want to go home a try them. I'm hoping to see some of those ideas come to the fore this year. Let's hear about them. I'd also love to see your laundry list of places you talked at and what you presented (On SQL Developer of course)

Of course I should mention that we started to talk about SQL Developer Data Modeling and by the end of 2008 had released our second early adopter. We've had over 10,000 downloads of the beta software, so we're really excited that there is an interest and that many of your are testing and sending us feedback. Thank you! If you haven't tried it yet, try now. (That link takes you to OTN with viewlets and white papers and the link to the download.)

SQL Developer in 2008
We had planned to go from SQL Developer 1.2.1 to SQL Developer 2.0, but then the list of user requests on the SQL Developer Exchange was so long, that we decided on an interim release and so SQL Developer 1.5 was born. As ever we followed with a patch release to fix a few issues and so 1.5.1 was the main release of the year. SQL Developer 1.5.2 does exist, it's the release that is part of JDeveloper 11g, which was production soon after Oracle Open World in September. The next release planned was to address our translations and so SQL Developer 1.5.3 was purely a translation drop, but we soon realized that not all the languages were quite accurate and as Japanese was the most requested translation release, we were able to release our Japanese build, SQL Developer 1.5.3. So although 2008 was the year of 1.5.x, we will be slipping another 1.5.x drop into the start of 2009 with the other translations in the build. This'll mean that SQL Developer supports 9 languages.

Mountains in the Snow...
I ended my year of travel in the mountains in Scotland. If you're going to walk on frozen ground, then I recommend you do that there! Actually I ended the year climbing up though a temperature inversion. Not a big deal if you're an 'plane or climbing in the Alps, but a feat in Scotland. What a day! We walked up through the mist and then the clouds onto the top of the mountain and bright skies. The white on the rock is feathery ice, not snow.

SQL Developer Events in 2009
It's probably not going to be the "year of the conferences", but there are a few to add to your diaries now:
Oracle Develop: Moscow (4-5 Feb)
Oracle Develop: Prague (10 - 11 Feb)
RMOUG: Denver (10 - 12 Feb)
Collaborate: Orlando (3-7 May)
ODTUG: Monterey (22-25 June)
OOW is in October this year in San Francisco.
That should keep us going for a while.

With the exception of Collaborate, we'll have hands on sessions at all the events. For the above events, the speakers will be Barry, Kris and I. We'll also have a few Oracle Developer Days, which provide hands on sessions and will be run by local staff. Details for those will be on OTN.

SQL Developer in 2009
So the piece you're probably most interested in - what's up for 2009?

Well, our first release will be soon and that's the 1.5.4 release I mentioned above. We're dependent on getting all the translated files back, and then we can go, but it shouldn't be too many months.
Then we head for SQL Developer 2.0. This is our "modeling" release. I use the quotes, because we'll be releasing SQL Developer Data Modeling as a stand alone release - a purely data modeling product, so that those of you who don't want to do any SQL or PL/SQL coding can just model. (and those of you who don't want to model, will not have that included in SQL Developer.) So the plan is to release 2 products, SQL Developer 2.0 and SQL Developer Data Modeling, at the same time, later this year.
If you're not a Data Modeler, don't despair, SQL Developer 2.0 will be including a number of smaller requested features and many updates to the features that are already there.

Requesting New Features
Before I close, if you're still there, I'm seeing a number of features requested that are already in the product. This means that you're not finding the nuggets of wealth ;-) in the product. If you have features you love to use or find a few things that you struggled with and then solved, why not write it up? We have a blog we'd love you all to participate in: http://sqldevelopersig.blogspot.com/ It only has my first entry in it, and I'd love you all to add a piece. You just need to let me know and we can give you access.

Otherwise, I'm hoping, but not committing to, writing a few more little entries here, so that you can enjoy the benefits of the features the developers have added. (According to my decline in blog entries over the last few years, I only need to do 4 this year!)

Have a good one.
Sue