We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Declaring an excepttionin PL SQL procedure

graziazi
graziazi asked
on
Medium Priority
224 Views
Last Modified: 2012-05-11
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.........
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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

Author

Commented:
I see. And when
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
"and when" what?

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

Author

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"

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Great thanks. That did the job.

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.