Select View | Connections Navigator.

Expand the Database node to show all database connections. Expand MyHRConn. By default, only the database objects owned by the current user are displayed.

Right-click MyHRConn and select Apply Filter from the context menu.

Select OE from the Available Schemas list and move it to the Displayed Schemas list.

Click OK. You should now see both the HR and OE schemas.

Expand the OE node in the Connections Navigator. Expand the Synonyms node. By default only your private synonyms are shown.

Right click on the Synonyms node and choose Apply Filter from the context menu.

Enter USER% in the Filter Text field (this is case sensitive). Check the Show Public Synonyms check box.

Click OK. You should now be able to see all the public synonyms whose names begin with USER.

Expand the HR node in the Connections Navigator (the remainder of this lesson pertains to the HR schema). Expand the Tables node.

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 data types, primary keys, and Not Null constraints. Additionally, information about indexes on the selected table is displayed in the Structure Pane ( Note that this is not the Structure tab in the Table Viewer, but the Structure Pane that opens in the left section of the JDeveloper user interface).

Click the Data tab in the Table Viewer. By default, the Table Viewer fetches 100 rows at a time.

Change the Fetch Size to 10.

Click Refresh to re-execute the query. This time you will only see the first ten rows.

Click Fetch Next to retrieve the next 10 rows.

Create the PL/SQL procedure: Right-click on the Procedures node in the Connections Navigator and choose New PL/SQL Procedure.

Enter emp_list as the Object Name. Click OK.

JDeveloper creates a skeleton procedure.

Edit the PL/SQL procedure. Copy and paste the following code into the PL/SQL editor:

PROCEDURE EMP_LIST(pMaxRows NUMBER)
AS
    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;
    JavaSPReturn VARCHAR2(50);
BEGIN
    OPEN emp_cursor;
    FETCH emp_cursor INTO emp_record;
    emp_tab(i) := emp_record;
-- add Java stored procedure call here --
    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;
    DBMS_OUTPUT.PUT_LINE(JavaSPReturn);
END;

Note: This code contains a syntax error that you will discover in the next section.

There are several ways to find syntax errors. To detect the syntax error in your sample code, perform one of the following:

Select View > Structure to display the Structure Pane, then expand the Errors node. You can then navigate to the detected error by double-clicking the error.

Place your cursor next to one of the parentheses in the WHILE statement. JDeveloper highlights 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 indicates that it does not have a matching symbol.

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 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 Connections Navigator:

Fix the syntax error: Add the missing ) at the end of the WHILE statement just after pMaxRows and before the LOOP keyword. Click the Save button in the toolbar. You should see a message in the status bar indicating a Successful compilation.

Run the PL/SQL procedure: Right-click the procedure and choose Run from the context menu.

This invokes the Run PL/SQL dialog. The Run PL/SQL dialog enables 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 some generated code that JDeveloper 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.

In the PL/SQL Block replace PMAXROWS := NULL; with PMAXROWS := 5;

Click OK. You should see the results of the 5 rows returned in the Log window.

Right-click on the MyHRConn node in the Connections Navigator and choose SQL Worksheet from the context menu..

Enter a simple statement, such as: SELECT * FROM employees;

Click the Execute Statement button.

Delete the SQL statement from the SQL Worksheet.

If the EMP_LIST source is no longer visible, double-click the EMP_LIST node to open the procedure in the code editor. Highlight the SELECT statement from the cursor declaration and copy it to the clipboard (Ctrl+C).

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;

Paste (Ctrl+V) the SELECT statement into the SQL Worksheet. Click the Execute Statement button.

You should see the results of the statement.

Click the Execute 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.

JDeveloper displays the results of the SQL explain plan.

By default, the system most likely performs a full table scan on all the tables as shown in the Explain Plan Results section in the following illustration:

Note: If your results are significantly different from those shown, it may be because the schema has not been analyzed. To analyze the schema, enter and execute the following in the SQL Worksheet, then try again:

BEGIN
DBMS_UTILITY.ANALYZE_SCHEMA('HR', 'COMPUTE');
END;

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 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;

Click the Execute Explain Plan button. You should now see a different explain plan that uses one or more indexes for data retrieval.

In the Applications Navigator, right click on the Applications node and choose New Application Workspace from the context menu.

Enter DBApplication as the Application Name. Select No Template [All Technologies] as the Application Template. You can enter a package name of your choice or leave the default package name.

Click OK.

Select the Project node in the Applications Navigator. Choose File > New from the main menu.

