Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, MOHANRAJ.

Asked: January 21, 2017 - 8:21 am UTC

Last updated: January 23, 2017 - 12:59 am UTC

Version: Oracle 10g Express Edition

Viewed 1000+ times

You Asked

Hi there, i am newbie for using function please help to solve it
I have tried this code and got error as follow

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

My code :
CREATE OR REPLACE FUNCTION FN_calulate(FromDate DATE)
  RETURN NUMBER(10) 
IS
DECLARE
 MONTH1 number(10);
 YEARS number(10);
 MONTHS NUMBER(10);
 DAYS NUMBER(10);
BEGIN 
YEARS:=TRUNC(MONTHS_BETWEEN(SYSDATE,FromDate)/12);  
MONTHS:=TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,FromDate),12)); 
DAYS:=TRUNC(SYSDATE-ADD_MONTHS(FromDate,TRUNC(MONTHS_BETWEEN(SYSDATE,FromDate)/12)*12+TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,FromDate),12))));

 MONTH1:= (YEARS*12)+MONTHS
    IF DAYS>1 THEN
     MONTH1:=MONTH1+1
    ELSE    
 MONTH1:=MONTH1   
    ENDIF     
RETURN MONTH1;
END;


with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/file/content_EGQD5UP3C77YR95KXCQ0HISUZ.html

and Connor said...

There's a few (simple) fixes to make

1) you dont need a DECLARE (you only need that for anonymous blocks)
2) you need semi-colons after each statement
3) you return a datatype not a precision (ie NUMBER not NUMBER(10))

So I did those changes and here you go

SQL> CREATE OR REPLACE FUNCTION FN_calulate(FromDate DATE)
  2    RETURN NUMBER
  3  IS
  4   MONTH1 number(10);
  5   YEARS number(10);
  6   MONTHS NUMBER(10);
  7   DAYS NUMBER(10);
  8  BEGIN
  9  YEARS:=TRUNC(MONTHS_BETWEEN(SYSDATE,FromDate)/12);
 10  MONTHS:=TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,FromDate),12));
 11  DAYS:=TRUNC(SYSDATE-ADD_MONTHS(FromDate,TRUNC(MONTHS_BETWEEN(SYSDATE,FromDate)/12)*12+TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,FromDate),12))));
 12
 13   MONTH1:= (YEARS*12)+MONTHS;
 14      IF DAYS>1 THEN
 15       MONTH1:=MONTH1+1;
 16      ELSE
 17   MONTH1:=MONTH1   ;
 18      END IF;
 19  RETURN MONTH1;
 20  END;
 21  /

Function created.



Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Solved

MOHANRAJ P, January 23, 2017 - 12:33 am UTC

Thank you so much error was cleared.. i have corrected my mistakes.. :)
Connor McDonald
January 23, 2017 - 12:59 am UTC

glad we could help

Sovled

MOHANRAJ P, January 23, 2017 - 12:35 am UTC

Thank you so much errors got cleared... i have corrected my mistakes..
:)
Connor McDonald
January 23, 2017 - 12:59 am UTC

glad we could help

A reader, June 25, 2018 - 12:08 pm UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library