No rows returned in Oracle causes SP to fail

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:
copyraw
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;
  1.  SELECT 
  2.              NVL(student_accounts.studentID, 0) 
  3.            INTO 
  4.              v_studentreference 
  5.            FROM 
  6.              student_accounts 
  7.            WHERE 
  8.              student_accounts.studentUsername = trim(:p_myinparameter)
  9.   
  10.            SELECT 
  11.              NVL(student_details.studentFirstname, 'John') "FORENAMES", 
  12.              NVL(student_details.studentSurname, 'Smith') "SURNAME" 
  13.            FROM 
  14.              student_details 
  15.            WHERE 
  16.              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:
copyraw
ORA-00936: missing expression
00936. 00000 -  "missing expression"
  1.  ORA-00936: missing expression 
  2.  0093600000 -  "missing expression" 
The solution was to enclose the subquery with an opening and closing parenthesis.
copyraw
-- 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;
  1.  -- Erroring: 
  2.  SELECT NVL( 
  3.            SELECT 
  4.              student_accounts.student_username 
  5.            FROM 
  6.              student_accounts 
  7.            WHERE 
  8.              student_accounts.studentID = trim(:p_myparameter) 
  9.        , '???') 
  10.        FROM DUAL; 
  11.  --> Yields error ORA-00936 
  12.   
  13.  -- Not Erroring: 
  14.  SELECT NVL( 
  15.            (SELECT 
  16.              student_accounts.student_username 
  17.            FROM 
  18.              student_accounts 
  19.            WHERE 
  20.              student_accounts.studentID = trim(:p_myparameter) 
  21.        ), '???') 
  22.        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.
copyraw
-- 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
  1.  -- Error I was getting 
  2.  ORA-20001: 1234567:$:ORA-01403: no data found 
  3.  ORA-06512: at "BASETABLEOWNER.MY_STUPID_PROCEDURE", line 56 
  4.  ORA-01403: no data found 
  5.  ORA-06512: at line 1" error 

Failing part of the stored procedure:
copyraw
-- 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);
  1.  -- the version of the first query which would fail the stored procedure 
  2.   
  3.      SELECT 
  4.        student_accounts.student_id 
  5.      INTO 
  6.        v_STUDENT_REF 
  7.      FROM 
  8.        student_accounts 
  9.      WHERE 
  10.        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:
copyraw
-- 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
  1.  -- the same query but which doesn't fail the procedure :) 
  2.      SELECT 
  3.        NVL( 
  4.        ( 
  5.         SELECT 
  6.          student_accounts.student_id 
  7.         FROM 
  8.              student_accounts 
  9.         WHERE 
  10.              student_accounts.student_username = trim(p_mystupidparameter) 
  11.        ),0 
  12.        ) 
  13.      INTO 
  14.        v_STUDENT_REF 
  15.      FROM 
  16.        dual 
  17.      WHERE 
  18.        rownum 

Other Searches
  • NO ROWS returned in subqueries problem
  • oracle no rows returned in subquery
Category: Oracle PL/SQL :: Article: 412

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.