Testing and Debugging Procedures with SQL Developer


Options



Before You Begin

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:

Creating a Database Connection

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.

    SQL Developer Start Page
    Description of this image
  3. In the Connections tab, right click Connections and select New Connection

    Create New Connection
    Description of this image
  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>

    New Connection Credentials
    Description of this image

    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.

    Test and Connect
    Description of this image
  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.

    Browsing the tables in HR schema
    Description of this image

Review Existing Objects 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.

    Browsing schema objects
    Description of this image
  2. Click the DEPARTMENTS table in the navigator.

    Departments table
    Description of this image
  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.

    Freezing the tabs
    Description of this image
  4. Then click the EMPLOYEES table again in the navigator.

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

    Employees and Departments tabs
    Description of this image
  6. You can see the data in the EMPLOYEES table. Click the Data subtab.

    Data sub tab for employees table
    Description of this image
  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.

    Execute SQL statement to view data
    Description of this image
  8. Enter the following SQL statement and select the Execute SQL Statement icon.

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

    SQL statement
    Description of this image
  9. The Query Results are displayed. In the next topic, you run the script you generated in the previous tutorial on Data Modeler.

    SQL statement execution result
    Description of this image

Executing a DDL Script

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.

    File menu
    Description of this image
  2. Locate the dm_mods.sql file and click Open.

    File browser
    Description of this image
  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.

    SQL script
    Description of this image
  4. Select the hr_orcl connection from the list and click OK.

    Select connection
    Description of this image
  5. All the statements in the DDL script executed successfully

    DDL script execution
    Description of this image
  6. Click the Refresh icon to refresh the list of tables.

    Refresh the database connection
    Description of this image
  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.

    New tables added
    Description of this image

Creating and Executing a Procedure

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.

    File menu
    Description of this image
  2. Locate the proc.sql  and click Open.

    Locate proc.sql in file browser
    Description of this image
  3. Click the Run Script icon to create the AWARD_BONUS procedure.

    Run the script
    Description of this image
  4. Select the hr_orcl connection and click OK.

    Choose hr_orcl connection
    Description of this image
  5. The procedure was created and compiled with an error. To see the error, expand Procedures in the navigator.

    Procedure compiled with an error
    Description of this image

Debugging a Procedure

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

    Check user privileges
    Description of this image
  2. You can see that the hr user doesn't have DEBUG CONNECT and DEBUG privileges

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

    Login in SYSDBA role
    Description of this image
  4. Execute the grant commands and ACL(Access Control List) script shown as a SYSDBA user

    grant commands
    Description of this image
    ACL script
    Description of this image
  5. Now login as hr user, who has a non-sysdba user role

    Login as hr
    Description of this image
  6. Execute the SQL statement shown to check whether required privileges are granted to hr user

    Check user privileges
    Description of this image

Debugging

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

    Award Procedure
    Description of this image

    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.

    Successfully compiled
    Description of this image

    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.

    Initializing values of variables
    Description of this image
  4. Change the default values to 149 for EMP_ID and 2000 for SALES_AMT and click OK.

    Assign new values to the variables
    Description of this image
  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.

    Successful execution of procedure
    Description of this image
  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.

    Start Debug
    Description of this image
  7. Click OK to accept the same input values as before.

    Accept input variables
    Description of this image
  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.

    Debug running
    Description of this image
  9. You can see all the data manipulated in the procedure in the Data tab.

    Data tab
    Description of this image

    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.

    Step Over
    Description of this image
  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.

    Values fetched from database
    Description of this image
  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

    New values of salary and commission
    Description of this image
  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.

    Resume and complete the execution of the procedure
    Description of this image
  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.

    Execution complete
    Description of this image

Creating a Unit Test Repository

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.

    New connection
    Description of this image
  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

    Connection properties
    Description of this image
  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.

    Create new user as sys user
    Description of this image
  4. Enter the following information and select the Granted Roles tab.

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

    New user properties
    Description of this image
  5. Select the Connect and Resource roles and click Apply.

    Select user privileges
    Description of this image
  6. In the Quotas tab, check the Unlimited check box for the USERS tablespace

    Allocate memory to USERS table space
    Description of this image
  7. The unit_test_repos user was created successfully. Click OK.

    User successfully created
    Description of this image
  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.

    New connection
    Description of this image
  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

    Oracle headquarters buildings
    Description of this image
  10. The unit_test_repos user and unit_test_repos_orcl connection were created successfully.

    Connection created successfully
    Description of this image
  11. Select Tools > Unit Test > Repository, then select Select Current Repository.

    Oracle headquarters buildings
    Description of this image
  12. Select the unit_test_repos_orcl connection and click OK.

    Select connection
    Description of this image
  13. You would like to create a new repository. Click Yes.

    Create new repository
    Description of this image
  14. This connection does not have the permissions it needs to create the repository. Click OK to show the permissions that will be applied.

    Create new permissions
    Description of this image
  15. Enter oracle for the sys password and click OK.

    Enter sys user password
    Description of this image
  16. The grant statement is shown. Click Yes.

    Grant privileges
    Description of this image
  17. The UNIT_TEST_REPOS user needs select access to some required tables. Click OK.

    Grant access to required tables
    Description of this image
  18. The grant statements are displayed. Click Yes.

    Execute grant statements
    Description of this image
  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.

    Privilege to manage repository owners
    Description of this image
  20. The grant statements are displayed. Click Yes.

    Execute grant statements
    Description of this image
  21. Your repository was created successfully. Click OK.

    Create repository successful
    Description of this image

Creating and Running a Unit Test

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.

    Select Unit Test
    Description of this image
  2. In the Unit Test navigator, right-click Tests and select Create Test.

    Select Create Test
    Description of this image
  3. In Select Operation, select the hr_orcl connection that you used to create the AWARD_BONUS procedure.

    Select hr_orcl connection
    Description of this image
  4. Expand Procedures, select AWARD_BONUS and click Next.

    Select Award_Bonus Procedure
    Description of this image
  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.

    Create with single dummy implementation
    Description of this image
  6. In Specify Startup window, click '+' icon and select Table or Row Copy from the drop down list box.

    Table or Row Copy in Startup Window
    Description of this image
  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.

    Enter employees for source table
    Description of this image
  8. Click Next.

    Click Next in the wizard
    Description of this image
  9. In the Specify Parameters window, change the Input string for EMP_ID to 149 and SALES_AMT to 2000 and click Next.

    Specify parameters
    Description of this image
  10. Select the '+' icon to add a validation and select Query returning row(s) from the drop down list.

    Process validation
    Description of this image
  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;
    Validation query
    Description of this image
  12. Click Next.

    Click next
    Description of this image
  13. In the Specify Teardown window,click the '+' icon and select Table or Row Restore from the drop down list.

    Specify tear down process
    Description of this image
  14. Leave the Row Identifier as Primary Key and click OK.

    Click ok
    Description of this image
  15. Click Next.

    Click next
    Description of this image
  16. Click Finish.

    Check the summary and click finish
    Description of this image
  17. Expand Tests. Your test appears in the list.

    Expand the unit test
    Description of this image
  18. Select the AWARD_BONUS test in the left navigator. Notice that the test details are displayed on the right panel.

    Unit test AWARD BONUS
    Description of this image
  19. Run the test by clicking the Debug Implementation icon. 

    Click Debug implementation
    Description of this image
  20. The Progress window appears.

    Progress window
    Description of this image
  21. When the test completes, the results are displayed. Click Close.

    Test result
    Description of this image

Want to Learn More?