what's wrong with this simple function ?

[Raj Ohadi]

DECLARE
FUNCTION GET(firstName IN STUDENT.firstName%TYPE) return STUDENT%ROWTYPE
IS
record STUDENT%ROWTYPE;
BEGIN
select * into record from STUDENT where STUDENT.firstName = firstName;
return record;
END;

It keeps complains for the last line..... I expected it to return multiple rows since there are many studetns with same first names. How should I modify this function to get multiple rows with same first names ?

[Chris Hendy]

If you are creating a stored function then you need

CREATE OR REPLACE FUNCTION GET(firstName IN STUDENT.firstName%TYPE) return STUDENT%ROWTYPE
IS
record STUDENT%ROWTYPE;
BEGIN 
select * into record from STUDENT where STUDENT.firstName = firstName;
return record;
END;
/

You don't need the DECLARE key word.

If it is a local function in an anonymous pl/sql block then you need a BEGIN and END to go with the DECLARE

DECLARE
  FUNCTION GET(firstName IN STUDENT.firstName%TYPE) return STUDENT%ROWTYPE
  IS
    record STUDENT%ROWTYPE;
  BEGIN 
    select * into record from STUDENT where STUDENT.firstName = firstName;
    return record;
  END;
BEGIN
 
  -- Program code including call to GET
  null;
 
END;
/