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.

Run a File in SQL Developer: Easing the Pain

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!