As Published In November/December 2008

TECHNOLOGY: SQL Developer

Programming Productively

Use Oracle SQL Developer to work efficiently with PL/SQL constructs.

You can become a more productive PL/SQL developer when you use efficient methods to debug code that won’t compile, locate constructs you want to reuse, test your code, and learn how much time your program spends in each subprogram. Working with PL/SQL in Oracle SQL Developer can help you accomplish these tasks. In the November/December 2007 issue of Oracle Magazine, this column covered PL/SQL development primarily from a debugging—including remote debugging—perspective. In this column, you’ll learn how to

• Create, edit, and run PL/SQL code efficiently
• Work with stored procedures and packages productively
• Access Oracle Database 11g features that enable PL/SQL hierarchical profiling and highly detailed search

This column’s examples use Oracle SQL Developer 1.5.1. You also need access to the sample HR schema in any Oracle Database instance.

Working with Anonymous Blocks

You can use Oracle SQL Developer to create anonymous PL/SQL blocks. Anonymous blocks are not stored in the database, so you can use them to test a piece of functionality quickly.

Start Oracle SQL Developer, and create a new database connection for the HR sample schema. (For detailed information on creating a connection, follow the link to “Creating a Database Connection” or see “Making Database Connections,” in the May/June 2008 issue of Oracle Magazine .) A SQL worksheet opens automatically.

Follow these steps to create and run an anonymous block that calculates the bonus amount for a given employee’s salary and displays the employee ID, bonus amount, and bonus rate:

1. Enter the code from Listing 1 into the SQL worksheet.

Code Listing 1: Calculating the bonus amount

DECLARE -- declare and assign values
  bonus_rate  NUMBER(2,3) := 0.05;
  bonus         NUMBER(8,2);
  emp_id       NUMBER(6) := 120;  -- assign a test value for employee ID
BEGIN
-- retrieve a salary from the employees table, then calculate the bonus and 
-- assign the value to the bonus variable
  SELECT salary * bonus_rate INTO bonus FROM employees
    WHERE employee_id = emp_id;
-- display the employee_id, bonus amount, and bonus rate
     DBMS_OUTPUT.PUT_LINE ( 'Employee: ' || TO_CHAR(emp_id) 
         || ' Bonus: ' || TO_CHAR(bonus) || ' Bonus Rate: ' || TO_CHAR(bonus_rate)); 
END; 
/

2. Click the DBMS Output tab (below the SQL worksheet) and the Enable DBMS Output button.
3. Run the PL/SQL code by clicking the SQL worksheet’s Run Script button or pressing F5.
4. Click the DBMS Output tab to see the employee ID, bonus, and bonus rate.

Using Code Insight

Now return to the SQL worksheet, and insert a new row in the declaration section, below the declaration of emp_id (insert the cursor and press Enter). In the new row, type

emp_name varchar2(25);

Note that as you start typing varchar2(25) , a code insight list appears so that you can select varchar2 rather than type it. Code insight is available for you in the SQL worksheet and in the PL/SQL Code Editor, which you’ll work with later in this column.

Now edit the SELECT statement so that it looks like this:

SELECT salary * bonus_rate, last_name INTO bonus, emp_name FROM employees
  WHERE employee_id = emp_id; 

As you start typing last_name , note that code insight can access details from the EMPLOYEES table and from the PL/SQL block declaration.

Navigating to Errors

Next, replace TO_CHAR(emp_id) with TO_CHAR(emp_name) . Press F5 to run the code. You should see the employee’s last name in the DMBS Output tab.

Now remove emp_name from the SELECT statement and rerun your code (press F5). An error report displayed on the Script Output tab shows the line number and column position of the error. Right-click in the SQL worksheet gutter (to the left of the code text), and select Toggle Line Numbers to switch on the line numbers. This helps you navigate to the point of the error. Find the error, fix the code (restore emp_name to the SELECT statement), and rerun it.

Refactoring Code

When you refactor code, it’s important not to change the meaning or function of any code you modify. Oracle SQL Developer 1.5 introduced code refactoring to help make refactoring easier and safer.

In the SQL worksheet, highlight the complete SELECT statement, right-click, and select Refactoring -> Extract Procedure... , as shown in Figure 1.

Figure 1: Refactoring

Select Stored , and enter bonus_update in the Name field. Click OK .

The Confirm Running SQL window shows the PL/SQL stored procedure that will be created. Note that each of the parameters created is an IN OUT parameter. Because you are passing in two parameters and expecting two out, you can adjust these later as appropriate. But the code will work as defined, so for now, click Yes.

