SQL%FOUND where SELECT query returns no rows

Question

I have the following function, which returns the next available client ID from the Client table:

CREATE OR REPLACE FUNCTION getNextClientID RETURN INT AS
ctr INT;

BEGIN
SELECT MAX(NUM) INTO ctr FROM Client;

IF SQL%NOTFOUND THEN
    RETURN 1;
ELSIF SQL%FOUND THEN
    -- RETURN SQL%ROWCOUNT;
    RAISE_APPLICATION_ERROR(-20010, 'ROWS FOUND!');
    -- RETURN ctr + 1;
END IF; 

END;

But when calling this function,

BEGIN 
DBMS_OUTPUT.PUT_LINE(getNextClientID());

END;

I get the following result:

which I found a bit odd, since the Client table contains no data:

Also, if I comment out RAISE_APPLICATION_ERROR(-20010, 'ROWS FOUND!'); & log the value of SQL%ROWCOUNT to the console, I get 1 as a result.

On the other hand, when changing

SELECT MAX(NUM) INTO ctr FROM Client;

to

SELECT NUM INTO ctr FROM Client;

The execution went as expected. What is the reason behind this behavior ?

Answer 1

Aggregate functions will always return a result:

All aggregate functions except COUNT(*), GROUPING, and GROUPING_ID ignore nulls. You can use the NVL function in the argument to an aggregate function to substitute a value for a null. COUNT and REGR_COUNT never return null, but return either a number or zero. For all the remaining aggregate functions, if the data set contains no rows, or contains only rows with nulls as arguments to the aggregate function, then the function returns null.

You can change your query to:

SELECT COALESCE(MAX(num), 1) INTO ctr FROM Client;

and remove the conditionals altogether. Be careful about concurrency issues though if you do not use SELECT FOR UPDATE.

Answer 2

Query with any aggregate function and without GROUP BY clause always returns 1 row. If you want no_data_found exception on empty table, add GROUP BY clause or remove max:

SQL> create table t (id number, client_id number);

Table created.

SQL> select nvl(max(id), 0) from t;

NVL(MAX(ID),0)
--------------
         0

SQL> select nvl(max(id), 0) from t group by client_id;

no rows selected

Usually queries like yours (with max and without group by) are used to avoid no_data_found.

Answer 3

Agregate functions like MAX will always return a row. It will return one row with a null value if no row is found.

By the way SELECT NUM INTO ctr FROM Client; will raise an exception where there's more than one row in the table.

You should instead check whether or not ctr is null.

Answer 4

Others have already explained the reason why your code isn't "working", so I'm not going to be doing that.

You seem to be instituting an identity column of some description yourself, probably in order to support a surrogate key. Doing this yourself is dangerous and could cause large issues in your application.

You don't need to implement identity columns yourself. From Oracle 12c onwards Oracle has native support for identity columns, these are implemented using sequences, which are available in 12c and previous versions.

A sequence is a database object that is guaranteed to provide a new, unique, number when called, no matter the number of concurrent sessions requesting values. Your current approach is extremely vulnerable to collision when used by multiple sessions. Imagine 2 sessions simultaneously finding the largest value in the table; they then both add one to this value and try to write this new value back. Only one can be correct.

See How to create id with AUTO_INCREMENT on Oracle?

Basically, if you use a sequence then you don't need any of this code.

---

As a secondary note your statement at the top is incorrect:

I have the following function, which returns the next available client ID from the Client table

Your function returns the maximum ID + 1. If there's a gap in the IDs, i.e. 1, 2, 3, 5 then the "missing" number (4 in this case) will not be returned. A gap can occur for any number of reasons (deletion of a row for example) and does not have a negative impact on your database in any way at all - don't worry about them.