asked on

Declaring an excepttionin PL SQL procedure

Hi. I want to declare an exception in my procedure........

CREATE OR REPLACE PROCEDURE updateNextofkinProc (nextof IN NVARCHAR2, patientname IN NVARCHAR2)
IS 
DECLARE
invalid_name EXCEPTION;

BEGIN
     UPDATE patient SET next_of_kin = nextof WHERE name = patientname;
     DBMS_OUTPUT.PUT_LINE('Next of kin changed to ' || nextof); 
END updateNextofkinProc;
/

Open in new window

However I get an error....

Encountered the symbol "end-of-file" when expecting one of the following:
begin.........

slightwv (䄆 Netminder)

There is no declare in a procedure. Remove the DECLARE.

graziazi

ASKER

I see. And when

slightwv (䄆 Netminder)

"and when" what?

Please provide more about when you wish to raise the exception and I'll try to provide working code.

graziazi

ASKER

Sorry ignore that above........

and when I want to implement the EXCEPTION in my code can I do it as follows.....

CREATE OR REPLACE PROCEDURE updateNextofkinProc (nextof IN NVARCHAR2, patientname IN NVARCHAR2)
IS 
Invalid_name EXCEPTION;
name NVARCHAR2(20);
BEGIN
[i]select next_of_kin into name from Patient where name = patientname;
IF name == nextof THEN 
RAISE invalid_name;
END IF;[/i]
     UPDATE patient SET next_of_kin = nextof WHERE name = patientname;
     DBMS_OUTPUT.PUT_LINE('Next of kin changed to ' || nextof); 

[i]EXCEPTION
WHEN invalid_name THEN
DBMS_OUTPUT.PUT_LINE('Name has not changed');
END;[/i]

END updateNextofkinProc;
/

Open in new window

I'm getting the error.....

ERROR at line 19: PLS-00103: Encountered the symbol "END"

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

graziazi

ASKER

Great thanks. That did the job.