Saturday

ORA-01722: Invalid Number - Weird Problem


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 11.1.0.7 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.


< TERM=xterm
---
> TERM=xterm-256color
5c5
< SSH_CLIENT=X.X.X.X 57952 22
---
> SSH_CLIENT=X.X.X.X 65516 22
7c7
< SSH_TTY=/dev/pts/1
---
> SSH_TTY=/dev/pts/4
10c10
< LS_COLORS=no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.btm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:
---
> LS_COLORS=
16c16
< LANG=en_US.UTF-8
---
> LANG=en_CA.UTF-8
23c23
< 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!

1 comment:

raju said...


Ecorptrainings.com provides ORACLE APPS TECH in hyderabad with best faculties on real time projects. We give the best online trainingamong the ORACLE APPS TECH in Hyderabad.
Classroom Training in Hyderabad India