Raise an error if a subquery returns no result-CodePudding

In DB2 it is possible to raise an error if a subquery returns no results with the help of coalesce and the raise_error scalar function. But how can this be done in oracle?

I checked scalar functions so far, but there is no raise error function.

example for db2 (note: the subquery needs to be replaced by something meaningful...):

SELECT 
    COALESCE(
        (SELECT 1 FROM SYSIBM.SYSDUMMY1 LIMIT 0), 
        RAISE_ERROR('70NUL', 'Value is missing')) 
FROM 
    SYSIBM.SYSDUMMY1;

UPDATE 1:

The use case is about finding a matching value in another table during an export of millions of records. The idea is to raise an error if there is no matching value so it is detected early and not afterwards.

CodePudding user response：

The way you describe it it sounds as if you need to mark a number of records based on whenever some values exist in a table. If PLSQL is an option you can just use the no_data_found exception:

invalid_data CONSTANT NUMBER := -70;
    
   DECLARE
     l_var NUMBER;
   BEGIN
     SELECT 1
       INTO l_var
       FROM dual
      WHERE 1 <> 1;
 
   EXCEPTION WHEN no_data_found THEN
     /* do your updating or error handling */  RAISE_APPLICATION_ERROR(invalid_data ,'Value is missing');
   END;

CodePudding user response：

You can use RAISE_APPLICATION_ERROR feature of oracle to resolve your problem. You can declare a procedure and define when to raise the exception in that procedure. Simply call the procedure to execute your task and it will raise the error whenever your given condition staisfies.

For knowing in details, please see the trapping user defined exceptions section of oracle documentation with example.

CodePudding user response：

In Oracle, you'd use similar query:

SQL> select coalesce((select dummy from dual where 1 = 2), 'Value is missing') result from dual;

RESULT
----------------
Value is missing

SQL> select coalesce((select dummy from dual where 1 = 1), 'Value is missing') result from dual;

RESULT
----------------
X

SQL>

I don't speak MS SQL Server so I can't comment it.

CodePudding user response：

Alternative to @Gnqz solution is to write a user function wrapper around raise_application_error() in conjunction with coalesce.

    CREATE OR REPLACE FUNCTION raise_error 
    RETURN NUMBER
    IS
    BEGIN 
        raise_application_error(-70, 'Mapping value is missing');
        RETURN(0); 
    END;

    select coalesce((select 5 AS result from dual fetch first 1 row only), raise_error()) AS result from dual;