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:
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.
Well i must say i am sorry not haveing received any answer whatsoever.
This absence of Oracle people here is worrying me, and is the second time in a row lately.
It seems Oracle is abandoning its own products.
Anyway, just to answer my own thread so that somebody else gets some benefit from it:
After investigation i find that it works like this when things go right:
The environment value NLS_LANGUAGE is used and parsed in the first bit. The corresponding bits pop up in the AUTH_ALTER_SESSION key-value pair. Modplsql finds the other info(i don't know where really) such as NLS_CURRENCY and puts that there.
The dads.conf PlsqlNLSLanguage setting is used and parsed in the second step. The second step is formed like alter session set nls_language='DUTCH' nls_territory='THE NETHERLANDS' .
So my assumption about modplsql not using the Plsql is wrong here, but due to the error i encountered my debug info never got past step 1.
If the environment value is not set then a default value of AMERICAN_AMERICA is used.
What went wrong in my case ?
The environment var was set to DUTCH. Modplsql uses this to lookup nls_currency as explained to output the info in step 1.
However nls_currency returned garbage instead of just the euro sign. This is the real problem btw and not solved yet in our case. If someone knows where modplsql gets this info i would like to know this. !
The other steps were never finished and therefore it looked to the database that the AUTH_ALTER_SESSION key-value pair was too long.
It could not authenticate this reqeuest causing the effect that nobody could login. Since these requests are handled as SYS users no logging takes place. Only a trace with the error:
*** SERVICE NAME:(SYS$USERS) 2013-07-10 11:01:29.414
*** SESSION ID:(458.3138) 2013-07-10 11:01:29.414
Buffer overflow for attribute AUTH_ALTER_SESSION - max length actual length
indicates there is something wrong here.
Setting the dads.conf file to override this environment parameter doesn'solve this ofcourse since this info is used somewhere else.
Fixing it, for now at least , means clearing the envrionment variable , then starting the http server.
And using DUTCH in the dads.conf file.
After starting the http server we reset the environment variable.
I am still looking for an answer on where modplsql gets the NLS_CURRENCY info since thats where the corruption is !
Hope somebody can use this info.