How to raise an exception when value is NULL

Question

How can I raise an exception if loadid is null when I compare loadid = V_LOAD_ID?

E_NULL_ID is the exception which I then wanted to use.

Here is my procedure:

CREATE OR REPLACE PROCEDURE GET_INFO(P_CURDATE DATE)
AS
    V_LOAD_ID CARSALES.LOADID%TYPE;
    E_NULL_ID EXCEPTION;
BEGIN
    -- Get load id from LoadIds table for current date.
    SELECT LoadId
    INTO V_LOAD_ID
    FROM LoadIds
    WHERE DateLoad = P_CURDATE;
    -- Select brand name and total sales.
    SELECT BrandName,  SUM(Cost) 
    FROM CarSales Sales INNER JOIN 
        CarLables Lables ON Lables.CarBrandId = Sales.CarBrandId
    WHERE LoadId = V_LOAD_ID
    GROUP BY CarBrandId
EXCEPTION
    WHEN E_NULL_ID THEN
        DMBS.OUTPUT.PUT_LINE('No loadId found');
END;

See [this question](https://stackoverflow.com/questions/6020450/oracle-pl-sql-raise-user-defined-exception-with-custom-sqlerrm) about raising custom exceptions. — Chris Hep, Mar 21 '18 at 17:57
Possible duplicate of [Oracle PL/SQL - Raise User-Defined Exception With Custom SQLERRM](https://stackoverflow.com/questions/6020450/oracle-pl-sql-raise-user-defined-exception-with-custom-sqlerrm) — Chris Hep, Mar 21 '18 at 17:58

score 0 · Answer 1 · answered Mar 21 '18 at 17:57

gosh there are a lot of ways to do that here is one way, using raise_application_error, maybe it is not how you want to do it it will throw an exception for sure but maybe you want to do something when it errors out

you might want to check if you get no rows (or too many rows) from your select by wrapping it in a begin-exception-end block?

CREATE OR REPLACE PROCEDURE GET_INFO(P_CURDATE DATE)
AS
    V_LOAD_ID CARSALES.LOADID%TYPE;
    E_NULL_ID EXCEPTION;
BEGIN
    -- Get load id from LoadIds table for current date.
    SELECT LoadId
    INTO V_LOAD_ID
    FROM LoadIds
    WHERE DateLoad = P_CURDATE;

    IF (V_LOAD_ID IS NULL) THEN
       RAISE_APPLICATION_ERROR(-20001,'V_LOAD_ID is null');
    END IF;

    -- Select brand name and total sales.

        SELECT BrandName,  SUM(Cost) 
-- you need an into here?

        FROM CarSales Sales INNER JOIN 
            CarLables Lables ON Lables.CarBrandId = Sales.CarBrandId
    WHERE LoadId = V_LOAD_ID
    GROUP BY CarBrandId;

--EXCEPTION
--    WHEN E_NULL_ID THEN
--        DMBS.OUTPUT.PUT_LINE('No loadId found');
END;

I don't need exception in the first select, `V_LOAD_ID` can't be `NULL`. I need it in the second one, where I compare `loadId = V_LOAD_ID` and raise it if `loadId` is `NULL` — music_junkie, Mar 21 '18 at 18:04

wolfrevokcats · Accepted Answer · 2018-03-21T18:26:59.447

First, your select statement is missing into clause, it should be something like:

SELECT BrandName,  SUM(Cost)
into l_brand, l_sum
FROM CarSales Sales INNER JOIN 
    CarLables Lables ON Lables.CarBrandId = Sales.CarBrandId
WHERE LoadId = V_LOAD_ID;

Second, comparisons to null in Oracle always evaluate to FALSE, so the only way to detect that some of the LoadId is null is to check for null explicitly:

declare
   ...
   l_nulls number;
begin
   ...
   SELECT count(*) into l_nulls
   FROM CarSales Sales INNER JOIN 
       CarLables Lables ON Lables.CarBrandId = Sales.CarBrandId
   WHERE LoadId IS NULL;
   if l_nulls > 0 then
      raise E_NULL_ID;
   end if;
   ...
end;

How to raise an exception when value is NULL

2 Answers2