[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Declaring an excepttionin PL SQL procedure

Posted on 2011-04-26
6
Medium Priority
?
215 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.........
0
Comment
Question by:graziazi
  • 3
  • 3
6 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35469174
There is no declare in a procedure.  Remove the DECLARE.
0
 

Author Comment

by:graziazi
ID: 35469279
I see. And when
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35469299
"and when" what?

Please provide more about when you wish to raise the exception and I'll try to provide working code.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:graziazi
ID: 35469302
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
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 35469338
>>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
 

Author Comment

by:graziazi
ID: 35469395
Great thanks. That did the job.

0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question