Out of box: Oracle proxy user with ADF Application

I was facing long time with oracle proxy user and ADF application module problem. Therefore, i need to share how i resolved that problem. Main purpose of that proxy connection: We need to audit user action in oracle database side (audit trail).

Problem: I have an implemented Oracle Proxy User connection into Application Module. Same as this (https://blogs.oracle.com/imc/entry/how_to_use_database_proxy). But after i implemented, there is coming one strange problem with application module session. It means, Our application module always replicated by different session and connection.

Solution: First of all, many thanks for Vijai, who is helped me on Oracle Forum.
This is our piece of code for Proxy Connection in the Application Module implementation class:

    @Override
    protected void prepareSession(Session session) {
        String username =
            ADFContext.getCurrent().getSecurityContext().getUserName().toUpperCase();

        Statement st =
            getDBTransaction().createPreparedStatement("rollback", 0);

        try {
            if (st.getConnection() instanceof PoolConnection) {
                PoolConnection poolConnection =
                    (PoolConnection)st.getConnection();
                OracleConnection connection =
                    (OracleConnection)poolConnection.checkConnection();


                if (connection.isProxySession() &&
                    username.equals(connection.getUserName())) {
                    // no proxying required, we already have a connection that is proxied for this user
                    super.prepareSession(session);
                    return;
                }

                clearStatementCache(poolConnection);

                if (connection.isProxySession()) {
                    connection.close(OracleConnection.PROXY_SESSION);
                }

                Properties properties = new Properties();
                properties.put(OracleConnection.PROXY_USER_NAME, username);
                connection.openProxySession(OracleConnection.PROXYTYPE_USER_NAME,
                                            properties);

                clearStatementCache(poolConnection);

            } else {
                throw new JboException("Not Pool Connection");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        super.prepareSession(session);
    }


    /**
     * Clear out any cached statements
     */
    @Override
    protected void beforeDisconnect() {
        Statement st =
            getDBTransaction().createPreparedStatement("rollback", 0);
        try {
            PoolConnection poolConnection = (PoolConnection)st.getConnection();

            clearStatementCache(poolConnection);

            poolConnection.close(OracleConnection.PROXY_SESSION);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        super.beforeDisconnect();
    }


    private void clearStatementCache(PoolConnection poolConnection) throws SQLException {
        poolConnection.clearStatementCache();

        OracleConnection connection =
            (OracleConnection)poolConnection.checkConnection();

        if (connection.getExplicitCachingEnabled()) {
            connection.purgeExplicitCache();
        }
        if (connection.getImplicitCachingEnabled()) {
            connection.purgeImplicitCache();
        }
    }

And if you want use one connection, you can make all AM to child connection of that AM.

Tips:
1. Before i could not import weblogic.jdbc.wrapper.PoolConnection class. Problem is my model project hasn't any technology scope. It means, We have to select ADF Business Component in technology scope of project properties.

2. Also we was getting different error from weblogic side. (Error: java.lang.ClassCastException: weblogic.jdbc.wrapper.JTAConnection_weblogic_jdbc_wrapper_XAConnection_oracle_jdbc_driver_LogicalConnection cannot be cast to weblogic.jdbc.wrapper.PoolConnection). After i changed XA datasource to NON-XA connection, it was work. In our case, it must be (oracle.jdbc.OracleDriver).

3. Additional tip: Regarding to http://javaosdev.blogspot.com/2011/01/adf-closed-statement-again.html that post. Because i get following error "Caused By: java.sql.SQLRecoverableException: Closed Statement". In this case, We have to turn off statement cache from connection pool. (Give parameter Statement Cache Size : 0).



If i found additional tips from real world problem, i will update it.

Thank you,
Erdenebayar

Linkedin: http://www.linkedin.com/in/erdenebayare
Twitter: http://twitter.com/#!/erdenebayare