Home > Tools > Information Designer > Tips and Examples > PL/SQL Functions

PL/SQL Functions

In the following example, we will retrieve sales information for employees in both USD and EUR. One way of doing this is to write a function in SQLplus that performs the calculation:

CREATE FUNCTION money_converter

( amount IN NUMBER)

RETURN NUMBER IS

return_val NUMBER (10,2) := 0;

BEGIN

return_val := amount * 0.75;

Return (return_val);

END;

/

The sales in USD is used as input, and the output is a value that is 0.75 times smaller. Store the function in the SDP_ADMIN schema (or elsewhere if a different administrator username is used) to make it accessible by Information Designer.

Now create a column element with the name "Sales (EUR)" as follows:

Also create a column element "Type" with product types, and "Sales (USD)" with the original sales values.

When an information link with these three columns is executed from Information Designer, the generated SQL looks something like this:

SELECT

S1."Type" AS "Type",

S1."Sales" AS "SALESUSD",

money_converter(S1."Sales") AS "SALESEUR"

FROM

"Sales"."dbo"."SalesandCost" S1