"No Data found" error when compiling PLSQL Function

Question

When trying to compile in order to execute both functions(@directory) I keep getting a no data error on line 48. (or line 13 for the function). This is the error i receive. NOTE: SYSTEM is the server I'm connected to.

ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SYSTEM.DEPTPROJECT", line 13
ORA-06512: at line 4
ORA-06512: at line 4

these are my function

SET ECHO ON;
SET FEEDBACK ON;
SET LINESIZE 100;
SET PAGESIZE 100;
SET SERVEROUTPUT ON;


CREATE OR REPLACE FUNCTION DEPTPROJECT(departmentNumber IN NUMBER)

RETURN VARCHAR IS
dep# DEPARTMENT.D#%TYPE;
depName DEPARTMENT.DName%TYPE;
counter NUMBER(10,0);
empNumber CHAR(5);
empName VARCHAR2(30);
result VARCHAR2(600);


BEGIN
SELECT D#, DNAME INTO dep#, depName FROM DEPARTMENT WHERE DEPARTMENT.D# = dep#;

result := result || 'Department'|| dep# || '' || depName || chr (10);


FOR i IN(SELECT  P# , PTitle , Budget FROM PROJECT WHERE PROJECT.D# = dep# ORDER BY BUDGET DESC)
LOOP
result:= result || chr(9)|| 'Project: ' || i.P# || '' ||  i.PTitle || i.Budget || chr(10); 

FOR j IN(SELECT  EMPLOYEE.Name, EMPLOYEE.E# FROM WORKSON INNER JOIN EMPLOYEE ON EMPLOYEE.E# = WORKSON.E# WHERE WORKSON.P# = i.P# ORDER BY EMPLOYEE.NAME ASC  )
LOOP
result:= result || chr(10) || j.E# || '' || j.Name || chr(10); 



END LOOP;
END LOOP;
RETURN result;
END;
/

BEGIN 
FOR x IN(SELECT D# FROM DEPARTMENT)
LOOP
DBMS_OUTPUT.PUT_LINE(DEPTPROJECT(x.D#));
END LOOP;
END;
/

Answer 1

When you are selecting INTO a variable and there are no records returned you should get a NO DATA FOUND error. I believe the correct way to write the above code would be to wrap the SELECT statement with it's own BEGIN/EXCEPTION/END block.

Example:

SET ECHO ON;
SET FEEDBACK ON;
SET LINESIZE 100;
SET PAGESIZE 100;
SET SERVEROUTPUT ON;


CREATE OR REPLACE FUNCTION DEPTPROJECT(departmentNumber IN NUMBER)

RETURN VARCHAR IS
dep# DEPARTMENT.D#%TYPE;
depName DEPARTMENT.DName%TYPE;
counter NUMBER(10,0);
empNumber CHAR(5);
empName VARCHAR2(30);
result VARCHAR2(600);


BEGIN
SELECT D#, DNAME INTO dep#, depName FROM DEPARTMENT WHERE DEPARTMENT.D# = departmentNumber;

result := result || 'Department'|| dep# || '' || depName || chr (10);


FOR i IN(SELECT  P# , PTitle , Budget FROM PROJECT WHERE PROJECT.D# = dep# ORDER BY BUDGET DESC)
LOOP
result:= result || chr(9)|| 'Project: ' || i.P# || '' ||  i.PTitle || i.Budget || chr(10); 

FOR j IN(SELECT  EMPLOYEE.Name, EMPLOYEE.E# FROM WORKSON INNER JOIN EMPLOYEE ON EMPLOYEE.E# = WORKSON.E# WHERE WORKSON.P# = i.P# ORDER BY EMPLOYEE.NAME ASC  )
LOOP
result:= result || chr(10) || j.E# || '' || j.Name || chr(10); 

END LOOP;
END LOOP;
EXCEPTION
      WHEN NO_DATA_FOUND THEN
        result := 'Record Not Found';
RETURN result;
END;
/

BEGIN 
FOR x IN(SELECT D# FROM DEPARTMENT)
LOOP
DBMS_OUTPUT.PUT_LINE(DEPTPROJECT(x.D#));
END LOOP;
END;
/

Answer 2

You are using following query to fetch dep# and depName but your where clause is WHERE DEPARTMENT.D# = dep# .

SELECT D#, DNAME INTO dep#, depName FROM DEPARTMENT WHERE DEPARTMENT.D# = dep#;

Here dep# is a variable which you have declared but not initialized.

So dep# is null and comparision to NULL will always lead to false which means your query is returning no record.

As you have used INTO in your query, Oracle is seeking for exactly one record from this query but it is returning no records. Hence, you are facing the issue.

According to your logic either change the WHERE clause to include departmentNumber in comparision or assign proper value to dep# before using it in your query.

Also, make sure to wrap your SELECT .. INTO query in BEGIN..EXCEPTION..END to avoid such a dynamic exception.

Cheers!!

Answer 3

I'd guess that line 13 should use the variable being passed to the function:

SELECT D#, DNAME INTO dep#, depName FROM DEPARTMENT WHERE DEPARTMENT.D# = dep#;

Should be:

SELECT D#, DNAME INTO dep#, depName FROM DEPARTMENT WHERE DEPARTMENT.D# = departmentNumber;