Testing and Debugging Procedures with SQL Developer

Purpose

This tutorial covers how to execute a DDL script, review changes to the database objects, create, execute, test and debug a procedure.

Time to Complete

Approximately 60 minutes

Introduction

Oracle SQL Developer is a free and fully supported graphical tool that enhances productivity and simplifies database development tasks. Using SQL Developer, users can browse, edit and create database objects, run SQL statements, edit and debug PL/SQL statements, build PL/SQL unit tests, run reports, and place files under version control.

In this tutorial, you use SQL Developer Release 4.1 to examine various tasks.

Prerequisites

Before starting this tutorial, you should:

• Have installed Oracle SQL Developer Release 4.1 or above
• Have access to an Oracle Database 11g database that has the sample schema installed.
• Grant HR user DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE privileges.
• Performed the Re-engineering Your Database Using Oracle SQL Developer Data Modeler 4.1 tutorial.
• Downloaded and unzipped the files.zip into your working directory.

In this topic, you will create a database connection to the HR Schema in SQL Developer:

1. Double click on the SQL Developer icon on the Desktop.
   

2. The first time SQL Developer is open, the "Start Page" is displayed. You can deselect the Show on Startup check box to turn it off.

   

3. In the Connections tab, right click Connections and select New Connection

   

4. Instructions and result (including collapsible image with text file for accessibility):

   A New / Update Database Connection dialog opens. Enter the connection details as follows and click Test.

   Connection Name: hr_orcl
   User Name: hr
   Password: <your password> (Select the Save Password checkbox)
   SID: <your own SID>

   

   Note: In this tutorial the Service Name is specified instead of SID.

5. The status of your test is 'Success'. Click Connect in the New/ Update Database Connection dialog to create the connection.

   

6. Expand the hr_orcl connection. Notice all the object types. Expand Tables. In the next section, you examine the objects currently in the hr schema.

   

In this topic, you review the existing objects in the hr schema.

1. Expand the EMPLOYEES table. Notice that the column definitions are listed.

   

2. Click the DEPARTMENTS table in the navigator.

   

3. Notice that the information in the EMPLOYEES tab was replaced by the DEPARTMENTS table information. If you want the table information in the tab to remain frozen, select the Pin icon to Freeze the pane.

   

4. Then click the EMPLOYEES table again in the navigator.

   

5. Notice this time you have 2 tabs, one for each of the tables because the DEPARTMENTS table pane is frozen.

   

6. You can see the data in the EMPLOYEES table. Click the Data subtab.

   

7. The data in the EMPLOYEES table is displayed. You can also enter a SQL statement in the SQL Worksheet. Click the hr_orcl tab.

   

8. Enter the following SQL statement and select the Execute SQL Statement icon.

   select * from employees
   where job_id like '%SA%';

   

9. The Query Results are displayed. In the next topic, you run the script you generated in the previous tutorial on Data Modeler.

   

In this topic, you execute the DDL script you generated in the Data Modeler tutorial. If you did not complete the previous tutorial, you can access the solution using the dm_mods.sql in the files folder.

1. Select File > Open.

   

2. Locate the dm_mods.sql file and click Open.

   

3. This SQL file contains all the DDL to change the HR Schema so that it is synchronized with the model changes you made in the previous tutorial. When you execute this script, the PROJECTS and TASKS tables will be created, and the new COST_CENTER column will be added to the DEPARTMENTS table. Scroll down to review the DDL.

   

4. Select the hr_orcl connection from the list and click OK.

   

5. All the statements in the DDL script executed successfully

   

6. Click the Refresh icon to refresh the list of tables.

   

7. Notice that the new tables PROJECTS and TASKS are contained in the list. Expand the DEPARTMENTS, PROJECTS and TASKS table nodes and review the results. In the next topic, you create a procedure and run it.

   

In this topic, you create, execute and debug a procedure that determines the commission any employee receives based on a sales amount and the employees commission percentage.

1. A script with the procedure has already been created so you can open the file. Select File > Open.

   

2. Locate the proc.sql  and click Open.

   

3. Click the Run Script icon to create the AWARD_BONUS procedure.

   

4. Select the hr_orcl connection and click OK.

   

5. The procedure was created and compiled with an error. To see the error, expand Procedures in the navigator.

   

The procedure created in the earlier section was created with an error. You can locate errors in the code by debugging the code. You have to run a script before you actually start the debug process

Before you Debug

In order to debug a sub program you should have DEBUG privileges.

