Re: PL/SQL Exception handling

Frank <fvanbortel_at_netscape.net> wrote in message news:<3E7A2D0D.7080705_at_netscape.net>...
> davidmac wrote:
> > Beware Newbie Question.
> >
> > I have a simple pl/sql script that I am trying to write that is
> > driving me bloomy! It seems that the contents of my exception is
> > being run even if there should be no exception raised. If I find a
> > value that is valid, the exception runs then I get the output
> > expected.
> >
> >
> > DECLARE
> >
> > v_physician physician%rowtype;
> > v_PHYS_ID CHAR(3);
> > v_PHYS_NAME VARCHAR2(20);
> > v_PHYS_PHONE CHAR(12);
> > v_PHYS_SPECIALTY VARCHAR2(20);
> >
> > BEGIN
> > DBMS_OUTPUT.ENABLE;
> > v_PHYS_ID := '&PID';
> > select *
> > into v_physician
> > from physician
> > where PHYS_ID=v_PHYS_ID;
> > dbms_output.put_line ( v_physician.PHYS_ID||' '||
> > v_physician.PHYS_NAME||' '||
> > v_physician.PHYS_PHONE||' '||
> > v_physician.PHYS_SPECIALTY);
> >
> > EXCEPTION
> >
> > when no_data_found
> >
> > then
> > DBMS_OUTPUT.PUT_LINE('Phys_ID not found, please enter new data');
> > begin
> > v_PHYS_NAME := '&PNAME';
> > v_PHYS_PHONE := '&PPHONE';
> > v_PHYS_SPECIALTY := '&PSPECIALTY';
> >
> > insert into physician(PHYS_ID, PHYS_NAME, PHYS_PHONE, PHYS_SPECIALTY)
> > values ( v_PHYS_ID, v_PHYS_NAME, v_PHYS_PHONE, v_PHYS_SPECIALTY);
> > commit work;
> > end;
> >
> > END;
> > /
> >
> >
> > are these lines the problem?
> >
> > v_PHYS_NAME := '&PNAME';
> > v_PHYS_PHONE := '&PPHONE';
> > v_PHYS_SPECIALTY := '&PSPECIALTY';
> >
> >
> > tia
>
> And you expect SQL*Plus to kick in and ask about the
> values of PNAME, PPHONE and PSPECIALITY? No dice.
> That's plus, not pl/sql

It asks for the values if the exception is not raised. that is the problem. Received on Fri Mar 21 2003 - 15:13:01 CET