Oracle BI Publisher Desktop Installation Error

Resources

http://docs.oracle.com/cd/E17904_01/bi.1111/e13881/T421739T481157.htm
https://blogs.oracle.com/xmlpublisher/?page=2
http://www.solutionbeacon.com/BIPublisherQuickStartGudepaper.pdf
http://oraclemaniac.com/2014/03/07/limiting-lines-per-page-and-fixed-length-table-in-xmlbi-publisher-reports/

Oracle BI Publisher Desktop Installation Error

---------------------
-- Symptoms
---------------------

When attempting to install BI Publisher Desktop 11g (11.1.1.6.0) for 32-bit Office on Windows 7 64-bit. During the installation process, the following error message displays and the installation fails:

Please make sure JRE version 1.6 or later is installed

Java JRE 6 update 32 is installed on this PC; no other Java versions are installed.

---------------------
-- Cause
---------------------

Bug 14091878: ERROR WITH BIPUBLISHERDESKTOP32.EXE INSTALLATION

BIP Desktop installer might be corrupt

---------------------
-- Solution
---------------------

Complete the following steps:

    Download the offline installer for Java from this page:
    http://www.java.com/en/download/manual.jsp

    Make sure to download both Windows Offline (32-bit) and Windows Offline (64-bit).

    In Windows, go to the Control Panel --> Programs and Features.
    From the list of installed programs, uninstall all Java / JDK programs from your PC (both 32-bit and 64-bit)
    Reboot the PC
    Install the Java Windows Offline (32-bit)
    Install the Java Windows Offline (64-bit)
    Reboot the PC
    Install Oracle BI Publisher Desktop

Showing first tab always when enabling MDS

I came across this scenario earlier, MDS customization is enabled for project, the project runs under webcenter portal.

User runs a page having three tabs, PanelTab along with showDetailItems used, when user select second tab and navigate away, then returns back to the previous page the last selected tab is displayed.

Using Persist property for the ShowDetailItem doesn't solve the problem, since panelTab component in Webcenter will always remember the last selected tab.

One of the WRONG scenario I tried is using Javascript to force the panelTab to set the tab property disclosed to false when page loads, this worked but it have a jumping effect between the tabs and still its a workaround.

The RIGHT way to do it is by using ChangeManager as shown below:

      public void tabDisclosureListener(DisclosureEvent disclosureEvent) {    
        RichShowDetailItem tab = (RichShowDetailItem)disclosureEvent.getComponent();
        FacesContext fc = FacesContext.getCurrentInstance();
        ChangeManager cm = RequestContext.getCurrentInstance().getChangeManager();
        ComponentChange cc = new AttributeComponentChange("disclosed", Boolean.FALSE);
        cm.addComponentChange(fc, tab, cc);
        ........
    }

This will reset the disclosed property when ever any of the tabs where clicked.

Tracking Entity Changes (instead of using Triggers on Database side)

In this example I am tracking all changes on specific set of columns or tables to trigger any events before transaction commit changes.

first you need to get a list of all view objects and compare its entity reference with list of tables you have, either stored in database or as fixed list of tables, note that ADF isAttributeChange method uses Java column naming convention not the column name you have on database, so you must build a list of java attribute names based on database column names.

so you need to retrieve all entity usages from viewDef, then get actual table name from the entity definition, also I am building a list of column (database_name) attribute (ADF_name) map, which will be used to check AttributeChangeStatus.

 public Map getViewObjectsNeedsToBeTracked(ViewObject[] allViews, ViewObjectImpl) {

        Map viewObjectsMap = new HashMap();

        //RowSetIterator iterator = Source Data Iterator or Array of tables

        for (int i = 0; i < allViews.length; i++) {

            iterator.reset();

            boolean viewPushed = false;

            while (iterator.hasNext() && !viewPushed) {

                Row rowFields = iterator.next();

                String tableName = (String)rowFields.getAttribute(tableFieldName);

                ViewObjectImpl view = (ViewObjectImpl)allViews[i];

                Map sourceMap = getSourceTableFromView(view);

                if (sourceMap != null) {

                    Object keys[] = sourceMap.keySet().toArray();

                    String sourceTable = (String)keys[0];

                    if (StringUtils.equalsIgnoreCase(tableName, sourceTable)) {

                        AttributeDef defs[] = sourceMap.get(keys[0]);

                        viewObjectsMap.put(tableName, createPairList(allViews[i], defs));

                        viewPushed = true;

                    }

                }

            }

        }

        return viewObjectsMap;

    }

