So What's Different Then? Are you a PL/SQL Developer?

Microgen's tools are new to me, and having worked with Oracle for so many years, I need to see where they fit in and why they may be of interest to you. I'm going to do another video to show you more "How To..." bits, but I thought the screenshot on our website might go a long way to helping you understand. The graphic on the left hand side is what you build, the code on the right hand side is what the product produces. I think it's really neat.  The main idea is that business end of the procedure can be discussed with a business user.

Take a look at Tristan's blog entry today, where he talks about just that "Why Microgen DBClarity Developer?" He's the product manager and has worked with customers and the products for some years.

There is a Free Download of DBClarity Developer (Beta), if you'd like to try it out.

Microgen DBClarity Developer: Getting Started

"Microgen DBClarity Developer enables the graphical definition and implementation of SQL queries and procedures” http://www.microgen.com/dbclarity/

What does that mean? Sometimes it’s difficult to get passed the marketing as you try to develop a real understanding of what a product can do for you. In the case of DBClarity Developer, what do I mean by a “graphical design” when it comes to writing SQL code? Lots of database-related products allow you to create SQL queries using a drag-and-drop approach, so what’s new here? Well, before I get into all that, you should take a look at the product.

It’s always been a bit of a challenge for me to tell the story of a product in 5 minutes! I tend to be overenthusiastic while I attempt to show users every available feature. That may be fine if you’ve used the product in question before, as this approach can help to fill in the gaps, but if you’re new to a product, it’s overwhelming at best. With this in mind, I’ve created a short video (Posted on YouTube) to walk you through the first few minutes of working with DBClarity Developer.
The video is specifically a “Getting Started” demonstration and is designed to help you get up and running, once you have downloaded and installed the product. You can see the various examples we have provided with the product, there is a brief product tour and I show you how to create a database connection and review the SQL queries we have provided. I also show you how to build the tables and set up the demo environment associated with the tutorials. Once you have watched the video, you can start creating your own queries or watch the video on how to create your first query, which you can find on the DBClarity Developer website. http://www.microgen.com/dbclarity/

Updates, News and Beta Software

Those of you who follow my blog, know that I write from time to time. I started blogging back in the day when I was working on modeling and the SQL support offered in Oracle JDeveloper and then in 2006, started this blog with the launch of Oracle SQL Developer and then later to SQL Developer Data Modeler. Recently my blogging dropped right off because, after 19 years at Oracle, I decided to “hang up my boots” and try something new. Although taking photos in Cornwall really appeals, it seems that when we decide to start fresh, perhaps we don’t venture too far from where we’ve been before- well, it is true for me.


After a restful, technology–free 2 month break, I started in a new position at Microgen, a company based in the City of London. (I can see St Paul’s Cathedral from the window!) I’m in the Microgen Aptitude Solutions Division; so still in technology, still working with products that build applications and still talking to databases. My new laptop has SQL Developer on it and I can connect to an Oracle Database!

Microgen DBClarity Developer is Beta

I’ll talk a little about the tools and technology I’m now involved with, in the weeks and months to come; today I want to tell you about a beta program we’re running for Microgen DBClarity Developer. This new product is focussed on people who want to query the database, by writing SQL queries and procedures, but who are not necessarily familiar with SQL syntax. The idea is that through the graphical interface IT and Business users can work together on gathering data. There is an overview on the website: http://www.microgen.com/dbclarity/

Consider writing a SELECT statement for a correlated query; if you’re a DBA, SQL or PL/SQL developer you can do this faster than I type these sentences, but if you’re with someone who knows what information they want from the tables they have access to, more complex SQL Queries may be daunting to them. If you can represent your queries graphically to them, then the whole discussion may become much easier. This tool is not a SQL Developer replacement, so why not download it, take a look and see what you think? I’d love to hear your feedback. I’ll be back soon with tips on how to get started and how to build a few basic queries. The software is shipped with a set of tutorial examples, so you can see what I mean quite quickly. Here’s how:

