Developing SQL and PL/SQL with Oracle9i JDeveloper

This hands-on session walks through the process of using JDeveloper to develop, tune, and debug the SQL and PL/SQL portions of an application.

In this session, you will complete the following tasks:

• Create a Connection and Browse the Database
  • Create a Connection
  • Navigate the contents of the database
  • View a Table and its data
• Create Database Objects
  • Create a Table
  • Create a Trigger
• Execute and Tune SQL Statements
  • Execute a Statement
  • Get the Explain Plan for a Statement
  • Tune the Statement
  • Recall a Previous Statement
• Develop and Debug a PL/SQL Subprogram
  • Create a PL/SQL Function
  • Work in the PL/SQL Code Editor
  • Compile PL/SQL Code
  • Run a PL/SQL Subprogram
  • Debug a PL/SQL Subprogram
  • Remotely Debug a PL/SQL Subprogram

Prerequisites

Please read the prerequisites and ensure that you have the proper environment to complete this lab.

Part 1: Create a Connection and Browse the Database

JDeveloper allows you to store the information necessary to connect to a database in an object called a Connection. A connection is stored as part of the IDE settings, and can be exported and imported for easy sharing among groups of users. A connection serves several purposes from browsing the database, building applications, all the way through to deployment.

Create a Connection

In this lab, you will create a database connection to browse and modify database objects.

To create a new connection:

1. From the JDeveloper main menu, select File > New.
2. In the New Gallery, select Database Tier from the Category list, and Database Connection from the Items list.
3. Click OK.

To provide the connection information:

1. In the first page of the Connection wizard after the Welcome page, enter MyHRConn as the name for your new connection.
2. The Connection Type drop-down list allows you to specify the type of database or database driver that you use to connect to your database. In this case, you will connect to an Oracle database, so make sure Oracle (JDBC) (Default) is selected.
3. Click Next.
4. In Step 2 of 4 of the wizard, enter the username and password for the database user you want to connect as.
   • Username: hr
   • Password: hr

   Note: You can enter the username and password on one line using the <username>/<password> syntax, for example, hr/hr.

5. Click Next.
6. In Step 3 of the wizard, specify the details about the location of the database you want to connect to, as well as the type of driver you want to use.
   • Driver: thin
   • Host Name: localhost
   • JDBC Port: 1521
   • SID: ORCL
7. Click Next.
8. Step 4 of the wizard allows you to test your connection. Click Test Connection.

   If there are any errors reported with your connection, use the Back button to go to the appropriate page in the wizard where you can fix the problem.

9. Once you have verified that your connection tests successfully, click Finish.

Navigate the contents of the database

One of the uses of a connection in JDeveloper is for browsing and editing database objects. In this section of the lab, you will use the connection you just created to browse the contents of the database.

To explore the objects in the database:

1. In the System Navigator, expand the Connections node.
2. Expand the Database node.
3. Expand the MyHRConn node.

   By default, this displays only the database objects owned by the current user.

To access other database schemas:

1. Select Tools > Preferences from the main menu.
2. Select the Database Connections preference category.
3. Check the Show all Oracle Schemas checkbox.
4. Click OK.

   Note: If the Connections node does not automatically update to show all the schemas, right-click on MyHRConn and choose Close Connection from the context menu. Then, expand the MyHRConn node to reopen the connection.

   You should now see all the schemas accessible by the connected user.

   

To find a node in the navigator:

When working with many objects in the navigator, it may be difficult to find the node you are looking for. JDeveloper provides a way to quickly navigate to a node.

1. Make sure the System Navigator has focus. The title bar of the navigator should be blue. If it's gray, click the navigator title bar to bring the focus to it.
2. Type the letter o.

   You should have navigated to the next entry in the navigator beginning with the letter o.

3. Type the letter r.

   You should have navigated to the next entry in the navigator beginning with the letters or.

4. Press the down arrow multiple times.

   This should cycle you through the navigator nodes beginning with the letters or.

   You can also use asterisk (*) as a wildcard in the first position.

5. Press backspace twice to remove or from the search string.
6. Type *ot.

   You should have navigated to the next entry in the navigator containing the string ot.

View a Table and its data

To browse a table:

1. Expand the HR schema node.
2. Expand the Tables node.
3. Double-click the EMPLOYEES table to open the table in the Table Viewer.

   The Table Viewer shows information about the table, including column names and datatypes, primary keys, and not null constraints. Notice as well that the Structure window shows information about indexes on the selected table.

   

