Function returns null instead of expected value

Question

When I call my function for a value that does not exist in the table:

SELECT pacjent_usun_PK_ERROR(5) from dual

(PESEL='5' does not exist in my table)

The function returns NULL.

But when I pass a valid value:

SELECT pacjent_usun_PK_ERROR(1) from dual

(PESEL='1' exists in my table)

The function returns 1.

Here is my function:

CREATE OR REPLACE FUNCTION pacjent_usun_PK_ERROR
    ( PES IN NUMBER )
    RETURN NUMBER
IS
    ILE NUMBER;
    ZMIENNA NUMBER;
BEGIN
    SELECT PACJENTID INTO ZMIENNA FROM PACJENT WHERE PESEL=PES;

    SELECT COUNT(PRZYJECIEID) INTO ILE FROM PRZYJECIE_NA_ODDZIAL
    WHERE  PACJENTID=ZMIENNA and rownum=1;

    RETURN ILE;
END;

When I test my last SELECT from my function, with manual inserted PRZYJECIEID (PACJENTID='1111' does not exist in my table)

SELECT COUNT(PRZYJECIEID) FROM PRZYJECIE_NA_ODDZIAL WHERE PACJENTID='1111' and rownum=1;

Result is: 0

and testing with PACJENTID='1' (exist in my table)

Result is: 1

I am trying to understand why the function returns NULL instead of 0 when there are no rows.

I am using Oracle SQL Developer

Please tag appropriately. MySQL and Oracle are different, with different syntax. — Sloan Thrasher
– Sloan Thrasher, Commented May 1, 2017 at 22:41

score 3 · Accepted Answer · 2017-05-02 00:10:59Z

I don't know what causes that behavior; if I can figure it out I will post again.

In any case, the following version should work. I created a similar function using the tables EMP and DEPT in the standard (Oracle) SCOTT schema, and the change I show below worked in that setting. Essentially I eliminate the intermediate variable; I do everything in a single query and assignment.

create or replace FUNCTION pacjent_usun_PK_ERROR
(PES IN NUMBER)
RETURN NUMBER
IS
  ILE NUMBER;
BEGIN
  SELECT COUNT(PRZYJECIEID) INTO ILE 
  FROM   PRZYJECIE_NA_ODDZIAL
  WHERE  PACJENTID=(SELECT PACJENTID FROM PACJENT WHERE PESEL=PES) 
     and rownum=1;
RETURN ILE;
end;

Added: To clarify... if select into... returns no rows, PL/SQL should raise an exception, specifically the NO_DATA_FOUND exception. The mystery that is worth investigating is why PL/SQL does not raise this exception in this case.

Final edit - it appears this has always been the behavior in PL/SQL functions, as opposed to PL/SQL procedures. NO_DATA_FOUND is an exception (it can be handled in the usual manner) but it is not raised. See this old discussion on AskTOM: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:10321465390114

This means that when the first select into returns no data, the function is in an unhandled exception state. This is why the second select into doesn't result in a count of 0 - execution doesn't even get that far.

This also means that you may keep your function code exactly as it is, but you need to add an exception handling block:

exception
  when no_data_found then return 0;

right before end;

I tested this on my "mock-up" of your function (in the SCOTT schema) and it works.

Collectives™ on Stack Overflow

Function returns null instead of expected value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related