1. Download the software from here: http://www.microgen.com/dbclarity/

2. Expand “My Projects” and double-click to select the DBClarity Tutorial of your choice. (This opens a new tab set with the project selected.)

3. The tutorial projects are seeded with a number of defined objects. (I prefer to use the Dictionary View. You can switch to this by selecting the view choice icon just above the project. Once you have switched views, you’ll see a list that includes SQL Rules, SQL Expressions etc.)

4. Whether you switch views or not, expand the SQL Rules, select and double-click to open the rule. This displays a visual representation of your SQL Query.

5. To see the SQL Query, select the Show SQL icon.

For more help and information, take a look at the set of tutorial slides related to the examples on the website.

SQL Developer Data Modeler 3.1

As for my history…I see that SQL Developer Data Modeler 3.1 is now beta. Have fun with that!

Expert PL/SQL Practices - Unit Testing

I wrote SQL Developer 2.1 while the product was under development.  As with all features in products, there is no guarantee that a feature will make a release until production, so it can be a nerve wracking process, writing, while things change as you write.  The UI is often the most fluid and so screen shots and menus need to be done as close to the release of the product as possible.  If you write a book after production you don't have the same problem, you just have to face the fact that in the IT world that if you do that, chances are that as your book is published, another, later release of the product is also published! It's a game of catchup. Regrettably, one of the features that I think is a killer feature in SQL Developer did not make it into that book.  Unit Testing was still under development as I was writing the book and the time required between writing and production of the book and the product was too great a gap.

All is not lost!

I was recently invited to write a single chapter for "Expert PL/SQL Practices".  A collaboration is great, because you only need to focus on one chapter, it's also harder to do, because styles vary and each author may have a different audience in mind.  So who ultimately buys the book is up for grabs. My chapter in the book is very much in the same style of my SQL Developer 2.1 book, (because that's how I write) and covers PL/SQL Unit Testing.  The book is available from Apress or Amazon and should be on the shelves soon.

New Events, Travel and Updates

The product managers for the Oracle Database Development Tools have been involved in a number of events in the last few months and I have been on the road with them.  We cover different types of events; the larger Oracle events, our own training days and Oracle user groups. The audience differs at the events, but generally we try to tell attendees as much about our products that we can. Here's how the recent trip break down for me.

The first of the event types that we cover are the OTN Developer Days.  These are a single full day event and include an afternoon of hands on tutorials. My last blog entry was about the Developer Day we had in Dallas.  The team have done a few of these; Dallas was followed by one on Toronto and one in Chicago.  Our next Developer Day is in Warsaw Poland, June 15th. (Oracle Technology Network Developer Day) and offer 4 tracks for the hands on: Oracle APEX, Database (SQL Developer, Data Modeler, XML DB and TimesTen), .Net and Java. 


Another important Oracle event we support is Oracle OpenWorld, which last year had a develop-centric component called Oracle Develop.  We have just taken Oracle Develop to Moscow and Hyderbad.  The great thing about these events is that you get the hands on sessions and most popular talks from Oracle Develop in San Francisco.  So if you can't attend OpenWorld in October this year, then watch out for the spill-over events near you.


The user group events we support vary from product manager to product manager, as typically we are invited to talk at these.   I am UK based, so doing a few trips in Europe is a good option for me and this year I was able to attend the Norway Oracle User Group and Orcan, the user group in Sweden.  My last trip was to Cleveland Ohio!  I really like these users groups because the audience is either using the products we talk about or are looking for the features we have to offer.  This means the talk is useful for the attendees and worthwhile for me!


I've just added the ODTUG KScope 11 logo to my blog, as that's the next big user group I'm off to.  This is a great event to attend, there is lots of new news and useful information and lots of useful technical talks, presented by people using the products they talk about. If you can get across to the West Coast in June, that that's an event worth attending.

Dallas: OTN Developer Days and New Features

