is there any expression error in this oracle function code?

    CREATE OR REPLACE FUNCTION id_generator(userType VARCHAR2) RETURN NUMBER IS
    new_id NUMBER;
BEGIN
    SELECT
        CASE
            WHEN userType = 'original' THEN original_user
            ELSE guest_user
        END
    INTO new_id
    FROM next_pk
    WHERE ROWNUM = 1;

    FOR UPDATE OF original_user, guest_user;

    IF userType = 'original' THEN
        UPDATE next_pk SET original = original + 256;
    ELSIF userType = 'guest' THEN
        UPDATE next_pk SET guest = guest + 256;
    END IF;

    COMMIT;

    RETURN new_id;
END id_generator;

it is simple oracle function that returns value of original_user column or guest_user's value depending on input value "userType". And this function also updates column value into value + 256

I used "select for update" because I want nobody can read the row handled by the function.

But I'm facing compilation error at "CASE" line constantly. Since I'm not familiar with plsql, I'm not figuring out what is the problem. What can be the problem??

Solution

Right; there are some issues in code you posted, such as

for update clause is lost in space
function should be autonomous transaction (as you'd get error if you try to use it in select statement which can't commit)
it is unclear which columns are contained in next_pk table (4 of them? 2 of them? What do they contain)? How many rows are there (should be only one, in my opinion)?

Here's example which works; see if it helps.

SQL> CREATE TABLE next_pk
  2  (
  3     original   NUMBER,
  4     guest      NUMBER
  5  );

Table created.

Initial empty row (just to avoid exception handling in function):

SQL> INSERT INTO next_pk (original, guest)
  2       VALUES (NULL, NULL);

1 row created.

SQL> COMMIT;

Commit complete.

Function itself:

SQL> CREATE OR REPLACE FUNCTION id_generator (usertype IN VARCHAR2)
  2     RETURN NUMBER
  3  IS
  4     PRAGMA AUTONOMOUS_TRANSACTION;
  5     new_id  NUMBER;
  6  BEGIN
  7     LOCK TABLE next_pk IN EXCLUSIVE MODE;
  8
  9     IF usertype = 'original'
 10     THEN
 11        SELECT NVL (original, 1) + 256 INTO new_id FROM next_pk;
 12
 13        UPDATE next_pk
 14           SET original = new_id;
 15     ELSIF usertype = 'guest'
 16     THEN
 17        SELECT NVL (guest, 1) + 256 INTO new_id FROM next_pk;
 18
 19        UPDATE next_pk
 20           SET guest = new_id;
 21     END IF;
 22
 23     COMMIT;
 24     RETURN new_id;
 25  END;
 26  /

Function created.

Testing:

SQL> SELECT * FROM next_pk;

  ORIGINAL      GUEST
---------- ----------


SQL> SELECT id_generator ('original') FROM DUAL;

ID_GENERATOR('ORIGINAL')
------------------------
                     257

SQL> SELECT * FROM next_pk;

  ORIGINAL      GUEST
---------- ----------
       257

SQL> SELECT id_generator ('original') FROM DUAL;

ID_GENERATOR('ORIGINAL')
------------------------
                     513

SQL> SELECT * FROM next_pk;

  ORIGINAL      GUEST
---------- ----------
       513

SQL> SELECT id_generator ('guest') FROM DUAL;

ID_GENERATOR('GUEST')
---------------------
                  257

SQL> SELECT * FROM next_pk;

  ORIGINAL      GUEST
---------- ----------
       513        257

SQL>

If I were you, I'd suggest you (not you personally, but company you work for) accept Erich's suggestion and switch to identity column (if your database version supports it) or sequence as it seems that you're just creating numeric values which doesn't mean anything.

If you had to separate next_id values per year or sub-organization or something like that, it might make sense to use your own function. Apart from that, most probably not.