To browse the table data:

1. Click the Data tab in the Table Viewer.

   By default, the Table Viewer fetches 100 rows at a time.

2. Change the Fetch Size to 10.
3. Click Refresh to re-execute the query. This time you will only see the first ten rows.
4. Click Fetch Next to retrieve the next 10 rows.

Part 2: Create Database Objects

Oracle JDeveloper provides support for creating the most common database structures and code.

Create a Table

To create a table:

1. Right-click on the Tables node in the System Navigator and choose New Table.
2. On Step 1 of the Table Wizard, enter AUDIT_TABLE as the name of the table.
3. Accept all the other defaults and click Next.
4. On Step 2 of the wizard, click the Add Column button.
5. Enter id as the name of a new column.
6. Set the size of the column to 6.
7. Accept all the other defaults and click the Add Column   button.
8. Enter who as the name of the column.
9. Change the datatype to VARCHAR2.
10. Set the size to 30.
11. Click the Add Column button.
12. Enter when as the name of the column.
13. Change the dataype to DATE.
14. Click the Add Column button.
15. Enter which as the column name.
16. Change the dataype to NUMBER.
17. Set the size to 6.
18. Click the Add Column button.
19. Enter old_val as the column name.
20. Change the dataype to NUMBER.
21. Set the size to 6.
22. Click the Add Column button.
23. Enter new_val as the column name.
24. Change the dataype to NUMBER.
25. Set the size to 6.
26. Click Next.
27. In Step 3 of the wizard, click the Add Constraint button.
28. Enter audit_pk as the constraint name.
29. Click the Add Constraint button.
30. Enter audit_emp_fk as the constraint name.
31. Set the constraint type to FOREIGN KEY.
32. Select EMPLOYEES from the Table drop-down list.
33. Change the Column entry under Associations to WHICH.
34. Click Next.
35. In Step 4 of the wizard, accept all the defaults and click Next.
36. In the summary page, view the generated SQL. It should appear as follows:

    CREATE TABLE "HR"."AUDIT_TABLE"
    ("ID" NUMBER(6, 0),
      "WHO" VARCHAR2(30),
      "WHEN" DATE,
      "WHICH" NUMBER(6, 0),
      "OLD_VAL" NUMBER(6, 0),
      "NEW_VAL" NUMBER(6, 0),
      CONSTRAINT "AUDIT_PK" PRIMARY KEY ("ID") ENABLE,
      CONSTRAINT "AUDIT_EMP_FK" FOREIGN KEY ("WHICH") REFERENCES "HR"."EMPLOYEES"("EMPLOYEE_ID") ENABLE)
    ;

    Note: If you have made a mistake, you can go back in the wizard to correct it.

37. Click Finish.

Create a Trigger

To create a trigger:

1. Right-click on the Trigger node in the System Navigator and choose New Trigger.
2. Enter AUDIT_TRIGGER as the name of the trigger
3. Select EMPLOYEES from the Table Name drop-down list.
4. Selet the After radio button.
5. Select the Row Level radio button.
6. Check the Insert and Update checkboxes.

   The Create Trigger dialog should look like the following:

   

7. Click OK.

   Note: If you do not see the trigger code in the editor, expand the Triggers node in the System Navigator and double-click AUDIT_TRIGGER.

8. Add code to perform a simple audit any time the table is modified. The procedure to do this is already implemented in a PL/SQL procedure. Just add the call to the procedure in place of the NULL statement so that your trigger code looks like this:

   TRIGGER "HR"."AUDIT_TRIGGER"
   AFTER INSERT OR UPDATE ON "HR"."EMPLOYEES"
   FOR EACH ROW
   BEGIN
     audit_procedure(:new.employee_id, :old.salary, :new.salary);
   END;

   Note: You can copy and paste the code from the online version of the exercise.

9. Compile the trigger by right-clicking on AUDIT_TRIGGER and choosing Make AUDIT_TRIGGER.

   You should receive a Successful Compilation message in the status bar.

Part 3: Execute and Tune SQL Statements

So far in this lab, you have explored some of the capabilities in JDeveloper for creating and browsing database objects. In this section, you will use JDeveloper's SQL Worksheet to enter, execute, and tune ad-hoc SQL statements.

