We have an ADF application, which hooks into Portal and SOA running on Weblogic 10.3.6 in a Fusion Middleware environment.
A few weeks ago after pushing a new release of the code to production, users encountered an ORA-01722 error in some screens of the application. We captured the SQL and could run it via SQL*Plus and Sql Developer with no errors.
We opened an SR with Oracle and they offered some suggestions and in the end recommended a patch to fix a shared cursor bug. We haven't applied that patch yet because we are on the latest PSU for 220.127.116.11 and that patch isn't available for it. So we've issued a patch request with Oracle.
The weird problem is, that when my co-worker starts the WebLogic Managed Server, the users do not encounter the ORA-01722 error. The only difference is that I am ssh'ing from a Mac and he is on windows. We ssh into the same generic account and execute the same steps. We've also confirmed that I can restart the server a dozen times with the error and my co-worker has never had the problem. We've verified this a few times.
Today we had some more maintenance and I tried restarting the server again. Users encountered the same ORA-01722 error. I restarted the server again but this time from my windows computer using SecureCRT. Error disappears.
The following is a diff between a mac and windows ssh session. There isn't a huge difference.
< SSH_CLIENT=X.X.X.X 57952 22
> SSH_CLIENT=X.X.X.X 65516 22
< SSH_CONNECTION=X.X.X.X 57952 X.X.X.X 22
> SSH_CONNECTION=X.X.X.X 65516 X.X.X.X 22
The only one that catches my eye is the LANG env variable but I honestly don't see that as having any affect on the application. If there was an environmental difference I would expect to see other types of issues. ORA-01722 is pretty specific.
Also, we can only reproduce this on our production server. If I restart DEV/TEST from my Mac there are no issues. During a future maintenance window I may try changing the different environment settings after I ssh in from my Mac just to see if the error re-occurs.
Regardless, we'll still proceed with the patch request just in case it happens again. Weird Problem!