1. To check the privileges you can execute a SQL statement

   

You can see that the hr user doesn't have DEBUG CONNECT and DEBUG privileges

2. If you don't have the required DEBUG privileges, a SYSDBA role user has to assign them. Login as a SYSDBA user

   

3. Execute the grant commands and ACL(Access Control List) script shown as a SYSDBA user

   

   

4. Now login as hr user, who has a non-sysdba user role

   

5. Execute the SQL statement shown to check whether required privileges are granted to hr user

   

Debugging

1. Now open the AWARD_BONUS procedure you created earlier. Compile the procedure

   

   You can see the error message in the compiler log. It specifies a line number where the error occurred

2. Modify the code in line 13 by adding a semi colon. Select the Compile icon.

   

   Run the procedure by clicking on Run icon.

3. The Run PL/SQL dialog window appears. Notice that the values for EMP_ID and SALES_AMT are currently set to 1.

   

4. Change the default values to 149 for EMP_ID and 2000 for SALES_AMT and click OK.

   

5. Note that the procedure executed successfully and the value for salary was changed. To see how debug works, you create a break point. Click the line number 7.

   

6. When a break point is created at line 7, the execution will break at line 7 and allows developer to monitor the data held in different variables. Click the Debug icon.

   

7. Click OK to accept the same input values as before.

   

8. The debugger is running and has stopped at line 8. Click the Smart Data tab. The Smart Data tab holds the values of variables in the PL/SQL block. These are currently set to NULL.

   

9. You can see all the data manipulated in the procedure in the Data tab.

   

   You see that the current values of l_salary and l_commission are NULL.

10. Click the Step Over icon to move to the next statement in the procedure.

    

11. Notice the values for l_salary and l_commission have changed to the existing values in the database, as the execution of select statement is complete, you can see the values from the database are fetched into the variables in the procedure.

    

12. Click the Step Over icon again to move to the next statement.As the execution of the update statement completes, you can see the new values of salary and commission in the Data tab

    

13. Notice that the debugger moved to the next statement in the procedure. You want to run the rest of the procedure, click the Resume icon.

    

14. Procedure execution and debugging is complete. In the next topic, you create a test repository so that you can create and run a unit test.

    

In this topic, you create a database user called UNIT_TEST_REPOS. You create this user to hold the Unit Testing Repository data. You will then create the repository in the schema of the user that you created. 

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: oracle
   Select Save Password checkbox
   Role: SYSDBA
   Service Name: pdb1

   

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

   

4. Enter the following information and select the Granted Roles tab.

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

   

5. Select the Connect and Resource roles and click Apply.

   

6. In the Quotas tab, check the Unlimited check box for the USERS tablespace

   

7. The unit_test_repos user was created successfully. Click OK.

   

8. 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.

   

9. Enter the following information and click Connect.

   Connection Name: unit_test_repos_orcl
   Username: unit_test_repos
   Password: oracle
   Select Save Password checkbox
   Service Name: pdb1

   

10. The unit_test_repos user and unit_test_repos_orcl connection were created successfully.

    

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

    

12. Select the unit_test_repos_orcl connection and click OK.

    

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

    

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

    

15. Enter oracle for the sys password and click OK.

    

16. The grant statement is shown. Click Yes.

    

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

    

18. The grant statements are displayed. Click Yes.

    

19. 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.

    

20. The grant statements are displayed. Click Yes.

    

21. Your repository was created successfully. Click OK.

    

Now that the Unit Testing Repository has been created, you will create a unit test for the PL/SQL procedure you created earlier in this tutorial. Then you will run the unit test to see if various values will work

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 '+' icon 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 149 and SALES_AMT to 2000 and click Next.

   

10. Select the '+' icon to add a validation and select Query returning row(s) from the drop down list.

    

11. 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 = 149
    AND salary = 11200;

    

12. Click Next.

    

13. In the Specify Teardown window,click the '+' icon and select Table or Row Restore from the drop down list.

    

14. Leave the Row Identifier as Primary Key and click OK.

    

15. Click Next.

    

16. Click Finish.

    

17. Expand Tests. Your test appears in the list.

    

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

    

19. Run the test by clicking the Debug Implementation icon. 

    

20. The Progress window appears.

    

21. When the test completes, the results are displayed. Click Close.

    

• Oracle SQL Developer Data Modeler on OTN

• Oracle Data Modeling and Relational Database Design course

• Oracle Learning Library