Sue's Blog... again...

Welcome to 2012! Is it Spring in the Air?

2012-01-04T12:00:00.000Z

I'm not sure we've had a winter here, well in the south eastern reaches of the UK at least. It's true that we're walking the dogs in the dark, which means it's winter, but my hands don't stick to the icy metal railings, instead we're walking in the dark to the tune of the dawn chorus! Yesterday a severe storm wrenched trees from their roots and blew things about the garden, but we're still in double digit degrees (that's Celcius) and so definitely too mild to really be called winter; or am I finally getting used to the weather in the UK?

Still we're back and at 'em now I hope the break was good, restful and fun. I'm back in London working with the team preparing for the launch of new software. To that end I have done a mini series of videos on how to do a number of activities in Microgen DBClarity Developer. Instead of listing each video, I have created a YouTube playlist - the videos include "Creating Database Connection", "Adding files to Projects" and "Deploying Projects".

Have fun!

DBClarity Developer Beta 2 is Now Out

2011-12-21T17:12:00.000Z

What a funny old world this is, the more things change, the more they stay the same..., more of that thought another day. What I can tell you is that Tristan's webinar introducing DBClarity Developer is now available to listen to and watch. This is just over 30 minutes and should show you where DBClarity Developer fits into the scheme of things - specially if you're still thinking it's another SQL Developer or TOAD tool. Go on try it.
For a tool new to this market, it's interesting to see which companies are looking at DBClarity Developer and why they are - the story is the same - They have loads of developers, building ad hoc reporting queries and they want them to be efficient. (that's both the developers writing the queries and the queries themselves!) Now we know we can't all be SQL gurus, so if we can use a tool to produce more efficient SQL Queries then why not? So if you have 100s of developers writing ad hoc SQL reports, have a look at DBClarity Developer. Today is as good as day as any as we have just released DBClarity Developer Beta 2!

I've spent the last few days recording more demo videos and will publish a few of those in the next few weeks.

First Webinar on DBClarity Developer - Learn More!

2011-12-13T14:41:00.000Z

Conference season may be over and you may all be into the swing of the end of year activities, but things are still happening here. DBClarity Developer Beta 1 has been out for just under 2 months and we're about to release a second beta. Before we do that, we thought we'd show you more about the product and give you the opportunity of seeing what it does and to ask questions. Register for the live Microgen DBClarity Developer Webinar to be run on Thursday 15th December, and because this is a webinar, it's "at a city near you"; just join in, watch, listen, learn, and ask questions.

DBClarity Developer is a standalone product that provides a graphical interface for creating SQL queries, procedures and functions. The beauty of the graphical interface is that you can create the code graphically and let the product build the SQL, once done, you can revisit the diagram. It's great for maintenance because you don't need to trawl through lines of code, in an attempt to figure out what it's designed to do, and it's great for collaborative work, because the visual aspect helps when you're explaining how the code works. DBClarity Developer generates SQL code for Oracle, Teradata and SQL Server and works alongside your other development tools.

Why not join the webinar, take a look and see what it's all about? Register here: Microgen DBClarity Developer Webinar.

Working on a few more demos: EDFs and the SQL Editor

2011-11-04T11:21:00.001Z

I tried adding a new entry to my blog a few times since my last entry. My plan is to highlight a few key areas in DBClarity Developer so that you can learn a little more about what the product does. I have a list of around 10 topics, which morphs and expands as my ideas change. Having shown you how to “Get Started” in my first video, the next step is to write about the SQL Editor, as described in the presentation, Tutorial 1 - Introducing the SQL Editor , which is on the DBClarity website. There’s the rub..., for those of you who know me and my back ground, I can almost hear you say, “SQL Developer does this and more” or even “TOAD has a richer editor than this”, and so what am I up to? Well, that’s the point, this is a different product.

I have created a new video, Microgen DBClarity Developer: SQL Editor, which instead of focusing on the SQL Editor, shows you where it fits in, and then goes on to show you how you can start creating the visual queries, what we call SQL Rules. Take a look.

In the next video I’ll take a look at how to deploy your project.

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

2011-10-26T12:59:00.001Z

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

2011-10-24T12:27:00.003Z

"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

2011-10-19T11:24:00.002Z

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

2011-06-25T17:43:00.001Z

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

2011-05-31T13:46:00.001Z

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

2011-03-04T11:29:00.001Z

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

2011-02-11T15:12:00.003Z

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

2011-01-31T16:34:00.001Z

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

2011-01-19T14:12:00.001Z

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

2011-01-12T11:03:00.001Z

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

2011-01-05T16:47:00.000Z

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

2010-10-15T13:30:00.005Z

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

2010-10-14T12:25:00.004Z

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

2010-10-14T12:13:00.002Z

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

2010-10-12T08:49:00.002Z

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

2010-06-18T10:32:00.002Z

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

2010-05-18T09:43:00.003Z

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

2010-05-07T09:36:00.003Z

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

2010-05-06T10:41:00.004Z

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!

2010-04-28T17:03:00.004Z

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!

Trains,Taxis, Ferries and Apologies

2010-04-26T16:53:00.003Z

Norwegian User Group

The week before last I went to Norway to present two papers at a new user group. Well, the Norwegian Oracle User Group is not new, but has been running for a few years now. The fun thing is that it's growing each year and so there is now a steady audience. Interestingly enough it's a pretty focused audience of DBAs and Database developers, with the application developers noticeably absent. I think a new stream has potential, JDeveloper? Oracle APEX developers? perhaps you need to look into this event and suggest an application developer addition.

Anyway the novel thing about this event is that it's held on a boat (huge cruise liner /ferry that runs between Oslo and Kiel), so the audience does not wander off to play golf or swim on the sea, as they can do at some events. (Fine for the organizers who have the registrations, not so fine for those of us hoping to talk to a nice big audience.) Anyway, this was good. I had a nice crowd for SQL Developer 2.1 Overview and New features and a nice crowd for SQL Developer Data Modeler. The downside with the latter talk was that I tried to fit in why you should model, how you should model and all the features of the Data Modeler in a single session. I should know better!!

Apologies
I must apologize to those waiting on the phone or trying to attend last week's SQL Developer 2.1 web cast - to no avail. Kris stood in for me, but he had to use his dial in and conference details and we were unable to get the new details to everyone before the event. The reason I was stuck was because I had been out in Norway when the airports were closed. The web cast was not recorded, but I'll do a cut down recoding and add that to the web cast site later this week.

Delays
Following the conference and back in Oslo we had the news that there were no flights out that day and possibly the next. I didn't think much of it. How bad can it be, I'd had my lost suitcase found, so I could stay on a little and fly back on the next available flight. The rest is history... By Sunday there was talk that this might be weeks and by that stage the ferries and various channel crossings were fully booked at least until Wednesday. So we decided the best decision was to make our way home. Here is where the Internet is so powerful and the world a truly amazingly connected place. I sat down and started to plan and book a train journey home. With lots of suggestions offered by friends and passers by, between two of us traveling the same route, we planned and booked a route home.
Starting at 4am on Monday morning, we started on a bus and then a train to get to Oslo, where the journey really did start - then over to Goteborg (Sweden), then Copenhagen (Denmark), then, still by train, across to Germany (with the train ion the ferry) to Hamburg, across Germany to Cologne and then to Brussels and across to the UK London and home, arriving back here on Wednesday night. The time delay was due to some trains being full and having to wait for the later trains. It is the way to travel of course. Assuming you have a seat, this is a much calmer way of crossing continents, and you get a much stronger feel for the vast distances we cover when just hopping on a plane and swapping countries. In all, the journey was a good experience and most travelers in good spirit, with some tales more absurd than others.

SQL Developer Exchange - Not Just for Feature Requests

2010-04-12T14:13:00.002Z

