PLS-00103 Error at end of function

Question

Receiving the following error relating to a function:

'ERROR at line 20: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: end not pragma final instantiable order overriding static member constructor map 0.01 seconds

CREATE OR REPLACE FUNCTION Function1
RETURN FLOAT
IS 
    PricePerBug FLOAT,
    NumberOfBugs NUMBER,
    TotalIncome FLOAT;
BEGIN
    SELECT SUM(ProjectValue) INTO TotalIncome FROM tblProject;
    SELECT COUNT(idBug) INTO NumberOfBugs FROM tblBug;
    PricePerBug := (NumberOfBugs)/(TotalIncome);
    RETURN PricePerBug;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No data found, no result to display; exception handled...');
    WHEN TOO_MANY_ROWS THEN
    RETURN 'Too many rows returned...';
    WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20015, 'Unknown exception in function Function1.');
END Function1;
/

Any suggestions appreciated...

Answer 1

The declarations should all end in a semi-colon rather than a comma:

PricePerBug FLOAT;
NumberOfBugs NUMBER;
TotalIncome FLOAT;

There are other issues, unrelated to this error, concerning exception handling:

1. If NO_DATA_FOUND were ever raised (it won't be) you would get the error "ORA-06503: PL/SQL: Function returned without value" because you handle the exception but do not return a value.

2. If TOO_MANY_ROWS were ever raised (it won't be) you would get an error because you can't return the text 'Too many rows returned...' from a function that returns only values of type FLOAT!

3. If any other exception were ever raised (it could be) you won't know what it is because you replace the useful Oracle exception with your own non-useful "unknown exception" message.

The correct amount of error handling for this particular function is none, i.e.:

CREATE OR REPLACE FUNCTION Function1
RETURN FLOAT
IS 
    PricePerBug FLOAT;
    NumberOfBugs NUMBER;
    TotalIncome FLOAT;
BEGIN
    SELECT SUM(ProjectValue) INTO TotalIncome FROM tblProject;
    SELECT COUNT(idBug) INTO NumberOfBugs FROM tblBug;
    PricePerBug := (NumberOfBugs)/(TotalIncome);
    RETURN PricePerBug;
END Function1;