Last week I was sitting cross legged on the floor with the children in a slum in Delhi teaching them English and learning a little Hindi, next week I'll be in Dallas, helping folk start up the latest Oracle Virtual Machine packed with new tutorials and software.  The only link between the two might be the fading henna tattoo, which the girls in the slum did on my arm! Maybe more, the children in the slums were enthusiastic and excited to learn and often at these Developer Days we find that too. Too often we don't get the opportunity of sitting down for a day and learning a whole lot of new stuff and an OTN Developer Day is a perfect opportunity for that. 

Take a look at the details, how to register and where it's being held in Dallas.  Kris and I will be there from the SQL Dev and Data Modeler teams, so come and see the new features and learn more!

Data Modeler: Working with Different Database Sites

SQL Developer Data Modeler supports the option to create different implementations (physical DDL scripts) based on the same relational model (tables, columns and constraints).  The Data Modeler itself can be visualized as a model!  Your complete design can be made up of logical (ERD) models, relational, physical models and indeed datatype and process models. If we just think of the logical, relational and physical, then one logical model can be transformed or forward engineered into one or more relational models.  (i.e your entities can be transformed to tables) and then each of those relational models can have many physical models.

So you may, for example, do a generic logical model for an airline application, but transform it to a number of slightly different relational models (applying different transformation rules or perhaps different glossaries.  Now once you have that relational model set, you can have a number of different physical models. Often we have a test, development and production environment and want to have slightly different physical settings for these. We can do that in the physical model.

So let's assume you have the relational model all set.  Add new database sites using the Tools > RDBMS Site Administration as shown above. This invokes the RDBMS Site editor. Here you can add as many new sites as you need. They can be reused, so there is no need to be too specific, you only need to add new sites if you want more than one, say Oracle Database 11g, physical model in a design. In the next image we show that three additional sites have been added for the Oracle 11g Database.(click on any of the images to see them in full size)

Now return to the relational model and create the new physical models. To do this, expand the Relational model and select the Physical Model node.  Right-click for the context menu and select New. This allows you to choose the database site you wish to associate with the model. You can do this for each of you test, development and production sites.  In the image shown above right, there are 4 different physical models.

Once you have made the physical property updates for each physical database, you can start the DDL generation.  Ensure you select the correct Physical model from the database sites in the DDL File Editor dialog before you start the generation.

Cloning Sites
Now each of these sites will have all the relational detail from the diagram, so the tables, columns, constraints will all be the same. You've created the different sites, so that you can have different tablespaces and possibly user definitions, but chances are you'll want your sequences and PL/SQL code to be the same.  You can do this by adding that content to one of the physical models and then cloning the others from this. Select the new physical model and right-click, select Clone From.  You can now select the model you want to clone.

SQL Developer Data Modeler 3.0 is Production

I'm please to say that SQL Developer Data Modeler 3.0 is now production.  The latest release is now available for download from OTN. There are various demonstrations available for you to get to know the new features and remind yourselves of other features.

See the main SQL Developer Data Modeler page on OTN for download links, new feature pages and demonstrations.

See the Oracle Press Release for more.

SQL Developer 3.0 Early Adopter Now Available

On Tuesday 19th January, the SQL Developer team released a third early adopter for general use. This SQL Developer 3.0 EA3 now includes the full Data Modeler 3.0, which is also running in an early adopter cycle. The significance of this is that you can open and edit data models from within SQL Developer.  If you are not a modeler, then this extension should not be in your way, but if you like to move between the Data Modeler and SQL Developer and prefer to work with one tool, you can.

The best way to start, once you have started SQL Developer is File > Data Modeler. If you are familiar with the Data Modeler, then you'll recognize all the menu options there.  Create a new model based on a DDL file or a schema in the database, then select > Import >...
The browser menu is available under View > Data Modeler > Browser and if you want to change any preference settings, just select Tools > Preferences > Data Modeler!

If you have any feedback on the integrated offering, then post a message on the forums: SQL Developer Forum or the Data Modeler Forum.

New SQL Developer 3.0 Webinar

I see that Lewis Cunningham is doing a free webinar on SQL Developer 3.0 for ODTUG in January.  Here is his post on the news http://it.toolbox.com/blogs/oracle-guide/sql-developer-30-new-features-webinar-43499 There is a link to register and a bit on how he uses SQL Developer in his day-to-day tasks.

Here http://www.odtug.com/apex/f?p=500:235:0::::P235_NEWS_ID:3061 is a list of free ODTUG webinars posted for 2011.

Check for Updates in SQL Developer

SQL Developer is an extensible product - that means that you can write extensions (Java or XML) and include them in the product.  We have teams at Oracle, not part of the SQL Developer development team, doing just that.  The Oracle TimesTen, Data Miner and Rdb teams are all examples of this.  The first Oracle team to write a significant extension, was the Migrations team  - the only difference is that they subsequently became part of the SQL Developer development team and so work on other features in addition to the Migrations work - but I digress...
There are teams outside Oracle who are also writing extensions for SQL Developer.  You can see a list of them here - SQL Developer Third Party Extensions. Listed on the site they're pretty harmless; you can look at the list of extensions, review the individual websites and decide to download and include the extension or not. Extensions can also be added using Check for Updates. If your company allows you to download software from OTN and other sites, then this is an easy way to get new updates and extensions. SQL Developer is no longer using this Check for Updates facility to update the core functionality.  We only use it to advertise when there is a new release available and if we have a full new extension for download.  We also use the Check for Updates utility to help you download and install the correct drivers to connect to some third-party databases. In the end the Check for Updates utility is most useful for our customers and partners who are developing extensions.

Yesterday I added a number of new extensions to the Check for Updates Utility and updated a few of the existing extensions. 

How to Install Extensions
Start up your current release of SQL Developer and select the menu Help > Check for Updates.... Navigate to Step 2 to search through the various update centers available. You may be prompted for Proxy details if you have a firewall, once that is confirmed you'll be presented with the screen below. If you have a local file, which you have downloaded or even created, then you can select "Install from Local File" and complete the install. For Oracle, SQL Developer or customer created extensions, select the "Search Update Centers.

The choices are as follows:

• Oracle SQL Developer: All version control extensions and  any announcements about the latest SQL Developer release.
• Oracle Extensions: All extensions created by Oracle teams external to the SQL Developer team. This excludes extensions we automatically ship with the product, like the TimesTen extension.
• Third party extensions:  This lists all extensions not developed by Oracle and includes some third-party database drivers.

All extensions are listed alphabetically and each has a description and version details. Select each extension you want to install and click Next.  Most extensions have license agreements associated with them. Read and accept each license.  Once you have agreed to all licenses, the extensions will be downloaded and installed. All extensions will be installed in the \sqldeveloper\sqldeveloper\extensions directory, where you installed SQL Developer.

Alert!
Oracle does not test these extensions and you should be aware of what you are downloading and installing. You should also be aware that some extensions are free and some have a free trial period. Most if the extension developers are active on the  SQL Developer forum. on OTN so if you have queries about the extensions, ask them on the forum.

SQL Developer 3.0 Does Schema Browsing

I hope you take an opportunity of popping over to Kris's blog - he's just done a piece on the new pdf support in SQL Developer 3.0 The pdf support is great when you export your data you can export it to PDF. But not just that - there are loads of settings to control and format the file. Take a look at his encrypted pdf blog for a taster.

Schema Browsing
I want to tell you about the Schema Browser. This is a great feature for those of you who work with objects you don't own. So for example, if you have access to run queries or update tables that have been created by another schema, in all releases, including SQL Developer 3.0, the way to review these tables is either through the Other Users node or by creating synonyms for the tables and then having the tables displayed via the synonyms in your own connection. So it's a little fiddly. In SQL Developer 3.0, we have added in a new schema browser. To find it, select a connections, right-click to invoke the context menu and select Schema Browser. This opens a new navigator for that schema. Now you can do a number of things.

Firstly, select the schema you want to work with. This is equivalent to finding the user in the Other Users node. Then you can select the object-type you want to work with. Let's say, for arguments sake, that you select tables - You can filter the tables listed. The filter option is the same filter dialog you can use to filter any of the nodes in the Connections navigator.

In the image, I have circled the first two options. The filtering criteria can be set by using the additional drop lists.
There is another filter - it's a client-side filter - and this allows you to quickly filter objects in the already filtered list. In my example, I have a number of tables in the filtered list, and then I have reduced the list further by using the extra client-side filtering.

Try it out.

SQL Developer 3.0 Does Table API

Well, I couldn't wait a day to do another post. Here is a feature you would not have seen at OpenWorld. SQL Developer 3.0 generates a Table API. Now those of you who go far back enough will know other tools that did this and we have had a number of feature requests to add in a table API generator, so here we go, a TAPI!

Why a Table API? Well, some teams would never dream of letting developers insert, update , delete or generally work directly with tables. Many teams want to track who, what, when, where events and having a Table API (I call them "table wrappers") is a start point to add in that kind of code. Here's how you'll find it:

SQL Developer 3.0 Early Adopter is Available

Same week, new post, different product, new early adopter release. It's busy bees in the development teams as everyone works to settle the releases. You may have heard that we demonstrated SQL Developer 3.0 at Open World in September, and many of you felt the release had to be imminent. Well it was, but we did want to add in a few updates following OpenWorld. So now you have a second product on an early adopter release cycle. There are a few demos on OTN and we get more out to in the next few weeks. Watch Kris's blog and mine in the next month or so and we'll also be bringing you new feature news. In the meantime - download the software. Please provide all feedback on the SQL Developer forum (not here) where the developers will be camping out and helping you out.

SQL Developer Data Modeler 3.0 - Early Adopter Release

The news just keeps getting better. At Oracle OpenWorld, we announced that the Data Modeler is now a free product. This was very exciting for us and for the many customers who came to talk to us at the show. I'm really pleased as there is no excuse for not modeling your database, or at the very least to connect to a data dictionary and see how your tables are all connected - visually!

The news that the product is free means more than that to us, it now means that we can run a public early adopter program and so you can go, today, and download SQL Developer Data Modeler 3.0 Early Adopter 1. All the features that you used in SQL Developer 2.0 are still there, in addition there are a few new features that I think you'll like. The best bet is to go to the Data Modeler home page and take a look at the online demonstrations available. The first three walk through the new functionality in the product. They're about 12 - 15 mins each and I show the new features and explain as much as I can it he given time. The last two are short viewlets on two aspects of the current functionality. Of course if you know nothing of the product, then just look at the brief online Data Modeler 2.0 demonstration. This is for the current production release, but it'll get you started and the flow of work is the same.

A Focus on Unit Testing and new supporting material

SQL Developer's Unit Testing feature is doing well. Those who are using this new functionality in SQL Developer 2.1 are finding it very useful and are already asking for more functionality. (Isn't that the way, once you get to grips with a product you can start saying "wouldn't it be good if we could also...") so the SQL Developer Exchange is starting to build up a few requests.

