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

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: 11 Apr 2006 11:45:59 -0700
Message-ID: <1144781159.073285.275430@i40g2000cwc.googlegroups.com>

Your exception handler simply rolls back and exits, it doesn't return to where the exception was raised and continue from there. You need to move

> dbms_output.put_line('HOST NOT CONFIGURED....EXITTING;');

into the exception handler block. Handle correct exception, too, CASE_NOT_FOUND is not the exception you are expecting.

The code should look like this:

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'

;

dbms_output.put_line('HOST '||this_host||' CONFIGURED....EXITING;');

exception

     when NO_DATA_FOUND then
                dbms_output.put_line('HOST NOT
CONFIGURED....EXITING;');
                raise; -- this is to re-throw the exception so that it

is not masked by the handler
end;

Hth,

     Vladimir M. Zakharychev
     N-Networks, makers of Dynamic PSP(tm)
     http://www.dynamicpsp.com

Received on Tue Apr 11 2006 - 13:45:59 CDT