Execute a Statement

To execute a SQL statement:

1. Right-click on the MyHRConn node in the System Navigator and choose SQL Worksheet.
2. Enter a simple statement, for example:

   SELECT *
   FROM audit_table;

3. Click the Execute Statement button.

   At this point, there are no rows in the new table.

To work with multiple statements in the SQL Worksheet:

1. Below the SELECT statement, add an INSERT statement:

   INSERT INTO audit_table (id, who, when) VALUES (1, USER, SYSDATE);

2. Highlight the entire line containing the INSERT statement.
3. With the line highlighted, click the Execute Statement button.

   You should see a 1 row affected message in the Results area.

4. Highlight the lines containing the SELECT statement.
5. Click the Execute Statement button.

   You should see the row you just added to the table.

Get the Explain Plan for a Statement

To get the explain plan for a statement:

1. Delete all of the contents of the SQL Worksheet.
2. Enter the following statement into the SQL Worksheet (again, copy and paste from the online lab is recommended):

   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

3. Click the Execute Statement button.

   You should see the results of the statement.

4. Click the Explain Plan button.

   Note: If you do not have a PLAN_TABLE in the current schema, you will be prompted to create it. In this case, simply click OK to create the PLAN_TABLE.

   You should see the results of the SQL explain plan.

   By default, the system will likely be performing a full table scan on all the tables as shown in the Explain Plan Results section in the following illustration.

   

Tune the Statement

To tune the statement:

1. Edit the SQL statement to include the optimizer hint FIRST_ROWS. In this case, you should see a visible difference in the explain plan by requesting the server to tune to retrieve the first set of rows as quickly as possible.

   After inserting the FIRST_ROWS optimizer hint, the query should appear as follows:

   SELECT /*+ FIRST_ROWS */ 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

2. Click the Explain Plan button.

   You should now see a different explain plan that uses one or more indexes for data retrieval.

   

Recall a Previous Statement

JDeveloper keeps track of the SQL statements you've issued in the current SQL Worksheet session so you can easily recall a previous statement.

To recall a previous SQL statement:

1. Click the SQL History button.
2. Select a previous statement, for example, SELECT * FROM audit_table and click OK.

   The statement should appear in the SQL Worksheet.

Part 4: Develop and Debug a PL/SQL Subprogram

In addition to Java and XML, JDeveloper is a full-featured development environment for PL/SQL. In this last section of the lab, you will see how to create, edit, compile, test, and debug PL/SQL code with JDeveloper.

This release of JDeveloper uses the context of the current project when running and debugging PL/SQL code. In preparation for this section of the lab, ensure that the correct project is selected in the Navigator.

To set up JDeveloper in preparation for this lab:

1. Expand the Workspaces node in the System Navigator.
2. Expand Database.jws in the System Navigator.
3. Expand DBProj.jpr in the System Navigator.
4. Select DBProj.jpr in the System Navigator.

Create a PL/SQL Function

To create a PL/SQL function:

1. Right-click on the Functions node in the System Navigator and choose New PL/SQL Function.
2. Enter get_datetime as the package name.
3. Click OK.

   This will create a skeleton function.

Work in the PL/SQL Code Editor

JDeveloper's PL/SQL Code Editor shares many features in common with the Java Code Editor. For example, you can use bookmarks, code templates, brace matching, Code Insight, and many other editor features with PL/SQL. In the following labs, you will get an idea of some of the powerful editor features provided by JDeveloper.

To edit the function to return the current date and time using a code template:

JDeveloper provides many code templates, and you can easily create your own. In this case, a code template has already been created for you as a shortcut to typing in the code for this part of the lesson.

1. Go the end of the line in the function RETURN (''); so that your cursor is after the semi-colon.
2. Press Ctrl-Enter to expand the pre-defined code template.

   Your function should now appear as follows:

   FUNCTION GET_DATETIME
   RETURN VARCHAR2
   AS
   BEGIN
     RETURN (TO_CHAR(SYSDATE, 'yyyy-mm-dd, hh:mi:ss');
   END;

   Note: This actually contains a syntax error which you will discover in the next section.

To find the syntax error:

There are several ways to detect the syntax error in your sample code:

1. Expand the Errors folder in the Structure window. You can then navigate to the detected error by simply double-clicking on the error.
2. Place your cursor next to one of the parentheses in the RETURN statement. JDeveloper will highlight the matching symbol for the parenthesis at the cursor. If you place your cursor next to the first parenthesis in the statement, you will notice that it is highlighted in red which indicated that it does not have a matching symbol.
3. Compile the PL/SQL subprogram by clicking the Save button in the toolbar. 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. Note that when an invalid PL/SQL subprogram is detected by JDeveloper, the status is indicated with a red X over the icon for the subprogram in the System Navigator.

To fix the syntax error:

1. Add the missing ) at the end of the RETURN statement just before the semi-colon.
2. Click the Save button in the toolbar.

   You should see a message in the status bar indicating a Successful compilation.

