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.
Overview
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:
1.
Open Windows Explorer and double-click
<your_path>\sqldeveloper\sqldeveloper.exe.
Note: If you receive a dialog window asking whether
you want to migrate settings from a previous release, click No.
2.
In the Connections tab, right-click Connections and select New Connection.
3.
Enter 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 Test.
4.
The status of the connection was tested successfully. The connection was not saved however. To save the connection, click Connect.
5.
The connection was saved and you see the
database in the list. Expand HR_ORCL.
6.
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:
1.
Expand the Tables node.
2.
Click the EMPLOYEES table to view the table definition.
3.
To see the data, click the Data tab.
4.
Click the DEPARTMENTS table in the navigator.
5.
There are a number of constraints for the DEPARTMENTS table. When the table definition displays, select the Constraints tab.
6.
Click the Edit icon.
7.
The dialog has a number of tabs, select the Foreign Keys tab.
Review the Foreign Keys. Then click OK.
8.
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:
1.
Right-click on the Procedures node in the Connections Navigator, to invoke the context menu, and select Create PROCEDURE.
2.
Enter EMP_LIST as the procedure name. Then click the + to add a Parameter.
3.
Double-click on param to allow you to change the value to pMaxRows and then change VARCHAR2 to NUMBER. Make sure you press enter before you click OK.
4.
The skeleton of the procedure with the parameter specified is displayed.
5.
Replace the following PL/SQL:
BEGIN
NULL;
END;
With the following code:
(This code is also in the plsql.zip
into your working directory (i.e. d:\wkdir) in the file emp_cursor.sql)
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; emp_record emp_cursor%ROWTYPE; TYPE emp_tab_type IS TABLE OF emp_cursor%ROWTYPE INDEX BY BINARY_INTEGER; emp_tab emp_tab_type; i NUMBER := 1; BEGIN OPEN emp_cursor; 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; END LOOP; CLOSE emp_cursor; FOR j IN REVERSE 1..i LOOP DBMS_OUTPUT.PUT_LINE(emp_tab(j).last_name); END LOOP; END;
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.
6.
Expand Procedures in the navigator.
7.
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.
8.
Click the Compile icon.
9.
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:
1.
Right-click on EMP_LIST in the left navigator and select Run.
2.
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 is 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.
Change PMAXROWS := NULL; to PMAXROWS := 5; Then click OK.
3.
The results of the 5 rows returned are displayed in the 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:
1.
Set a breakpoint in the EMP_LIST procedure by clicking in the margin at the line with the OPEN emp_cursor; statement.
2.
Click the Debug icon (ladybug).
3.
The Debug PL/SQL dialog should still show the value PMAXROWS = 5; Click OK.
4.
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 Prerequisites.
5.
Click Step Into .
6.
This takes you to the first line of the cursor. Click Step Into again.
7.
You should now be selecting the first row of the cursor. Click Step Into 3 more times.
8.
Click the Data tab.
9.
The 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.
10.
Right-click the line that reads DBMS_OUTPUT.PUT_LINE(emp_tab(j).last_name); and select Run to Cursor.
11.
Expand emp_tab > values > [1] > _value. You see the values of the fields in a given record of the table. Select the LAST_NAME field.
12.
Right-click the LAST_NAME field and select Modify Value.
13.
Change the name to something else and click OK.
14.
Click the Resume icon to allow the PL/SQL to run to completion.
15.
Check to see that your modified value is displayed in the Log window.
Bookmark
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.)
button in the toolbar. 
. 
to allow the PL/SQL to run to completion. 