Unit Test Online Demonstrations
For those of you not yet familiar with PL/SQL Unit Testing or have not yet quite got to grips with this feature, we have published a number of new short online demonstrations. You can find these from the Online Demonstrations link on our main page on OTN. All the unit test demonstrations are grouped together, so you can pick the one's you're interested in or watch them all.

Unit Test Webcast
If watching the online demos is not your thing, you can watch the 30 min webcast. The latest webcast was on Unit Testing, which has now been recorded and is available on OTN for you to watch and listen to. All our webcasts are on OTN on the Webcast Series.

SQL Developer 2.1 Webcast is Published

I started a web cast series a few months ago and am still not sure if this is the right way to spread the news and provide feature demonstrations to the customers. Firstly I'm alternating between SQL Developer and the Data Modeler and so if you're only interested in one area, this is quite a gap. Also there's the whole time zone thing... Where I can only reach half the world in daylight/work hours. Let's see hopw it goes int he next few months.

Anyway, I apologized for the chaos caused when I didn't run up for the event in April, due to longer travel home after the volcanic clouds caught us off guard. Kris ran the event, but not everyone was aware of the dial in changes and so missed the event. I have now recorded and published that session and you can find it on the web casts page on OTN. The session is on SQL Developer 2.1 and the demos cover the new features. It's quite a big file to download, but once done, you can listen/ watch the session at your leisure, with having to worry about Internet latency.

