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.........
graziaziAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
>>IF name == nextof THEN

No '=='. just '='.

also, not a good idea to use variable names the same as column names.

I also suggest raise_application_error.

Try below (untested).
CREATE OR REPLACE PROCEDURE updateNextofkinProc (nextof IN NVARCHAR2, patientname IN NVARCHAR2)
IS 
v_name NVARCHAR2(20);
BEGIN
   select next_of_kin into v_name from Patient where name = patientname;
   IF name = nextof THEN 
    raise_application_error(-20101, 'Name has not changed');
   END IF;
     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

0
 
slightwv (䄆 Netminder) Commented:
There is no declare in a procedure.  Remove the DECLARE.
0
 
graziaziAuthor Commented:
I see. And when
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
"and when" what?

Please provide more about when you wish to raise the exception and I'll try to provide working code.
0
 
graziaziAuthor Commented:
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"

0
 
graziaziAuthor Commented:
Great thanks. That did the job.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.