To navigate PL/SQL code from the Structure window:

The Structure window in JDeveloper shows the structure of many different document types, including PL/SQL.

1. Expand the Packages node in the System Navigator.
2. Expand the EMP_MAIN node.
3. Double-click the EMP_MAIN Body node to open it in the code editor.

   Notice that the Structure window displays the structure of EMP_MAIN.

4. Expand the new_emp(VARCHAR2, VARCHAR2, VARCHAR2) node in the Strcuture window.
5. Double click the v_result node to navigate to the location in the code where this variable is declared.

To edit code in the Code Editor to display the current date and time using Code Insight:

Note: Perform the following steps in a new line after where you see the comment -- add code here in the NEW_EMP procedure:

1. Type DBMS_OUTPUT. . After typing the period, if you pause for a moment, you should see PL/SQL Code Insight. PL/SQL Code Insight examines what you've typed before the period and makes suggestions for what might come next. (It takes some time on the first invocation, but is much speedier subsequently.) You can manually invoke Code Insight using Ctrl-[Space].
2. Select PUT_LINE from the Code Insight suggestions.
3. Type (GET_DATETIME);. (You could also invoke Code Insight again and pick GET_DATETIME from the Code Insight suggestions.) The line should now appear as follows:

   -- add code here
   DBMS_OUTPUT.PUT_LINE(GET_DATETIME);

4. Compile your code by clicking the Save button in the toolbar.

Run a PL/SQL Subprogram

Once your program compiles successfully, you can test run it from JDeveloper.

To run a PL/SQL Subprogram:

1. Right-click on EMP_MAIN in the System Navigator and choose Run EMP_MAIN.
2. Select NEW_EMP as the target run procedure in the Run PL/SQL dialog.
3. In the generated PL/SQL block, supply values for the parameters for add_dept by replacing NULL with actual values, for example:

   ...
   BEGIN
     PFIRSTNAME := 'Your';
     PLASTNAME := 'Name';
     PJOBID := 'AD_VP';
   ...

   Note: The value used for PJOBID must be a valid job id. Use AD_VP as suggested above.

   

4. Click OK.

   To verify that the program was run, you can either look in the Log window for the DBMS_OUTPUT message (the current date and time), or you can use the SQL Worksheet to re-execute a query and see that a new row has been added to the audit_table.

Debug a PL/SQL Subprogram

JDeveloper also supports PL/SQL debugging with Oracle8i and Oracle9i databases.

To debug a PL/SQL Subprogram:

1. Set a breakpoint in the EMP_MAIN Body by clicking in the margin at the line with the OPEN my_cur; statement in new_emp:

   

2. Right-click on the EMP_MAIN node in the System Navigator and choose Debug EMP_MAIN.
3. Select NEW_EMP as the target to run.

   The values you passed as parameters before should reappear in the generated PL/SQL Block.

4. In the generated PL/SQL, change 'AD_VP' to 'AD_PRES'.
5. Click OK.

   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.

   

To step through code:

1. Click Step Into .

   You should now be in the my_cur cursor declaration.

   

2. Click Step Into .

The Smart Data window shows a limited list of variables, namely those used in the line of code that is about to be executed, and in the previously executed line.

1. Click the Data tab to see all the variables that are in scope.

   

2. Click Step Into twice more.

   You should now be about to execute the statement v_result := getHelloFromJava;

   

   Note: getHelloFromJava is a Java stored procedure.

3. Click Step Into .

   You should now be debugging the Java code for the Java stored procedure.

   

   The Stack window shows the PL/SQL call stack and the Java call stack together.

   

4. Click Step Out to complete the Java stored procedure and return to the NEW_EMP procedure.

   You should now be at the line that calls DBMS_OUTPUT.

   

