Why Doesn't This Simple PL/SQL Code Work?

From: DBA9999 <cdavis10717_at_comcast.net>
Date: 11 Apr 2006 11:29:40 -0700
Message-ID: <1144780180.375527.204110@j33g2000cwa.googlegroups.com>

OK, I give up, I can not make this simple pl/sql code block give me a message when no data is returned from a table.

I correctly get the output line when a row is returned, but not when no row is returned.

What am I doing wrong, please?

Thanks so much.

C

REM ==================================================

REM
REM confirm that THIS_HOST is Configured REM

REM ==================================================

set serveroutput on

declare

	this_host varchar2(25) := NULL;
	this_nmon_dir varchar2(25);
	this_temp_dir varchar2(25);

begin

select

	host,
	nmon_dir,
	temp_dir
into
	this_host,
	this_nmon_dir,
	this_temp_dir
from
	nmon.nmon_server_config
where
	host = '&1'

;

begin

                dbms_output.put_line('HOST VALUE IS '||this_host); end;

if

        this_host is NULL
then

	begin
		dbms_output.put_line('HOST NOT CONFIGURED....EXITTING;');
	end;
else
	begin
		dbms_output.put_line('HOST '||this_host||' CONFIGURED....EXITTING;');
	end;

end if;

exception
  when case_not_found then
    this_host := NULL;

        when others then rollback;

end;

.
run; Received on Tue Apr 11 2006 - 13:29:40 CDT