I am not entirely sure if this the right place but here it goes anyway:
We are using Oracle Workflow Manager Standalone(2.6.4) as part of our Warehouse Builder setup on a 10.2.0.3.0. Enterprise database on Linux .
As such the setup has just recently stopped working where as before it worked for a long time.
The problem is that it is not possible to log in to Oracle Workflow Manager with any user.
I have traced this problem to the mod_plsql.so library of the Oracle HTTP Server part of the owf setup.
What happens is that this module tries to login to the database when a user tries to login with hhis browser and sends an ALTER SESSION statement.
(This is also described in the docs)
This statement is misformed however, it contains to much characters.
Instead of :
ALTER SESSION SET NLS_LANGUAGE='DUTCH' NLS_TERRITORY='THE NETHERLANDS' NLS_CURRENCY='E'
the last bit , nls_currency, is being filled with random characters .
Since the total is more than the allowed limit the database returns, or mod_plsql decides, a ora-1017.
I used the proxy method described here, January 24, 2006: On a breakable Oracle, to find out what the mod_plsql.so package sends to the database.
Just read DADS /mod_plsql for SQLPlus.
I have to do this because these requests are handled as a SYS user and as such are not logged.
The mod_plsql library is supposed to use the DADS.CONF directives over any environment values.
However in the case of the PlsqlNLSLanguage directive this does not work.
The environment variable NLS_LANGUAGE , which is set to dutch , is given precedence.
It uses that to construct the ALTER SESSION statement.
If i change the environment variable to AMERICAN, the modplsql.so uses this to pick the currency and it gets the $ sign for NLS_CURRENCY.
Then the ALTER SESSION statement that is being sent is correct and there is no buffer overflow anymore.
And the database subsequently allows us in. However this changing of NLS_LANGUAGE at an environment variable level is not desirable for us since we get other translate problems.
Finally The Questions:
- Why does the mod_plsql.so package also send the NLS_CURRENCY ? This is mentioned in none of the (Oracle) documentation but we can clearly see it happening.
- Where does the mod_plsql.so package get this NLS_CURRENCY from? We don't set it anywhere in the environment or the .conf files, yet it is retrieved somewhere. In our case this is retrieveing some garbage data and thus causing the login to fail. Even looking in the .so library i see no mechanism for nls_currency.
- Why does the mod_plsql.so package favor the environment variable over the DADS.CONF PlsqlNLSLanguage directive. All the manuals say otherwise yet in our case it is not being used. And when i load the library in an editor i see remarks that indeed point to my statement.
The most important question here is where do i need to look to get the NLS_CURRENCY . It is somehow corrupt and i want to correct this ofcourse.
Another important one is how we can force the mod_plsql.so package to use the PlsqlNLSLanguage directive since we do not want to change the environment variable.
I hope someone can help us out here.
rgrds Mike