How to Apply Patches Using Opatch utility in OBIEE 11g

Step:1  Ensure you have the latest Opatch:

Make sure you have the latest supported version of OPatch downloaded.

You can download the latest Opatch and install it in the directory on your machine under:
D:\Oracle\Middleware\Oracle_BI1\OPatch

Step 2 : Download the patch to PATCH_TOP

Download the patch to PATCH_TOP directory, as instructed in the README

Step: 3
 cd to patch (for example 10042883) directory:
D:\PATCH_TOP>cd 10042883

Step 4 : Make sure Opatch is in the PATH
Add D:\OPatch to the PATH:
D:\PATCH_TOP\10042883>set PATH=%PATH%;D:\Oracle\Middleware\Oracle_BI1\OPatch
Verify that the PATH is set:
D:\PATCH_TOP\10042883> PATH
Step 5 : Set ORACLE_HOME

Set ORACLE_HOME to your BI Home.
For example, in this case,  BI Home is set to D:\Oracle\Middleware\Oracle_BI1
D:\PATCH_TOP\10042883>set ORACLE_HOME=D:\BI11\Oracle_BI1
Verify that the Oracle Home is set correctly:
D:\PATCH_TOP\10042883>echo %ORACLE_HOME%

Note: You are setting ORACLE_HOME for this command-prompt session only. This is set to only facilitate patch application/rollback/inventory, etc. Do not make this a permanent environment variable as it will affect other Oracle Products running on your system which may need a permanent ORACLE_HOME environment variable.

Step 6 : Verify Opatch version

Verify you have the correct OPatch version:
D:\PATCH_TOP\10042883>opatch version
Invoking OPatch 11.1.0.8.3
OPatch Version: 11.1.0.8.3
OPatch succeeded.
Step 7 : Apply the OBIEE patch (for example 10042883)
7.1: Shutdown all BI services:
Apply patch 10042883 -- Before applying any OBIEE patch, make sure all
BI Services are shut down.
Start->Programs->Oracle BI->Stop BI Services
7.2: Apply the patch:
D:\PATCH_TOP\10042883>opatch apply

If you are running a 64-bit OS: Windows64bit / Linux 64bit / AIX 64bit, make sure
you use the following command:
opatch apply -jre %ORACLE_HOME%/jdk/jre
OPatch succeeded.
Step 8 : lsinventory command
Verify that the patch was applied correctly by checking the inventory:
D:\PATCH_TOP\10042883>opatch lsinventory
If you are running a 64-bit OS: Windows64bit / Linux 64bit / AIX 64bit, make sure
you use the following command:
opatch lsinventory -jre %ORACLE_HOME%/jdk/jre

For more info refer below Oracle Doc:

Thanks,
Allam

Evaluate() Function in OBIEE to Embedded DB Functions

This Evaluate functions (since version 10.1.3.3) enables users and administrators to create more powerful reports by directly calling Database functions in OBIEE from:

1. Either By Oracle BI Answers.
2. OR by using a Logical column (in the Logical Table source) within the Metadata (repository).

The following new BI Server database functions are supported

Functions For

1. EVALUATE( ) --> It is a scalar function that are computed post-aggregation and analytic calculations

2. EVALUATE_AGGR ( ) --> It is a aggregate function with group by clause

EVALUATE( ) Function :


Syntax:
EVALUATE('db_function(%1...%N)' [AS datatype] [, column1, columnN])

Example:

1. Evaluate('TO_DATE(%1,%2)' AS CHAR ,'20130326','YYYYMMDD')

2. Evaluate('substr(%1,%2,%3)' as Char,'AllamNarasingaRao','1','5')

Passes the specified database function with (optional) referenced columns as parameters to the back-end data source for evaluation. This function is intended for scalar and analytic calculations, and is useful when you want to use a specialized database function that is not supported by the Oracle BI Server, but that is understood by the underlying datasource.

The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.

EVALUATE_AGGR( ) Function:
Syntax:

EVALUATE_AGGR('db_agg_function(%1...%N)' [AS datatype] [, column1, columnN])
  Example:

EVALUATE_AGGR('sum(%1)', "sales"."quantity")

Passes the specified database function with (optional) referenced columns as parameters to the back-end data source for evaluation. This function is intended for aggregate functions with a GROUP BY clause.
The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.

Let see How can use the Evaluate Function properly.

Need Click on the Edit column Formula>f(...) to select the Evaluate( ) Function.

In Insert Option Dialog prompt need to select the DB Functions to specify the Evaluate Function.

 

After select the Evaluate Function in the Prompt dialogbox, the syntax of the Evaluate function will see like the above in screen shot.

 

There we need to replace the inner things with our valid syntax formate. As specified Below

 

 

Here

 

Evaluate('substr(%1,%2,%3)' AS Char,'AllamNarasingaRao','1','5')

• Evauate ( ) is the function we know about it.
• Substr ( ) is the DB Function. It needs to enclosed in the single quotes, like this 'substr( )'
• AS Char is the Datatype, We need to specify this also after enclosed your DB function. Then after we need to place the , .
• 'AllamNarasingaRao','1','5'

In our example the above 3 strings are the perameters, those we need to pass into the Evaluate Function by specifying the %1 for first string ('AllamNarasingaRao'), %2 for Second String ('1') and %3 is for Third String ('5').

Lets run this by clicking on the Results Tab. And See what will happen.

In this case we will get one common error in Evaluate Function that is

[nQSError: 42015] Cannot function ship the following expression:Evaluate( substr(%1,%2,%3),'AllamNarasingaRao', '1', '5') . (HY000)


This Error is came beacouse of taking the only one column that too it has Evaluate function. In this case we can resolve this error by taking at least one more column from exisiting subject area.    

    Like the following above steps we can resolve that type of errors comming while using the Evaluate function.   Note: We need to add at least one more column to the exsisting Evaluate function column.  

• Here I am specifying some examples with Evaluate function used in the OBIEE 11g.

1. Evaluate('TO_CHAR(%1 ,%2)' AS CHAR ,"Itemwarehouse"."Last Update Date",'DD-MON-YYYY')

2. Evaluate('NEXT_DAY(%1,%2)' ,"Itemwarehouse"."Last Update Date",'SUNDAY')

3. Evaluate('TO_DATE(%1,%2)' AS CHAR ,'20130326','YYYYMMDD')

4. Evaluate('TO_CHAR(TO_DATE(%1,%2),%3)'AS CHAR ,'20130326','YYYYMMDD','DD-MON-YYYY')

5. Evaluate('TO_CHAR(TO_DATE(%1,%2),%3)'AS CHAR ,"Itemwarehouse"."Last Update Date",'DD/MM/YY HH:MI:SS AM','DD-MON-YYYY HH:MI:SS AM')    

---The End---