Thanks, bharj. I was looking at the documentation for 9i, and it says:
If expr1 is null, NVL returns expr2. If expr1 is not null, NVL returns expr1. The arguments expr1 and expr2 can have any datatype. If their datatypes are different, Oracle converts expr2 to the datatype of expr1 before comparing them.
The datatype of the return value is always the same as the datatype of expr1, unless expr1 is character data, in which case the return value's datatype is VARCHAR2 and is in the character set of expr1.
This seems to imply to me that the datatypes don't really matter. But then I realized, we're running 8.0.6. So I guess they do matter!
Anyway, that seems to have been the problem.
Thanks!
-----Original Message-----
From: gagandeep.[Email Address Removed] [mailto:gagandeep.[Email Address Removed] Friday, February 28, 2003 2:06 PM
To: Harmon, David
Subject: RE: 'Invalid number' in cursor loop
Have you checked if <col5>, <col7>, <col8> and <col9> are numeric? If there
are not then that's where your problem is.
-----Original Message-----
From: Harmon, David [mailto:David.[Email Address Removed] Friday, February 28, 2003 12:02 PM
To: Kothandaraman, Suryakanth (TRANS, Contr-LGS)
Cc: gagandeep.[Email Address Removed] RE: 'Invalid number' in cursor loop
That's what I would assume, but the SELECT runs just fine outside the
cursor. It has no conversions, but it does have a few nvl functions.
Thoughts?
Cursor definition:
select <col1>, <col2>, <col3>, <col4>, nvl(<col5>,0) as _cd, <col6>,
nvl(<col7>,0) as _score, nvl(<col8>,0) as product, <col9>
from <table_name>
where <col3> = as_of_date_in and <col9> <> 0;
-----Original Message-----
From: Kothandaraman, Suryakanth (TRANS, Contr-LGS)
[mailto:Suryakanth.[Email Address Removed] Friday, February 28, 2003 12:17 PM
To: [Email Address Removed] Harmon, David
Subject: RE: 'Invalid number' in cursor loop
The error is not in FOR LOOP. The error would be in the cursor definition.
Try to execute the SELECT stmt in the cursor seperately to see it has any
errors.
-Suri.
-----Original Message-----
From: Harmon, David [mailto:David.[Email Address Removed] Friday, February 28, 2003 10:54 AM
To: LazyDBA.com Discussion
Subject: 'Invalid number' in cursor loop
Hi all.
Anyone ever seen an ORA-01722 - (invalid number) pointing to the 'FOR'
statement of a cursor loop?
The line number the error points to just says:
For row1 in c1 loop
Strange?
TIA.
David
--------
Oracle documentation is here:
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
To unsubscribe: send a blank email to oracledba-[Email Address Removed] subscribe: send a blank email to oracledba-[Email Address Removed] the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Tell yer mates about http://www.farAwayJobs.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page