Skip to main content

Almost foolproof ADF Oracle proxying

1) Create a common application model (this also sets up the oracle users in the context just for auditing)
@Override
    protected void prepareSession(Session session){

        super.prepareSession(session);

        try{
            String userName = ADFContext.getCurrent().getSecurityContext().getUserName();
            String host = ((HttpServletRequest)ADFContext.getCurrent().getEnvironment().getRequest()).getRemoteHost();
            String ip = ((HttpServletRequest)ADFContext.getCurrent().getEnvironment().getRequest()).getRemoteAddr();

            if (userName != null){
                setupSessionInfo(getDBTransaction(), host, ip, userName);
            }
            proxyUser(getDBTransaction());
            //this is done because of a crappy weblogic caching error
            clearPoolCache(getDBTransaction());
        } catch (Exception e){
            session = null;
            System.out.println("------> Error in user proxy");
            e.printStackTrace();
            throw new ApplicationModuleException(e);
        }
    }



2) Extend this AM in all you application modules that require proxy
public class MyAMImpl extends MyParentApplicationModuleImpl implements MyAM{

3) Grant user rights to connection pool user

alter user MyUser grant connect through CPUser;

4) Proxy user method

public static void proxyUser(DBTransaction transation) throws SQLException{

        Statement stat = transation.createPreparedStatement("rollback", 0);
        OracleConnection oConn = (OracleConnection)stat.getConnection();

        Properties props = new Properties();
        String uname = ADFContext.getCurrent().getSecurityContext().getUserPrincipal().getName();
        props.put(OracleConnection.PROXY_USER_NAME, uname);
        if (oConn.isProxySession()){
            oConn.close(OracleConnection.PROXY_SESSION);
        }
        try{
            oConn.openProxySession(OracleConnection.PROXYTYPE_USER_NAME, props);
        } catch (SQLException e){
            oConn.abort();
            if (!stat.isClosed()){
                stat.close();
            }
            throw e;
        }

        if (!stat.isClosed()){
            stat.close();
        }

    }

5) Clear cache method (could just switch weblogics cache off)
public static void clearPoolCache(DBTransaction transation) throws SQLException{

        Statement stat = transation.createPreparedStatement("rollback", 0);
        OracleConnection oConn = (OracleConnection)stat.getConnection();
        clearPoolConnStmntCache(oConn);
        if (!stat.isClosed()){
            stat.close();
        }
    }

6) setupSessionInfo method
protected void setupSessionInfo(DBTransaction transaction, String host, String ipAddress, String userName){

//this sql is just an example you can do more here
        String sql = " BEGIN dbms_session.set_identifier(:host); dbms_application_info.set_client_info(:ipAddress); END;";

        Map parameters = new HashMap();
        parameters.put("host", host);
        parameters.put("ipAddress", ipAddress);
        parameters.put("userName", userName);
        AdfJdbcUtil.executeNamedCall(false, transaction, sql, parameters);
    }

Comments

  1. Hello, It is interesting post. But where is clearPoolConnStmntCache method?

    I'm working on proxy user and tuning side.

    Erdenebayar

    ReplyDelete
    Replies
    1. Yhea sorry missed that one..

      public static void clearPoolConnStmntCache(Connection conn) throws SQLException {
      if(conn instanceof WLConnection) {
      ((WLConnection) conn).clearStatementCache();
      }
      }

      Delete

Post a Comment

Popular posts from this blog

ADF Encountered deferred syntax #{ in template text.

OracleJSP error: oracle.jsp.parse.JspParseException:  Error: Encountered deferred syntax #{ in template text.  If intended as a literal, escape it or set directive  deferredSyntaxAllowedAsLiteral This normally happens when you have some tag lib dependancy problems but this was  not the case for me... My problem: For some reason my model project had web stuff in it(public html etc)  so I had to remove the public html stuff from my project and manually edit the Model.jpr project file and remove the tag lib entries at the bottom o the file. Go figure.    

JBO-25013: TooManyObjectsException

oracle.jbo.TooManyObjectsException: JBO-25013: Too many objects match the primary key oracle.jbo.Key[Key null ]. Ok so for you it may be trying to insert a duplicate record this should explain your problem (also check trigger they could be the cause.) NOTE: You can also try to create a new duplicate EO if you have a page with two VO's using the same EO. This could sort your problems. For me I needed to add a launch listener on my LOV and clear the cache of my vo. LOV <af:inputListOfValues id="NameId" popupTitle="#{bindings.Name.hints.label}" value="#{bindings.RolName1.inputValue}" label="#{bindings.RolName1.hints.label}" model="#{bindings.RolName1.listOfValuesModel}" required="#{bindings.RolName1.hints.mandatory}" columns="#{bindings.RolName1.hints.displayWidth}" shortDesc="#{bindings.RolName1.hints.tooltip}" launchPopupListener="#{backingBeanScope.backingBean.launchPop

OJDeploy: Documentation for the tool

Real DOCS:  http://docs.oracle.com/cd/E26098_01/user.1112/e17455/deploying_apps.htm#OJDUG645 OJDeploy Documentation if you run it from the command line - I keep looking for this so I though I would post it here so I remeber. Oracle JDeveloper Deploy 11.1.2.1.0.6081 Copyright (c) 2003, 2010, Oracle and/or its affiliates. All rights reserved. Usage:   ojdeploy -profile <name> -workspace <jws> [ -project <name> ] [ <options> ]   ojdeploy -buildfile <ojbuild.xml> [ <options> ]   ojdeploy -buildfileschema Arguments:   -profile               the name of the Profile to deploy   -workspace      full path to the JDeveloper Workspace file(.jws)   -project              name of the JDeveloper Project within the .jws where the Profile can be found. If omitted, the Profile is assumed to be in the Workspace.   -buildfile            full path to a build file for batch deploy   -buildfileschema  print XML Schema for the build file