Recently I wrote about the SQL Developer Exchange (http://sqldeveloper.oracle.com) , focusing on the new feature requests area. You may or may not be aware that there is also a Tips and Tricks area. Anything you you find useful and want to pass along, just add it here! The latest Tip is on working with SQL Developer and Linux. Srini has a few SQL Developer/Linux blog entries and he's added the latest blog entry to the Exchange and so if you are unaware of his blog and want a quick tip, take a look at the steps in the SQL Developer Exchange | Tips & Tricks section.

Steven Feuerstein's PL/SQL Challenge

2010-04-09T11:06:00.003Z

If you have not already seen this then take a look at Steven's latest initiative: "The PL/SQL Challenge " http://plsqlchallenge.com The Challenge was launched on the 1st April and so you can get involved right now. The Challenge should do a number of things, you might "meet" some new PL/SQL whizz-kids or indeed find you are greatly proficient yourself, I'm certain you'll learn stuff and have some fun while you're at it.

As Steven says
"The Challenge is free and simple: you play the quiz each weekday. We keep score. You get ranked. Every three months, the top-ranking players compete in a quarterly championship to award first, second and third prizes. Cash prizes, among others. But that's not all. Every month, we will raffle off other prizes to anyone who played the quiz that month. And the more you play, the greater your chance of winning. "

The site looks great and is easy to get around, you should be able to nip in each day and play. Take a look at the rules too, it all looks like good fun. What's more you'll probably learn bits too, with the Oracle database, rules change over the years, some of the questions and answers may well have you delving into an area you are unfamiliar with or even scratching your head with "I didn't realize that was true". Nice.

An Oracle Developer Day Near You!

2010-04-08T08:57:00.003Z

Oracle runs Oracle Developer Days (ODD) for all kinds of topics. Typically they're a 1 day event, jam packed full of information. Most start with a keynote session, and then offer two or more tracks, so you hear a general talk and then have a choice of tracks and subject areas to switch to. In these tracks the speakers go into more detail about the topic. What's more these are designed to introduce you to new subjects, so if you've always worked with one set of products, you can sit in on something quite new and learn. Some include hands-on sessions while others are all presentations, with a variety of speakers who demonstrate the features.

The Database Development Tools PM team are running a series of these Developer Days and the next one is in Orange County, USA. This is a 4 track event, with two main keynote talks in the morning and you get to spend the afternoon doing hands-on. Just bring your own laptop. Read all the details and register by clicking on this event link.

There is another planned for June in Reston, near Washington DC and another in August near Oracle HQ, near San Francisco. These Developer Days are also being run in EMEA (Europe Middle East and Africa) and in APAC (Asia Pacific), so you're sure to find one near you.

SQL Developer Exchange: Features Requests

2010-03-31T16:52:00.006Z

Every so often there is a query about the SQL Developer Exchange. (http://sqldeveloper.oracle.com) If you don't know about it, this is the site that we send SQL Developer and Data Modeler users to, in order to log feature requests, add reports and snippets. From the beginning its main function has been as a place to log feature requests and it serves as a really useful link between what we're planning in a release and what the community are looking for in the tool. Many users don't believe we use this site or take the requests into account, if you're one of them, you're much mistaken. In fact we changed plans to add in the SQL Developer 1.5 release based on all the feature requests that had come in.

I have now updated the filter on the Feature Requests page. This filters out all features that have been requested and are now in a release. To see them all, click the red filter cross to remove this filter.

Things to note: If you have logged more than one request in the feature and it's marked as "in release xxx", it may only be one of the features listed in the request that is in the release. It's easier for voters and for us, if you specify a single feature at a time and then give as much detail about what you have in mind. That way others can understand your request and support it or indeed, assign a low score to it.

Here's the list of Status values:

Scheduled for 3.0: This is scheduled for the next release. It means it's in the bug database and is assigned to a developer for the next release. This does not mean it will definitely be in the release, but it's got a good chance.
Accepted: This feature has been read, voted on and accepted as a future request. This means it is in the bug database, but is as yet unassigned, reviewed or written up. Some accepted features may make it into the next release, but not all will. For example, while fixing bugs for the patch release 2.1.1, the developers were able to clear up quite a few of the minor features requests that were related to the features they'd been working on. (See the latest Bugs Fixed list on OTN.)
Under consideration: We've looked at it and we're still thinking about it. This is not yet in the bug database. The next time we do a feature request review we'll revisit these. To add visibility to these, please add more votes and more detail.
Awaiting Community Votes: Hmmm, you really want this feature? Let's hear from others first.
Rejected: Speaks for itself. Nope we're not doing this. See the comment posted on the feature. I sometimes reject duplicate requests, so see what the comment states. If it's a duplicate, find the other one and vote there.
BUG - Log in Metalink: Again, the explanation is in the status. Seems a bit harsh that we won't log the bug from here, but this is not the role of this forum and we don't want that to change. We ignore any bugs logged here.
Closed: This comment/request is no longer relevant. Alternatively you say something like "The product is slow" This is not helpful nor measurable. We also mark Exchange related requests with this.

The Feature Requests Reports
By default the requests are filtered to show you only those requests that are not yet features of the product. Click the red filter cross to remove this filter and you'll be able to search on all requests. When I review the requests I often find quite a few requests for features that are in the product. This may well happen more as the product grows, so before you add a feature, look to see if it's not already been accepted or indeed, may already be in the product.
Dynamic Column Ordering
The interactive reporting in Oracle APEX also allows you to sort and filter the columns based on the column content. For example if you click on the Status column, there is a drop list that allows you to select a specific status or to set the order by on the column. When reviewing the requests, I set the status to Open, and then I order by the Weight. Features with zero weighting, means lows votes and so we generally focus on the high weight requests.
Using Actions on Interactive Reports
When using the Feature Requests page, you can use the interactive report capabilities. For example, click the little green cog next to the Go button, this invokes the action menu and select, for example "Selected Columns". You can use this include or exclude columns in the report. So you can add in the Product column, if you're just looking for the Data Modeler requests. This makes it easier to filter on the product.
Add New Reports
Finally, you can add your own filters and save them. To do this use the Actions menu to add a filter and then, using the same Actions menu, select Save Report. This means that the filter will be saved for you in a new report. Each report you save, gets saved as a new tab and so you can create a number of reports.

Extensions, extensions extensions...and finally the XSDs.

2010-03-29T11:40:00.003Z

SQL Developer is developed on an extensible framework. This provides great flexibility for you, the end user, and for teams in Oracle. For example, the Oracle JDeveloper team have built an extension to JDeveloper that provides integrated support for file based version control. With not too many changes, we are able to consume this extension. So when you start SQL Developer, you can open the Versioning Navigator by using View > Team. We only ship the extension for Subversion, but if you want to use CVS, Perforce or Serena Dimensions, then you can use the Help > Check for Updates menu. These additional extensions are listed under the SQL Developer section in the Check for Updates utility.

Extensions are not just Oracle extensions. Last week I updated the Extension Exchange page on OTN to reflect the new customer developed extensions available to SQL Developer users. As ever these extensions cover a broad range of functionality. Instead of itemizing them here, I suggest you visit the page and see if there is something that suits you. Note, not all of these are free. Some offer a 30-day trial license at which point the software ceases to work.
Instead of downloading and following the installation direction, some of these extensions can also be installed by using the SQL Developer Check for Updates utility. You'll still be walked through any licensing agreements, so be sure to read them. The licensing and pricing agreements are in these licenses. You'll find these extensions listed in the Third Party SQL Developer Extensions section in the Check For Updates utility.

There are also Oracle teams who are also building extensions, some working directly with the SQL Developer team, like TimesTen and others working independently, like the Oracle Rdb and Data Mining. In the case of the Oracle TimesTen extension, the extension is automatically shipped and installed when you install SQL Developer; you are only exposed to it if you have the Oracle TimesTen client installed. The Oracle Rdb and Data Mining extensions are both available for download from their sites on OTN. You can reach these through links on the SQL Developer Extension Exchange page. The Oracle Rdb extension is also available under the Oracle Extensions section in the Check for Updates utility.

It would be remiss of me not to mention the Migrations extension while I am here. This extension was built and integrated with SQL Developer some time ago, and so regular users will be familiar with the menu option. Like TimesTen, if you don't have the associated prerequisite files, the extension is not exposed to you and so is not intrusive. In this case, you need to have the required third-party database driver to see the extra third-party database tabs in the connections dialog. Some of these drivers are available through the Check For Updates utility under Third Party.

If you are inspired to write your own extensions then the Oracle Wiki is the place to start. There are examples for both the Java or the XML developer. We have now added the XSDs for the most commonly used areas. The availability of the XSDs is a much requested update to this site, so take a look.

Please post any questions you have on the SQL Developer forum and not as a comment here. There is a broad audience who want to learn about these and so it's better for everyone if the discussion is held there.

Data Modeler Overview and Demo: Webcast Recording

2010-03-25T10:25:00.004Z

Last week I did the first in the series of the webcast recordings I plan to do over the next few months. As product manager for both the Data Modeler and for SQL Developer, I have proposed alternating the monthly broadcasts between the two products . So I launched the series with the Data Modeler. Having trialed the webcast internally I was happy that the technology would stand up to the rigors of the international broadcast, as luck would have it, I must have done something wrong on the day and did not get the voice recorded. Technology - who'd have it? Actually - I would! After all these years, I still get a kick out of the fact that I can run a webcast, demonstrate a product and answer questions asked from locations all around the world.

Back to the Data Modeler demo and webcast - I reran the webcast this time recording the sound too. We now have a webcasts web site and the first recording is available for download. It might look a little lonely there now, because it is the first in the series, but this should grow.

This webcast introduces the SQL Developer Data Modeler and then I go into a high level demonstration and feature review.

US on DayLight Saving this Week - Take Note of Webcast Times

2010-03-15T18:02:00.003Z

Having been away for a few weeks, I'm now catching up on mail and work and backlog and it seems the US have changed to Daylight Saving while I was out walking...

If you're joining the Data Modeler webcast on Wednesday, please take note that the US times are as originally advertised, and the UK and Europe times are an hour earlier.

For all details on this webcast, please see the details on OTN.

Oracle SQL Developer and Oracle SQL Developer Data Modeler Public Webcast Series

2010-03-05T17:57:00.003Z

I have done quite a bit of traveling since October last year. Every other week or so I've been at a conference, training event, user group or special interest group. When I'm back at my desk, I spend a good chunk of time doing webcasts and demos for small groups of people. In all I have noticed that there are growing SQL Developer and Data Modeler audiences, which is great. What I've also found is that lots of folk are using a lot of the features and not investigating further to see what the product has to offer. For some time now Kris and I have discussed the need for a webcast series, where we'd demo features of the product, drilling down into different areas. Both SQL Developer and the Data Modeler audiences need this, so I'll be doing a monthly webcast, alternating between the two products, to show off some of the features, and to drill down into some specific details. I'll kick this series off with an overview of the Data Modeler (this month) and a review of the SQL Developer 2.1 new features, next month. After that we'll start drilling into more of the detail.

First Public Webcast

Here are the details:

17 March 2010
09:00 PST | 12:00 EST | 17:00 UCT | 18:00 CET

Introducing the new webcast series
Oracle SQL Developer Data Modeler update
Product Overview Demonstration
Q&A

Duration:
One Hour

Audience:
This is general public webcast, designed for anyone interested in or using Oracle SQL Developer Data Modeler

The details of this event will be announced on OTN next week. For more information on Oracle SQL Developer Data Modeler see Oracle Technology Network: http://www.oracle.com/technology/products/database/datamodeler

All calls are recorded and published on Oracle Technology Network.

The next webcast is scheduled as follows:
21-Apr-10: SQL Developer: SQL Developer 2.1 New Features, Q&A

SQL Developer 2.1.1 is Available for Download

2010-03-02T22:43:00.003Z

We have just released the first patch release for SQL Developer 2.1. SQL Developer patch releases are full downloads and installs - see the release notes - we call them "patch releases" because they exist only to provide fixes to issues. Most of these are found and reported in the early adopter releases and did not make the production release, some were older issues and some found in the production release. A patch release is not a vehicle for introducing new functionality. As ever, not all bugs are addressed with a patch release and so you should look at the list of bugs fixed or the known issues to determined whether you need or want to make the switch.

There are new bits!
While a patch release is not for new functionality, there are quite a few little additions based on feature requests from the Exchange that have been included in the patch. I blogged about the Font setting earlier in the week, to name but one of these. Take a look at the Bugs Fixed list to see a few of the others. Alternative look at the Exchange and search on Fixed in 2.1.1 to see the new features added.

For links to the downloads, bugs fixes and known issue (listed in the release notes), see the OTN pages for SQL Developer.

In my next blog I'll tell you more about the updates on the Exchange. In the meantime, download and try out 2.1.1.

Back to Basics: Changing the Font Setting in SQL Developer

2010-03-01T01:15:00.003Z

An ongoing request in SQL Developer is to have the ability to update the font size in a Data Grid. In the all the releases up to and including SQL Developer 2.1, you can't change the font in the data grid, you can control the full IDE font and the font for the editors, but that's it. Well SQL Developer 2.1.1 has an update to that.

I wrote about changing fonts when we first started SQL Developer, so I'm going to recap the functionality before adding the 2.1.1 tidbit.
The thing about the fonts in SQL Developer is that they essentially controlled through the underlying framework or IDE. (now more commonly referred to as the Fusion Client Platform). So to change the font for the full product, that is the Navigators, Menus, Dialogs and so on, you need to shutdown the product update the font for the IDE, which is locates the ide.properties file in the system folder.

This system folder is created the first time you start SQL Developer and, by default, is located in the C:\Documents and Settings\\Application Data\SQL Developer\system2.1.1.64.38\ folder. Once you have located your system folder, find the sub folder o.sqldeveloper.11.1.1.64.38 (this is the latest SQL Developer 2.1.1 patch, but you can browse the the version of the product you are working with). Edit the ide.properties file and uncomment the FontSize propert. So change
# Ide.FontSize=11 to your font size of choice, for example;

Ide.FontSize=16

Restart SQL Developer.

Notice that this does not affect the SQL Worksheet and Data Grids. Once you are back in SQL Developer select Tools > Preferences. Expand the Code Editor node in the tree and select Font. Changing this font setting increases the size of the font for any of the code editors. In SQL Developer 2.1.1, changing this font size also affect the output of the SQL Worksheet script output and the Data Grids.

Of course, there are lots of folk who just want the Editors and Data Grids to be resized, in which case, you don't need to edit the ide.properties file.

This has been a much request feature on our Exchange, I hope the update helps those of you who have been looking for this support.

RMOUG next week, with a Hands-on University Session

2010-02-09T11:03:00.003Z

Next week we head for Denver and the Rocky Mountain User Group. This is a great conference; lots of technical talks, lots of networking - sharing ideas and making contacts, and of course, meeting a few friends too!

Some years back the RMOUG committee introduced university sessions on the Tuesday afternoon before the event. Last year I said I'd do this, but I wanted the attendees in my room to bring their laptops and we'd do a hands-on session. By all accounts it was a success. We brought the software and the training material and the attendees installed the bits they needed before setting off.

This year we're doing the same, we're asking attendees to bring their laptops and we're bringing the software for the session. Earlier this year we hosted an Oracle Developer Day and provided a Virtual Machine using Sun's VirtualBox. We learned a lot that day and have spent the last two weeks on and off updating and getting this new Virtual Machine built and tested. The idea is that everything you need is in the Virtual Machine and so once you have finished the afternoon, you can delete the Virtual machine and your computer is the same as it was when you arrived. The added bonus is that we're using Linux as the OS in the Virtual Machine, so can bring you Oracle database 11g Release 2! If you want to join us, bring your own laptop (Windows, Linux, or Mac with minimum 2Gb RAM) and 20 GB free space.

The afternoon includes playing with the Oracle SQL Developer Data Modeler and SQL Developer, where we'll do some PL/SQL work and you can play with the latest feature, the PL/SQL Unit Testing. If that's not enough, we also have a few bits for working with Version Control and user defined extensions. The whole afternoon is guided-free format, so you can selected what you want to work on, and then use the guided hands-on sessions - of course a few of us from the team will be there to help with questions.

...and there may be a book prize at the end... see the latest publication SQL Developer 2.1

I hope to see you there. I think there are a few spaces still.

Check out RMOUG to register: http://rmoug.org/training.htm

The Book is Published and our Products are Production!

2010-01-06T14:26:00.003Z

Last year was quite the year for the SQL Developer team, who worked on and released a number of products and releases.

SQL Developer Releases
On the SQL Developer side of things we released:

SQL Developer 1.5.4: The first full translation release supporting Japanese, Spanish, Italian, German, French, Brazilian Portuguese, Simplified Chinese and Korean (March 2009)
SQL Developer 1.5.5: Shipped with Oracle Database 11g Release 2 (July 2009)
SQL Developer 1.5.6: Part of Oracle JDeveloper 11g (July 2009)

and then in December 2009, we released SQL Developer 2.1. This is a significant release for the team with the introduction of Unit Testing and the Data Modeler Viewer. In addition to these two feature chunks, are a multitude of other features. Based on community feedback, the team have rewritten the SQL Worksheet to support the ability to run tasks in the background. Feature lists and details of the new features are on OTN. You can download SQL Developer from OTN and of course get involved in the discussions on the forum. Kris and Barry have been blogging about some of the latest features.

Data Modeler Releases
We also have a few developers on the team dedicated to the Data Modeler. The first production release of SQL Developer Data Modeler was in July 2009 and we released the first patch release in December 2009. This patch release is a full install and addresses many of the initial customer issues.

SQL Developer 2.1 Publication
On a personal note, I spent many a weekend writing last year and in December '09, my book on SQL Developer 2.1 was published. Having been approached by the publisher in 2008, I asked other authors if this was a good idea. I was told there three phases:

Before the book - seems like a good idea, what fun and all that.
During the book - not a good idea, will this ever end, who's idea was this? and all that
After the book - that was easy...where's the next?

Well I'm not sure I've quite reached the last phase yet, but it was a strange and good feeling to finally see the hard copy of the book. The most difficult part of the exercise was writing with the product changing as I was doing so. It is true that most of the product remains the same as in earlier releases, which means that you can and should be able to use the book and stay on SQL Developer 1.5.x or even earlier. In addition to the new features in SQL Developer 2.1, what has changed is much of the look and feel, so menus will be in different places and icons have been updated and so on. We have tried to consolidate menus and tidy things up a bit to ensure we keep an uncluttered UI.
The other snag about writing a book as the product is being developed is that very new features or last minute updates won't make it into the book. The case in point here is that there is no section on Unit Testing. Perhaps next time.
If you have no idea about SQL Developer the first few chapters provide an easy walk through and examples of the product and features. While designed as a "dip in and read" book, the later chapters go into more detail about lesser used features. Although the full read/write capability of the Data Modeler is not part of SQL Developer, there is a complete chapter on this product, allowing you an insight into the complete functionality. You can also use the read-only viewer built into SQL Developer.

In the meantime, I'm going to take back my weekends for photography, my garden, and my other hobbies, so I need to dust down my camera and get out into the country side again. Aah, and the cover shot on the book was taken in Scotland, at the foot of Ben Nevis.

To order the book, use Packt.com, where you can buy the hard copy or an eBook. Packt also provide a list of other book sellers such as Amazon. Happy reading!

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

2009-09-29T10:58:00.003Z

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

2009-08-07T06:13:00.004Z

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

2009-08-06T15:03:00.002Z

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

2009-08-04T11:08:00.003Z

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)

2009-07-31T07:33:00.005Z

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

2009-07-30T13:27:00.003Z

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

2009-07-29T10:57:00.005Z

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

2009-07-25T09:03:00.004Z

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!

2009-07-24T09:30:00.003Z

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

2009-07-21T10:35:00.002Z

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

2009-07-15T09:11:00.003Z

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

2009-07-02T09:06:00.002Z

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

2009-01-09T15:29:00.009Z

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

First Early Adopter: Oracle SQL Developer Data Modeling.

2008-10-10T12:37:00.000Z

I've just put this announcement onto OTN:

The SQL Developer team is pleased to announce their first Early Adopter release of Oracle SQL Developer Data Modeling.

Review the main data modeling page, with links to initial documentation and supporting collateral.
Access the Early Adopter Download. This download link walks you through a brief one-time survey, before you can download the product. For future early adopter releases, you will not be required to repeat the survey.
Provide feedback to the the team through the Feedback application, where you can add all your comments and log issues.
Read the supporting Release Notes.

The Oracle SQL Developer SIG kicks off at Oracle OpenWorld

2008-10-04T13:47:00.002Z

I have entered the first post on http://sqldevelopersig.blogspot.com/ "What? Another Blog and you don't update this one regularly!" I hear you say. Yes, you may be right, but the new blog is a shared community blog. Well that's the plan, and it was suggested at the first SIG meeting at the Unconference at OpenWorld in San Francisco. When many of the delegates were turning to airports or having a last day lie-in, a small group of enthusiast joined a few team members to discuss the possibility of have a SQL Developer SIG. All ideas raised were discussed and noted and you'll find those ideas, and some initial feedback on http://sqldevelopersig.blogspot.com

Take a look, we'd love you to get involved. The great thing about a community blog is that you can write up an idea when you want and do not need to feel committed to be a regular contributor. You can, of course add comments at any stage.

Aah, and the team members will continue to update their own blogs in much the same way as we have to date. When we get the time and have something we really want to tell you. Entries are mostly driven by the former, not the latter.

SQL Developer Visits Oracle OpenWorld

2008-10-01T13:13:00.007Z

Last week the SQL Developer team met in San Francisco for Oracle OpenWorld. We love it as it's a great opportunity to talk to customers and hear what they really like and don't like and to listen to ideas. It's also a great opportunity for the team to get together and catch up as we're scattered across a few countries!

OpenWorld is a huge undertaking for Oracle and many staff are involved on some level or other, whether at the event or behind the scenes before the show. There is a secondary developer focused event that runs simultaneously, this year starting on Sunday, called Oracle Develop. It's a smaller event, a conference-within-a-conference, and is focused on technical talks and hands-on sessions. We had a few talks and a variety of hands-on sessions at Oracle Develop and a few talks and demo pods at OpenWorld. Our talks and hands on sessions were mostly well attended, but it was the news of the new Data Modeling support that stole the show. Everyone was really interested in what the product will offer and of course when it will be available. As ever we can't provide exact release dates, but we're looking at 2009 for production. We're currently running internal preview releases and will move to external preview or early adopter releases within the next month or so. There are a few final things that need to be sorted before we do that. Watch the OTN Forum for an update on that.

SQL Developer SIG
We launched our SQL Developer SIG at OOW. One of the concerns we have is that writing XML extensions is easy and yet we don't hear of many folk doing that. Maybe you all are, but don't share your experiences! We also have a growing set of developers building more complex extensions, mostly java, who are looking for advice and pointers. We have now setup an area on http://wiki.oracle.com for the SQL Developer SDK and will continue to add detail and examples to this.
So the idea was that if we set up a SIG then we'd have a vehicle to talk to those developers regularly. But a SIG is also much more and so we held the initial, kick-off of the SQL Developer SIG the Unconference (at OOW) on Thursday morning early and we discussed options and plans. The SIG is for everyone using SQL Developer, not just those wanting to build extensions. There is so much to discuss and share and as ever it's good to get opinions and input from the community. One of the plans is to have a communal SQL Developer blog. More on that later.

More Conferences
There are still more conferences around, Oracle Develop is on the road and along with other events, SQL Developer is visiting Scotland, South Africa, Switzerland, Germany, UK, China, and India and that's this year!

We're starting to plan our events for next year, if there is a conference near you or a subject area you think we should look into, let me know.

Conference Season

2008-06-24T19:05:00.002Z

I think the whole of 2008 is conference season! It's starting to feel like it. I think I don't travel much, certainly I know that Oracle Sales Consultants seems to be on the road permanently and I know some of the Oracle Server Tech PMs are really busy on the road. I tend to be desk based, well, that's how I see myself. So here's how a desk-based PM spends her time.

I'm at HQ this week - nice spot - lovely weather, although deceptively cold. I say that because the sky is the same colour as it was last week and the hotel room temperature is the same as last week, but it all changes when I step outside. Where I was last week might explain why I feel that. Last week was ODTUG in New Orleans!

Great event. Lots of very enthusiastic and experienced developers. Most willing to learn new technologies and excited and positive about the future.
As ever the conference supported 50% new attendees. Each year it does (as so most conferences) and each year we are surprised. "[shocked and amazed] You mean half of these folk have never been before? Where are the oldies?" Anyway, there were enough of "the oldies" about and that's great. Although sometimes I wonder what it feels like for the new folk, as there is something of a family feel about the place as folk catch up on news since last everyone met and lots of name dropping in the various talks as if everyone knows everyone else. I guess most do.

I did 2 talks. One I call my "drill down" talk, where I assume you use the tool or a similar one, and show some of the more in depth things you can do. Typically, I pick 4 features and spend about 15 mins on each topic. My personal favorite is the XML extensibility, which we can all do. No need for Java skills to extend some aspects of SQL Developer and certainly a nifty aspect to the tool. (Most DBAs I know have a collection of utilities or tasks they regularly perform). If you have no idea what I am talking about, search this blog or the OTN pages, there is a tutorial on the SQL Developer pages on just that feature.

My other talk is the SQL Developer overview and as someone pointed out, now requires more than an hour. Next time I'll skip the intro completely. I did a few positioning slides and then dived into a 50 minute demo. By all accounts - well received.

That an hour is a little short is easily illustrated - I was in Denmark a few weeks back, where I spent the day with a group of DBAs and developers, each armed with their laptops, and together we walked our way through SQL Developer, from the APEX support to the Migration, through XML extensions and reporting. It was a great day.

News
We were excited to bring some news of the next release of SQL Developer to ODTUG. You might have seen this in the "twittersphere", on other blogs, or have stumbled across our latest Statement of Direction. The plan is to incorporate data modeling in SQL Developer. This is a much requested feature in a tool already being used by many database developers and architects. We'll publish an FAQ on OTN in due course to address queries about this.

More Conferences
As I submitted abstracts to events and then registered for these events, I've meant to let you know of their existence, but did not get to it. So ODTUG is over for 2008, but I'll remind you now that it'll be there next year and that they'll probably be asking for abstracts in November-ish, so diarise that!

There are quite a few events that you have not missed yet, like Oracle OpenWorld coming up in September. Details of talks are already available and what's great is that the "Develop" part of the event is at the Marriott, so not blocks away from the main event. Easy access between talks and hands on sessions. SQL Developer has 4 different hands on sessions, and 4 different talks, not to mention the demopods, where you can meet the developers and ask questions.

I've submitted abstracts to DOAG (in Germany), Israel, UKOUG (Birmingham) and Scotland (Glasgow) and will be submitting to the New York user group. So if you missed the chance to hear more about SQL Developer, there should be something near you. You'll note there is nothing in APAC and regrettably I can't get to AUSOUG.

Let me know if there is a conference near you. I can add that kind of news to the SQL Developer Exchange News page and keep everyone up-to-date.

Using Oracle Database 11g Hierarchical Profiler in SQL Developer

2008-05-07T15:10:00.003Z

Oracle SQL Developer 1.5 exposes a few new Oracle 11g database features. These include the Flashback and the PL/SQL Hierarchical Profiler. For details on either of these feature it's best to dive in the the 11g documentation, available online here. The PL/SQL Hierarchical Profiler..."identifies hot spots and performance tuning opportunities... It reports the dynamic execution program profile organized by subprogram calls..."

I have recorded a brief demo to illustrate using the PL/SQL Hierarchical Profiler in SQL Developer.

Not only do you need Oracle 11g, you also need to have access to a set of tables and a new package called DBMS_HPROF. SQL Developer takes control of setting this up and so you need only acknowledge the steps being taken. If you don't want SQL Developer to create the required profiler tables, review the 11g documentation and ensure you set this up before hand.

Once you have created a profile you can review the detail. The reports provided include details such as:

Number of calls to the subprogram
Time spent in the subprogram itself
All subprograms that a given subprogram called (children)

Have fun!

Oracle SQL Developer 1.5 is Production

2008-04-29T16:20:00.002Z

You might be linked into the announcements RSS feed on the OTN forum and so will have received the alert from the SQL Developer team announcing that we have released SQL Developer 1.5.

The latest release of SQL Developer expands the file-based support by introducing tight integration with the CVS and Subversion version control systems. In addition to browsing and editing files in the file system, in this release you can now import files into your source control system, check them out, modify them, and commit the changed files back into the source control system.

SQL Developer 1.5 has two new navigators; a File Navigator, available on start up, and a Versioning Navigator. This latter is not opened by default, instead is accessed when needed using the View menu. Once you have created a connection to your version control system, you can use SQL Developer to import files and version files. SQL Developer tracks the history of a file and you can compare recent changes and accept or reject those changes. Once you have accepted changes and committed them back to the version control system, you can generate a patch, based on the differences.

SQL Developer 1.5 is more than version control. This is the release where we wanted to address as many of the community requests as we could. The snag with that is as soon as you respond to one request, a dozen more new ones pop up. We're no daunted by this, just accepted a long list of requests and if you look at the Exchange, you'll see that many made it into 1.5. Requests added include code insight, updated formatter and more connection options. For a full list of new features, you can review the New Feature list on OTN.

For all new supporting documents, please visit SQL Developer on OTN

An Update from Five and a Half Hours East of ‘Normal’

2008-03-07T04:17:00.002Z

Even though I’m not a regular blogger, here’s an update about why things are quiet now. As a rule I keep this blog for SQL Developer related comment or features. I plan to continue in that vein, but sometimes it’s nice to talk about or read something slightly different. I was pinged a few times in January by various friends and colleagues to add a list of “8 Things …” At the time I thought this was quite a fun activity and despite the various mumbles and grumbles on a few sites, planned to add a short list to my blog, thinking it would be a great way to get blogging in 2008 and to break my ever-sporadic habit. You are welcome to stop reading at this point, I should just add that this is not a list of 8 things…just one. For those who pinged me, this would have been on the list of ‘8 things you don’t know about me’

If you are a regular reader or know me, you’ll know that I live and work in London. This month I am living and working in New Delhi, India. I’m working for an Indian based charity called Asha. Dr Kiran Martin, a paediatrician, established Asha some 18 years ago, when she started to treat the slum dwellers during a cholera outbreak. Asha is now active in about 40 slums in Delhi, working with over 200 000 people, with a programme that encompasses community and individual development, working on environmental improvements and providing health care. Asha uses volunteers to help, with many hundred from the slum communities. The slum volunteers are trained by Asha staff and then become part of the team working together with professional health care workers. Last year I came out with a team for 10 days from the UK, during a break from Oracle, to help paint a clinic in one of the slums. I documented my trip during that time on this separate blog. http://delhiminimission.blogspot.com
While we were there, we were told about their plans to start English and Computer Literacy programmes and to call for volunteers to get involved. They were hoping for TEFL or TESOL trained volunteers who could spare 2 – 3 months. I am here for a month, as one of the volunteers, teaching children English in one of the slums.

I do have access to the Internet sporadically and so you might see me pop up on the forum from time to time, but I’m officially on leave, so if you think I’m quieter than normal, you might find me chatting here, http://delhiminimission.blogspot.com or maybe even here, http://sueharper.visualblogging.com

I’ll be back in April.

SQL Developer EA2 is out now

2008-02-21T11:21:00.004Z

Oracle SQL Developer1.5 Early Adopter's Release 2 (EA2) is out now. We made it available last night and already the forum is buzzing with EA2 feedback and the developers are focusing on the next phase.
Did you know you can build a gauge into a report or a data grid query in SQL Developer 1.5? Take a look at this quick demo. Below is a preview screen shot.

New Year, New Features, New Release and a Few Tips!

2008-02-06T10:46:00.000Z

I should be blogging! So much to tell you and so little time! I thought when I was first tagged for the "8Things", that I'd use that to start blogging again, and then the second time I was tagged I was sure I would, but even the third time didn't help... Aah well, maybe one day I'll tell you a few things about me that you don't know. I want to tell you I'm going back to India in March (did you know I went last year?) and never got around to that either. All in good time.

The SQL Developer news is that Oracle SQL Developer 1.5 Early Adopters 1 (EA1) is out. If anything that should have had me straight on the blog, but I am tracking feature issues and feedback on the forum and so didn't. Then one of the team sent me this tip and it was just too good to lose:

You can use alt-shift-# (where # is a number) to create a "editor bookmark", then use alt-# to navigate to it.

So, if you have a number of worksheets open, you can create a bookmark selecting alt-shift-1, which assigns an alt-1 navigator shortcut to the active editor. You'll see a small numbered icon on editor's tab. If you repeat that for each open tab, then you can then navigate between tabs using keyboard strokes, alt-1, alt-2, etc.

A Few Other Keyboard Strokes:

To use code editor templates you have created (in the preferences) use the keyboard shortcut ctrl+shift+T.
The keyboard shortcut for the SQL History is crtl +up or ctrl + down. This replaces whatever is in the worksheet with lines from your history.
To create a new, unshared worksheet, use the keystroke ctrl+shift+N. This creates a separate, unshared worksheet. The unshared worksheets are labeled as follows: HR_ _<1>

Did you know that if you open a xxx.trc file in Oracle SQL Developer you'll see a sortable, formatted view of the file?

So it seems there are lots of tips about, which you might like to know about or which you have discovered on your own and wished someone had told you. I'm going to try to tell you more and often as I can. If you have some for me, I'd love to hear about them.

Sue

After the Storm - Oracle Open World and a New Podcast

2007-11-26T15:35:00.000Z

I've had a week back at the office following Oracle Open World in San Francisco and had some time to reflect on all that went on. If you have been following various blogs and write ups about the event you'll have heard about the various dinners bloggers or Ace's attended, which, while they may seem frivolous, are as an important part of any conference as the sessions themselves, by providing a valuable opportunity of meeting or catching up with folk in person. (See the updates from Mark Rittman on those.) Actually these kinds of events are really good for talking to folk about the products they use, their concerns, dislikes and likes.
This year the Oracle SQL Developer team were busy at the main event on the demo grounds. We had great feedback on the new Migration Workbench extension, which has been a great help to a number of companies. I also heard very positive feedback about the new Times Ten extension, now available in preview. We spent most of the time demonstrating the new features the team are currently working on. I found that SQL*Plus users, DBAs who use the command-line and our existing database developers all liked what they saw.

We also had a number of papers and a hands-on session at the Oracle Develop event. One of our sessions was in a room too small for the crowd and had to be moved to one larger. I was very pleased to see that our hands-on event was a sell out and only wished we had time for a second or even longer session. I'll upload all the hand-on material onto OTN.

I had an opportunity to meet and talk to Tom Haunert, editor of the Oracle Magazine. The brief podcast we recorded is available from the general Oracle Podcasts site on OTN. You'll find it under the Oracle Magazine.
I'd love to hear the kinds of topics you'd be interested in hearing. (Bearing in mind this is audio only and has a max time of about 12 minutes, although most are about 8 to 10 minutes.)

My only regrets not seeing everyone I'd hoped to see and not stopping to talk for longer with some folk I saw along the way, as I dashed to a presentation or back to the demogrounds. Maybe next year.

Gearing up for Oracle OpenWorld

2007-11-02T16:55:00.000Z

One week to go before Oracle OpenWorld in San Francisco. There is a buzz in the air, with the preparation build-up, whether we are in California right now or not. One of the things they have added this year is "Oracle Connect" an on-line community for the conference, along with all the other information and events on the go. I think you can safely say, there is something for everyone! For those who don't like the formalities of some of the talks, there are 2 areas you might be interested in. The one is the "Unconference" and the other is the "No Slide Zone".

This year Larry is talking on Sunday night too. Take a look at the keynotes scheduled.

Kris and I will both be at the conference again this year, along with other developers from the team. Stop by the stand and introduce yourselves, we'd like to meet you.

Oracle's Official WIKI

2007-10-25T09:00:00.000Z

There is a new place for information about all things Oracle! http://wiki.oracle.com/
While it may feel like there are so many places to find information, I think that having the various formats available suit different people and different styles. The new site that you may or may not be aware of is the Oracle Wiki. It's a place where the Oracle community, internal and external, can contribute and share content. The site already has links to Oracle OpenWorld and the different that attendees can see and do. You can get lost in the wiki content about Oracle World, without going to the main site, although I do recommend you do.

Back to the Wiki. Under the navigation section, there is a Developer Tools node and you'll find SQL Developer nestling there among some of oracle's older (and not so old) tools.

Content for SQL Developer can be found through a chatty forum, or more formally on the OTN technical pages or very formally through the SQL Developer documentation which we link to from the OTN pages listed above. So the wiki allows you add small components, or larger explanations, specific FAQs or links to articles you have written. Adding to the wiki is easy, so have fun and make it your space.

Browse Oracle APEX Applications Using SQL Developer

2007-10-24T13:40:00.000Z

This is a quick alert that there is now a good article on the Integration of Oracle Application Express (APEX) and Oracle SQL Developer. You may already know that this feature was introduced with SQL Developer 1.2, but unless you had created an Oracle APEX connection you might not have realised the extent of the support that is available. The article provides an overview of all you can do:

"Using SQL Developer, you can perform the following functions:

Browse your Application Express Applications
Export and Import Applications
Drop Applications
Deploy Applications
Modify Applications
Export Pages
Tune your Queries
Included Application Express Reports
Custom Exception Reports"

Have a look. You can also find links to this article from the news section on the Oracle SQL Developer pages on OTN and it's listed with the other technical documents and white papers.

Updates to Training and Online Material

2007-07-18T10:31:00.000Z

The Oracle SQL Developer Tutorial has been updated to support SQL Developer 1.2 features. This tutorial is packed full of short demonstration viewlets; from installation, creating connections, through using functions, working with collections of data, PL/SQL debugging to creating reports.

The great thing about this tutorial, is that you can dip in and out of it at any point. There is a flow from installation to building reports, as the tutorial follows a scenario, but if you just want to know how to work with constraints you can go straight there.

The brief viewlets are interactive, so you can get a feel for the actions. If you just want to flick through the demonstration you can click the forward control.

If you are new to Oracle, then this tutorial is also great in that it walks you through the basics of SQL and getting to grips with DDL and DML, all the while using SQL Developer.

Other Collateral Updates

You may be aware of our homepage on OTN: http://www.oracle.com/technology/products/database/sql_developer/
The front page has links to documents that are a result of general questions that often come up, for example, Pricing, Support & Licensing Questions. The front page also links to viewlets, white papers, magazine articles and tutorials. Each of these categories contain recently updated material. Some of you have been asking for some material on how to create extensions for SQL Developer. There is now a published article in the latest Oracle magazine on that.

So dip into the tutorial or have a browse around the SQL Developer content. If there is anything you'd like to see added, then drop me a line and we'll see what we can do.

Sue

Multiple Table Quick Export in SQL Developer 1.2

2007-06-29T10:23:00.000Z

There are a number of ways to export DDL scripts in SQL Developer. Two approaches have been in the product since its inception. The first approach is to select the SQL tab when you invoke the definition tabs for any object in SQL Developer. The second approach is to invoke the Export DDL dialog, using the Tools menu. This dialog allows you to select and filter different object types. You can also filter the data exported, should you choose.

New to SQL Developer 1.2 is a multi-select quick export DDL option:

Expand any object node in the Navigator, e.g. Tables
[ctrl] select the objects you want to export
Right-click to invoke the context menu
Select the option of choice:
- Save to File
- Save to Clipboard
- Save to Worksheet

The syntax format of the DDL created is driven by the parameters set in the Preferences dialog. (Database -> ObjectViewer Parameters)

Magnification and General Font Size in SQL Developer

2007-06-28T12:45:00.000Z

As a speaker at conferences, it is important that customers can see what I am demonstrating, no matter the size of the room, or how far back they sit. When I was working in the JDeveloper team, I used to change the font settings in JDeveloper so that the text would be clearly visible. For some time now, I have been using a Magnifying Glass instead of changing the font. Mostly becuase of the loss of real estate when making all the fonts much bigger. The magnifier I like is one I got from Source Forge - http://magnifier.sourceforge.net/

Using a magnifier is great for what I need, when speaking and doing demos, because I can show the audience just what I want them to see. It's no good if you need the font on your IDE to be a little bigger while you are working. Also real estate is typically less of problem when working at home or in the office, as most of us are not limited to the small screen of a laptop.

Changing the font size in SQL Developer is an enhancement that a few have asked for. In JDeveloper we just changed the setting in the ide.properties file. As we don't have the property in our ide.properties file, it didn't seem to be something we could control. While working on something quite different today, I came across the ide.properties file in SQL Developer again, and while the font property is not there, it occurred to me that by adding it, it might override the default. It does!

Increasing the Font Size in SQL Developer

Locate the ide.properties file in your installation in the following folder:
{your SQL Developer install directory}\sqldeveloper\sqldeveloper\system\oracle.sqldeveloper.1.2.0.2998
- you'll see I am using SQL Developer 1.2
Add the line Ide.FontSize=14 or what ever size suits you. The default for the IDE is 11
Restart SQL Developer

The Code Editors are not affected by this. To update those, you need to change the settings once you have restarted SQL Developer. Select the menu Tools -> Preferences... Expand the Code Editor node and select Fonts to update those. This property affects the SQL Worksheet, along with the Execute script (F5 ) output, and the PL/SQL Editors. My initial review seems to show reasonable dialogs throughout, with the exception of some of the new Migration Workbench dialogs.

Have fun!

Conferences: ODTUG Kaleidoscope and Oracle Develop...

2007-06-13T09:46:00.000Z

Someone reminded me, yesterday, that I fly to the States this weekend. It's been a while since I've been at a conference, and I hadn't really forgotten, it just that it's suddenly there! Maybe that's happened to you. You know there are a few events on and you've been meaning to sort things out to go to one, but suddenly it's June and you've done nothing about it yet. There is still time. There is an event in the States and a few in Europe.

Next week is Daytona, Florida. It's the ODTUG big annual event and I have been going for years, through various guises, a good many of them as Designer PM and then as the PM for MDA, more specifically database modeling and all things database in JDeveloper and now as PM for SQL Developer. There is a common thread there if you know the tools.

So I have a couple of papers at the event , but also love to hear what others, from around the world, are doing too. and of course catch up. Often at these events, it's an opportunity of putting a face to a name from a forum or new group. If you live in the area, or haven't finally decided, take a look at what's on. http://odtugkaleidoscope.com/
I know that this year the conference is attracting lots of great speakers, and they have pulled in a bigger crowd - that just means more networking. Perhaps I'll see you there.

When I get back, Oracle Develop is on in London. (Next week Develop is on Prague and Munich) This is a two day event, with hands on, parallel streams and words and news about current and new products. Unlike ODTUG, which is a users group with lots of talks from users, Oracle Develop is an Oracle event, where we bring you news about what current in the company. Take a look here: http://www.oracle.com/technology/events/develop2007/index.html

This is the place to come and learn and ask Oracle questions. The event has already been a great success in Asia.

Try to get to one of these, specially if they're just down the road from you.

Sue

More details from other team members..

2007-06-12T18:33:00.001Z

I said I'd add in the detail about the Export DDL and just how it's done, and I will... I do just want to point out that Kris and Donal have written up more specific details about the 2 main aspects of SQL Developer 1.2. So, if you've not already seen them:

Kris and the APEX updates: SQL Developer 1.2 will support the upcoming Oracle APEX release.

Donal and the New Migration Workbench: Donal walks through the top new features and what you can expect from the new integrated Migration Workbench.

Enjoy!

Oracle SQL Developer 1.2 is Production

2007-06-11T15:27:00.000Z

You may have forgotten me as it's been sometime since I last updated this site. The reasons are multiple and not important here, but I do want to tell you that Oracle SQL Developer 1.2 has landed!

What, you might ask, is SQL Developer 1.2 and how is it different from 1.1.2 or 1.1.3 for that matter? In our world of numbers, each point in the release number is a point of significance! So 1.1.2 and 1.1.3 are patch releases to the 1.1 production release. This means that 1.2 is more than a patch release and indeed it is. SQL Developer 1.2 is the first release that we're shipping with the new, rewritten Migration Workbench. So, as a feature of SQL Developer, you'll find details about it on the SQL Developer homepages on OTN.

The Migration Workbench team work on a number of migration related products and utilities and so they have a separate Migration Technology Center on OTN. There you'll see the work they have done on migrating Microsoft Access applications to Oracle APEX - but that's another story. The Migration Technology Center links to a section focused on the new Migration Workbench. The documents include Getting Started, a data sheet and a bunch of viewlets, in bite size chunks, covering all aspects of Migrating from MySQL, Microsoft SQL Server and Microsoft Access to Oracle.

While we're talking updates, the SQL Developer pages themselves have had a bit of a revamp, with a number of new or updated articles. Take a look.

Tomorrow, I'll tell you about the new export options available...

How to Add Tabs to SQL Developer

2007-04-19T16:09:00.000Z

Kris has blogged about this, so you may have solved all your extra tab requirements already, but I have seen a few request for features that will make your lives easier, and that you can add into SQL Developer today, without waiting for us. So if you haven't worked out how to do this yet, then take a read.

Suppose you want to add a new tab to your tables definitions that shows all the column comments, or more partitioned information, or sub-partitined information. Today if you need this information, you might use SQL*Plus or the SQL Developer SQL Worksheet and you'd query the Dictionary Tables. You might might say something like:
SELECT COLUMN_NAME,
COMMENTS
FROM ALL_COL_COMMENTS
WHERE OWNER = :OBJECT_OWNER
AND TABLE_NAME = :OBJECT_NAME;

So let's put this code into an XML file:
Open a blank page in something like Notepad or Wordpad and add the following:


<items>
<item type="editor" node="TableNode" vertical="true">
<title><![CDATA[Column Comments]]></title>
<query>
  <sql><![CDATA[select COLUMN_NAME, COMMENTS
from ALL_COL_COMMENTS
where owner = :OBJECT_OWNER and table_name = :OBJECT_NAME]]></sql>
        </query>
 </item>
</items>

Did you notice that same piece of SQL in the middle of the XML tags? Yup, that's our Column Comments SQL. Save this file to a sensible location. I have an Extensions folder, but you might want to add this to a folder under your Documents and Settings. Now open Oracle SQL Developer 1.1.2.25.79 and navigate to Tools -> Preferences. Expand the Database Node and select User Defined Extensions.
Select Add Row and use the drop-list under type to add a new EDITOR type. For the location, browse to your saved file location and select the file.

Shut down SQL Developer and when you restart it, navigate to a connection, expand your tables node and select a table. The new tab appears after your SQL tab and if your table has comments, you'll see them.

So that's easy, now you can do more: Let's look at the sub-partition request I had.

What do I need to know about Sub Partitions? You might want to start by describing the table to see the kinds of detail you are interested in. You'll see that if you describe all_table_subpartitions that it has table_owner, not just owner, like the previous query. I was interested in these columns:

Select PARTITION_NAME, SUBPARTITION_NAME,
HIGH_VALUE, HIGH_VALUE_LENGTH,
SUBPARTITION_POSITION, TABLESPACE_NAME
from ALL_TAB_SUBPARTITIONS
where table_owner = :OBJECT_OWNER and table_name = :OBJECT_NAME;

I'd suggest you try that in the SQL Worksheet first and then once again add that code into a new empty file.
The XML file would look something like this:

<items>
<item type="editor" node="TableNode" vertical="true">
<title><![CDATA[My Sub Partitions]]></title>
<query>
  <sql><![CDATA[Select PARTITION_NAME, SUBPARTITION_NAME, HIGH_VALUE,
HIGH_VALUE_LENGTH, SUBPARTITION_POSITION, TABLESPACE_NAME
from ALL_TAB_SUBPARTITIONS
where table_owner = :OBJECT_OWNER and table_name = :OBJECT_NAME]]></sql>
        </query>
 </item>
</items>

Once you have saved that file, add a new User Defined Extensions of Type EDITOR as you did before.
You'll need to restart SQL Developer to register the new extension. When you restart you should see the tabs as follows:

Let us know how that goes, what extensions you have added and let's add them to the SQL Developer Exchange!

Conferences or not...

2007-04-05T16:27:00.000Z

Looking at the next few months, it looks like April, May, June is conference season, and that's just the spring conference season. I remember when conference season was Feb, June and November, now it seems that conference season is pretty much all the time.

The first one you might be aware of is Collaborate. This is in Las Vegas, starting on the 15th April. It looks like there's a lot on, so if you're in the area, take a look at the agenda and get on down. Not one I can make, so report back if you get to go!

The next one was to be held here in Europe. The EOUC was to be held in Amsterdam in early May, but we have been told and have now seen the announcements that this event has been cancelled.

This means that you really have to look out for Oracle Develop! You may remember events similar to these in the past, when Oracle organized 2-day events in lots of cities around the world. These events are focused on developers and should give you the technical detail you are looking for in your subject area.
There will be hands on sessions and an opportunity to meet members from the development organization, depending on which city you go to. I'll be doing 2 SQL Developer papers in London, in June. See the agenda, get registered! See you there.

Shay's Blog on being a Good Forum Member

2007-03-13T17:10:00.001Z

Shay Shmeltzer, a PM for JDeveloper, wrote a great blog for OTN forum users. Please read it.

Everyone of his points are pertinent and relevant, so even though I am loathe to single out a few, I will! The ones I said "YES" out loud to, are #1, 6 and 8.

If you haven't found it before, the SQL Developer Forum on OTN is quite active.

Sue

Some New Bits for You: The Migration Workbench and a new Search

2007-03-12T11:51:00.000Z

What Release of SQL Developer are you Using?
I am sure you are all on SQL Developer 1.1 Patch 2 (1.1.2.25.79) If you are, then you might be interested in the 2 features I am going to talk about below. I do know some of you are not on patch 2 and there are still many of you still on 1.0 (1.0.0.15.57). I am sure there are lots of reasons you don't upgrade to a new release. Better the devil you know...etc. Maybe you are from a big company and so new software is rolled out slowly. Maybe you just didn't hear the news. (I've been a pretty poor blogger...) Maybe you are concerned about some of the entries on the forum and so won't try the new release. Did it occur to you that you may use the product for different things? That you might find the updates just perfect for you? Performance improvements, a few new context menus, some powerful reporting capabilities?

Let's look at the releases. SQL Developer 1.0 was our initial release and many of you were excited by the release and started to use it. It does what you need, so you're happy. SQL Developer 1.1 brought a whole bunch of new features and a whole rewrite of underlying technology. When a rewrite happens, inevitably a few features may not make it back into the product. We had to weigh up delaying the release or getting it out to you quickly. We decided that the number of new features outweighed the few that were missing. In the same way as we did for 1.0, we quickly followed with 2 patch releases, filling in the gaps with the bugs that slipped the net. I know this is frustrating for you and there are a few of the inevitable rants about testing and quality. We agree, but the overwhelming feedback has been better in each release and we feel the latest patch release of SQL Developer brings you more functionality, performance and stability than the others. Try it, download from here!

Migration Workbench - Early Adopter Release
Just last week we added something new. It might not be something you all need, but we know that many of you do have multiple databases and they're not all Oracle. We have seen your interest in the ability to connect to and browse third party databases. Well, the team that brought you the Oracle Migration Workbench, have rewritten the Workbench as an extension to SQL Developer. This extension is designed to give you a seamless experience; moving from browsing a third party database to migrating the database schema definitions and schema data to an Oracle database schema. Once you have completed the migration you can continue to work with and query your data, using SQL Developer.

The Workbench Extension is currently a preview or early adopter release and so is only available through Check For Updates, using SQL Developer 1.1. patch 2 (1.1.2.25.79) So that's one really important reason to move up to Patch 2. We will release SQL Developer Migration Workbench as a complete SQL Developer install for production. For more information about the new extension see this page.

A Snippet of Information - Searching Database Objects.

In SQL Developer 1.1, you might have notice the new DB Search Object. Have you tried it? You'll find it in a minimized window on the right, near your Snippets window. (That is until you start rearranging your windows.)

It's neat in that you can do a search for a database object and it'll search across schemas. In my example below I did a wild card search for occurrences of EMP%. It produced a healthy list.
Once an object is found, the same context menus, you'll find for your objects in the navigator, are available to you in the search window.

So this week's task for you is to update your SQL Developer release and have a play. My tasks for this week are to update each of my blogs daily! (Aside from being slack here, I have outstanding photo and Delhi entries to update!) Let's see how we do.

Sue

A Conference I am Not Attending

2007-02-12T11:07:00.000Z

SQL Developer, Migrations and Third Party Databases

2006-12-11T12:04:00.000Z

A week or so ago, I popped over to Ireland to see some of the developers who are based there. I have mentioned before that the team based in Dublin are the Migration Technology Group. They are a busy team; building the Migration Workbench as an extension to SQL Developer, are also working on SQL Developer and have just released the Oracle Application Express Application Migration Workshop. Take a look at Donal's blog for a bit more on that.

There is lots to tell you about the trip, the work they are doing and have already released and I'll come back and tell you more another time, right now I want to give you a dip into something you can test in SQL Developer.

Part of the Migration Workbench functionality is the ability to connect to and review the objects in a third party database, such as MySQL, SQL Server and Access. In fact these are the three third party databases the team are targeting for their first release. So the Migration Workbench extension to SQL Developer, will allow you to browse your third party database, and then migrate all or some of these objects and data to Oracle. Very exciting! Well, the full Workbench is only due for next year. What we've added to SQL Developer 1.1 is the ability to connect to and browse a third party database. Here's How:

1. JDBC Drivers: Assuming you have an MySQL or SQL Server database, verify you have the correct jdbc drivers. This Getting Started document on SQL Developer 1.1 has links for you to download the drivers you will need.
2. SQL Developer 1.1: This functionality will only be available in SQL Developer Release 1.1. You can test the functionality with the current evaluation release, 1.1.0.22.71, available from here.
3. In SQL Developer, navigate to the Tools menu: Preferences >Database > ThirdParty JDBC Drivers.
4. Click Add Entry and add your specific jar file.

The jars on this preference panel are used for all third party databases.

5. Now you can add your connections. Navigate to the Connections dialog and add a new connection.
My example is for a MySQL Database. As for Oracle, be sure your third party database is up and running!

6. Test and save the connection. Note, in my example I have an ACCESS connection tab too. This will be available in the SQL Developer releases post Evaluation Release 3 (1.1.0.22.71.)

7. You should now see the third party connection in the navigator. Remember this is read only access you have. However, the appropriate tabs and details are available for you to browse and review your database objects.
Have fun!

SQL Developer 1.1. How Do I Build a Master Detail Report (with Chart)?

2006-11-15T15:50:00.000Z

Perfect, a query on the Forum triggered a thought for this entry. "How do I create a report that has a table and chart in it?"

Let's start with a Master-Detail report.

1. Using SQL Developer 1.1 Evaluation Release 2 (1.1.0.21.97) go to the reports tab and select "Add a Report" under the User Defined Reports. I'll leave you to fill in the name, description and tooltip details. The report we are creating uses the demo schema HR, with the EMPLOYEES, DEPARTMENTS and LOCATIONS tables.

2. Although you only see one panel at this stage, you are creating the Master query. Set the Style as Table and enter the following query:

select d.department_id, department_name, city
from departments d, locations l
where d.location_id = l.location_id

Note: No trailing ';'

You can elect to test the reports at any stage, using the Test button.

3. Now to add the detail.
Select the Add Child button to open a new panel in the dialog.

The basic detail query, without chart or any complexities is as follows:

Select * from employees
where department_id = :DEPARTMENT_ID

Note: The bind is CASE sensitive. Also useful to note, the child query knows that this bind is from the table above.

Another example using that bind variable is:

select :DEPARTMENT_NAME, last_name, salary
from employees where department_id = :DEPARTMENT_ID

It is not that interesting, to have the department name in the detail records, because you'll just see that name duplicated.

4. Run the report to see the master detail in operation.

That's it, a Master -Detail report in action.

Making the Detail a Chart.

Let's take it further and pull the CHART into play...

5. Select Edit to open the report again.

From the previous blog entry on charts, you saw that we need to select X, Y and data, so we'll do the same here. Replace the child query with:

select m.last_name, e.last_name, e.salary
from employees e, employees m
where m.employee_id = e.manager_id
and e.department_id = :DEPARTMENT_ID

So now I am looking at the departments table and for each department, I want to know who works for each manager in that department and see their salaries.

To create the chart, set the child style to CHART and then look at the chart details and set them to BAR_VERT_STACK and select the Test button.

When you are happy select Apply. Here's mine:

SQL Developer 1.1. How Do I Build a Chart?

2006-11-13T16:27:00.000Z

I did a few "Sneak Peeks" into 1.1, in an attempt to show you what you might expect down the line. Now that 1.1 evaluation release is available, you can go back and look a those and find the features I mentioned.

I'd now like to take this further and do a series of "SQL Developer 1.1: How Do I ....", first using the evaluation drops as they become available and continue that into the production release.

In showing how to go about discovering new things and working with 1.1 in general, I could take 2 quite different approaches. The one approach would be to be quite organized and plan a whole schedule of entries and the other is to write random ad hoc entries that just jump in and out of the tool all over the place. I'm opting for the latter approach, because right now I am inspired to do a quite write up, just covering a basic chart in SQL Developer 1.1

Building a Chart using SQL Developer 1.1

At this stage I should point out, that the only 1.1 software you have access to is SQL Developer 1.1 Evaluation Release 1.1.0.21.41. That's what I am going to use. If you do not yet have that release, then go to OTN and follow the links to download it.

Assumptions:
The query I will use in my example is for the user SCOTT/TIGER and requires that you have access to SCOTT's EMP and DEPT tables and a Connection to SCOTT.

Starting with the Reports Tab, select User Defined Reports and, using the context menu, select Add Report. If you're diligent, you could add a folder first, so that all your reports are saved into carefully planned categories.
Once you have invoked the new report dialog, there are three areas you should take special note of:

The Style of report, in this case a CHART
The SQL text
Chart Details

It is obviously important to complete other details, such as name and tooltip, these I'll leave you to do. I want to create a chart of Employees' Salaries per Department. Here's my query. Note, no trailing ';'

select emp.deptno, emp.ename, sal
from emp, dept
where emp.deptno = dept.deptno
order by dept.deptno, sal

When creating a chart, the rule of thumb is select group, series, data from table

Before you test the chart, select the Chart Details and change the chart type to BAR_VERT_STACK. For the rest of the options, you can make your own choices. For bigger reports, the Legend can take up quite a bit of real estate, so I tend to set that to False. You can see what suits you. Once you have set the Chart Details, you can return to the Details tab and Test the chart or just select Apply.

Once back in the Navigator, select your report and select the connection you have for SCOTT.

The stripy pajama report reflects the 4 departments in the table , with each employee and salary. The mouseover for each node indicates Employee name and Salary. The power of the chart is that a glance you can see that, based on my data, Department 20 has the greatest salary bill, while Department 10 appears to have the highest paid employee.

This is a trivial example, but you'll note that the results returned are quite rich. Why not try this against some of your System data?

SQL Developer Presentations from Oracle World Available

2006-11-09T16:06:00.000Z

If you missed Oracle World in San Francisco, or even if you were there, the presentations for the talks are now available for you to browse.

This link takes you to the Content Catalog. Note this app does require a username and password, but they are there for you to use.

If you want to see the talks that Kris and I did, just enter "sue harper" into the Speaker name field and you'll get the Overview and Advanced presentations. (No demos, just power points!) Kris and I will be doing blog entries of the demos in the next few weeks.

If you want to see more SQL Developer related talks, enter "SQL Developer" and you'll also get Donal's talk on the work the Migration Workbench team are doing and how they're using SQL Developer.

Of course you know how to use a simple app like this, but I want to be sure you see the good stuff... ;-)

Oracle SQL Developer 1.1 Evaluation Release is Now Available!

2006-11-07T23:34:00.000Z

Need I say more? The long awaited evaluation copy of Oracle SQL Developer 1.1 is now available for you to play with. Please send us your comments! I'll give you the links in a mo, please note, this is NOT a Supported release, it is for evaluation purposes only. The team is still working on the code and closing bugs as I write. We'll be doing regular drops for you to evaluate over the next few weeks and will be using a FeedBack Application to track issues and comments.

You will not be supported through Oracle Support or the Forum, only the feedback site. You will not get the evaluation software from the production download site, instead follow the links through the short survey and on to the evaluation release download. (For subsequent downloads you'll be redirected past the survey) When you reach the download page, there are links to a few very useful documents:

New Features - This is a list of new features, quite comprehensive, possibly missing a few little features here and there
Known Issues - This is a list of bugs already logged, some already fixed in a later build and which you'll pick up in next week evaluation drop.
Getting Started - This is really important. If you are using 1.0, there are some files you need to back up if you want to preserve them and go back to SQL Developer 1.0.

But first...download here

No, I can't tell you the production date yet.

Have fun!

SQL Developer Exchange: Vote for Features, Log Requests

2006-10-30T12:07:00.000Z

At conferences, we always get people who have never heard of our products, or have heard of them but don't really use them, or use them a daily. Then there is always a group that always 'just LOVES' them. This year, when asking around the responses almost all fell into the last two categories, many of whom had a list of feature requests, only to find their requests will be solved in 1.1. That's another story.
...BUT...BUT ...No-one seemed to have heard of this site: http://sqldeveloper.oracle.com
That site is SQL Developer Exchange!

Now you might have a few regular URLS at your fingertips... www.oracle.com, technet.oracle.com, or even google.com and asktom.oracle.com

From http://sqldeveloper.oracle.com you can add your own feature requests. Admittedly, for a while it was difficult to find out if something had already been requested, or to see if we had even looked at your requests. Now we have updated and tweaked the feature area a touch. The more we use it, then more we see there are more things we could do. However, you can now sort on last updated, date created and search. You can see if a feature has made it into 1.1 or is still on a list for a future release.
Please VOTE: If you see a feature you like or want, please select that feature and add a vote and a comment. If there is only one request and no further votes, then we might assume the community is not after such a feature.

Please keep adding requests. What more is there to say. Requests may be little or large, we'll update the site with feedback and, who knows, your request might make it into a release sooner than you think. If you don't tell us, we don't know.

The main page for http://sqldeveloper.oracle.com has a link to the forums and the main OTN page for SQL Developer. It allows you to see snippets other folk find useful and there are a few hints and tips too.

Make http://sqldeveloper.oracle.com work for you...

Busy, Busy at Open World

2006-10-25T14:55:00.000Z

It seems everyone linked to Open World is busy, busy. There are the Oracle staff who are on Hands On Session duty up near Union Square in the Hilton, for an hour or two , then back down to the Moscone for a shift on the demo pods and then back to the Hilton to do a presentation. There are a few aching feet around.
Then of course as an attendee, if you want to do some Hands On and then see more on the demogrounds, and then attend a talk, you're also dashing up and down.

Kris Rice, SQL Developer architect, and I had 2 talks at the Hilton yesterday, so we just hung out up there until we were done. Our attendees didn't though, because there was a paper down at the Moscone between ours they wanted to see. There were a few out of breath folk!

I attended Thomas Kurian's keynote, one of the 2 simultaneous keynotes running on Monday. It was jam packed full of information. As an employee and working in the area either previously with the JDeveloper crowd, or now as one of the Database Tools PM, I've heard most of this before, but there was one piece that really "wow'ed" me.
At the end of the talk, they announced the Oracle Developer Depot. It is focused on the Java, SOA developer and is a great concept. So often this new technology is intimidating and all you need is an example to see how something works, and help on getting setup so that you can at least start and some sample code, installed right where you need it, so that you can get started. That is what this site does. Very, very impressive.

Unrelated to the depot, but related to SQL Developer is the Migration Workbench team. They are here at the show and demonstrating how you can migrate your data structures and data from other database to Oracle. It's an extension to SQL Developer. Very nice. Donal Daly heads up that team and he has just started a blog. Take a look at that here: http://donaldaly.blogspot.com/

Tent City

2006-10-23T12:01:00.000Z

It's a dilemma for me. I really want to keep my blog focused on products and not to have daily ramblings about "life and the universe" , this week tho' I think I'm going to slip into "diary mode". You've been warned!

I think it's got to be done. Just look at this (double click for full size or potter over to my photo blog)
We're in San Francisco. If you're an Oracle blog reader, you'll have seen entries and you'll be seeing lots of entries about the conference this week. For the past few years we have spilled over from Moscone South and North into Moscone West. These are city blocks, I must add, and the halls and demo areas and theatres are on levels above and below street level. A lot happens under the street between Moscone North and South. So what happens when you run out of space under the street? You tent over the street! Fab! That street scene is Howard and it's a pretty busy street as a rule. So for this week, there going to be an interesting traffic puzzle while cars have to divert around the block and negotiate the one way system that is San Francisco. The tented area is carpeted, the length of the city block. This is where invited guests were welcomed last night and where attendees will do meals for the week. Quite remarkable. The buzz on Monday should be great.

If a piece of your world touches Oracle, then there should be something for you here this week. There are talks in Moscone and in the Hilton near Union Square, where a bunch of the technical talks will be in smaller rooms, seating around 90 - 120. Small is good, because it's less intimidating for people to ask questions and it's a cozier atmosphere. At a conference with 45 000 people milling around, cozy might not be good for all the attendees. Our SQL Developer talks on Tuesday are almost at capacity. Last I looked we'd been swapped to a bigger room.

Kris has managed to get another hour for us in the OTN Lounge on Tuesday, so hopefully we'll be able to talk to more folk. Also we'll be hanging around at OTN night on Monday while there is a big Linux Install fest and of course folk will be installing SQL Developer too.

When we're not presenting we'll be on the demo rounds in Moscone West, demonstrating some new stuff! It should be fun.

I was chatting to the Times Ten team yesterday afternoon. They are very excited about showing off Times Ten capabilities using SQL Developer.

I'll be back and hope to keep you informed about snippets of news through the week. When I'm back next week, I'll get back to the serious business of product news.

aah, of course if you're at OOW in San Francisco this year, stop by the booth and say Hi!

Running PL/SQL Code Using SQL Developer

2006-10-04T17:07:00.000Z

I have seen a few questions about running PL/SQL using SQL Developer and what to do when encountering the PL/SQL error message "wrong number or types of arguments in call to '||' "

Here is some code for you. Assume I am using the HR schema and the EMPLOYEES table in Oracle 9i or 10g or Express Edition. In this instance we'll create a Package, but the principle is the same for functions and procedures. I'll give you some code to play with. (We have this same example in one of the JDeveloper tutorials on OTN if you are a JDeveloper user.)

Using SQL Developer 1.0:

Step1. Create a new Object TYPE. Here is the code:

create or replace type EMP_REC as object
(
employee_id number(6),
last_name varchar2(25),
job_id varchar2(10),
manager_id number(6),
hire_date date,
salary number(8,2),
commission_pct number(2,2),
department_id number(4)
);

You can copy and paste the code into the SQL Worksheet or use the dialog for Types, in the Connections Navigator.

Step 2. Create the package spec. Here is the code:

create or replace package emp_fetcher as
FUNCTION get_emp(emp_no IN NUMBER) RETURN emp_rec;
END;

Step 3. Create the package body. Here is the code:

CREATE OR REPLACE PACKAGE BODY emp_fetcher AS
FUNCTION get_emp(emp_no IN NUMBER) RETURN emp_rec IS
emp_found employees % rowtype;
emp_rtn emp_rec;
BEGIN
SELECT *
INTO emp_found
FROM employees
WHERE employees.employee_id = emp_no;
emp_rtn := emp_rec(emp_found.employee_id,
emp_found.last_name,
emp_found.job_id,
emp_found.manager_id,
emp_found.hire_date,
emp_found.salary,
emp_found.commission_pct,
emp_found.department_id);
RETURN emp_rtn;
END;
END;

Step4.
Now we are ready!
You can either select the package spec in the Navigator and use the context menu to run it.

or you can switch to the PL/SQL editor and run the Package from there.

Step 5. Now this is where we are headed. Oracle SQL Developer (and JDeveloper, if you are interested) creates an anonymous block for you. It provides a place to add "IN" parameters and the DBMS_OUTPUT for "OUT" parameters.

Here is the window before you add the parameters:

You can see the EMP_NO is a NULL. Update this with the employee number as required.
Note the DBMS_OUTPUT statement is commented out. Remove the comments and specify which of the values in the record you are interested in. The example I have given is "LAST_NAME", but if you refer to the record type we created, "SALARY" would also do, as would "hire_date" .

This is where you'd run into the error specified above. If you uncomment the DBMS_OUTPUT command, you must then pass the correct value.

Have fun with your PL/SQL.

Count and Filters...

2006-09-18T11:42:00.000Z

I'm still testing and working with SQL Developer 1.1, so have neglected this blog for a while. I am sure you're out and about doing the last of summer things, assuming you live north of the equator, or looking forward to spring, if you're down south! Either way, I doubt you'll have missed this too much.

I'm working through my morning tasks and today, that includes reviewing my bugs that have been closed. Here's one I've just looked at.

Users on the forum were frustrated that when you apply a filter to your data, in SQL Developer 1.0 and then tried to Count the records, the response was a count of all the records. So apart from writing the SQL, the request was to know how many records are returned, with a filter applied. Here you go, a simple context menu option:

Quick Sneak Peak...Setting NLS parameters

2006-09-05T14:49:00.000Z

How quickly a week passes and I've not written up any news. It seems there is no time for blogging... (that, or there is no news)

Anyway, I'm working through a new drop of SQL Developer and thought I'd mention this set of preferences very briefly. The preference category is NLS Parameters and the screen shot says it all.

In case it doesn't, there have been a number of queries about the date format and being able display the time portion in a date field. Other users have configured the .cfg file to add in other NLS settings. Now you'll be able to handle these from the preferences window, using the NLS Parameter settings.

A JDeveloper Tip that Might be Handy for SQL Developer

2006-08-25T14:24:00.001Z

There is a blog that might be a handy one to dip into every so often. The site is DuffBlog. Brian Duff is a developer in the JDeveloper team. He is one of the JDeveloper developers who works closely with the SQL Developer development team. Brian is works on the IDE and framework and so is an important chap for us. Anyway Brian blogs from time to time and if you're a SQL Developer user, there may be a few useful gems for you among his entries.

Take a look at Seriously Customizing JDeveloper. The basic premise in the entry is about how to disable any extension in JDeveloper. The very last paragraph of that blog entry says "As an added bonus, you can also do this in SQL Developer..."

Then he did it again this week...just a little piece of useful information on opening files....

Just thought you might be interested.

SQL Developer Exchange is Launched!

2006-08-22T11:58:00.000Z

Over the years the Oracle users have often asked for some way of logging feature requests. It's part of what I do, collect feature requests and take them to development and present the business case. The thing is, which request is important? Is it the guy who shouts loudest and longest? Is it the eloquent chap who puts forward a good business case or is it the big spender? If it is any of these, no luck then for the quiet guy in the small company, who might have a very sound idea. Even when we have a list of features, does it make sense, for Oracle, for the product, for the broader community, to add this particular feature to a particular product? It's not worth getting bogged down in the possible negatives, product feedback and feature requests are important.

The SQL Developer team launched the web site SQL Developer Exchange this week, to support the SQL Developer user community. The Exchange has a few sections, they include:
Reports
SQL Developer has a bunch of shipped reports. They're great. They are the kind of reports I used to run when I was steeped in doing SQL and PL/SQL stuff in the good ole days. They're the reports we all have in scripts and carry around with us. The tool also offers you the ability to add your own reports. So if the scripts you carry around have more reports than we ship, then just add your reports to SQL Developer. This reports.xml file, of user defined reports, can be saved on the network and you can share this file. (see earlier blog entries on creating reports) What's useful about the Exchange is that you almost certainly have a few fab reports that others might find useful. Next time you run a report that you run daily or weekly, consider sharing it on the Exchange. I'm sure others will find it very useful too.
Snippets
I've recently blogged on adding snippets, now you should share the snippets you have added! Remember, not every function, date conversion etc from the Oracle online database doc is in our list of snippets, so you can just use the code from the online doc and add these to your snippets file. Perhaps you and your team use bits of code regularly, add them to the snippets section in the Exchange.

Tip and Tricks
You know how sometimes when you watch someone else using a program and you see them do something quick and easy, something that you never knew about, something that causes you to say "oh wow". That's what we want added. You know the "hey, did you know that you can rerun any report for any user, by toggling the user in the drop list to the right of the screen?" type of comment.
And if you like a tip, which has been added, then rate it!

Feature Requests
To bring you back to where I started. You can request features in SQL Developer. Once a feature request has been added, you can rate the features. So you might think the feature warrants a "must have" (score = 10), but someone else might rate it 3 or 4. The more votes we get, the better we get a feel for whether something is really wanted or if it's a lone voice in the dark. Our main concern is that while we have tracked many feature request on the forum, we're concerned that some smaller enhancements may have become lost in threads.

I should warn you that this does not guarantee a feature will make the product as there are a lot of reasons why something does not make the cut, but we want to build a product that the community loves, so your feedback is key.

The site is built using Application Express, referred to as APEX in the corridors of Oracle and probably known to you as HTMLDB! ;-) So as soon as you type in http://sqldeveloper.oracle.com you'll be flicked through to an APEX URL for the SQL Developer Exchange

Have fun, and do send us feedback

Conferences, conferences, conferences...

2006-08-21T12:37:00.000Z

Oracle Open World
I know, it's only August, but if you've not booked your hotel for Oracle Open World in San Francisco yet, you're probably not going to get your first choice...That's for the end of October and should be a cracking event. It looks like it'll be bigger than ever and, for a change, there'll be lots of technology sessions on the go. (We have a couple for SQL Developer) Check it out if you're in a quandary about going. What's more, you'll get to meet some of the developers!

OUG Scotland
I'm going to run into a deadline in a few days for my material for the Scottish User Group. That's September, in Glasgow. I have a small workshop running on the first day and a paper on the second. It should be good fun.
I should mention that the UKOUG conference is also going to be a big event this year. It's November, so we have time to book hotels.

AUSOUG
Then, this morning my feedreader rolled over Doug Burn's latest blog entry, with loads of details on the Australian User Group and I see there a few top names speaking. What's more I see that Chris Muir is doing a SQL Developer workshop...

So, whether you are on the map to the left of Greenwich, or to the right or in the middle... you should find a conference near you (well more or less).

Add Your Own Snippets Today in SQL Developer 1.0

2006-08-17T14:00:00.000Z

Earlier this week I showed you some screens shots for adding snippets using a menu in 1.1, that's no good to you today, so this entry is devoted to adding snippets manually in 1.0.

Kris has blogged on this before in a few places. His initial entry on this, back in January, referred to raptor files and extensions. The blog entry is still accurate, just that file names have changed, which is why I thought I'd update this manual step for SQL Developer 1.0. Kris goes on later to talk about an extension that he put on SourceForge that "enables support for read only XMLTypes and adds export to excel in native excel in the worksheet". He updated this extension (blog entry 8th March) to support highlighting some text and then adding it to the snippets file through the extension.

So this entry is not about adding an extension, but creating a snippets file and hooking that into your SQL Developer install. I'll break this down into steps.

I'm using SQL Developer 1.0

Step 1. Create a directory for your snippets. I have created a "mysnippets" directory. I have chosen to keep it with my product installation, but there is no reason why you can't keep this file centrally on a separate file server, you just need to direct SQL Developer to where you have placed it.

Step 2. Create a snippets.xml file. If you have a favorite XML editor, use that. I used Textpad!

Step 3. Add the snippet code.

The XML is pretty straight forward. Here is a skeleton of code:

I know, Textpad does not highlight code like that! I used JDeveloper's XML editor, it uses code highlighting and so it's easier to see the structure.

Here is an example of a small snippet file. Once again, I used JDeveloper's XML editor, for the code highlighting. Notice within one Category, i.e.XML tag , I have 2 separate snippets.

The piece of code between the CDATA [] is what is dragged onto the worksheet. Below is that same piece of code to cut and paste into Textpad:

<?xml version = '1.0' encoding = 'UTF-8'?>
<snippets>
<group category="Sue's Repeated Queries" language="PLSQL">
<snippet name="ALLEmployees"
         description="This is just selecting columns from EMPLOYEES">
  <code>
    <![CDATA[SELECT employee_id, first_name, last_name, email,
               phone_number,hire_date, job_id, salary,
  commission_pct, manager_id, department_id, last_name_id
                FROM hr.employees;]]>
  </code>
</snippet>
<snippet name="Sue's Locations Countries Regions Join"
         description="This does the inner join">
  <code>
    <![CDATA[SELECT COUNT(locations.city) "Number of Cities",
  regions.region_name regions
                FROM(locations INNER JOIN countries USING(country_id))
                INNER JOIN regions USING(region_id)
                GROUP BY regions.region_name ORDER BY 1;]]>
  </code>
</snippet>
</group>
<group category="More Character Functions" language="PLSQL">
<snippet name="REPLACE"
         description="REPLACE lets you substitute one string
                     for another as well as to remove character strings.">
  <code>
    <![CDATA[REPLACE(expr,search_string,replacement_string)]]>
  </code>
</snippet>
</group>
</snippets>

So now you have the snippets.xml file. The last step is to tell SQL Developer about it.

Step 4. Update the sqldeveloper.conf file. You'll find this in the sqldeveloper/jdev/bin directory.

Once again, using Texpad to edit the sqldeveloper.conf file, add the line of code below to point to your snippets file.

AddVMOption -Draptor.user.snippets=../../mysnippets/snippets.xml

It's important to note that the path depends on where you have the file. You can put the full path here. Note too that any path separators must be # UNIX style forward slashes '/', even on Windows.

Step 5. Test it!

Handling Snippets: Sneak Peak into 1.1

2006-08-15T17:30:00.000Z

Here's another sneak peak into what's coming in SQL Developer 1.1.

In 1.0 you can add your own snippets. Kris gives some suggestions on his blog on how to go about it. There are a other notes about suggesting alternative approaches. We're not happy about some of these i.e. The suggestion to yank the XML file from the jar, update it with the snippet and replace the file in the jar. I have done it myself and it works and is easy. It's not recommended, because if you get into the habit of essentially hacking the jar files you can break stuff. Simpley out, editing the .jar files is not supported. Kris does give you a way to do it legally.

Anyway, to save all that hassle, we've given you a small menu and dialog

The menu shows the Add and Edit icons in the Snippet window. Below is the dialog for adding a new category and snippet.

So you can use the drop list and add a new snippet to any existing category, or you can create your own category by just typing it in.

I didn't know you could do that!

2006-08-11T11:32:00.000Z

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.

The file empxt1.dat contains the following sample data:

360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus
361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper
362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr
363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aalda

The file empxt2.dat contains the following sample data:

401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel
402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega
403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins
404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard

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 as
required. (The full code is below)

You can of course just copy and paste this code into the SQL Worksheet and execute it, but I challenge you to use the dialog, just this once. Here's the code:

CREATE TABLE admin_ext_employees
(employee_id NUMBER(4),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
job_id VARCHAR2(10),
manager_id NUMBER(4),
hire_date DATE,
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
department_id NUMBER(4),
email VARCHAR2(25)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY admin_dat_dir
ACCESS PARAMETERS
(
records delimited by newline
badfile admin_bad_dir:'empxt%a_%p.bad'
logfile admin_log_dir:'empxt%a_%p.log'
fields terminated by ','
missing field values are null
( employee_id, first_name, last_name, job_id, manager_id,
hire_date char date_format date mask "dd-mon-yyyy",
salary, commission_pct, department_id, email
)
)
LOCATION ('empxt1.dat', 'empxt2.dat')
)
PARALLEL
REJECT LIMIT UNLIMITED;

Checking the Data

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:

INSERT INTO employees (employee_id,
first_name, last_name, job_id, manager_id,
hire_date, salary, commission_pct, department_id, email)
SELECT * FROM admin_ext_employees;

You can finish off the exercise with that too if you fancy. Either way, you'll have done a small walk through External tables.

Now, back to work.

Another Peep into the Future...F4 and Filters

2006-08-10T17:50:00.000Z

A quick entry today. There are 2 small features I said I'd show you, the first is the much requested 'Describe' (F4) feature.

The problem we're solving is that you might be halfway through writing a statement in the worksheet and you want to have more detail about a table or view. You can of course expand the object in the navigator to see the columns. Sometimes that is not enough, so you can click on the table or view and click through the tabs, but what you really want is to be able to describe any object and have the definition show up in a modeless window. If you're lucky enough to work with two monitors, or even have a really big monitor, you can move this window off to one side and carry on with your SQL, while the window describing the object stays visible. The image below shows the dialog describing the table selected.

There will be two ways to access this Describe feature; one is through a short cut key, currently F4, which is configurable, as are your other shortcut keys.The other is through a context menu as shown below:

The second is a brief look at the filtering option. In SQL Developer 1.0, when you filter objects in the Navigator, you are really only limited to searches for objects that start or end with a letter or letters, or those that contains a letter or letters. e.g. E%. or %EMP% pr %ING. Here is the old filter:

The new allows for a combination of requests. Here is an example of the new...

Let's Get Started with an Extension for SQL Developer (in 10 Steps)

2006-08-08T12:03:00.000Z

So apparently it's possible, but have you seen it done? You've heard about it. They say..."If there is other stuff you want in SQL Developer just build an extension..." Huh, it's as easy as that ?

So let's do it. Let's add an extension to SQL Developer, starting at the very beginning. Getting started, from scratch...installs and all.
I'll be using JDeveloper. If you're an Eclipse user, then you should check out Kris Rice's blog.

For this exercise, I created a fresh directory and did fresh installs, to keep things uncluttered and so that the directory can just be deleted when I'm done. If you have the products installed, you can use them, just skip to Step 4. If you want to follow along, then create a separate, fresh directory. If you use your existing products, the extensions can all be deleted, so you can tidy up afterwards. I did the full exercise over broadband connection, and none of the installs or downloads take long.

STEP 1. Getting JDeveloper
Download JDeveloper from OTN. [If you already have JDeveloper 10.1.3, you can use that.] If you are downloading JDeveloper for this purpose, then all you need is the Java install. (The others are bigger downloads and installs. The process that follows is the same.)

I chose the Complete Install (i.e. with JDK 5.0.5). This is a 111MB download. If you already have the JDK 5.0 on your machine you do not need this. I did this install for completeness. When I install SQL Developer I'll install without the JDK, as I only need it once.
Step 2. Getting SQL Developer
Download SQL Developer (or use what you have)

Please note, the SQL Developer site talks about JDK 1.5. Well, this is JDK 5.0. Go figure. There was a name change, so they're the same. The J2SE latest release is 5.0. The name and version changed from 1.5.0 to 5.0. So where you see 1.5, read 5.0! The short of it is that all you need is the smaller SQL Developer install. (28.9MB) if you've already downloaded and installed JDeveloper with JDK 5.0.

Step 3. Installing the Software
Unzip both files. ( I like to keep things clean, so have a JDeveloper folder and a SQL Developer folder)

Step 4. Getting Started
Start JDeveloper ( Double click jdeveloper.exe)

Step 5. Setting up the SDK

5a. In JDeveloper, use the menu Help -> Check for Updates...

5b. Select the Official Oracle Extensions, you don't need any of the others, so deselect them if they are already checked. Click Next.

5c. Select the Extension SDK from the list. Deselect any others that might be checked. You can always come back and get other updates later. Click Next.
When you select the SDK and click next, the Check for Updates utility will go to OTN to get the required update.The installation is 15.95 MB. Before the download starts, you'll be prompted for your username and password. This is your OTN account, the same as the sign on you use for the Forums. If you are new to this, just create a sign on at this point. It's free.

5d. Once the updates are installed, you'll need to restart JDeveloper. You'll be prompted to upgrade from a previous version, because you have installed the SDK updates, which will bring in your database connections and other preference settings. As I am working with a clean install, I do not want to do this.

5e. As JDeveloper restarts, you'll be prompted to install the SDK Samples. I'll be using the samples in this walk through, so if you want to follow along, say Yes.
You do not need the samples to create your own extension. You only need the SDK which has now been installed.Step 6. Finding the New Workspace

A new Application Workspace, extensionjdk.jws is created. It has a long list of projects, all of which are a sample extensions in varying degrees of complexity. They are all documented in the SDK help.

If you are using an existing JDeveloper install and you already have a number of workspaces, you might not be aware that this new application workspace has been created for you. It's easy to see if you have started the process from scratch and have nothing else built yet!

Either way, find the extensionjdk.jws application workspace.

Step 7. Working with the Samples

There are loads of samples, but let's start with something easy. What I want to do is install the HelloX.project into my SQL Developer setup. Find HelloX.jpr and expand the project. Expand the packages oracle.ide.extsamples and hellox. You'll see an image file and a .java file. If you double click the java file you'll see the java code. That's what you will need to write when you get down to it. What we're going to do is assume we wrote that fab code and just deploy it.

Step 8. Changing the Deployment Profile

8a. What we want to do is deploy this project to SQL Developer. By that I mean, having "written my extension", I want to run it from SQL Developer. To do this we need to change the deployment profile for the project. Select HelloX.deploy

8b. Double-click, or select Properties from the context menu, to invoke the profile properties dialog.8c. You want to change the location of the .jar file. (That's the 'zipped' version of all your files for the extension) Browse to your SQL Developer folder and find jdev\extensions (This 'jdev' folder name will change in SQL Developer 1.1.)
8d. Click OK to apply the changes.

Step 9. Deploying the Updated File
9a. You'll see the HelloX.deploy is now in italics. It means you have changed the file. Click Save.

9b. You can now deploy the file. Use the context menu and select Deploy to Jar.The Deployment Log window should display a message indicating the file was deployed to your SQL Developer folder.

Step 10. Now to Run the Extension in SQL Developer

Start SQL Developer!

This particular piece of Java Code adds a new item to the New Gallery (trust me on this one) So, in SQL Developer use the File -> New... menu option to invoke the New Gallery. Expand General -> Projects. You should see the HelloX option displaying in the New Gallery.

Double-click HelloX to invoke and use the extension.

There you go.

In a while I'll do a blog entry on building a small extension from scratch.

In the meantime, have a think about a few extensions you might like to build! Or play with more of the Samples in the SDK. Remember, if you're a PL/SQL junkie, and not a Java junkie, then buy your Java buddie some coffee in exchange for a few suggestions here or there.

...and I loved this book....Head First Java by Kathy Sierra - Now she knows how to write a great techie book...

SQL Developer Reports: Sneak Peak 2

2006-08-03T13:01:00.000Z

More in the Sneak Peak series...I'll try to alternate with current and next release blog entries, so that you are not frustrated by future features. As the last entry was on running files in SQL Developer today, it means this entry is a piece about what's coming down the line. (It's mostly screen shots - as before, click each image for larger view.)

I am sure by now you have read and absorbed LewisCunningham's fab article, previously mentioned, covering all SQL Developer reports, shipped and user defined.

In release 1.1 we bring you the same functionality and more. In 1.1, you'll be able to create master/detail reports anduse the new charting capabilities. Also, users will be able to query and kill sessions using this reporting functionality. This latter, a much requested feature. I hasten to add, this is all subject to change, but will give you an idea of what's coming down the line. For more information of planned features, please read the statement of direction on OTN.

The first screen shot illustrates a new report in SQL Developer; a chart displaying the object distribution for a user. You'll also have access to this charting capability, when creating user defined reports.

Of course you'll still have the option of switching users to run the report for another user, without too much difficulty. Below is the same report, but I've switched from HR in my Enterprise database to HR in my XE database, who's not a busy user!

The context menu on this section of reports has also changed. There are a few useful options, including offering you the ability to import or export reports. Notice too that you'll be able to create your own folders and add reports in this section, thus giving you the added benefit of keeping all your Data Dictionary reports together.

Now for a little change to a report you know...

Under the Sessions node, you currently have a Sessions report. In SQL Developer 1.1, this has been changed to a master/detail report, as illustrated in the image below. It would be tedious here to go through each of the tabs, but you can see for the active SYSTEM session selected, more detail is displayed below.

Users with the privileges to do so...

...can kill a session.

Let's briefly look at creating a user defined master/detail report.

I'll do a basic EMP and DEPT report for SCOTT. In the image below, note the ability to test your query. When testing, initially you select and run for a particular database connection. The test feature is all the more useful when you have a more complex report, with a few more clauses, driving the data selection.

I could have chosen to display this as a chart instead. Having selected Chart for the report style, I get more options for defining the chart. See the Chart Details tab to the bottom left.

Now to add the detail (child) report. Note the bind variable matches the column name in the master report.

Before you save the report, you can test it again. This time you'll be testing the full master/detail report and can verify that not only do your queries work, but that you do indeed have the correct binds. I won't show you that test, instead have just shown you the executed report below. Notice the same column ordering ability I mentioned in SneakPeak1.

More sneaks peaks next week.

Run a File in SQL Developer: Easing the Pain

2006-08-01T16:40:00.000Z

At some point in your working day, assuming you potter with databases, you're probably going to want to run a script. Typically we might use SQL*Plus for this and copy and paste the full directory path. I tend to start the SQLPLUS.exe, and once logged in locate and drag the file to the SQL> prompt and run it. (By using drag n drop, I get the full path and file name.) If I start SQLPLSW.exe, either I type in the full path and file name or resort to various copy and paste steps.

A colleague sitting nearby made one of those "Hey Sue, I really like this feature" comments today. So I wandered over to see her 'feature of the moment'. She was in SQL Developer and running a bunch of SQL scripts. SQL Developer has really eased the pain of doing this task. Maybe the following will help you to.

Start with SQL Developer and open the file you want to run. I'll use demobld as it's short and sweet and pretty safe to execute. (demobld drops and creates EMP and DEPT and is typically used to refresh SCOTT's tables. To find demobld.sql, do a search on your database directory or find another simple script to run.) If you look at the image below, I have circled File ->New, as this is where you can start to open a file. You can see that I have previously opened demobld and so can use the "reopen" menu option. [click image for larger view]

Of course, if you have associated SQL Developer with .sql files, then just double click the file and it'll open in SQL Developer.

Now the first thing to notice is the name of the tab, is the file name. If you have a bunch of tabs open this is useful. The next thing, which is often a great frustration to our users, is the Run related commands are all grayed out.

That's because you have not yet associated the file with a user! Use the droplist on the right-hand side to select your user.

I selected the SCOTT connection. As soon as a connection is selected, the run commands are available. You should use Run Script (F5), as F9 only executes a single statement. This will run the file for your selected user.

By running the script using F5, you run all the commands as the connected user and the output is printed to the window below. If it's a long script with lots of feedback, you can scroll up and down through the window. SQL*Plus can be maddening like that if you don't spool everything out to a file.
...and of course, if you want to rerun the script for another user? Just change the user by using the droplist!

Something to Whet the Appetite: Sneak Peak 1

2006-07-31T14:38:00.000Z

Have you been to the SQL Developer home page on OTN recently? Did you spot the Statement of Direction? We have just released this document on OTN and hope it'll give you an idea of the features planned for next few releases.

A statement of direction typically does not drill down to the finer level of the features, so as I was tracking forum issues and the progress of bugs and enhancements logged in the latest developer build, I thought you might be interested in a sneak peak at a few little bits you've been asking for. You won't see these until SQL Developer 1.1, and I'm not a great fan of 'vaporware', so I won't do too many of these, but in the next few weeks I'll do a few little blog entries to show a few things to look out for in SQL Developer 1.1.

Take a look at the Table Definition Tabs:

The illustration shows one of the most frequently requested features, which is to be able to sort on column headers. This feature is throughout the product now on any grid of data, whether it's instance data or the object definitions. Notice that once you've clicked on the column header, in this case Column Name, the direction of the sort is also indicated.

(Just click on the image for a larger view)

The second illustration shows another frequently asked for feature, which is to display a single record on a form layout. This is particularly useful if you have great chunks of text that you need to read.

More later this week.

Reporting with SQL Developer

2006-07-17T10:49:00.000Z

Know nothing about the Reports available in SQL Developer? Want to know how to create drill down reports? Oracle Ace Lewis Cunningham (blogs on I Ttoolbox ) has written and published a great article about all the reports you can run using SQL Developer. He also goes on to show you how to create your own reports, even your own drill down reports and he gets into the nitty gritty of fixing the titles of parameters for your user defined reports that require parameter screens.

For a few weeks this article will be easily accessible from the front page of OTN, after that we'll link to the article from the SQL Developer home page, so you'll be sure to continue to have access to this detail.

Remote Debugging with SQL Developer

2006-07-13T16:04:00.002Z

It occurs to me that while we talk about using the remote debug facility in SQL Developer that you may not know how to use it. So in a few steps and with a few screen shots, I'd like to show you how it's done.
(Please note, to see any of the images clearly, just double click on them.)
1. Let's start with a connection to the database. Create a database connection. (File -> New Connection) and complete the details. I only use the basic tab, so don't need to set up tnsnames or anything else. You'll see the database is on my own machine in this example, but it need not be.

2. Now you can connect to any objects this user owns, using SQL Developer. You can browse the various objects the user has access to. I'm only interested in this procedure, which my user HR owns.

3. You can run and debug this procedure in SQL Developer, but that's not the purpose of the exercise. What we want to do is debug the procedure when it is executed from elsewhere, such as another programme or application. The starting point is to start a remote debug session in SQL Developer. You do this from the Database Connection as shown.

4. A dialog will display, requesting the listening port number and the IP address of the machine running SQL Developer - effectively it's your computer, listening for the database to connect and using that port range to do so. You can set the range of ports through a Preference in SQL Developer. If you run into issues, look out for firewalls between the machines, blocking these ports.

5. Once you have set the remote debug details, you should see the run manager display these.

6. Now you should start a remote session. Using a SQL *Plus command line session will do.

* Invoke SQL *Plus for this user
* In the SQL *Plus session enter the following command:
exec DBMS_DEBUG_JDWP.CONNECT_TCP( '127.0.0.1', 4000 );

You should recognize the parameters from the previous dialog.

If you are debugging an application remotely, then this PL/SQL procedural call will need to go into that application, just for the debug purposes. You'll remove it afterwards.

7. Return to SQL Developer and set a breakpoint in the procedure.
Note: If you are debugging a procedure, you must remember to Compile for Debug, before you can start debugging.

8. Now you need to return to the SQL*Plus session and execute your procedure. If you debug in SQL Developer, an anonymous block is created for you to execute the procedure. In this case you'll need to write one to execute the procedure from SQL*Plus.

Note: In the procedure we have a DBMS_OUTPUT command, so you should also add the 'Set Serveroutput on' command.

9. As soon as you execute the anonymous block, you'll be returned to SQL Developer to debug the session there. Step into the code as you would in a usual debug session.

10. You can watch data and modify values in the same way as a normal debug session.

In this example, I'll modify the hire date.

11. Once you have reviewed the data, or made the modifications you want, resume debugging. Once the debug session is complete the control is returned to your remote session. In this case, SQL*Plus. You'll notice the modified date reflected in the output.

Quite a long piece, I know, but I think we often veer away from the unknown or untried. Hopefully once you have walked through an example, you might be able to make more use of this very useful feature.

ODTUG's new Website

2006-06-28T10:20:00.000Z

Following my last post, Kent has now posted a comment on how you can find his presentation on the new ODTUG site. You can download his talk on the site here:

If you look at the URL...http://www.odtug.com/pls/htmldb/f?p=500:...
you'll note the htmldb format. This is because the site has been redeveloped using Application Express (htmldb). It's worth noting this on 2 fronts. One is that many folk think that an Apex (affectionate abbreviation for Application Express) is not capable of producing complex or interesting sites and the other is that the redesign makes getting to articles much easier.
You'll see that some articles are for members only (Kent's is public). Not sure what drives the decision as to which articles are public and which are for members only, so you'll need to watch for that. There are certainly a good number of public articles and presentations available on the site.

ODTUG and Quality Data Models through Humiliation?!

2006-06-19T20:34:00.000Z

This week I'm in Washington DC at the ODTUG annual conference. For those of you who don't know, this is a well established Oracle Developer's User Group, originally focused primarily on Oracle Designer (then CASE) with a close second in Oracle Forms.
Today's keynotes saw two old familiar faces; Marco Tilli and Sohaib Abassi, both long time friends of the conference and in their time, were Volley Ball players on whichever beach or piece of sand wherever that year's ODTUG conference was being held. Back, this time not talking about Forms, Portal or Designer, but how we should all by now be more aware of SOA, of how business services can be reused, of integration and of the whole importance of data integration and its importance in the industry today.
With Java and the whole J2EE explosion, ODTUG have had to reinvent themselves. Something I was not sure they'd do with success, but I think they have cracked it. The halls are buzzing with old familiar faces. Some developers are not currently working with Oracle technology, yet still they return, the contacts and friends made at this event too important to lose. Other developers are reinventing themselves, learning new technology and bringing wisdom and experience to the table. This year, not too many grumpy old men!

Interesting too was the large number of first timers at the conference this year. I'm always amazed at this, the number of newbies arriving at the various conferences I attend. It's always a healthy percentage.

This year's ODTUG conference offers half day hands-on sessions alongside regular presentations. The weekend prior to the event, they ran a full day Web Architecture Symposium, which I have only heard highest praise for, and a couple of full day hands on sessions. I'd say attendees can get their monies worth if they participate in all on offer, specially if they get involved and mingle and talk, listen and learn!

Peer Reviews
The first talk I attended was Kent Graziano's talk, "Data Design Reviews: Using Extreme Humiliation to Ensure Quality Data Models". What a title!
Using the peer reviews they use within his department as a basis for his talk, he told us about building effective data models. They have a fixed set of standards, build logical models, which must conform to those standards, and have rigorous peer reviews. Once accepted, these logical models are then transformed to physical models, which have to conform to standards, and then have rigorous reviews of these. If and when these all pass and are accepted, the DDL is generated and reviewed before being passed on to the department that consumes them. They do not spend months on building models before reviews, but have incremental builds, so the review is brief, less than an hour, and they have them often.
I think the rigorous could be scary, specially for new folk, but the peer to peer knowledge transfer and the team work must outweigh any disadvantages. I loved the sound of the approach. (Specially having seen a few sick models in my time) I also think this practice can and should be translated to any part of a company - from those writing coding, to those writing papers and producing power point presentations. You might think this would slow down the pace, but I suspect teams would be more efficient, and the resulting work of a much higher quality.

Aah, by the way, Kent stressed, "it's business, it's not personal" For example in a peer review, when you criticize the name of entity, it's not because you don't like it, it's because it doesn't comply to the standard. I think so often we are too defensive, we assume someone is critiquing us, our approach, but if you have a set of standards, then things are less subjective and in the end, better for the good of the project and in the end, the company.
If you can, take a look at Kent's paper on the ODTUG site. It should be up after the conference. Kent is also author of a few books on data models, if you are on the lookout for one.