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

Lokesh Gaglani

Software Test Engineer

more>>

Purpose

This tutorial shows you how to perform a unit test of your PL/SQL code in Oracle SQL Developer 3.0.

Time to Complete

Approximately 30 minutes

Overview

The SQL Developer unit testing framework involves a set of sequential steps for each test case. The steps are as follows, including the user input for before the step is run and the framework activities for the step while the test is being run.

Identify the object to be tested.
- User Input: Identify the object, such as a specific PL/SQL procedure or function.
- Framework Activities: Select the object for processing.
Perform any startup processing.
- User Input: Enter the PL/SQL block, or enter NULL for no startup processing.
- Framework Activities: Execute the block.
Run the unit test object.
- User Input: (None.)
- Framework Activities: Execute the unit test.
User Input: Identify the expected return (result), plus any validation rules.
- User Input: (None.)
- Framework Activities: Check the results, including for any validation, and store the results.
Perform any end processing (teardown).
- User Input: Enter the PL/SQL block, or enter NULL for no teardown activities.
- Framework Activities: Execute the block.

Prerequisites

Before starting this tutorial, you should:

Install Oracle SQL Developer 3.0 from OTN. Follow the release notes here.
Install Oracle Database 11g with sample schema.
Unlock the HR user. Login to SQL Developer as the SYS user and execute the following command:
alter user hr identified by hr account unlock;

Download and unzip the files.zip to a local folder on your file system. In this tutorial, we use the C:sqldev3.0 folder.

Create a Procedure to Award Bonuses to Employees

In the HR schema, you will create a PL/SQL procedure called AWARD_BONUS which will calculate an employee's bonus if they have a commission_pct. The input parameters for the AWARD_BONUS procedure are the emp_id and the sales_amt. The emp_id identifies the employee, the sales_amt is used in the bonus calculation. Perform the following steps:

1 .	If you installed the SQL Developer icon on your desktop, click the icon to start your SQL Developer and go to Step 4. If you do not have the icon located on your desktop, perform the following steps to create a shortcut to launch SQL Developer 3.0 directly from your desktop. Open the directory where the SQL Developer 3.0 is located, right-click sqldeveloper.exe (on Windows) or sqldeveloper.sh (on Linux) and select Send to > Desktop (create shortcut).
2 .	On the desktop, you will find an icon named Shortcut to sqldeveloper.exe. Double-click the icon to open SQL Developer 3.0. Note: To rename it, select the icon, press F2 and enter a new name.
3 .	Your Oracle SQL Developer opens.
4 .	Right-click Connections and select New Connection.
5 .	Enter the following and click Test: Connection Name: HR_ORCL Username: hr Password: <your_password> Select Save Password checkbox Hostname: localhost Port: 1521 SID: <your_SID>
6 .	Check for the status of the connection on the left-bottom side (above the Help button). It should read Success. Click Save. Then click Connect.
7 .	Now you need 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 employees WHERE employee_id = emp_id; IF commission IS NULL THEN RAISE comm_missing; ELSE UPDATE employees SET salary = salary + sales_amt*commission WHERE employee_id = emp_id; END IF; END award_bonus;
8 .	Your procedure was created successfully. In the next section, you will create a database user for the unit testing repository.

Creating a Database User for the Testing Repository

In this section, you create a database user called UNIT_TEST_REPOS. You create this user to hold the Unit Testing Repository data.

Perform the following steps:

1 .	Create a connection for the SYS User. Right-click Connections and select New Connection.
2 .	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: <your_SID>
3 .	Your connection was created successfully. Expand the sys_orcl connection and right-click Other Users and select Create User.
4 .	Enter the following information and select the Roles tab. Username: unit_test_repos Password: <your_password> Default Tablespace: USERS Temporary Tablespace: TEMP
5 .	Select the Connect and Resource roles and click Apply.
6 .	The unit_test_repos user was created successfully. Click Close.
7 .	You now need 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.
8 .	Enter the following information and click Connect. Connection Name: unit_test_repos_orcl Username: unit_test_repos Password: <your_password> Select Save Password checkbox Hostname: localhost Port: 1521 SID: <your_SID> The unit_test_repos user and unit_test_repos_orcl connection were created successfully.

Creating the Unit Testing Repository

In order to create a unit test, you need to create a unit testing repository. You will create the repository in the schema of the user that you created. Perform the following steps:

1 .	Select Tools > Unit Test > Repository, then select Select Current Repository.
2 .	Select the unit_test_repos_orcl connection and click OK.
3 .	You would like to create a new repository. Click Yes.
4 .	This connection does not have the permissions it needs to create the repository. Click OK to show the permissions that will be applied.
5 .	Login as the sys user and click OK.
6 .	The grant statement is shown. Click Yes.
7 .	The UNIT_TEST_REPOS user needs select access to some required tables. Click OK.
8 .	The grant statements are displayed. Click Yes.
9 .	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.
10 .	The grant statements are displayed. Click Yes.
11 .	A progress window appears while the repository is created.
12 .	Your repository was created successfully. Click OK.

Creating a Unit Test

Now that the Unit Testing Repository has been created, you need to create a unit test for the PL/SQL procedure you created earlier in this tutorial. Perform the following steps:

1 .	Select View > Unit Test.
2 .	In the Unit Test navigator, right-click Tests and select Create Test.
3 .	In Select Operation, select the HR_ORCL connection that you used to create the AWARD_BONUS procedure.
4 .	Expand Procedures, select AWARD_BONUS and click Next.
5 .	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.
6 .	In Specify Startup window, click and select Table or Row Copy from the drop down list box.
7 .	Enter EMPLOYEES for Source Table and click OK. Note that the table affected by the test will be saved to a temporary table and the query to the table is automatically generated.
8 .	Click Next.
9 .	In the Specify Parameters window, change the Input string for EMP_ID to 177 and SALES_AMT to 5000 and click Next.
10 .	In the Specify Validations window, select to create a process validation.
11 .	Select Query returning row(s) from the drop down list.
12 .	Specify the following query and click OK. This query will test the results of the change that the unit test performed. SELECT * FROM employees WHERE employee_id = 177 and salary = 9400;
13 .	Click Next.
14 .	In the Specify Teardown window,click and select Table or Row Restore from the drop down list.
15 .	Leave the Row Identifier as Primary Key and click OK.
16 .	Click Next.
17 .	Click Finish.
18 .	Expand Tests. Your test appears in the list.

Running the Unit Test

Next you will run the unit test to see if various values will work. Perform the following steps:

1 .	Select the AWARD_BONUS test in the left navigator. Notice that the test details are displayed on the right panel.
2 .	Run the test by clicking the Debug Implementation .
3 .	The results are displayed. Notice that the test ran successfully. Click Close.
4 .	Expand AWARD_BONUS in the navigator to see the detail nodes.
5 .	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.
6 .	Enter empty_comm_pct for the Test Implementation Name and click OK.
7 .	Select empty_comm_pct in the left navigator to show the test details for this implementation.
8 .	Change the Input parameter for EMP_ID to 101 and SALES_AMT to 5000. Click Debug Implementation again.
9 .	Click Yes to save your changes before running the test.
10 .	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.
11 .	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 Debug Implementation
12 .	Click Yes to confirm changes.
13 .	Notice that the test executed successfully this time because the exception was handled. Click Close.
14 .	At this point, you want to run the test and save the results. Click run .
15 .	Your test run has been saved with results for both implementations.

Write your comment now

Courses