Registering Custom PLSQL Functions in Discoverer


Although Discoverer provides many functions for calculation in reports, sometime we require to use custom PL/SQL functions to meet additional Discoverer end user requirements (for example, to provide a complicated calculation). For this we first need to create the functions in database through Toad or other PL/SQL editors.

To access custom PL/SQL functions using Discoverer, you must register the functions in the EUL. When you have registered a custom PL/SQL function, it appears in the list of database functions in the “Edit Calculation dialog” and can be used in the same way as the standard Oracle functions.

Note: To register a PL/SQL function you must have EXECUTE privilege on that function.

You can register custom PL/SQL functions in two ways:

  •  Import automatically, by importing the functions (recommended)
  •  Manually

How to register custom PL/SQL functions automatically:

To register PL/SQL functions automatically you must import them in the following way:

 1. Choose Tools | Register PL/SQL Functions to display the “PL/SQL Functions dialog: Functions tab”.

  

 2. Click Import to display the “Import PL/SQL Functions dialog”. This dialog enables you to select the PL/SQL functions that you want to import.

 

3. Select the functions that you want to import. You can select more than one function at a time by holding down the Ctrl key and clicking another function.

 

4. Click OK.

Discoverer imports the selected functions and displays the function details in the “PL/SQL Functions dialog: Functions tab”. Information about the selected functions is imported automatically. In other words, you do not have to manually enter information or validate the information.

 

5. Click OK.

The PL/SQL function is now registered for use in Discoverer.

How to register custom PL/SQL functions manually:

To manually register a PL/SQL function for use in Discoverer:

1. Choose Tools | Register PL/SQL Functions to display the “PL/SQL Functions dialog: Functions tab”.

2. Click New and specify the function attributes.

 

3. Click Validate to check the validity and accuracy of the information you have entered.

4. If the function is invalid, correct the attributes and click Validate again.

5. (Optional) if the function accepts arguments:

a. Display the “PL/SQL Functions dialog: Arguments tab”.

b. On the Arguments tab, click New and specify the argument attributes.

 

6. Click OK when you have finished defining the function.

The custom PL/SQL function is now registered for use in Discoverer.

It is always recommended to register PL/SQL functions by importing automatically (especially if you have many functions to register), because it is easy to make mistakes when manually entering information about functions. When you import functions, all of the information about each function (for example, names, database links, return types, lists of arguments) is imported.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: