Re: SQR - ORA-01722

[Christian Hagen <christian.hagen@SMTPGATE.CRESTAR.COM>]

I believe (and hope) that I have found the problem that "occasionally"
caused the ORA-01722 error.

If you are familiar with PeopleSoft Financial Tables you know that there is
a PROCESS_INSTANCE Field.

PROCESS_INSTANCE        Nbr             10

I was inserting into this field a variable that is created in a sqc.  The
variable was $prcs_process_instance which is a string variable.

What (I think) needed to be done is:
Let #prcs_process_instance = to_number($prcs_process_instance)
!to ensure that the PROCESS_INSTANCE to be inserted was a number.

I do not know for sure that this is the problem as I have not been able to
capture the Field that caused the ORA-01722 error.

**Does this sound reasonable that a Field defined as a number would
"sometimes" take a string variable with all digits? **
If so than explicitly converting it to a numeric variable and inserting that
variable into the numeric field should get rid of this error appearing
again.

Let me know if this sounds faulty.

Thank you very much for all the help and great answers.
-------------
Original Text
>From SQR-USERS@list.iex.net ("Discussion of SQR,              SQRiBE
Technologies's database reporting language"), on 10/16/97 10:17 AM:
To: SQR-USERS@list.iex.net (Multiple recipients of list SQR-USERS)

Has anybody ever seen the Oracle error (ORA-01722:  invalid number) when
running an SQR?  We are getting it on one of our programs "sometimes".  By
"sometimes" I mean that the program runs fine the majority of the time but
twice in the past 5 days we have gotten the error shown below.  It ran for 3
weeks with out fail but failed the first time this week.  We got the error
again last night and ran it again immediately and the program ran
successfully. We are using SQR3, Oracle 7.3.3, Sun Solaris.

The error message returned from the SQR is listed below as is the Cause and
Action provided by Oracle for this error.  The cause and action do not seem
to fit this seemingly simple insert into a table.  The fact that this error
is produced only at times and the program can be run immediately after
failing with a successful result is puzzling.

It has even been suggested by an Oracle DBA here that it may be an Oracle
bug.  Again, if anybody has seen this or could pass along some insight to
this probelm please let know.

Thank you for your time.

________________LOG FILE______________
Operator Id:  ISRUNGL
Process Instance:  7538.lis

/opt/psoft/v6.01/sqr/glfupca.sqr

Start Time:  15-OCT-1997_05:42:29_PM

/opt/psoft/v6.01/sqr/glfupca.sqr: GLFUPCA - SQL Statement =
Insert_Header - INSERT INTO ps_jrnl_header
SQL Status =-1722 SQL Error  = ORA-01722: invalid number


__________________ORACLE DESCRIPTION OF ERROR______________________

ORA-01722 invalid number

Cause:  The attempted conversion of a character string to a number failed
because the character string was not a valid numeric literal.  Only numeric
fields or character fields containing numeric data may be used in arithmetic
functions or expressions.  Only numeric fields may be added to or subtracted
from dates.

Action:  Check the character strings in the function or expression.  Check
that they contain only numbers, a sign, a decimal point, and the character
"E" or "e" and retry the operation.