5. Click Step Into .

   You should now be in the GET_DATETIME function.

   

6. Click Step Out to return to the NEW_EMP procedure.
7. Click Step Over to step over the DBMS_OUTPUT statement.

   You should now be at the INSERT statement. The INSERT statement causes the trigger that you created earlier to fire.

   

8. Click Step Into .

   You should now be in the AUDIT_TRIGGER you created earlier.

   

   Notice that the Data window shows the OLD and NEW records, which contain the values in the table before and after the insert statement. If you wish, you can expand the NEW node in the Data window to see the values.

9. Click Step Into .

   You should now be at the IF statement in the AUDIT_PROCEDURE.

   

   A quick read through the code will indicate that an exception will be raised if the salary is over 16000. In this case, our new employees salary is 20000. (Look at the value of PNEWSAL in the Data window, or position your mouse over the pNewSal variable in the code editor.)

10. Right-click on the PNEWSAL node in the Data window and choose Modify Value.
11. Change the value to 16000 in the Modify Value dialog as shown below:

    

12. Click OK.
13. Click Step Into . Notice that the value was changed successfully and no exception was raised. The PL/SQL debugger in JDeveloper supports a wide range of breakpoint types, including conditional breakpoints and exception breakpoints. You will see an example of exception breakpoints in the next section.
14. You are now finished with debugging. Click Resume to allow the PL/SQL to run to completion.

Remotely Debug a PL/SQL Subprogram

Oracle9i JDeveloper provides support for remotely debugging PL/SQL. In this case, rather than initiating the debug action from within JDeveloper, you start the debugging from the client your PL/SQL is normally called from. In this example, you will use SQL*Plus as the client, but the concept applies to a Forms client, OCI client, Web client, etc.

The first step is to configure JDeveloper to listen for debug requests from the database.

To configure JDeveloper for remote debugging:

1. Select DBProj.jpr in the System Navigator.
2. Select Project > Project Setings from the main menu.
3. Expand the Debugger node in the tree.
4. Select the Remote node under Debugger in the tree.
5. Select the Remote Debugging checkbox.
6. Select the Listen for JPDA radio choice.
7. Select MyHRConn from the Database Connection for Locating PL/SQL Source drop-down list.

   

8. Click OK.
9. Right-click on DBProj.jpr and select Debug DBProj.jpr from the context menu.
10. In the Debugger - Listen for JPDA dialog, accept the default settings and click OK. Note that the default port is 4000.

    This will start the debugger listener in JDeveloper. You should see the Debug Listener in the Run Manager.

    

The next step is to invoke the client that will be used to initiate the remote debugging, and establish a connection to the JDeveloper debugger listener.

To invoke the client:

You can launch SQL*Plus from JDeveloper directly using the connection you've already created. Of course, you can also launch SQL*Plus from the Windows Program menu or from the command line.

Note: It doesn't matter how you launch the client (or even what machine it's on) for remote debugging. Just for convenience, this lab directs you to launch SQL*Plus from a JDeveloper connection.

1. Right-click on the MyHRConn Connection node in the System Navigator and select SQL*Plus from the context menu.

To establish the JPDA connection to JDeveloper:

1. In SQL*Plus, enter the following command:

   EXEC DBMS_DEBUG_JDWP.CONNECT_TCP( 'localhost', 4000);

   This attempts to establish a JPDA connection from the server to a debug listener on the specified host (localhost in this case) on the specified port (4000 in this case).

   You should see a success message in SQL*Plus:

   

   In JDeveloper you should see that a process has connected to the debug listener. (Select View > Run Manager from the main menu if the Run Manager is not visible.)

   

To begin debugging from the client:

You've already set a breakpoint in a prior part of this lab, so all that's left to do is call the PL/SQL that will eventually cause the breakpoint to be reached.

1. In SQL*Plus enter the following command:

   EXEC emp_main.new_emp('Your','Name','AD_PRES');

   You should now have reached a breakpoint in your code in JDeveloper. From this point on, remotely debugging is essentially the same as locally debugging until you are ready to stop debugging.

To stop remotely debugging:

1. Click Terminate to stop the debugger.
2. In the Run Manager, right click on Debug Listener (Port=4000) and select Stop Listening from the contect menu.

This concludes the hands-on session for database and PL/SQL development in Oracle JDeveloper. Thank you!