CODEWITHSUNDEEP
oracle-databaseplsqloracle10g
Ankit
Ankit

Reputation: 433

Error on function in PL/SQL

I tried to create following function:

CREATE OR REPLACE
function CATEGORYTEST(n in number) return VARCHAR(200)
IS
  catname VARCHAR(200);
  BEGIN
  SELECT NAME AS PARENTCAT INTO CATNAME FROM CATEGORY_LANGUAGE WHERE CATEGORY_ID = (
      SELECT PARENT_ID
      FROM CATEGORY
      WHERE ID        = N
      AND CAT_TYPE_ID = 7
      AND IS_ACTIVE   = 1
      AND IS_DELETED  = 0
      )
      AND IS_ACTIVE = 1
      AND IS_DELETED = 0;
   RETURN CATNAME;   
end;

I am getting following error:

Error(2,51): PLS-00103: Encountered the symbol "(" when expecting one of the following:     . @ % ; is authid as cluster order using external character    deterministic parallel_enable pipelined aggregate

I had tried varchar2 also but getting same error.

Upvotes: 0

Views: 1974

Answers (2)

Ollie
Ollie

Reputation: 17538

Try this:

CREATE OR REPLACE 
FUNCTION CATEGORYTEST(
   n IN NUMBER
) 
   RETURN VARCHAR2
IS
   catname VARCHAR2(200);
BEGIN
   SELECT NAME AS PARENTCAT
     INTO CATNAME
     FROM CATEGORY_LANGUAGE
    WHERE CATEGORY_ID = (SELECT PARENT_ID
                           FROM CATEGORY
                          WHERE ID = 'N'
                            AND CAT_TYPE_ID = 7
                            AND IS_ACTIVE   = 1
                            AND IS_DELETED  = 0)
      AND IS_ACTIVE = 1
      AND IS_DELETED = 0;

    RETURN CATNAME;
end CATEGORYTEST;

You needed single quotes around your string literals, VARCHAR changed to VARCHAR2 and some other small changes.

BTW, your catname variable would be better declared as:

catname CATEGORY_LANGUAGE.NAME%TYPE;

You might also want to look into the return type of the function also being flexibly declared e.g.:

RETURN CATEGORY_LANGUAGE.NAME%TYPE

Hope it helps...

Upvotes: 3

user330315
user330315

Reputation:

Use this header:

CREATE OR REPLACE function CATEGORYTEST(n in number) return VARCHAR

(no length specification for the return type)

Upvotes: 2

Related Questions