Home » SQL & PL/SQL » SQL & PL/SQL » Raising exceptions
Raising exceptions [message #248754] Sun, 01 July 2007 11:21 Go to next message
Xeon
Messages: 4
Registered: June 2007
Junior Member
I have a students table and pl code as shown below:
CREATE SEQUENCE student_sequence
START WITH 10000
INCREMENT BY 1;

CREATE TABLE students (
id NUMBER(5) PRIMARY KEY,
first_name VARCHAR2(20),
last_name VARCHAR2(20),
major VARCHAR2(30),
current_credits NUMBER(3)
);
(I've inputted values for them)

pl/sql code:
CREATE OR REPLACE PROCEDURE Delete_A_Student
(
t_student_id NUMBER
)

IS
not_found EXCEPTION;

BEGIN
If SQL%NOTFOUND THEN
RAISE not_found;
ELSE
DELETE FROM Students WHERE (id = t_student_id);
DBMS_OUTPUT.PUT_LINE('DONE');
END IF;

EXCEPTION
WHEN not_found THEN
DBMS_OUTPUT.PUT_LINE('Error found, no such ID number found!');

END Delete_A_Student;
/
The pl/sql works in terms of deleting a record by id. However when I've tried to put an id not available in the table, it wouldn't raise the exception. What could I do to fix the problem?
Re: Raising exceptions [message #248755 is a reply to message #248754] Sun, 01 July 2007 11:28 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
>What could I do to fix the problem?
Change the code.
It is NOT an exception when the WHERE returns 0 rows.
Re: Raising exceptions [message #248757 is a reply to message #248754] Sun, 01 July 2007 11:45 Go to previous messageGo to next message
Xeon
Messages: 4
Registered: June 2007
Junior Member
Ok I've made some rewriting and it now works:
BEGIN
DELETE FROM Students WHERE (id = t_student_id);
If (SQL%NOTFOUND) THEN
RAISE not_found;
END IF;
Thanks, now I know a little more of how the SQL%NOTFOUND supposed to work.
Re: Raising exceptions [message #248758 is a reply to message #248754] Sun, 01 July 2007 11:46 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First:
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version (4 decimals).

Then:
why "If SQL%NOTFOUND THEN RAISE not_found;" at the beginning of your procedure? This will raise an error if the statement PRECEDING the procedure call does not return anything.
Place this AFTER the delete statement.

Regards
Michel


Previous Topic: writing text on new lines
Next Topic: "Error" , "Block" merged silly topic titles
Goto Forum:
  


Current Time: Mon Dec 05 15:07:32 CST 2016

Total time taken to generate the page: 0.10177 seconds