Re: SQR - ORA-01722

["William G. Northen" <wgn3c@VIRGINIA.EDU>]

Could be that if you try to convert a field with a blank or space into a
number you
will get an error?

At 13:21 10/16/97 EDT, Christian Hagen wrote:
>In general is is better to but in a $Space or $Blank into date or number
>field?
>
>Let $Space = ' '
>Let $Blank = ''
>
>We are placing some blanks and spaces into dates fields that will be filled
>in when a particular process runs.
>-------------
>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.
>
>
Wm. G. Northen                  "Founded by Thomas Jefferson, 1819"
Programmer/Analyst              Unix, SQL, SQR, Sybase, Windows, ECT,
Development Group               Internet wgn3c@phil.dev.virginia.edu
University of Virginia          Tel (804) 924-3949