How to setup concurrent calls in Oracle 10g Java VM

Question

If somebody can explain me how to properly configure plsql java wrapper when different database users invoke same procedure to ensure correct concurrent resource access handling.

DBMS and JAVA: Oracle 10g, internal JavaVM 1.4.2

I have MyDatabse with 1 shema owner and 10 db users granted to connect to it:
DBOWNER
DBUSER01
DBUSER02
...
DBUSER10

I have PL/SQL wrapper procedure: my_package.getUser() that wrapps UserHandler.getUser()

I have java class UserHandler uploaded to MyDatabase with loadjava:

public class UserHandler {
    private static final int MAX_USER_COUNT = 10;
    private static final String USERNAME_TEMPLATE = "EIS_ORA_20";
    private static int currentUserSeed = 0;
    /**
    * Generates EIS user according to pattern agreed by EIS developers. It
    * circles user pool with round-robin method ensuring concurrent calls.
    * 
    * @return valid EIS USERNAME
    */
    synchronized public static String getUser() {
        String newUser = USERNAME_TEMPLATE + currentUserSeed;    
        currentUserSeed++;    
        currentUserSeed = currentUserSeed % MAX_USER_COUNT;    
        return newUser;    
    }    
}    

The idea of wrapper is to ensure proper distribution of external information system usernames to DBUSERS connected to MyDatabase with Oracle Forms Client Application.

My problem is that when 5 users concurently call procedure my_package.getUser() I got:

DBUSER01 - call to my_package.getUser() returned EIS_ORA_200
DBUSER02 - call to my_package.getUser() returned EIS_ORA_200
DBUSER03 - call to my_package.getUser() returned EIS_ORA_200
DBUSER04 - call to my_package.getUser() returned EIS_ORA_200
DBUSER05 - call to my_package.getUser() returned EIS_ORA_200

I was expected that each DBUSER would get different user (as I confirmed in my JUnit tests where multiple concurrent threads invoke UserHandler.getUser()). Later I've red that plsql wrapper calls can be setup in 2 maner:

1. to share java memory space between DBUSERS or
2. to separate memory space for each DBUSER

My conclusion is that UserHandler class is loaded for each DBUSER separately and that is why I have no use of static counter and synchronized method.

How to configure MyDatabase to force calls to my_package.getUser() use same java space for each DBUSER?

Thank you very much!

Answer 1

I don't believe there is any way to configure Oracle to share a JVM between multiple user sessions. The Java Developer's Guide for 10g states:

Oracle JVM model

Even when thousands of users connect to the server and run the same Java code, each user experiences it as if he is running his own Java code on his own JVM...

Generally the appropriate way to share data between sessions in an RDBMS is with database objects. In this case the simplest thing would be to use an Oracle sequence, with minvalue 1, maxvalue 10, and cycling enabled. You could just select from the sequence directly in the Java code.

Another approach would be to simply generate a uniformly-distributed random number between 1 and 10. If there are enough sessions then over time this should distribute the sessions evenly.