SQL PL/SQL user defined multiple exception error handling (null)

Question

I'm trying to write a stored procedure that will have 2 exception errors.

Create table Employee 
(emp_num varchar(10) primary key, 
emp_name varchar(10),
DOB date,
job_title varchar(15),
marriage_date date,
spouseid varchar(10) references Employee(emp_num),
dept_id varchar(10) references Department(dept_id));

AS you see it's a table that will have employee info. There is a recursive relationship here that says that you need to provide spouse information if spouse is also employee in company (spouseid + marriage_date). I created a stored procedure that asks for employee number and gives you spouseid, name, and marriage date.

CREATE OR REPLACE PROCEDURE DISP_SPOUSE
(SP_EMP_NUM IN EMPLOYEE.EMP_NUM%TYPE) AS
SP_NAME EMPLOYEE.EMP_NAME%TYPE;
SP_SPOUSEID EMPLOYEE.SPOUSEID%TYPE;
SP_MARRIAGE_DATE EMPLOYEE.MARRIAGE_DATE%TYPE;

BEGIN
SELECT SPOUSEID, EMP_NAME, MARRIAGE_DATE
INTO SP_SPOUSEID, SP_NAME, SP_MARRIAGE_DATE
FROM EMPLOYEE
WHERE EMP_NUM = SP_EMP_NUM;

DBMS_OUTPUT.PUT_LINE ((SP_SPOUSEID)||' '||
(SP_NAME)||' '||(SP_MARRIAGE_DATE));
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO EMPLOYEE WITH THAT NUMBER:'||SP_EMP_NUM);

END;
/

I also need to add a second user defined exception that will be raised if there is no spouseid. so i'm thinking something like this:

DECLARE
SP_EXCEPTION EXCEPTION;
PRAGMA EXCEPTION_INIT (SP_EXCEPTION, -20001);
BEGIN
RAISE_APPLICATION_ERROR (-20001, 'NO EMPLOYEE SPOUSE');
EXCEPTION
WHEN SP_EXCEPTION
THEN
DBMS_OUTPUT.PUT_LINE ( SQLERRM );
END;
/

And then add second exception to the stored procedure code (after or before no_data_found exception:

WHEN SPOUSEID IS NULL
RAISE SP_EXCEPTION;

end;
/

I'm having problem on the spouseid = null. is there a different way to write that? (some values that were inserted in spouseid are indeed null).

Answer 1

Nevermind I solved it.

CREATE OR REPLACE PROCEDURE DISP_SPOUSE
(SP_EMP_NUM IN EMPLOYEE.EMP_NUM%TYPE) AS
SP_NAME EMPLOYEE.EMP_NAME%TYPE;
SP_SPOUSEID EMPLOYEE.SPOUSEID%TYPE;
SP_MARRIAGE_DATE EMPLOYEE.MARRIAGE_DATE%TYPE;

SP_EXCEPTION EXCEPTION;


BEGIN

SELECT SPOUSEID, EMP_NAME, MARRIAGE_DATE
INTO SP_SPOUSEID, SP_NAME, SP_MARRIAGE_DATE
FROM EMPLOYEE
WHERE EMP_NUM = SP_EMP_NUM;

IF 
SP_SPOUSEID IS NULL THEN
RAISE SP_EXCEPTION;


ELSE
DBMS_OUTPUT.PUT_LINE ((SP_SPOUSEID)||' '||
(SP_NAME)||' '||(SP_MARRIAGE_DATE));

END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE
('NO EMPLOYEE WITH THAT NUMBER:'||SP_EMP_NUM);
WHEN SP_EXCEPTION THEN
DBMS_OUTPUT.PUT_LINE ('NO SPOUSE AS EMPLOYEE');
END;
/