40.2 Create a New PL/SQL Library

The steps in this section will show you how to create a new PL/SQL library, then create a function that will live in this library.

To create the library:

1. Launch Reports Builder (or, if already open, choose File > New > Report).

2. In the Welcome or New Report dialog box, select Build a new report manually, then click OK.

3. In the Object Navigator, choose File > New > PL/SQL Library.

   A new library displays in the Object Navigator below your report name, under the PL/SQL Libraries node.

4. If it is not already expanded, expand the node of the new library to show the two subnodes: Program Units and Attached Libraries.

5. Click the Program Units node, then choose Edit > Create.

6. In the New Program Unit dialog box, in the Name field, type BONUS_PAY.

7. Select Function, then click OK to display the PL/SQL Editor.

8. In the PL/SQL Editor, use the template to enter the following PL/SQL code:

   FUNCTION BONUS_PAY(JOB_ID IN CHAR, SAL IN NUMBER, COMM_PCT IN NUMBER) RETURN NUMBER IS
   BEGIN
     IF JOB_ID != 'SA_REP' THEN
       RETURN (SAL * 0.15);
     ELSE
       IF SAL * COMM_PCT >= 500 THEN
         RETURN ((SAL + SAL * COMM_PCT) * 0.15);
       ELSE
          RETURN ((SAL + SAL * COMM_PCT) * 0.10);
       END IF;
     END IF;
   END;
   

   Note:

   You can enter this code by copying and pasting it from the provided text file called plsql_code.txt.

9. Click Compile.

10. If there are compilation errors, match your code to the code we have provided (either in the example RDF file or in this chapter), then compile it again.

11. Once there are no compilation errors, click Close.

    Your new function displays in the Object Navigator.

12. Choose File > Save to save your new function.

13. In the Save Library dialog box, type bonus.pll, make sure File System is selected, then click OK.

14. In the Object Navigator, under the MODULE1 report you have created, click the Attached Libraries node. Be sure to select this node, and not the one under the PL/SQL Libraries node.

15. Choose Edit > Create.

16. In the Attach Library dialog box, in the Library field, type bonus.pll.

    Note:

    If you saved bonus.pll to another directory, you can click Browse to find it on your file system. Just make sure you have selected File System before browsing.

17. When the library name displays in the Library field, click Attach to attach the library.

    Note:

    If you attach a library whose name also includes a path, Reports Builder will inform you that the path names are not portable, and will give you the option of deleting the path. If you choose to continue using a path specification, Reports Builder will only look in that specific location for the library. So, if you move the library, Reports Builder will not be able to find it. If you delete the path, Reports Builder will use a standard search path to locate the library if it is moved.

    The objects in your Object Navigator should now look something like this:

    Figure 40-2 Object Navigator

    
    Description of "Figure 40-2 Object Navigator"
    
    

18. Save your report as plsqlreport_your_initials.rdf.