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 Unitsand Attached Libraries.
5. Click the Program Unitsnode, then choose Edit > Create.
6. In the New Program Unit dialog box, in the Namefield, type BONUS_PAY.
7. Select Function, then click OKto display the PL/SQL Editor.

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;

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

 

Note:

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

1. Click Compile.
2. 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.
3. Once there are no compilation errors, click Close.

Your new function displays in the Object Navigator.

4. Choose File > Saveto save your new function.
5. In the Save Library dialog box, type pll, make sure File Systemis selected, then click OK.
6. In the Object Navigator, under the MODULE1report you have created, click the Attached Libraries Be sure to select this node, and not the one under the PL/SQL Libraries node.
7. Choose Edit > Create.
8. In the Attach Library dialog box, in the Libraryfield, type 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.

1. When the library name displays in the Libraryfield, 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

1. Save your report as plsqlreport_your_initials.rdf