Re: SQR - ORA-01722

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

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.