-
Syntax Question for an Exception
Hi Everyone,
I'm writing a procedure that deletes a SINGLE row in a table based on two column data values given as parameters for that procedure (these two columns have a combined unique constraint).
It works, but I'm stuck on this: This procedure should raise an exception if the combination of these two parameter values doesn't exist in the table, thereby rejecting the delete with an error message.
Basically, how do I check in the IF / END IF block for the exception to be raised, whether the combination of two values in any data row already exists?
I can only think of doing a select for these two values in the IF / END IF block for the exception check, but the procedure seems to reject 'SELECT' in this context.
Help would be greatly appreciated.
Thanks
Peer
-
This might clarify my problem:
CREATE OR REPLACE PROCEDURE DeletePermission ( --deletes row with the two values below
p_recht IN VARCHAR2, --(combined unique constraint)
p_kontext IN VARCHAR2) --(combined unique constraint)
IS
-----------------------------------------------
-- [Any values here?];
non_existent_value EXCEPTION;
BEGIN
-----------------------------------------------
-- What do I do here to check if the combination doesn't exist?
IF [value combination already exists] THEN
RAISE non_existent_vaues;
END IF;
-----------------------------------------------
DELETE FROM pj_recht WHERE
(p_recht = recht and p_kontext = kontext);
COMMIT; --this works
EXCEPTION --this is the output I need
WHEN non_existent_values THEN
DBMS_OUTPUT.PUT_LINE
('ERROR: Combination of Recht and Kontext does not exist. Delete rejected!');
END;
Last edited by Peer Jones; 06-02-2006 at 08:47 AM.
-
No need to bother, just solved it.
Bye
Peer
-
Don't perform the select.
Just do the delete.
Let it raise the exception No_DATA_FOUND. Then handle that exception.
-
An INSERT, UPDATE or DELETE that finds no rows does not raise a NO_DATA_FOUND exception. You would need to check explicitly for SQL%ROWCOUNT = 0, or SQL%NOTFOUND.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|