Now let’s continue to build the body part for this package. Perform the following operations to create this body part:
1) Click on the drop-down arrow and select Compile to compile this package. A Compiled message should be displayed in the Message-Log window if the compile is successful.
2) Now right-click on our new created package, FACULTYINFO, under the Packages folder in the left and select the Create Body item to add a package body into this package.
3) In the opened Package Body wizard, enter the code that is highlighted and shown in Figure 6.31.
4) Click on the drop-down arrow and select Compile to compile this package body. A Compiled message should be displayed in the Message-Log window if the compile is successful, as shown in Figure 6.32.
5) Your finished package, FACULTYINFO, is shown in Figure 6.32.
Let’s have a closer look at this piece of code to see how it works.
A. The OPEN Faculty _ Info FOR command is used to assign the returned data col-umns from the following query to the cursor variable Faculty _ Info.
B. The SELECT query is executed to get all columns from the FACULTY Table.
FIGURE 6.32 The finished FACULTYINFO package (Copyrighted by Oracle and used with permission).
C. The input argument, facultyName, is assigned to the faculty _ name column as a query criterion for this package.
If for some reason the compile process fails, a debug process is needed, and one of the most likely errors is the misspelling of variables used in either the definition part or body part or incon-sistent spellings for variables used in the definition part and body part.
In fact, it is unnecessary to build this query as a package due to its simplicity, but here we prefer to use this query as an example to illustrate the building process for an Oracle package.
Now we can test this package in the Oracle SQL Developer environment to confirm its correct-ness. Click on the green arrow (Run) button on the task bar, as shown in Figure 6.32, to run this package. The opened Run PL/SQL wizard is shown in Figure 6.33.
Enter a desired faculty member’s name, such as Ying Bai, into the Input Value box (Figure 6.33), and click on the OK button to run this package.
To check the run result, click on the Output Variables tab at the lower-right corner, and all eight queried columns related to the faculty member named Ying Bai in the FACULTY Table are displayed under the Value tab in the Output Variables window at the bottom, as shown in Figure 6.34.
Now let’s go to the File|Save All menu item to save this package, close Oracle SQL Developer and return to our Java application to develop the code to try to call this package to perform the data query using the CallableStatement method.