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.
Launch Reports Builder (or, if already open, choose File > New > Report).
In the Welcome or New Report dialog box, select Build a new report manually, then click OK.
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.
If it is not already expanded, expand the node of the new library to show the two subnodes: Program Units and Attached Libraries.
Click the Program Units node, then choose Edit > Create.
In the New Program Unit dialog box, in the Name field, type
Select Function, then click OK to display the PL/SQL Editor.
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
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.
Once there are no compilation errors, click Close.
Your new function displays in the Object Navigator.
Choose File > Save to save your new function.
In the Save Library dialog box, type
bonus.pll, make sure File System is selected, then click OK.
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.
Choose Edit > Create.
In the Attach Library dialog box, in the Library field, type
Note:If you saved
bonus.pllto another directory, you can click Browse to find it on your file system. Just make sure you have selected File System before browsing.
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:
Save your report as