No rows returned in Oracle causes SP to fail

: Joel Lipman; Oracle PL/SQL; 24 January 2012; Hits: 11166

Why?
We have a stored procedure which contains two select queries. The first query will retrieve a student ID number where the input parameter is the student's username. The second query will return data using the student ID number found in the first query. The stored procedure compiles successfully and without any warnings.

So What?
The problem is that if the student does not have a username but has an ID number, then the first query returns NO ROWS and then the second query errors and the whole stored procedure fails. The same problem happens when using these as a subquery which has an empty result set.

Furthermore
I am using this stored procedure in a SQL Server Reporting Services (SSRS) environment and don't want to return any rows if there are no matches (not a blank row either) as one of my reports uses a row counter to display a "No data found" message when no rows are returned. This does not affect the above problem as the stored procedure simply fails and discontinues processing the overall report.

How?
Browsing the web for a solution, most suggest using NVL() but this only replaces a NULL value with a string of your choice; and even if you NVL all returned fields, no rows are returned, and not a row of NULL/blank/empty values. Sounds confusing? That's just me, the answer was using two NVL functions which allowed the stored procedure to return no rows without erroring (ie. where rownum/rowcount = 0).

Consider the following:

copy raw

SELECT
            NVL(student_accounts.studentID, 0)
          INTO
            v_studentreference
          FROM
            student_accounts
          WHERE
            student_accounts.studentUsername = trim(:p_myinparameter);

          SELECT
            NVL(student_details.studentFirstname, 'John') "FORENAMES",
            NVL(student_details.studentSurname, 'Smith') "SURNAME"
          FROM
            student_details
          WHERE
            student_details.studentID = v_studentreference;

SELECT
NVL(student_accounts.studentID, 0)
INTO
v_studentreference
FROM
student_accounts
WHERE
student_accounts.studentUsername = trim(:p_myinparameter);
SELECT
NVL(student_details.studentFirstname, 'John') "FORENAMES",
NVL(student_details.studentSurname, 'Smith') "SURNAME"
FROM
student_details
WHERE
student_details.studentID = v_studentreference;

This errors when the student does not have a username. No rows are returned for the first query and a zero isn't inserted.

Note:
Running just one of the queries will work as normal with the NVL function. Running the second based on the first will error this procedure.

One Issue
I just kept getting this error over and over again:

copy raw

ORA-00936: missing expression
00936. 00000 -  "missing expression"

ORA-00936: missing expression
00936. 00000 - "missing expression"

The solution was to enclose the subquery with an opening and closing parenthesis.

copy raw

-- Erroring:
SELECT NVL(          
          SELECT
            student_accounts.student_username
          FROM
            student_accounts
          WHERE
            student_accounts.studentID = trim(:p_myparameter)
      , '???')
      FROM DUAL;
--> Yields error ORA-00936

-- Not Erroring:
SELECT NVL(         
          (SELECT
            student_accounts.student_username
          FROM
            student_accounts
          WHERE
            student_accounts.studentID = trim(:p_myparameter)
      ), '???')
      FROM DUAL;

-- Erroring:
SELECT NVL(
SELECT
student_accounts.student_username
FROM
student_accounts
WHERE
student_accounts.studentID = trim(:p_myparameter)
, '???')
FROM DUAL;
--> Yields error ORA-00936
-- Not Erroring:
SELECT NVL(
(SELECT
student_accounts.student_username
FROM
student_accounts
WHERE
student_accounts.studentID = trim(:p_myparameter)
), '???')
FROM DUAL;

The other issue
The "other" issue was the problem after all. The first query was erroring despite the stored procedure compiling successfully. An empty dataset is still an error it would seem.

copy raw

-- Error I was getting
ORA-20001: 1234567:$:ORA-01403: no data found
ORA-06512: at "BASETABLEOWNER.MY_STUPID_PROCEDURE", line 56
ORA-01403: no data found
ORA-06512: at line 1" error

-- Error I was getting
ORA-20001: 1234567:$:ORA-01403: no data found
ORA-06512: at "BASETABLEOWNER.MY_STUPID_PROCEDURE", line 56
ORA-01403: no data found
ORA-06512: at line 1" error

Failing part of the stored procedure:

copy raw

-- the version of the first query which would fail the stored procedure

    SELECT
      student_accounts.student_id
    INTO
      v_STUDENT_REF
    FROM
      student_accounts
    WHERE
      student_accounts.student_username = trim(p_mystupidparameter);

-- the version of the first query which would fail the stored procedure
SELECT
student_accounts.student_id
INTO
v_STUDENT_REF
FROM
student_accounts
WHERE
student_accounts.student_username = trim(p_mystupidparameter);

Rectified part of the stored procedure:
The solution in the end was to null the first query and make sure it didn't return the errors "no data found" and send this to the second query:

copy raw

-- the same query but which doesn't fail the procedure :)
    SELECT
      NVL(
	  (
	   SELECT 
		student_accounts.student_id 
	   FROM 
	        student_accounts 
	   WHERE 
	        student_accounts.student_username = trim(p_mystupidparameter)
	  ),0
      )
    INTO
      v_STUDENT_REF
    FROM
      dual
    WHERE
      rownum