Review the code in the SQL worksheet, and note that the SELECT statement has been replaced with a call to the new PL/SQL bonus_update procedure. Now expand the Procedures node in the Connections Navigator. Right-click BONUS_UPDATE , and select Compile . Now run the PL/SQL code in the SQL worksheet again. Select the DBMS Output tab to verify that the code works as it did before.

Working with Stored Procedures

Next, select BONUS_UPDATE in the Connections Navigator to display the code you created by refactoring. Right-click BONUS_UPDATE , and select Run.

Whenever you run a PL/SQL procedure or package in Oracle SQL Developer, a Run PL/SQL window opens. It displays an anonymous PL/SQL block that is automatically created for PL/SQL programs. Here you can see the IN OUT parameters listed at the top and in the anonymous block. Update this PL/SQL block by changing

BONUS_RATE := NULL;
EMP_ID := NULL;

to 

BONUS_RATE := 0.05;
EMP_ID := 120;

Scroll down to review the rest of the code, and click OK. The output now displays in the Running - Log window.

Working with Packages in the PL/SQL Editor

Oracle SQL Developer provides convenient features for creating and maintaining both the specifications and bodies of PL/SQL packages.

Creating the specification. To create a new package, start by creating the package specification. Select the Packages node in the Connections Navigator. Right-click, and select New Package... . Enter Emp_Actions as the package name, and click OK . The EMP_ACTIONS tab that appears is the PL/SQL Code Editor. With it you can create, edit, compile, and debug your PL/SQL code. You also use the same code insight, refactoring, and formatting features that are available in the SQL worksheet.

A PL/SQL specification skeleton is prepared for you, based on the details you supplied. Replace the TODO comment with the code in Listing 2, and click the Compile button (or press Ctrl-Shift-F9) to compile the code, as shown in Figure 2.

Code Listing 2: Editing the emp_actions package

PROCEDURE hire_employee (lastname VARCHAR2, 
    firstname VARCHAR2, email VARCHAR2, phoneno VARCHAR2,
    hiredate DATE, jobid VARCHAR2, sal NUMBER, commpct NUMBER,
    mgrid NUMBER, deptid NUMBER);
PROCEDURE remove_employee (empid NUMBER);
FUNCTION emp_sal_ranking (empid NUMBER) RETURN NUMBER;

Figure 2: PL/SQL package specification

Generate and edit the body stub. Once you have compiled the code, the EMP_ACTIONS specification appears in the Connections Navigator. Right-click EMP_ACTIONS , and select Create Body . Oracle SQL Developer creates a skeleton (or stub) for each of the procedures and functions you declared in the specification, along with the parameters declared. In the gutter, right-click and select Collapse procedure declarations/bodies . Replace each of the three program units with the corresponding code in Listing 3, and then compile. Right-click EMP_ACTIONS in the Connections Navigator, and select Run.

Code Listing 3: Editing a package specification

PROCEDURE hire_employee (lastname VARCHAR2,
    firstname VARCHAR2, email VARCHAR2, phoneno VARCHAR2, hiredate DATE,
    jobid VARCHAR2, sal NUMBER, commpct NUMBER, mgrid NUMBER, deptid NUMBER) IS
    min_sal    employees.salary%TYPE; -- variable to hold minimum salary for jobid
    max_sal    employees.salary%TYPE; -- variable to hold maximum salary for jobid
    seq_value  NUMBER;  -- variable to hold next sequence value
  BEGIN
    -- get the next sequence number in the employees_seq sequence
    SELECT employees_seq.NEXTVAL INTO seq_value FROM DUAL;
    -- use the next sequence number for the new employee_id
    INSERT INTO employees VALUES (seq_value, lastname, firstname, email,
     phoneno, hiredate, jobid, sal, commpct, mgrid, deptid);
     SELECT min_salary INTO min_sal FROM jobs WHERE job_id = jobid;
     SELECT max_salary INTO max_sal FROM jobs WHERE job_id = jobid;
     IF sal > max_sal THEN
       DBMS_OUTPUT.PUT_LINE('Warning: ' || TO_CHAR(sal) 
                 || ' is greater than the maximum salary '
                 || TO_CHAR(max_sal) || ' for the job classification ' || jobid );
     ELSIF sal < min_sal THEN
       DBMS_OUTPUT.PUT_LINE('Warning: ' || TO_CHAR(sal) 
                 || ' is less than the minimum salary '
                 || TO_CHAR(min_sal) || ' for the job classification ' || jobid );
     END IF;
  END hire_employee;

