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:

id_pl_sql.png

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

See also:

Creating a Column Element

Column Element Tab