An Introduction to Oracle SQL Developer – Part III

Welcome to the next installment of this Oracle SQL Developer series. You can check out Part I and Part II here. In this post, we will cover the creation of a database function using Oracle’s free SQL Developer application.

Oracle’s SQL Developer application is a very powerful GUI interface for working with Oracle databases. SQL Developer makes those of us that are not classically trained Database Administrators (DBA’s) look like we know what we are doing.

Preparation

As we stated in the previous posts, you will need to download Oracle’s SQL Developer application. It’s free, but you will have to create an Oracle.com account to actually download the software.

At the time of this post, the current version is 2.1 and is available at

http://www.oracle.com/technology/software/products/sql/index.html.

The software is available for several types of platforms. Also, since it’s based on Java, there is a download version that includes the proper JDK. Once you uncompress the zip file, SQL Developer is ready to be used. Find the sqldeveloper.exe file and double click it to get started.

For this post we are also going to use Oracle’s HR example database that is packaged with Oracle 10g Express Edition. If you want to run Oracle 10g Express Edition, you can download it here:

http://www.oracle.com/technology/products/database/xe/index.html.

If you want a simple sql file of the data we will be working with, you can download it here. Open the file, highlight and copy everything (Command-A on OS X or CTRL-A on Windows) and paste it into your SQL Developer worksheet. Next, you will want to run the pasted text as a “script”.

Click the Run Script button which looks like a piece of paper with a green arrow in front of it. The Run Script button is indicated with a red box in the following image:

If you were to use the Run Statement button which sits to the left of the Run Script button, you run the risk of only executing the last sql statement in the whole list of statements we just pasted into the worksheet. The Run Script button will run all statement listed in the worksheet.

Once you have ran all of the statements you just pasted into the worksheet, your Connection Explorer should look like this:

Creating an Oracle Function Using SQL Developer

We are going to create a function that calculates how much an increase in sales commission (percentage of yearly wage) for our sales staff would cost the company.

If everything works well, the function will accept a number that represents the percentage of yearly wage that we want to use as the increase value, the function will use that percentage and calculate how much each sales staff member would be receiving and then display it on the screen.

To get started, right click on Functions in the connection explorer and choose New Function from the drop down menu. Doing so should display the Create PL/SQL Function window. Ours looks like this:

As you can see, we have given our new function the name CalcPayIncreaseforSalesStaff. We have also chosen the data type of NUMBER for our return value.  We also created an input parameter for our function titled RAISEPERCENT. Our input parameter will be of data type NUMBER and its mode is IN.

Next, click on the OK button and you should be presented with the code tab of your new function. We added the math calculation to our function’s code. Our function now looks like this:

If you need to copy and paste the function code, here it is as text:

create or replace

FUNCTION CALCPAYINCRESEFORSALESSTAFF( RAISEPERCENT IN NUMBER)

RETURN NUMBER IS PayIncreaseCost number;

sumsal number;

BEGIN

– First we must gather the total amount of sales staff yearly salary

– We select the value into our sumsal variable

SELECT sum(salary) INTO sumsal from employees where job_id like ‘SA_%’;

– Next we multiply sumsal by the percent we passed in as a parameter

– We are also selecting the result of the multiplacation into our return variable

SELECT (sumsal * RAISEPERCENT) INTO PayIncreaseCost FROM DUAL;

– Next we return the result of our function

RETURN(PayIncreaseCost);

END CALCPAYINCRESEFORSALESSTAFF;

The final step in creating a stored function using Oracle’s SQL Developer is to compile the function code. You should first compile for debugging. This will alert you to any issues during the compile process. The Compile for Debug option can be found by clicking on the down arrow next to the icon that looks like a cogs or gears. Here’s what our compile choices look like:

If everything compiles correctly, you should see something like this in your Messages Log window:

If you don’t see any errors listed, you should go back and compile the function using the Compile command (not the Compile for Debug command).  You should also see the new function listed in the connection explorer panel under the Functions heading.

In the future, if you ever need to adjust your new function, all you have to do is right click on it in the connection explorer panel. After right clicking on our new function in the connection panel, we can see the following options:

One of the nice features of a powerful GUI for databases is the ability to right click and choose an option rather than typing text on a command line. Especially if you are not a full time DBA, who can remember all of those commands?

Using Our New Function

When working with MySQL we can run a stored function by simply typing SELECT functionName();. When working with Oracle, we must always use a FROM clause in SELECT statements. For example, in MySQL you can add 3 + 3 with the following command line statement:

SELECT 3 + 3;

When working with Oracle, the SELECT 3 + 3; statement throws a “FROM keyword not found where expected” error. The solution is to use the DUAL table.

Think of the DUAL table as a blank table, dummy table or logic table. When working with Oracle SELECT statements, use FROM DUAL whenever you need a table to meet the requirement of a “FROM keyword not found where expected” error. For example, in Oracle, the following statement will return the number 6 :

SELECT 3 + 3 FROM DUAL;

In order to use our new function, we must select it with the FROM DUAL clause. Using our SQL Developer SQL Worksheet, we have incorporated the FROM DUAL clause and ran our SELECT statement.  Having passed .02 as our parameter for the amount of a raise, we can see that a 2% increase in payroll budget for the sales staff would cost the company $6,230. Our worksheet looks like this:

Wrap It Up

In this post we covered how to create a stored function using Oracle’s free SQL Developer application. We also covered a necessary clause in Oracle for SELECT statements that really have no logical table.

The example we provided was really simple. Feel free to let your non-DBA imagination run wild and create the stored functions that you need. Also, stay tuned for future segments of this series.

In the future we will cover backing up your database using Oracle’s SQL Developer, the “Holy Grail” of using SQL Developer (connecting to MySQL tables) and any other topics that you might request between now and then.