CREATE SEQUENCE S1 SESSION;

SQL> CREATE TABLE test_1 (id NUMBER);

Table created.

SQL>
SQL> CREATE OR REPLACE FUNCTION f_max_1
  2     RETURN NUMBER
  3  AS
  4     PRAGMA AUTONOMOUS_TRANSACTION;
  5     l_next   NUMBER;
  6  BEGIN
  7     UPDATE test_1
  8        SET id = NVL (id, 0) + 1;
  9
 10     IF SQL%NOTFOUND
 11     THEN
 12        INSERT INTO test_1 (id)
 13             VALUES (1);
 14     END IF;
 15
 16     COMMIT;
 17
 18     SELECT id INTO l_next FROM test_1;
 19
 20     RETURN l_next;
 21  END;
 22  /

Function created.

SQL>
SQL> SELECT f_max_1 FROM DUAL;

   F_MAX_1
----------
         1

SQL> SELECT f_max_1 FROM DUAL;

   F_MAX_1
----------
         2

SQL> SELECT f_max_1 FROM DUAL;

   F_MAX_1
----------
         3

SQL>