Unable to compile function script

General Discussion
1

Unable to compile below function script through liquibase getting invalid statement error.

–liquibase formatted sql

–changeset EV_BFN_POST_QUERY_RI_FRM_19:modification-1

create or replace FUNCTION EV_BFN_POST_QUERY_RI_FRM_19 (P_ARRANGE_CODE IN RIMT_POLICY_ARRANGEMENTS.V_ARRANGE_CODE%TYPE,
P_RI_POLICY_NO IN rimt_policy_treaty.V_RI_POLICY_NO%TYPE,
P_FLAG VARCHAR2) RETURN VARCHAR2
IS

CURSOR cr_arrange IS
SELECT * FROM RIMM_TREATY_ARRANGEMENT 
WHERE v_arrange_code = P_ARRANGE_CODE;

CURSOR cr_pol_arr IS 
SELECT  V_PREM_TABLE_ID
FROM rimt_policy_arrangements 
WHERE v_arrange_code = P_ARRANGE_CODE;

CURSOR CR_RIMT_FAC IS 
SELECT v_normal_fac FROM rimt_policy_treaty WHERE V_RI_POLICY_NO=P_RI_POLICY_NO;

lv_normal_fac		VARCHAR2(10);
lv_fac_override	VARCHAR2(10);

LV_RI_PREM_TABLE_ID VARCHAR2(30);
LV_RI_COMM_TABLE_ID VARCHAR2(30);
LV_RI_STD_PREM_FACT_TABLE_ID VARCHAR2(30);
LV_RI_OTHER_PREM_FACT_TABLE_ID VARCHAR2(30);

BEGIN

OPEN CR_RIMT_FAC;
FETCH CR_RIMT_FAC INTO lv_normal_fac;
CLOSE CR_RIMT_FAC;

IF lv_normal_fac = 'F' THEN
	FOR i IN cr_pol_arr
	LOOP
		LV_RI_PREM_TABLE_ID:= i.V_PREM_TABLE_ID;	
		LV_RI_COMM_TABLE_ID := NULL;	
		LV_RI_STD_PREM_FACT_TABLE_ID := NULL;	
		LV_RI_OTHER_PREM_FACT_TABLE_ID := NULL;

  END LOOP;
ELSE		
	FOR i IN cr_arrange
	LOOP
		LV_RI_PREM_TABLE_ID := i.V_RI_PREM_TABLE_ID;	
		LV_RI_COMM_TABLE_ID := i.V_RI_COMM_TABLE_ID;	
		LV_RI_STD_PREM_FACT_TABLE_ID := i.V_RI_STD_PREM_FACT_TABLE_ID;	
		LV_RI_OTHER_PREM_FACT_TABLE_ID := i.V_RI_OTHER_PREM_FACT_TABLE_ID;			
	END LOOP;
END IF;

IF P_FLAG=‘P’ THEN

RETURN LV_RI_PREM_TABLE_ID;

ELSIF P_FLAG=‘C’ THEN

RETURN LV_RI_COMM_TABLE_ID;

ELSIF P_FLAG=‘S’ THEN

RETURN LV_RI_STD_PREM_FACT_TABLE_ID;

ELSIF P_FLAG=‘O’ THEN

RETURN LV_RI_OTHER_PREM_FACT_TABLE_ID;

ELSE

RETURN NULL;

END IF;

END;

2

You need to set endDelimiter on your changeset since semicolon is the default delimiter, and you code has many semicolons. Choose a character that is not in your code.

--liquibase formatted sql
--changeset BOB:create_function endDelimiter:/ runOnChange:true stripComments:false
    create or replace function test_func return date is
      v_dummy1 date;
      v_dummy2 date;
    begin
      --This is select 1
      select sysdate into v_dummy1 from dual;
      --This is select 2
      select sysdate-1 into v_dummy2 from dual;

      return v_dummy2;

    end test_func;
    /  
--rollback drop function test_func;