NVL( ) with NUMBER field

From: Stephen B <stephen.bell_at_cgi.ca>
Date: Wed, 16 Oct 2002 08:09:53 -0400
Message-ID: <mGcr9.4694$Lb1.754400@news20.bellglobal.com>

Good day all,

Oracle 8.1.7.3 on NT 4.

I've been given a trigger to debug...
It was encountering NO_DATA_FOUND errors due to several SELECT...INTO statements where the fields of a non-related table are selected into scalar variables after insert. These values are then placed into an audit table.

Several of these fields are optional and in fact are often null which caused the no_data_found problem.

I'm thinking I can just use NVL(field_name,' ') to insert an empty string into the audit table when there is no data to select into a variable. If you have a better plan please let me know.

Even if "Plan A" above works though, some of the fields are NUMBER fields..so, even if I use NVL(TO_CHAR(number_field),' ') I think I'm going to run into a problem trying to insert the empty string into the number field...is there some other character I should look at that would allow me to use NVL with a number field ?

If I'm headed in the wrong direction entirely please let me know.

Thanks and best regards in advance,
Steve Received on Wed Oct 16 2002 - 07:09:53 CDT