Error(31,64): PLS-00103: Encountered the symbol “(” when expecting one of the following

Question

I have below function in oracle,

create or replace FUNCTION CALINTEREST
( DNumber IN VARCHAR2,
  IRate IN NUMBER
) RETURN NUMBER IS
    AInterest NUMERIC(17,8):=0;
    PDue NUMERIC(11,2);
    IDue NUMERIC(17,8);
    INTRate NUMERIC(5,2);
    IntDate DATE;
    IntDATEDIFF NUMERIC(6):=0;
BEGIN
BEGIN
  SELECT
  DBRD_PRI_DUE,
  DBRD_INT_DUE,
  DBRD_INT_RATE,
  DBRD_INT_DATE INTO 
            PDue 
            ,IDue 
            ,INTRate  
            ,IntDate 
        FROM
            DBRD 
        WHERE
            DBRNO = DNumber;     

        IF(INTRate = 999)
    THEN
             INTRate := IRate;
        END IF;     
    IntDATEDIFF:=Cast((Sysdate-IntDate) AS NUMBER(11,0));
        AInterest := IDue + (PDue * INTRate * IntDATEDIFF / 365 / 100);   
   END;
  RETURN AInterest; 
END;

I am getting below two error, Error(31,64): PLS-00103: Encountered the symbol "(" when expecting one of the following: . ) @ % The symbol ")" was substituted for "(" to continue. Error(31,70): PLS-00103: Encountered the symbol ")" when expecting one of the following: . ( * % & = - + ; < / > at in is mod remainder not rem <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset The symbol "(" was substituted for ")" to continue.

Answer 1

Not sure if this will work for you. On my machine the only errors I get on compilation have to do with the table DBRO (because I don't have a table by that name), but that doesn't mean this does the computations you want it to do. Anyway, play with it and write back if you have more questions.

I changed NUMERIC to NUMBER (the correct name of the Oracle data type for numbers), I changed the computation for IntDATEDIFF in a way that will be immediately obvious when you look at it (Trunc changes the time portion of an Oracle DATE to 00:00:00, and taking the arithmetic "minus" between two dates returns the difference as a number of days), and I removed BEGIN and END; for the inner block- there was no need for an inner block.

create or replace FUNCTION CALINTEREST
( DNumber IN VARCHAR2,
  IRate IN NUMBER
) RETURN NUMBER IS
    AInterest NUMBER(17,8):=0;
    PDue NUMBER(11,2);
    IDue NUMBER(17,8);
    INTRate NUMBER(5,2);
    IntDate DATE;
    IntDATEDIFF NUMBER(6):=0;
BEGIN
  SELECT
  DBRD_PRI_DUE,
  DBRD_INT_DUE,
  DBRD_INT_RATE,
  DBRD_INT_DATE INTO
            PDue
            ,IDue
            ,INTRate
            ,IntDate
        FROM
            DBRD
        WHERE
            DBRNO = DNumber;
        IF(INTRate = 999)
    THEN
             INTRate := IRate;
        END IF;
    IntDATEDIFF:=trunc(Sysdate) - trunc(IntDate);
        AInterest := IDue + (PDue * INTRate * IntDATEDIFF / 365 / 100);
  RETURN AInterest;
END;
/

Answer 2

create or replace FUNCTION CALINTEREST(
  DNumber IN VARCHAR2,
  IRate IN NUMBER
) RETURN NUMBER
IS
    PDue NUMERIC(11,2);
    IDue NUMERIC(17,8);
    INTRate NUMERIC(5,2);
    IntDate DATE;
BEGIN
  SELECT DBRD_PRI_DUE, DBRD_INT_DUE, DBRD_INT_RATE, DBRD_INT_DATE
  INTO   PDue,         IDue,         INTRate,       IntDate 
  FROM   DBRD 
  WHERE  DBRNO = DNumber;     

  IF (INTRate = 999) THEN
    INTRate := IRate;
  END IF;     
  RETURN IDue + (PDue * INTRate * TRUNC(SYSDATE - IntDate) / 365 / 100); 
END;
/
SHOW ERRORS;

Or moving the logic into SQL:

create or replace FUNCTION CALINTEREST(
  DNumber IN VARCHAR2,
  IRate IN NUMBER
) RETURN NUMBER
IS
  p_interest NUMBER(17,8);
BEGIN
  SELECT DBRD_INT_DUE
         + ( DBRD_PRI_DUE
             * CASE DBRD_INT_RATE
                    WHEN 999 THEN IRate
                    ELSE DBRD_INT_RATE
                    END
             * TRUNC( SYSDATE - DBRD_INT_DATE )
             / 365
             / 100
           )
  INTO   p_interest 
  FROM   DBRD 
  WHERE  DBRNO = DNumber;     

  RETURN p_interest; 
END;
/
SHOW ERRORS;