Oracle Developer

As well as calling any of the Oracle predefined functions you can also create and use your own user defined functions in OBI Answers.

For this example we will create a function that formats a customer’s name and use this function in a report.

Step 1

First we will create a simple report.

Step 2

When run, the report looks like this.

Step 3

We’d like the customer name to appear in our standard corporate name format (<Title> <First Initial>. <LastName>) so we decide to use our standard name formatting function.

e.g. Mrs Maureen Baker would appear as Mrs. M. Baker

This is all well and good, but as this is an example, I don’t currently have a ‘corporation’ let alone a ’standard corporate name format’ or a ’standard name formatting function’! So I will now create one. I’m only saying this because this is why you would logically call a user defined function because you have certain ones which are ’standard’ throughout your organisation.

The function we will call I will call ‘fullname’ and it will take 5 parameters:

1. first_name
2. last_name
3. gender
4. marital_status
5. name_format

The parameters are all quite obvious except the last one. A name can appear in many different ways. ‘name_format’ is a constant which defines which name format we want to use. If a new name format is required then this can easily be added to the function so that it will return this new format.

For those of you that understand PL/SQL, this is the function I will use.

CREATE OR REPLACE
FUNCTION fullname(first_name     IN VARCHAR2 DEFAULT NULL
                 ,last_name      IN VARCHAR2 DEFAULT NULL
                 ,gender         IN VARCHAR2 DEFAULT NULL
                 ,marital_status IN VARCHAR2 DEFAULT NULL
                 ,name_format    IN NUMBER   DEFAULT 1
                 )
RETURN VARCHAR2
IS
/*************************************************************
* Name:        fullname
* Description: Formats a name based on the name_format value.
*              The index number indicates the name_format value.
*              Possible formats for Susan Taylor (F) Married are:
*
*              1. Susan Taylor
*              2. S. Taylor
*              3. Mrs. Susan Taylor
*              4. Mrs. S. Taylor
*              5. S. Taylor (Mrs)
*
* Parameters:  first_name     = First name
*              last_name      = Last name (Surname)
*              gender         = Can take values Male, Female, M, F
*              marital_status = Takes value 'married'.
*                               Any other value assumes is 'not married'
*              name_format    = Number indicating name format (see description)
*                               which want returned
*
* History
*
* Version   Date        Author      Description
* ------------------------------------------------------------
* 001       10-Jan-2010 O. Dev      Original Version
**************************************************************/

    lc_PROCEDURE_NAME   CONSTANT VARCHAR2(30) := 'fullname';

    l_fullname      VARCHAR2(100);
    l_title         VARCHAR2(10);
    l_fullstop      VARCHAR2(1) := '';
    l_initial       VARCHAR2(2);

BEGIN

    CASE (UPPER(SUBSTR(fullname.gender, 1, 1)))

        WHEN 'F' THEN
            IF (UPPER(fullname.marital_status) = 'MARRIED') THEN
                l_title := 'Mrs';
                l_fullstop := '.';
            ELSE
                l_title := 'Miss';
            END IF;           

        WHEN 'M' THEN
            l_title := 'Mr';
            l_fullstop := '.';

        ELSE
            l_title := '';   

    END CASE;

    l_initial := UPPER(SUBSTR(fullname.first_name, 1, 1));

    IF (l_initial IS NOT NULL) THEN
        l_initial := l_initial||'.';
    END IF;

    CASE (fullname.name_format)

        --5. S. Taylor (Mrs)
        WHEN 5 THEN
            l_fullname := TRIM(l_initial||' '||fullname.last_name||' ('||l_title||')');

        --4. Mrs. S. Taylor
        WHEN 4 THEN
            l_fullname := TRIM(l_title||l_fullstop||' '||l_initial||' '||fullname.last_name);

        --3. Mrs. Susan Taylor
        WHEN 3 THEN
            l_fullname := TRIM(l_title||l_fullstop||' '||fullname.first_name||' '||fullname.last_name);

        --2. S. Taylor
        WHEN 2 THEN
            l_fullname := TRIM(l_initial||' '||fullname.last_name);

        --1. Susan Taylor
        ELSE
            l_fullname := TRIM(fullname.first_name||' '||fullname.last_name);

    END CASE;        

    RETURN l_fullname;                                   

END fullname;

This function needs to be compiled in the SH schema, or if not, the SH schema needs to be granted access to it.

Step 4

So, now we have the report we want to write and we have a function we can call that will return a customer name in the format we want. So how do we call the pre-defined function in OBI?

To call a function we need to use the EVALUATE function.

The syntax of this function is:

EVAULATE(’your db function(%1,%2)’, parameter list)

where,

• %1 and %2 – the number of parameters
• parameter list – comma separated list of values we are passing into the function

e.g.

EVALUATE(’upper(%1)’, ‘new york’)

gives the result:

NEW YORK

Step 5

So we would call our function as follows:

EVALUATE(’fullname(%1,%2,%3,%4,%5)’, <first name>, <last name>, <gender>, <marital status>, <name format>)

e.g.

EVALUATE(’fullname(%1,%2,%3,%4,%5)’, ‘James’, ‘Thompson’, ‘Male’, ‘Single’, 3)

Step 6

Add the function to the report. To do this we add a new field to the report, e.g. ‘Customer Name’ then ‘Edit Formula’ of this field.

Step 7

Replace Customers.”Customer Name” with our user defined function.

Step 8

Click OK and you will get the following error.

Step 9

This is caused by the EVALUATE function expecting all the parameters to be strings and the last parameter in our function is actually a NUMBER. Convert the last value to a string and click OK. This will save okay. Oracle will now convert the string to a number when it calls the ‘fullname’ function. Bit silly, but it’s a way around the problem.

Step 10

Now run the report and you will probably get some unexpected results.

We’re expecting the customer column to contain ‘Mr. J. Thompson’, instead we get the first 8 characters, ‘Mr. J. T’. This is a strange consequence of using hardcoded values in a function we call using the EVALUATE function. Here it defaults a string to 8 characters and we can’t change this default! If we were to choose name_format = 1 and make the first name 38 characters long then we would get returned a customer name which is 38 characters long. i.e. it defaults to the length of the first parameter used in the EVALUATE function.

Step 11

Fortunately for us (and Oracle) the EVALUATE function works if we pass in column names instead of hardcoded values. But it’s good to know what can happen if our function does not always use column names!

Change the function to now use four column names and set the fifth parameter to ‘4′.

Step 12

Click on the ‘Results’ tab. The report should now look like this.

Step 13

Now the report is working we can now tidy it up so that it only contains 2 columns. (We have hidden the firstname and lastname columns but still use them to order the report by lastname followed by firstname.)

Step 14

If we do not like the customer name format it only takes a moment to change the name_format parameter and use a new format, e.g. name_format = 5.

Footnote

You can see how easy it is to create and use a user defined function. However, this may not necessarily be the best way to write an OBI Answers report. The reason being that the function being called will have to be processed for every record in the report. This may be extremely costly in processing time. It may be better to use this function during the development phase of the datawarehouse so that the data is easily available in OBI Answers.

A good time to use a user defined function is during development. A developer can produce a function and test it in an OBI Answers report. An end user can also use it. If the function is useful it can then be added as part of the ETL processes during the update of the data warehouse.