25 August 2006
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.
22 August 2006
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:
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.
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!
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
21 August 2006
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!
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.
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).
17 August 2006
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
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'?>So now you have the snippets.xml file. The last step is to tell SQL Developer about it.
<group category="Sue's Repeated Queries" language="PLSQL">
description="This is just selecting columns from EMPLOYEES">
<![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
<snippet name="Sue's Locations Countries Regions Join"
description="This does the inner join">
<![CDATA[SELECT COUNT(locations.city) "Number of Cities",
FROM(locations INNER JOIN countries USING(country_id))
INNER JOIN regions USING(region_id)
GROUP BY regions.region_name ORDER BY 1;]]>
<group category="More Character Functions" language="PLSQL">
description="REPLACE lets you substitute one string
for another as well as to remove character strings.">
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.xmlIt'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!
15 August 2006
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 dialogThe menu shows the Add and Edit icons in the Snippet window. Below is the dialog for adding a new category and snippet.
11 August 2006
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
The file empxt2.dat contains the following sample data:
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.
required. (The full code is below)
CREATE TABLE admin_ext_employees
DEFAULT DIRECTORY admin_dat_dir
records delimited by newline
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')
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;
Now, back to work.
10 August 2006
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.
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...
08 August 2006
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 SDK5a. 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.
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.deploy8b. 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...
03 August 2006
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...
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.
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.
01 August 2006
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!