SQL Developer, Migrations and Third Party Databases

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)?

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?

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

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!

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

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

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

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

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...

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

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

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!

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...

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

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

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!

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

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)

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

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.