-- code for procedure remove_employee, which removes an existing employee
  PROCEDURE remove_employee (empid NUMBER) IS
     firstname employees.first_name%TYPE;
     lastname  employees.last_name%TYPE;
  BEGIN
    SELECT first_name, last_name INTO firstname, lastname FROM employees 
      WHERE employee_id = empid;
    DELETE FROM employees WHERE employee_id = empid;
    DBMS_OUTPUT.PUT_LINE('Employee: ' || TO_CHAR(empid) || ', ' 
                      || firstname || ', ' || lastname || ' has been deleted.');
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Employee ID: ' || TO_CHAR(empid) || ' not found.');
  END remove_employee;

-- code for function emp_sal_ranking, which calculates the salary ranking of the
-- employee based on the minimum and maximum salaries for the job category
  FUNCTION emp_sal_ranking (empid NUMBER) RETURN NUMBER IS
    minsal        employees.salary%TYPE; -- declare a variable same as salary
    maxsal        employees.salary%TYPE; -- declare a variable same as salary
    jobid          employees.job_id%TYPE; -- declare a variable same as job_id
    sal             employees.salary%TYPE; -- declare a variable same as salary
  BEGIN
-- retrieve the jobid and salary for the specific employee ID
    SELECT job_id, salary INTO jobid, sal FROM employees 
       WHERE employee_id = empid;
-- retrieve the minimum and maximum salaries for the job ID
    SELECT min_salary, max_salary INTO minsal, maxsal FROM jobs
       WHERE job_id = jobid;
-- return the ranking as a decimal, based on the following calculation
    RETURN ((sal - minsal)/(maxsal - minsal));
  END emp_sal_ranking;

Just as when you worked with the stored procedure, the Run PL/SQL window displays code to help you run the procedures and functions in the package. Click EMP_SAL_RANKING in the Target list to display the anonymous block for this procedure. Update the block as follows:

1. Replace EMPID := NULL; with EMPID := 120; .
2. Replace 'v_Return: = ' with 'The employee's salary ranking is ' .
3. Click OK.
4. View the results in the Running - Log window.

Using the PL/SQL Hierarchical Profiler

This section is for readers who have access to Oracle Database 11g, which provides support for profiling PL/SQL code. Oracle Database 11g lets you determine how much time is spent on each subprogram within a program, how frequently a subprogram is accessed, and which other subprograms access a program.

The PL/SQL Hierarchical Profiler, which is implemented by use of DBMS_HPROF, stores the results in database tables. Oracle SQL Developer reviews each schema, tries to access the PL/SQL Hierarchical Profiler, and runs the scripts necessary to set up the environment. Once it is set up, you can repeatedly run and review the output through an easy-to-read report.

Using the preceding code examples, right-click the EMP_ACTIONS specification and select Profile . Select the EMP_SAL_RANKING procedure again. Click OK . If you have never run the PL/SQL Hierarchical Profiler for this HR schema before, a once-only window will display the setup details. Click Yes to execute the SQL and prepare to use the PL/SQL Hierarchical Profiler. Figure 3 displays the detail.

Figure 3: Preparing to use the PL/SQL Hierarchical Profiler

This dialog box is followed by one that creates the PL/SQL hierarchical tables. (This also executes only once.)

When the setup is complete, again right-click EMP_ACTIONS and select Profile in the Connections Navigator. This time, because the environment has been prepared and the tables are in place, the profile can be revealed. Select the Execution Profiles tab in the EMP_ACTIONS editor. (Each time you select Profile , you re-execute the PL/SQL code and a new profile is created.) Select the profile, and inspect the detail.

Select the Hierarchy tab, as shown in Figure 4. Even in as small a program unit as EMP_SAL_RANKING, you can see the hierarchical structure and the various program units called. More-complex PL/SQL programs reveal more detail, which can be of great benefit for tuning your code.

Figure 4: PL/SQL hierarchical profiles

Search for Detail

Oracle SQL Developer 1.5 introduced an Extended Search mechanism. It relies on PLScope—a new Oracle Database 11g PL/SQL feature—when it’s available and on basic search functionality when it’s not. When used with Oracle Database 11g, Extended Search drills into your PL/SQL code, looking for variable declarations, references, calls, and more. Follow these steps to try it out:

1. In Oracle SQL Developer, select View -> Extended Search.
2. Select the HR user, and enter hire% in the Name field. Leave the remaining fields with their default values.
3. Click Lookup . Note that the HIRE_DATE variable is displayed in addition to HIRE_EMPLOYEE.
4. Select any one of the search results, and note that Oracle SQL Developer opens the table where the variable is used.
5. Select the Usage list (available only when you are using Oracle SQL Developer against Oracle Database 11g), and review the options available.

Conclusion

This column has shown how Oracle SQL Developer can enhance your productivity when you write, edit, compile, and troubleshoot PL/SQL code. Whether you are a novice or an experienced PL/SQL developer, Oracle SQL Developer can make your day-to-day PL/SQL development tasks easier.
 

Send us your comments