ORA-00904: invalid identifier from PL/SQL code while the SQL part within works

Question

When I defined a PL/SQL function in SQL developer and tried to run it, it returned "ORA-00904: "SYS"."FUNC1": invalid identifier;00904. 00000 - "%s: invalid identifier"", which isn't very helpful. I don't know what has gone wrong. The SQL part alone can run though.

• the PL/SQL that return error

CREATE OR REPLACE FUNCTION func1 (
    emp_id IN NUMBER
) RETURN NUMBER AS
    emp_fname VARCHAR2(50);
BEGIN
    SELECT
        firstname
    INTO emp_fname
    FROM
        employees
    WHERE
        employeeid = emp_id;

    RETURN emp_fname;
END func1;
/

select sys.func1(9) from dual;

• the SQL that run

    SELECT
        firstname
    FROM
        employees
    WHERE
        employeeid = 9;

• Definition of Table Employees

Name            Null?    Type          
--------------- -------- ------------- 
EMPLOYEEID      NOT NULL NUMBER        
LASTNAME        NOT NULL VARCHAR2(20)  
FIRSTNAME       NOT NULL VARCHAR2(10)  
TITLE                    VARCHAR2(30)  
TITLEOFCOURTESY          VARCHAR2(25)  
BIRTHDATE                DATE          
HIREDATE                 DATE          
ADDRESS                  VARCHAR2(60)  
CITY                     VARCHAR2(15)  
REGION                   VARCHAR2(15)  
POSTALCODE               VARCHAR2(10)  
COUNTRY                  VARCHAR2(15)  
HOMEPHONE                VARCHAR2(24)  
EXTENSION                VARCHAR2(4)   
PHOTO                    LONG RAW      
NOTES                    VARCHAR2(600) 
REPORTSTO                NUMBER        
PHOTOPATH                VARCHAR2(255) 

Please help!

---

Test environment:

• OS: Oracle Linux 7.9
• Oracle DB 21c Express Edition for Linux
• SQL Developer for Linux 21.2.1.204 build 204.1703

Answer 1

Don't use SYS as a work area. If you mess with any of the system tables then you may make your database unusable.

Use the SYS user to create a new user and then work in that user's schema.

---

Your function compiles successfully; however, it will fail at runtime (except for those rare people who are known by numbers and not names) as the signature is RETURN NUMBER but it returns the firstname which is a string and you would get the exception:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "FIDDLE_RAHCBFZAHWTUCSZNOWGB.FUNC1", line 14

You can fix it by making the return type the same as the firstname column and the simplest method is to use %TYPE. You also ought to handle a NO_DATA_FOUND exception:

CREATE OR REPLACE FUNCTION func1 (
  emp_id IN EMPLOYEES.EMPLOYEEID%TYPE
) RETURN EMPLOYEES.FIRSTNAME%TYPE
AS
  emp_fname VARCHAR2(50);
BEGIN
  SELECT firstname
  INTO   emp_fname
  FROM   employees
  WHERE  employeeid = emp_id;

  RETURN emp_fname;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END func1;
/

If you have the sample data:

INSERT INTO employees (employeeid, lastname, firstname)
VALUES (9, 'Abbots', 'Alice');

Then:

select func1(9) from dual;

Outputs:

FUNC1(9)
Alice

db<>fiddle here