Syntax Question for an Exception
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Syntax Question for an Exception

  1. #1
    Join Date
    Nov 2001
    Posts
    118

    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

  2. #2
    Join Date
    Nov 2001
    Posts
    118
    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 09:47 AM.

  3. #3
    Join Date
    Nov 2001
    Posts
    118
    No need to bother, just solved it.

    Bye
    Peer

  4. #4
    Join Date
    Jun 2006
    Posts
    259
    Don't perform the select.

    Just do the delete.
    Let it raise the exception No_DATA_FOUND. Then handle that exception.

  5. #5
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    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
  •  


Click Here to Expand Forum to Full Width