Computing: Oracle SQL Developer

In order to create unit tests for oracle's procedures of functions you need to download sql developer. Follow this link: http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html

I will start explaining how to create unit test. The purpose of this post is not how to use sql developer. If you need to establish a new connection follow this link: http://www.oracle.com/technology/obe/sqldev/sqldev.htm

Create the following table

Then insert the following values

Now if you check the data of your table you will see the following:

Next create the following procedure

On the menu under Tools go to Unit Test and click "Select Current Repository"

Select your Repository Connection

Enter Your Username and Password

Add this point follow the guide to create the repository. You will need to grant permissions.

When you are done under View click Unit Test

A new tab on the left appears about Unit Test

Click right click on Tests and then "Create Test"

Specify your connection and then select the procedure that you created earlier "ADD_BONUS" and click next

Select "Create with single Dummy implementation" and click next

Select "Table or Row Copy"

Then click Browse and select the table "Accounts". Click OK and click OK again.

Next Click Next

For expected Result select "Success" and click next

For Process Validation select "Query Returning Rows"

Then you will the process Validation window. Enter the sql query that will be tested.
Select first_name from accounts where balance=3750

Then choose at step 6 select "Table or Row Restore". A new window "Teardown Process" will appear as the following. Click OK.

Finally, you will see a summary of the test that you are about to create. Click Finish.

On the left under test click on the test that you just created.

On the right you can see the details of the test. For the two input variables add
AC_ID ----> 234568 and
AC_BALANCE ------> 0.5

Now, click the debug icon on the top

Click "Yes" to save changes

As you can see the test was successful

Now if you click on the button next to debugging (run test) you can view
the results of the test.

The test was successul because we added 0.5 for AC_BALANCE
which means the following:

for customer 234568 the balance was 2500. After running the query
we said select this customer where balance is 3750.
Hence, after running the procedure we get
2500+2500*0.5 = 3750

If we set AC_BALANCE to 0.6 the the test will not pass.

If you wish to test a function (returns a value) instead of a procedure the steps are the same.

Consider the following table:

with some data like the following

Create the following function

Follow the same steps to create the test. When the time comes to enter the sql query which you will test write the following:
select sum(max_persons) from ab_slots where booking_id=1