In the New Gallery, select General from the Categories list on the left side, and Java Class from Items on the right.

Click OK.

Enter JavaStoredProc as the name of the new Java class.

Click OK.

Write the code for the Java stored procedure. You can write any Java code with a public static method for your Java stored procedure. In this case, you can copy and paste the following:

public class JavaStoredProc
{

public JavaStoredProc()
{
}

public static String getHelloFromJava ()
{
    String _string = new String();
    for (int i = 0; i < 3 ; i++)
    {
        _string = _string + "Hello World ";
    }
    return _string;
    }
}

Choose File > Save All from the main menu to save your work.

Select the Project node in the Applications Navigator. Right-click and choose Make from the context menu.

Choose File > New from the main menu. Select the General > Deployment Profiles category.
Select the Loadjava and Java Stored Procedures profile and click OK.

Enter MyJavaSPProfile.deploy as the Deployment Profile Name and click OK.

Click OK to accept the default values in the Deployment Profile Properties window.

Select MyJavaSPProfile.deploy in the Applications Navigator. Right click and choose Add Stored Procedure from the context menu.

Select the getHelloFromJava() method.

Click OK. Choose File > Save All from the main menu.

Select MyJavaSPProfile.deploy in the Applications Navigator. Right click and choose Deploy to > MyHRConn from the context menu.

Verify that the deployment completed successfully. Check for a success message in the Log window:

Invoking loadjava on connection 'MyHRConn' with arguments:
-order -resolve -thin
Loadjava finished.
Executing SQL
Statement:
CREATE OR REPLACE FUNCTION getHelloFromJava RETURN VARCHAR2
AUTHID CURRENT_USER AS LANGUAGE JAVA NAME 'JavaStoredProc.getHelloFromJava()
return java.lang.String';
Success.
Publishing finished.
---- Stored procedure deployment finished. ----

Expand the Functions node in the Connections Navigator. Right click on the GETHELLOFROMJAVA node and choose Run from the context menu. (If you are unable to see the GETHELLOFROMJAVA node, then select View > Refresh from the JDeveloper menu.)

In the Run PL/SQL window, click OK.

Confirm that the output v_Return = Hello World Hello World Hello World appears in the Log window.

In the Connections Navigator, expand the MyHRConn connection, the HR node, and the Procedures node. Double-click EMP_LIST to open it in the code editor.

Look for the line of code in the EMP_LIST procedure:
-- add Java stored procedure call here --

Just below this line, add the following:
JavaSPReturn := getHelloFromJava;

Click the Save button in the toolbar.

Set a breakpoint in the EMP_LIST procedure by clicking in the margin at the line with the OPEN emp_cursor; statement

Right-click on the EMP_LIST node in the Connections Navigator and choose Debug.

Note: If you receive the error "ORA-30683: failure establishing connection to debugger", and if you are using a Virtual Private Network (VPN) to connect to the database, you must set JDeveloper to prompt for the host IP address. Choose Tools > Preferences, select the Debugger node, and select the check box labeled Prompt for Debugger Host for Database Debugging. Then when you run the debugger, enter the VPN IP address when prompted.

In the Debug PL/SQL dialog, 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.

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 3 of Prerequisites.

Click Step Into . You should now be in the emp_cursor cursor declaration.

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. Click the Data tab to see all the variables that are in scope.

Click Step Into twice more. You should now be about to execute the statement:
JavaSPResult := getHelloFromJava;

Note: getHelloFromJava is the PL/SQL wrapper for the Java stored procedure you created earlier.

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

Note: If you get a message about not being able to locate the source of the Java stored procedure, select the option to look in a project, and select Project from the list.

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

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

Right-click the line that reads DBMS_OUTPUT.PUT_LINE(emp_tab(j).last_name); and choose Run to Cursor from the context menu.

Use the data window to drill into the PL/SQL table of records called emp_tab. In the data window you can access the entire structure of composite data types.

Keep drilling down until you see the values of the fields in a given record of the table.

Right click on the LAST_NAME field of the record and choose Modify Value from the context menu. Modify the last name to a name of your choice. Click OK.

You've now modified the value in the PL/SQL table of records.

Click Resume to allow the PL/SQL to run to completion. Check to see that your modified value is displayed in the Log window.

Browsing database objects and table data

Defining a filter for browsing database objects

Creating and compiling a PL/SQL procedure

Executing and tuning a SQL statement

Creating and deploying a Java stored procedure

Debugging a PL/SQL procedure and a Java stored procedure