Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)
Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.
Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks. With Oracle SQL Developer, you can browse database objects, run SQL statements and SQL scripts, and edit and debug PL/SQL statements. You can also run any number of provided reports, as well as create and save your own. This tutorial focuses on creating, compiling, running and debugging PL/SQL.
To create a database connection, perform the following steps:
Open Windows Explorer and double-click
Note: If you receive a dialog window asking whether you want to migrate settings from a previous release, click
In the Connections tab, right-click
Connections and select
HR_ORCL for the Connection Name (or any other name that identifies your connection),
HR for the Username and Password, specify your
<hostname> for the Hostname and enter
ORCL for the SID. Click
The status of the connection was tested successfully. The connection was not saved however. To save the connection, click
Once the connection is saved, you will see the database in the list. Expand
When a connection is opened, a SQL Worksheet is opened automatically. The SQL Worksheet allows you to execute SQL against the connection you just created.
The Connections Navigator in Oracle SQL Developer allows you to browse and edit database objects. This tutorial creates and debugs PL/SQL and uses a selection of tables from the HR schema. In this topic, you review the tables you will use later in the tutorial. Perform the following steps:
EMPLOYEES table to view the table definition.
To see the data, click the
DEPARTMENTS table in the navigator.
There are a number of constraints for the DEPARTMENTS table. When the table definition displays, select the
The dialog has a number of tabs, select the
Foreign Keys tab.
Review the Foreign Keys. Then click
Verify that the JOBS and LOCATIONS tables exist, and have data, by selecting each in the Navigator in turn and reviewing the definitions and data.
In this topic you create, edit and compile a PL/SQL procedure. Perform the following steps:
Right-click on the
Procedures node in the Connections Navigator, to invoke the context menu, and select
EMP_LIST as the procedure name. Then click the
+ to add a Parameter.
Double-click on the parameter name to allow you to change the value to
pMaxRows and then change VARCHAR2 to
The skeleton of the procedure with the parameter specified is displayed.
Replace the following PL/SQL:
With the following code:
(This code is also in the
plsql.zip into your working directory (i.e. d:\wkdir) in the file
CURSOR emp_cursor IS
SELECT l.state_province, l.country_id, d.department_name, e.last_name,
j.job_title, e.salary, e.commission_pct
FROM locations l, departments d, employees e, jobs j
WHERE l.location_id = d.location_id
AND d.department_id = e.department_id
AND e.job_id = j.job_id;
TYPE emp_tab_type IS TABLE OF emp_cursor%ROWTYPE INDEX BY BINARY_INTEGER;
i NUMBER := 1;
FETCH emp_cursor INTO emp_record;
emp_tab(i) := emp_record;
WHILE ((emp_cursor%FOUND) AND (i <= pMaxRows) LOOP
i := i + 1;
FETCH emp_cursor INTO emp_record;
emp_tab(i) := emp_record;
FOR j IN REVERSE 1..i LOOP
Notice how the reserved words are formatted by Oracle SQL Developer. To format the code further, right-click to invoke the context menu and select Format SQL.
Compile the PL/SQL subprogram by clicking the Save
button in the toolbar.
Procedures on the navigator, EMP_LIST can be viewed.
Note that when an invalid PL/SQL subprogram is detected by Oracle SQL Developer, the status is indicated with a red X over the icon for the subprogram in the Connections Navigator.
Compilation errors are shown in the log window. You can navigate to the line reported in the error by simply double-clicking on the error. Oracle SQL Developer also displays errors and hints in the right hand gutter. If you hover over each of the red bars in the gutter, the error message displays
In this case, the error messages indicate that there is a formatting error in the LOOP statement. After reviewing the code further, you see an extra parenthesis in the WHILE statement. Delete the extra parenthesis.
The procedure compiled successfully. You are now ready to run the procedure.
Once you have created and compiled a PL/SQL procedure, you can run it using Oracle SQL Developer. Perform the following steps:
EMP_LIST in the left navigator and select
This invokes the Run PL/SQL dialog. The Run PL/SQL dialog allows you to select the target procedure or function to run (useful for packages) and displays a list of parameters for the selected target. In the PL/SQL block text area, you will see the generated code that Oracle SQL Developer uses to call the selected program. You can use this area to populate parameters to be passed to the program unit and to handle complex return types.
The results are displayed in the Running - Log window.
Oracle SQL Developer also supports PL/SQL debugging with Oracle databases. In this topic, you debug a PL/SQL Procedure, step through the code and modify a value at runtime. Perform the following steps:
To assist with debugging, line numbers can be added to the
Code window. Click on the margin and a
Toggle Line Numbers tip will appear. Select it.
Edit on the toolbar.
Set a breakpoint in the EMP_LIST procedure by clicking in the margin at the line with the
OPEN emp_cursor; statement.
The Debug PL/SQL dialog should still show the value
PMAXROWS = 5; Click
The debugger should halt at the line where you placed the breakpoint. You can now control the flow of execution, modify values of variables and perform other debugging functions.
Note: If at this point you receive the error message "This session requires DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE user privileges", you need to complete Step 4 of
This takes you to the first line of the cursor. Click
You should now be selecting the first row of the cursor. Click
3 more times.
Data window starts to show a limited list of variables which are used in the line of code that is about to be executed, and in the previously executed line.
Right-click the line that reads
DBMS_OUTPUT.PUT_LINE(emp_tab(j).last_name); and select
Run to Cursor.
emp_tab >_ values >  > _value. You see the values of the fields in a given record of the table. Select the
LAST_NAME field and select
Change the name to something else and click
Select the Debugging - Log tab.
Click the Resume icon
to allow the PL/SQL to run to completion.
Check to see that your modified value is displayed in the Log window.