I will not be doing a live web cast on Wednesday 19th as planned. This will be a recorded session and the file will be made available for download from that same web casts page.

Updating the Database with SQL Developer Data Modeler

This may be one of my most frequently asked questions - "How do I create the "alter" statements to update the database, using SQL Developer Data Modeler?" Most people know how to use the the Data Modeler to generate DDL scripts to create new objects, but not everyone knows how to create the DDL required to update the database. We have created a new online tutorial which walks you through this process. I did mention it recently when I wrote about the new Learning Library, and we have now also added the link to the the Data Modeler page on OTN.

Data Modeler: Launches the First Public Classroom session

In March this year I attended the pilot of the Data Modeler (Instructor Led Training) class. It was fun because the course writer presented the class and the attendees came from various Oracle offices from around the US and Europe. We all attended for various reasons, but mostly because we'll be working with customers and SQL Developer Data Modeler. The group were/are experienced data modelers, familiar with other tools and getting to know the capabilities of the new tool. This meant a lively interaction throughout the week and feedback rolled into the final training product.

I was a bit nostalgic, as this was my old area, as I was first involved in training then writing courses and running these "train the trainer" events. Anyhow, I'm pleased to say that the course is now up and running and you can start registering for or requesting classes. It's a great class, covering all aspects of SQL Developer Data Modeler, including the Data Flow diagram, logical, relational and physical models, all in the context of designing and developing data models. There are lots of examples and we worked on and developed a number of different models, which kept it all interesting. I definitely recommend this event.

