Home » SQL & PL/SQL » SQL & PL/SQL » NVL Function (Oracle 10g)
NVL Function [message #569313] Wed, 24 October 2012 11:39 Go to next message
Messages: 41
Registered: July 2012
Location: Argentina
Good Evening to everyone.

I'm currently facing a problem making a Query in Oracle, the problem I have is, the user of the application send regulary an amount of cases to be solved from the DB, but in case that the case doesn't exist we must let'em know.
So, to try to to this I'm aplying the NVL function witch retrieves the parameter if the value is null, but it doesn't work because the record doesn't exist, in PL/SQL could be a NO_DATA_FOUND. Is there any built-in function wich allow me to do that ?
Re: NVL Function [message #569314 is a reply to message #569313] Wed, 24 October 2012 11:50 Go to previous messageGo to next message
Michel Cadot
Messages: 65153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a way (that can be adapt to any language and client tool):
SQL> host type m.sql
set feedback off
col f new_value flag
def flag=0
select dummy, 1 f from dual where 1=&&value;
set head off
select '&&value does not exist!' from dual where &flag=0;
set head on
undef flag
undef value

SQL> @.\m.sql
Enter value for value: 1
D          F
- ----------
X          1
SQL> @.\m.sql
Enter value for value: 0
0 does not exist!

Re: NVL Function [message #569327 is a reply to message #569314] Wed, 24 October 2012 15:11 Go to previous message
Bill B
Messages: 1663
Registered: December 2004
Senior Member
The following will work

>select nvl(max(dummy),0)
  2  from dual;


Wrote file afiedt.buf

  1  select nvl(max(dummy),0)
  2  from dual
  3* where 1=2


Previous Topic: To_Date()
Next Topic: how to get unique value in column ?
Goto Forum:

Current Time: Tue Aug 22 08:13:27 CDT 2017

Total time taken to generate the page: 0.04500 seconds