Re: SQR - ORA-01722 - Invalid number

["Young, Larry" <LYoung@MEDIAONE.COM>]

I believe I had this happen to me awhile ago.  I was looking at zip
codes expecting to always get numerics.  Some records came in as (oh)
o1940 instead of (zero)  01940.  The & conversion created the error.
Can't remember
at what point the problem occurred.  Just that you should look at the
field you are treating as numerics.  It may not always have numerics in
it and can explain why the program sometimes works.  Hope this helps.

============================================
Larry Young                    VISIT:  http://www.mediaone.com
Voice:     617-942-0400 x2402
Email:      mailto:lyoung@mediaone.com
Personal: mailto:lyoung@world.std.com
MediaOne
This is Broadband. This is the way.
335 Main Street
Reading, Ma 01867



> ----------
> From:         Christian
> Hagen[SMTP:christian.hagen@SMTPGATE.CRESTAR.COM]
> Sent:         Thursday, October 16, 1997 10:17 AM
> To:   Multiple recipients of list SQR-USERS
> Subject:      SQR - ORA-01722
>
> 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.
>