Re: SQR - ORA-01722

[Ketan Kothari <ketan@MASTECH.COM>]

No, You should initialize the number fields with zero and not with spaces. That
should solve your problem. Date field need not be initialized at all. They can
be null in the peoplesoft. Only character fields are initialized with spaces and
number fields with zeroes.

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.