Raise exception in stored procedure based on view entries

Question

I have a stored procedure. I would like to implement the below logic, which I have written in pseudocode.

If the below query has one of more entries:

SELECT
    NULL
FROM
    table1
WHERE
    condition
GROUP BY
    column
HAVING
    COUNT(1) > 1
UNION ALL
SELECT
    NULL
FROM
    table1 a
WHERE
    condition
    AND EXISTS (
        SELECT
            NULL
        FROM
            table2 b
        WHERE
            condition
    );

Then raise an exception and stop the stored procedure.

Does this answer your question? https://stackoverflow.com/q/67891381/230471 — William Robertson, Jun 11 '21 at 08:22
Hmm. the if exists part might be what I want, but how to raise an exception from there? — banana_99, Jun 11 '21 at 08:23
Have a look at [`raise` and `raise_application_error()`](https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/plsql-error-handling.html#GUID-66346B68-7A18-438C-951B-A60F970D98B8) — William Robertson, Jun 11 '21 at 08:26

Koen Lostrie · Answer 1 · 2021-06-11T12:52:31.973

Let's do this with the sample emp/dept schema - just plug in your own statement for your use case. You do need to declare since in pl/sql you cannot "just select". You always need to select into a variable. I usually just select the number 1 into a dummy variable of type number. The trick is to raise the exception after the SELECT INTO and do nothing on NO_DATA_FOUND.

You can use named exceptions to distinguish different cases but since a no data found will throw an exception you have to do each of the cases in its own block. The cleanest is to handle all named exceptions in the final exception block.

DECLARE
  l_dummy NUMBER;
  king_exists EXCEPTION; 
  dave_exists EXCEPTION; 
BEGIN
  BEGIN
    SELECT 1 INTO l_dummy FROM emp WHERE ename = 'DAVE';
    RAISE dave_exists;
  EXCEPTION WHEN NO_DATA_FOUND THEN
    NULL;
  END;
  BEGIN
    SELECT 1 INTO l_dummy FROM emp WHERE ename = 'KING';
    RAISE king_exists;
  EXCEPTION WHEN NO_DATA_FOUND THEN
    NULL;
  END;
EXCEPTION 
  WHEN dave_exists THEN
    raise_application_error(-20000,'My expection error message');
  WHEN king_exists THEN
    raise_application_error(-20001,'King exists');
END;
/

Thanks for your answer. Would this be the approach if I needed to raise different messages in the exception based on different variables? — banana_99, Jun 11 '21 at 12:15
I'm not sure I understand what you want to achieve. Can you be more precise please ? Based on my example if possible... — Koen Lostrie, Jun 11 '21 at 12:26
I would like to have l_dummy, l_dummy2, l_dummy3 which will be the select 1 into of different queries. I would like to raise the exception with a custom message depending what l_dummy is. — banana_99, Jun 11 '21 at 12:33

score 1 · Accepted Answer · answered Jun 11 '21 at 21:40

1

Here is an example of raising an exception if a particular value is found from a query:

declare
    somevar dual.dummy%type;
begin
    select 'Y' into somevar
    from   dual;

    if somevar = 'Y' then
        raise_application_error(-20123, 'Hull breach on deck 15. Abandon ship.'); 
    end if;
end;

The "select from dual" can be any query, so feel free to substitute your unions and counts (though we should really stick to the standard count(*), not count('Dracula') etc).

answered Jun 11 '21 at 21:40

William Robertson

15,273
4
38
44

I have found in the internet the following lines as well. review_files EXCEPTION; PRAGMA exception_init ( review_files, -20001 ); Do you know if they add anything useful to my necessity? – banana_99 Jun 13 '21 at 16:29
1

That could be useful in a procedure that called this. Say procedure xyz calls `raise_application_error(-20001, 'Something bad happened.');` Now another procedure calls xyz and it wants to catch the ORA-20001. That's when you would use `pragma exception_init`. – William Robertson Jun 14 '21 at 18:46

Raise exception in stored procedure based on view entries

2 Answers2