EJB Applications Return NULL When Accessing a Database Proxy User (Doc ID 793213.1)

Last updated on NOVEMBER 05, 2016

Applies to:

Oracle Containers for J2EE - Version: 10.1.3.3.0 and later   [Release: AS10gR3 and later ]
Information in this document applies to any platform.

Symptoms

J2EE Application is accessing Oracle Database Server  via a connection obtained from a proxy data source.
Fom this application, the  SQL query  select sys_context('userenv','proxy_user') from dual  returns NULL value.
However, the expected value is 'proxy_user' as the proxy data source/connection pool is configured with the "proxy_user" user.

Here is the output of the test program  (see testcase  Proxy.zip ) showing the issue   :

09/03/30 15:18:37 !!!scott!!!
09/03/30 15:18:37 test 1
09/03/30 15:18:37 #null#
09/03/30 15:18:37 test 2

Steps To Reproduce

Here are the  steps  showing how to reproduce the issue :

1. Firstly, the proxy database user needs to be created as shown in the in section Setup the Database Side in the following note.

<> :  How To Use a Proxy Session Data Source with OC4J 10.1.3.3

2. Edit file $ORACLE_HOME/j2ee/<container-name>/config/data-sources.xml with a data source as follows for the proxy user created above. You must set the property proxy-sessions to TRUE.

<managed-data-source name="proxyDS"
connection-pool-name="ProxyConnectionPool"
jndi-name="jdbc/proxyDS"/>

<connection-pool name="ProxyConnectionPool"
proxy-sessions="true">
<connection-factory factory-class="oracle.jdbc.pool.OracleDataSource"
user="proxy_user"
password="proxy_user"
url="jdbc:oracle:thin:@<your_hostname>:<port>:<sid>">
</connection-factory>
</connection-pool>


3. Now,  restart the container to pickup the new data sources created above :

OAS (Oracle Application Server)
> opmnctl stopproc process-type=<container_name>
> opmnctl startproc process-type=<container-name>

Standalone OC4J
> oc4j -shutdown -port <ORMI port> -password <password>
> oc4j -start

4. Download the sample application EAR file application1.ear.

5. Deploy EAR file application1.ear to the container where the data source in step (2) was created with the Application Name = proxysvr.


6. Run the sample application.

http://<host>:<port>/proxycus/proxysvr

7. At the login prompt enter the user credentials that is accessing for the proxy user i.e. in this case scott/tiger.

8. In the browser, the following message will appear :

The servlet has received a GET. This is the reply

9a. If using standalone OC4J go to the command prompt where OC4J was started  and the following trace is displayed :

09/03/13 08:00:23 #null#

 
9b. If deployed to OAS 10.1.3.X then go to the container log file (i.e.
default_group~home~default_group~1.log) and the following trace is displayed :

--------
09/03/12 09:57:07 Start process
--------
09/03/12 09:57:45 Oracle Containers for J2EE 10g (10.1.3.4.0) initialized
09/03/13 06:35:42 !!!scott!!!
09/03/13 06:35:46 test 1
09/03/13 06:35:46 #null#

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms