Performing a Unit Test of Your PL/SQL in Oracle SQL Developer 2.1

Your test was successful. Click Connect.

Your connection was created successfully. You will create an EMPLOYEES2 table from the EMPLOYEES table for purposes of this tutorial. In the SQL Worksheet window, enter the following SQL command and click Run Statement.

create table "HR".EMPLOYEES2 as select * from "HR".EMPLOYEES;

Your table was created successfully.

Now you want to create a procedure. In the SQL Worksheet window, enter the following script and click Run Script. This code is also in the file award_bonus.sql from the directory where you downloaded the zip file from the Prerequisites section.

create or replace
 PROCEDURE award_bonus (
  emp_id NUMBER, sales_amt NUMBER) AS
  commission    REAL;
  comm_missing  EXCEPTION;
BEGIN
  SELECT commission_pct INTO commission
    FROM employees2
      WHERE employee_id = emp_id;
 
  IF commission IS NULL THEN
    RAISE comm_missing;
  ELSE
    UPDATE employees2
      SET salary = salary + sales_amt*commission
        WHERE employee_id = emp_id;
  END IF;
END award_bonus;

Your procedure was created successfully. In the next section, you will create a database user for the unit testing repository.

Create a connection for the SYS User. Right-click Connections and select New Connection.

Enter the following information and click Connect.

Connection Name: sys_orcl
Username:sys
Password: <your sys password>
Select Save Password checkbox
Role: SYSDBA
Hostname: localhost
Port: 1521
SID: orcl

Your connection was created successfully. Expand the sys_orcl connection and right-click Other Users and select Create User.

Enter the following information and select the Roles tab.

Username:unit_test_repos
Password: oracle
Default Tablespace: USERS
Temporary Tablespace: TEMP

Select the Connect and Resource roles and click Apply.

The unit_test_repos user was created successfully. Click Close.

You now want to create a connection to the unit_test_repos user. This user will hold the unit testing repository data. Right-click Connections and select New Connection.

Enter the following information and click Connect.

Connection Name: unit_test_repos_orcl
Username:unit_test_repos
Password: oracle
Select Save Password checkbox
Hostname: localhost
Port: 1521
SID: orcl

The unit_test_repos user and unit_test_repos_orcl connection were created successfully.

Select Tools >Unit Test > Repository, then select Select Current Repository.

Select the unit_test_repos_orcl connection and click OK.

You would like to create a new repository. Click Yes.

This connection does not have the permissions it needs to create the repository. Click OK to show the permissions that will be applied.

Login as the sys user and click OK.

The grant statement is shown. Click Yes.

The UNIT_TEST_REPOS user needs select access to some required tables. Click OK.

The grant statements are displayed. Click Yes.

The UNIT_TEST_REPOS user does not currently have the ability to manage repository owners. Click OK to see the grant statements that will be executed.

The grant statements are displayed. Click Yes.

A progress window appears while the repository is created.

Your repository was created successfully. Click OK.

Select View > Unit Test.

In the Unit Test navigator, right-click Tests and select Create Test.

In Select Operation, select the HR_ORCL connection that you used to create the AWARD_BONUS procedure.

Expand Procedures, select AWARD_BONUS and click Next.

In Specify Test Name window, make sure that AWARD_BONUS is specified for Test Name and that Create with single Dummy implementation is selected, then click Next.

In Specify Startup window, select Table or Row Copy from the drop down list box.

Enter EMPLOYEES2 for Source Table and click OK. Note that the table affected by the test will be saved to a temporary table and query to the table is automatically generated.

Click Next.

In the Specify Parameters window, change the Input string for EMP_ID to 177 and SALES_AMT to 5000 and click Next.

In the Specify Validations window, select the '+' icon to create a process validation.

Select Query returning row(s) from the drop down list.

Specify the following query and click OK. This query will test the results of the change that the unit test performed.

SELECT * FROM employees2
  WHERE employee_id = 177 and salary = 9400;

Click Next.

In the Specify Teardown window, select Table or Row Restore from the drop down list.

Change the Row Identifier to ROWID since the table you copied doesn't currently have a Primary Key and click OK.

Click Next.

Click Finish.

Expand Tests. Your test appears in the list.

Select the AWARD_BONUS test in the left navigator. Notice that the test details are displayed on the right panel.

Run the test by clicking the Debug Implementation icon.

The results are displayed. Notice that the test ran successfully. Click Cancel.

Expand AWARD_BONUS in the navigator to see the detail nodes.

At this point you want to test when an Employee does not have a commission percent to see what will happen. You can create another implementation of this same test and then change the test parameters. Right-click AWARD_BONUS and select Add Implementation.

Enter empty_comm_pct for the Name and click OK.

Select empty_comm_pct in the left navigator to show the test details for this implementation.

Change the Input parameter for EMP_ID to 101 and SALES_AMT to 5000. Click the Debug Implementation icon again.

Click Yes to save your changes before running the test.

Notice that you received an error. This error indicates that there was an exception because a commission_pct does not exist for this employee. You want to specify this exception in your test. Click Close.

For Expected Result, select Exception and enter 6510 in the field next to it. This means that an error will not be found if the exception has an error code of 6510. Click the Debug Implementation icon.

Click Yes to confirm changes.

Notice that the test executed successfully this time because the exception was handled. Click Close.

At this point, you want to run the test and save the results. Click the icon.

Your test run has been saved with results for both implementations.