The easiest way to sign up for any Oracle University class is to go straight to http://www.oracle.com and click on the Education. (Alternatively use http://education.oracle.com)
Either way you end up on the education site and in the right hand corner you can search for the class you are after. If you start typing "Data Modeling" in the search list, then a drop list will allow you to select SQL Developer Data Modeling . The class is Oracle Data Modeling and Relational Database Design and the next available event is May, 10th in Chicago! It starts next week and so there is not much time to decide.
If you are not be able to make this one, you can watch out for future classes, or express your interest in a future date or location by providing feedback on the same course detail.

A Learning Library!

Oracle has added a new Learning Library which will help you when you're looking for demonstrations or online tutorials. This is a neat (Oracle APEX) application which basically provides a new user interface to the same Oracle by Example (OBE) tutorial content available on OTN. The difference is that you don't need to scroll down through lists of OBEs or search various sites to find online demonstrations.

You can find the Learning Library here: http://otn.oracle.com/obe. The material from my team is under the database section, so you can select that node and then add in your search criteria. For example, just add "model" to the search and you'll see the 3 current Data Modeler tutorials and the latest data Modeler tutorial, published just this week.
We also have a new SQL Developer 2.1 New Features OBE.

The library also lists online demonstrations, which are all typically around 3 - 5 minutes long, focusing on just a small feature area. This means that a search for SQL Developer material will result in tutorials and online demonstrations. It saves you wading through endless pages looking for help. Select "All Content" and add the search SQLDEV and you'll find quite a nice long list... Let us know what you'd like to see next!