CODEWITHSUNDEEP
plsqloracle11g
mungaih pk
mungaih pk

Reputation: 1911

oracle pl sql function having errors

I want to create a function that returns the number of rows in a table called Rating with a where clause.Where am i going wrong before the declare statement and the end statement?

 create or replace
FUNCTION get_movies(user IN NUMBER) RETURN NUMBER
IS
  DECLARE cnt NUMBER;
BEGIN
SELECT count(*)
INTO cnt 
FROM rating
where userid= user;
RETURN cnt;
END;

I will appreciate help.Thanks.

Upvotes: 0

Views: 52

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

You should not have the DECLARE keyword. You only need that for an anonymous block (or a sub-block).

create or replace
FUNCTION get_movies(p_userid IN NUMBER) RETURN NUMBER
IS
  cnt NUMBER;
BEGIN
  ...

user is a reserved word so I'd suggest not using that as your parameter name. In the where clause I'm not sure if it will use your parameter value, or the name of the user executing the function; which would error as that string value couldn't be implicitly converted to a number.

Upvotes: 3

Related Questions