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.