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.