PLS-00103: Encountered the symbol “IF” when expecting one of the following

Question

Below is my function in Oracle:

create or replace
FUNCTION CALCULATEINT
( DebtNo IN VARCHAR2
, JFlag IN VARCHAR2
, FloatingInterestRate IN NUMBER
) RETURN NUMBER IS
AccInt NUMERIC(17,8):=0;
BEGIN
DECLARE 
      PrincipalDue NUMERIC(11,2);
      InterestDue NUMERIC(17,8); 

  IF (JFlag IN ('B', 'C', 'Y')) THEN
   BEGIN
        SELECT
            DEF_JUDG_PRINC_DUE ,
           DEF_JUDG_PRINC_RATE          
       bulk collect into PrincipalDue ,InterestDue 
        FROM
            DANT 
        WHERE
            AND DE_NO = DebtNo ;
      END;
  END IF;
  RETURN AccInt;
END;

I am getting errors below:

1.PLS-00103: Encountered the symbol "IF" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior The symbol "begin" was substituted for "IF" to continue.

2.PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge

A function definition does not require the declare block. You can also not use bulk collect to store data in a scalar variable. You need to store the result of a bulk collect into a collection. — a_horse_with_no_name
I'm guess because of the bulk collect into scalars, like @a_horse_with_no_name said. Also, you have to guarantee that only one row is found. And noticed that you are not setting AccInt. — DBug

Rajorshi Mukherjee Rajorshi Mukherjee · Accepted Answer · 2015-12-08T04:49:28

create or replace
FUNCTION CALCULATEINT
( DebtNo IN VARCHAR2
, JFlag IN VARCHAR2
, FloatingInterestRate IN NUMBER
) RETURN NUMBER IS
AccInt NUMERIC(17,8):=0;
BEGIN
DECLARE 
      PrincipalDue NUMERIC(11,2);
      InterestDue NUMERIC(17,8); 


   BEGIN
    IF (JFlag IN ('B', 'C', 'Y')) THEN
        SELECT
            DEF_JUDG_PRINC_DUE ,
           DEF_JUDG_PRINC_RATE          
       bulk collect into PrincipalDue ,InterestDue 
        FROM
            DANT 
        WHERE
            AND DE_NO = DebtNo ;

  END IF;
  END;
  RETURN AccInt;
END;

Move the if statement from before the anonymous begin to after the begin. I am certain that is the error. Correspondingly you also need to move the end statement after the end if block. Hope this helps.

PLS-00103: Encountered the symbol “IF” when expecting one of the following

1 Answers