PL SQL Raise Exception when data found

Question

I've been looking at the predefined Oracle pl/SQL Exceptions. I'm wanting to raise an exception when data is found. I've been able to see the NO_DATA_FOUND exception. But not it's opposite. What would be the best way to do this, or is it not advised to perform this kind of operation.

My Procedure is like the following

PROCEDURE CHECK_ASSIGNED AS

PLACEHOLDER INT;   

BEGIN

SELECT v.id
INTO PLACEHOLDER
FROM vinegar v
WHERE NOT EXISTS (
SELECT 1
FROM home h
WHERE h.sid = v.sid
)

END;
-- when data is found it means that and sid does not exist in the home table
-- should raise exception and stop package

Answer 1

There is no built in error for "data found", because finding data is not considered an error by Oracle. However, it's not too difficult to treat this case as an error in specific cases.

It depends somewhat on what you want to do with the exception. If you want to react to it in some way within the procedure, you can define your own exception, then raise it:

PROCEDURE check_assigned AS
   exception err_data_found;
   placeholder INT;
BEGIN
   SELECT v.id
   INTO   placeholder
   FROM   vinegar v
   WHERE  NOT EXISTS
             (SELECT 1
              FROM   home h
              WHERE  h.sid = v.sid);

   IF SQL%FOUND THEN
      RAISE err_data_found;
   END IF;
EXCEPTION
   WHEN err_data_found THEN
      NULL;
      --do something
END;

On the other hand, if you just want to return the error to the caller, you can use raise_application_error:

PROCEDURE check_assigned AS
   placeholder INT;
BEGIN
   SELECT v.id
   INTO   placeholder
   FROM   vinegar v
   WHERE  NOT EXISTS
             (SELECT 1
              FROM   home h
              WHERE  h.sid = v.sid);

   IF SQL%FOUND THEN
      raise_application_error (-20001,
                               'Data was found in the vinegar table.');
   END IF;
END;

---

If the goal is to raise an error if any row exists and continue otherwise, doing it this way is somewhat perilous, as you risk triggering either NO_DATA_FOUND or TOO_MANY_ROWS. You can get around this by selecting a count as suggested in the comments, but I prefer to use an explicit cursor to avoid those issues.

PROCEDURE check_assigned AS
   placeholder INT;

   CURSOR cur_vinegar_check IS
      SELECT v.id
      INTO   placeholder
      FROM   vinegar v
      WHERE      NOT EXISTS
                    (SELECT 1
                     FROM   home h
                     WHERE  h.sid = v.sid)
             AND ROWNUM = 1;
BEGIN
   OPEN cur_vinegar_check;
   FETCH cur_vinegar_check INTO placeholder;

   IF SQL%FOUND THEN
      raise_application_error (-20001,
                               'Data was found in the vinegar table.');
   END IF;

   CLOSE cur_vinegar_check;
END check_assigned;

You'll notice that I added AND ROWNUM = 1 to the query. This is one of the few times that rownum is predictable in an outer query: all you care about is if a row exists; you don't care which row it is. If there is a possibility that you'll get a lot of rows returned, this can have significant performance benefits.