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
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 3.1 to examine various tasks.
Before starting this tutorial, you should:
- Have installed Oracle SQL Developer Release 3.1
- 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 Data Modeler 3.1 tutorial.
- Downloaded and unzipped the files.zip into your working directory.
Creating a Database Connection
In this topic, you will create a connection to the HR schema in SQL Developer.
Reviewing Existing Objects in the HR Schema
In this topic, you review the existing objects in the hr schema.
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 in the /home/oracle/Desktop/solutions/dm_lab directory in the VM.
Creating Table APIs
In this topic, you create API packages for the DEPARTMENTS table. Table APIs allows you to make PL/SQL calls to insert, update, delete records in your application without writing or managing raw INSERT, UPDATE, and DELETE statements.
Creating, Executing and Debugging 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.
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.
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
In this tutorial, you have learned how to:
- Create a database connection
- Review existing objects in the HR schema
- Execute a DDL script
- Create table APIs
- Create and execute a procedure
- Create a unit test repository
- Create and run a unit test
To help navigate this Oracle by Example, note the following:
- Hiding Header Buttons:
- Click the Title to hide the buttons in the header. To show the buttons again, simply click the Title again.
- Topic List Button:
- A list of all the topics. Click one of the topics to navigate to that section.
- Expand/Collapse All Topics:
- To show/hide all the detail for all the sections. By default, all topics are collapsed
- Show/Hide All Images:
- To show/hide all the screenshots. By default, all images are displayed.
- To print the content. The content currently displayed or hidden will be printed.
To navigate to a particular section in this tutorial, select the topic from the list.