private Map getSourceTableFromView(ViewObjectImpl view) {

        String sourceTable = null;

        Map map = null;

        ViewDefImpl viewDef = (ViewDefImpl)view.getDef();

        EntityReference references[] = viewDef.getEntityUsages();

        if (references != null && references.length > 0) {

            try {

                map = new HashMap();

                EntityDefImpl entityDef = references[0].getEntityDef();

                sourceTable = entityDef.getSource();

                sourceTable = StringUtils.substring(sourceTable, sourceTable.indexOf(".") + 1, sourceTable.length());

                map.put(sourceTable, entityDef.getAttributeDefs());

            } catch (Exception exp) {

                sourceTable = StringUtils.substring(sourceTable, 0, sourceTable.length());

            }

        }

        return map;

    }

Then get list of Attribute definition for source EntityDef, make sure to get persistent attributes only, these are the columns will be posted to database when transaction is committed, other attributes such as transient or constraints should be skipped.

                AttributeDef def = entityAttributeDefs[i];

                if (def.getAttributeKind() == AttributeDef.ATTR_PERSISTENT) {

                    String defColumnName = def.getColumnName();

                    String defName = def.getName();

                   

Check attribute changes methods, passing all captured View Rows along with list of parameters need to tracked:

 private static boolean rowAttributeStatusCheck(ViewRowImpl row, Object[] parameters) {

        boolean result = false;

        if (row != null && parameters != null) {

            for (Object param : parameters) {

                result = result || row.isAttributeChanged(param.toString());

            }

        }

        return result;

    }

Great resource for layouts

Great resource for ADF layouts, informative and direct:
http://www.slideshare.net/AMIS_Services/lbors-goodbyenightmarekscope

Running ADF in High availability

Some of the things you should consider when developing ADF application running in HA environment is changes to pageFlowScope or ViewScope during a request. Making ADF aware of the changes you made to a ViewScope or pageFlowScope params is essential to replicate the changes across cluster.


To accomplish this:
1- Set the adf-scope-ha-support parameter inside adf-config.xml config file to true, this will make sure that changes will automatically replicated within a cluster.
 < adf-controller-config xmlns="http://xmlns.oracle.com/adf/controller/config">
 < adf-scope-ha-support>true</adf-scope-ha-support> </adf-controller-config>

2-  marking the scope is Dirty by adding additional code after scope changes:
    ControllerContext ctx = ControllerContext.getInstance();
    ctx.markScopeDirty(AdfFacesContext.getCurrentInstance().getViewScope());

note if you are making a change by reference without putting (replacing) param value in the Map you must call markScopeDirty.

Example:

Map<String, Object> viewScope = AdfFacesContext.getCurrentInstance().getViewScope();

MyObject obj = (MyObject)viewScope.get("myObjectName");

Obj.setFoo("newValue");

Putting value again in the map will replicate the change across cluster without calling markScopeDirty method.
viewScope.put("myObjectName");

http://docs.oracle.com/cd/E12839_01/core.1111/e10106/adf.htm

Row status using expression language

Fast and easy to get row status using expression language:

#{row.row.entities[0].entityState == 0}

entities[0] where 0 is the entity index, if your view based on different entities you should consider this.

(0 = New, 2= Modified, 1 = Unmodified , -1 =  Initialized)

Oracle Database - Changing Day Order of the Week after Saudi Arabia Changed Weekend days

I wrote this post before two or three months, but I didn't have the chance to publish it:
here it goes.....


We had a function in which it updates a table that holds today's Umalqura current date, this function perform its calculations depending on Day order of the week and most of user machines we have either (forms6i or ADF "weblogic servers") have Saudi Arabia set as default locale.

The NLS_TERRITORY set to United Arab Emirates have Saturday with day number equals to 1. 

So to change the day order of the week which depends on NLS_TERRITORY DB parameter, rather than changing every client machine, you could do the following:

Create a Database trigger in which it firs after user login to database, and then overwrite the NLS_TERRITORY parameter to AMERICA in which Sunday corresponds to 1 as day number of the week.

Change the NLS_LANG in regedit to ARABIC_AMERICA ARMSWIN1256 for forms6i users.

This is was tricky fast solution rather than opening all forms to modify day of the week calculation that developers did ....!!!!!!

Using Bean in Expression language with parameter to check user Authorization

There were many questions in how to build a customized bean and use it in expression language to return user authorization based on specific parameter passed through expression language. In here I will describe the steps in how to build your own bean, accessing its methods and passing parameter to it.

This first step is develop the method that you want to receive your parameter and return the success or failure logic, the authorization roles in my scenario are stored in ViewObject based on dynamic SQL statements that loads the roles from different tables depending on some HR and Assignment statues:


public class AuthorizationBean {
 
    private ApplicationModule appModule=ADFFacesHelper.getAppplicationModule("AppModuleDataControl");

    public AuthorizationBean() {
        super();
    }

    
    public boolean getUserInRole(String RoleCode){
        boolean havePriv=false;
        ViewObject userRoles=appModule.findViewObject("UserRolesVO1");
        RowSet rowSet=userRoles.getRowSet();
        Row row=rowSet.first();
        while(row!=null){         
            String roleCode=row.getAttribute("RoleCode").toString();            
            if(roleCode.equals(RoleCode)){
                havePriv=true;
            }
            row=rowSet.next();
        }
        return havePriv;
    }
 }

The next step is define a bean that will act as an interface between the Authorization bean and expression language, it will receive the parameter from expression language and pass it to a method developed earlier, the class should implements HashMap interface and implements get(Object) method:

import java.util.HashMap;

public class CallAuthorizationBean extends HashMap {
    public CallAuthorizationBean() {
        super();
    }

    public String get(Object key) {
        AuthorizationBean authorization=new AuthorizationBean();
         return ""+authorization.getUserInRole((String)key);
    }
}

Finally, you can call the customized method using expression language like this:
#{Authorization['20180100']=='false'}

Send Parameters to ActionListener and read it programmatically

You can send parameters when invoking ActionListener behavior by adding the f:attribute tag to Action issuer as explained below on commandButton component:

<af:commandButton text="send attribute" id="cb1"    actionListener="#{bean.method1}">    <f:attribute name="param" value="value"/></af:commandButton>

Then you can read  the attribute programmatically inside the method:

 public void method1(ActionEvent actionEvent) {

    String param = (String)actionEvent.getComponent().getAttributes().get("param");

    System.out.println(param);

    }

Row level Security using ADF and Sys_context package

When Developing a web application you must know that Database connection will be shared between different end users. Traditional Forms applications create a connection for each client connecting to the database while ADF shares the same connection pool and try to activate/passivate connection based on end user transactions.

Implementing Row level security or (Virtual Private Database) VPD can be into two ways:

- By Building a view on each table you have on your owner schema, and then adding the where condition inside view SQL script that will do the predicate based on user context variables.

- Or by using DBMS.RLS package by creating a policy procedure, and then add this policy to schema table using DBMS.RLS.Add_Policy(), the procedure returns the predicate to the table to execute the appropriate command in varchar2 format that contains your conditions.

In here I will demonstrate how to build and use context package through ADF web application using the first approach, the second one is easy to implement once you build the context package and you knew where to call it inside your ADF application.

At first you need to create CTX package at your owner schema:

CREATE OR REPLACE PACKAGE SEC_CTX_PKG IS 
   PROCEDURE SET_SESSION_ID (IN_SESSION_ID VARCHAR2); 
   PROCEDURE SET_CONTEXT (IN_NAME VARCHAR2, IN_VALUE VARCHAR2); 
 END SEC_CTX_PKG;
/
--------------------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY SEC_CTX_PKG 
 IS 
   GC$SESSION_ID  VARCHAR2 (100); 
   PROCEDURE SET_SESSION_ID (IN_SESSION_ID VARCHAR2) 
   IS 
   BEGIN 
    GC$SESSION_ID := IN_SESSION_ID;
    DBMS_SESSION.SET_IDENTIFIER (IN_SESSION_ID);
  END;  
-------------------------------------------------------------------------------------------------
   PROCEDURE SET_CONTEXT (IN_NAME VARCHAR2, IN_VALUE VARCHAR2) 
   IS 
   BEGIN 
    DBMS_SESSION.SET_CONTEXT ('SEC_CTX', IN_NAME, IN_VALUE, USER, 
                 GC$SESSION_ID); 
   END; 
 END SEC_CTX_PKG;
/

Then you need to create your own context variable:
CREATE OR REPLACE CONTEXT SEC_CTX
 USING SEC_CTX_PKG
 ACCESSED GLOBALLY;
 

You need to create a view on table that you want to filter at database which have the predicate. As an example if I have a table called user_info, I will be building a view called user_info_v as following:
SELECT *
     FROM user_info
    WHERE user_id = SYS_CONTEXT ('SEC_CTX', 'user_id_p');

All Developers should use this view to retrieve, insert, update and delete data.

This is all created at your table owner schema. Now moving to the ADF application part, you need to override the prepareSession method for the application module connecting to schema. The prepareSession allow the application module to initialize user defined context variable at our case user_id_p parameter.

    protected void prepareSession(Session session) {
        super.prepareSession(session);
        setVPDcontext(session);
    }


    private void setVPDcontext(Session session) {
        SessionImpl session2 = (SessionImpl)transaction.getSession();
        String username=session2.getUserPrincipalName();
        CallableStatement stmt = null;
       
        try {          
            String sql = "BEGIN  SEC_CTX_PKG.SET_SESSION_ID ('"+username+"');                  
                        CTX_PKG.SET_CONTEXT ('user_id_p', '"+username+"'); END; ";
            stmt = this.getDBTransaction().createCallableStatement(sql, 0);
            stmt.execute();
        } catch (Exception exp) {
            exp.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception exp) {
                    exp.printStackTrace();
                }
            }
        }
    }

Of-course you can add complex conditions, but this for demo only. If you want to filter the data on table base use DBMS.RLS package rather than building view on top of the table.