pl/sql : Error when declaring a cursor in exception section

Question

What is wrong with this function please : i'm getting this error : Encountered the symbol "CFCE" when expecting one of the following:= . ( @ % ; Note : when i declare the cursor in the declare section no error but i need to declare it in the exception section

CREATE OR REPLACE FUNCTION PMISHR.SYS_ENCASH_FC
(
    PRM_POLICY_KEY VARCHAR2,
    PRM_POLDEBTOR_SEQ NUMBER,
    PRM_SYSTEMIND VARCHAR2 DEFAULT 'G',
    PRM_AGTBDX_DATE DATE
)
   RETURN VARCHAR2 IS
   --V_FC_PREMIUM  VARCHAR2(3000 BYTE);
   V_FC_PREMIUM  CLOB :='';
   
BEGIN
    
    IF PRM_SYSTEMIND = 'G' THEN
        BEGIN
            /* AUTO RECONCILIATION */
            
        
            SELECT  AGB_COMP_REF_NO
            INTO    V_FC_PREMIUM
            FROM    CDNOTE_RECONCILE_DATA, CDNOTE, AGENTVOBRX, AGENTBORDEREAUX, GENPOLDEBTOR
            WHERE   CDR_RECONCILE_SOURCE = 'GENPOLICY'
            AND     CDR_RECONCILE_SOURCE = CDN_SOURCE_TABLE
            AND     NVL(CDR_MANUALRECONCILE_IND,'N') = 'N'
            AND     CDN_SOURCE_CDE = POL_POLICY_CDE
            AND     CDN_SOURCE_SEQ = POL_POLDEBTOR_SEQ
            AND     POL_POLICY_CDE = PRM_POLICY_KEY
            AND     POL_POLDEBTOR_SEQ = PRM_POLDEBTOR_SEQ
            AND     CDR_CDNOTE_TYPE = CDNOTE_TYPE
            AND     CDR_CDN_NUMBER_SYSTEM = CDNOTE_NUMBER
            AND     ((CDR_VO_BRX_PREM_CDN_TYPE = AVO_CDNOTE_TYPE AND AVO_CDNOTE_NUMBER = CDR_CDN_NUMBER_VO_BRX)
                      OR (CDR_CDN_NUMBER_VO_BRX IS NULL AND CDR_PREMIUMS_VO_CDE = AVO_PREMIUMSVO_CDE)
                    )
            AND     AVO_AGENTBORDEREAUX_CDE = AGENTBORDEREAUX_KEY
            AND     TRUNC(AGENTBORDEREAUX.A_DATE_TIME_CREATED) <= TRUNC(PRM_AGTBDX_DATE)
            AND     ROWNUM<=1; 
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                /* MANUAL RECONCILIATION */

                CURSOR cFCE(PRM_POLICY_KEY VARCHAR2,PRM_POLDEBTOR_SEQ NUMBER) IS
                SELECT AGB_COMP_REF_NO
                FROM    MANUALPREMIUM_RECONCILE, CDNOTE_RECONCILE_DATA, CDNOTE, AGENTVOBRX, AGENTBORDEREAUX
                WHERE   CDNOTE.CDN_SOURCE_TABLE = 'GENPOLICY'
                AND     CDNOTE.CDN_SOURCE_CDE = PRM_POLICY_KEY
                AND     CDNOTE.CDN_SOURCE_SEQ = PRM_POLDEBTOR_SEQ
                AND     CDR_CDNOTE_TYPE = CDNOTE_TYPE
                AND     CDR_CDN_NUMBER_SYSTEM = CDNOTE_NUMBER
                AND     CDR_RECONCILE_WITH = 'AGENTVOBRX'
                AND     NVL(CDR_MANUALRECONCILE_IND,'N') = 'Y'
                AND     CDR_MANUALRECONCILE_BATCH_CDE = MPR_RECONCILE_BATCH_ID
                AND     CDR_VO_BRX_PREM_CDN_TYPE = AVO_CDNOTE_TYPE
                AND     AVO_CDNOTE_NUMBER = CDR_CDN_NUMBER_VO_BRX
                AND     AVO_AGENTBORDEREAUX_CDE = AGENTBORDEREAUX_KEY
                AND     MPR_CDNOTE_TYPE = CDR_CDNOTE_TYPE
                AND     MPR_CDNOTE_NUMBER = CDR_CDN_NUMBER_SYSTEM
                AND     TRUNC(AGENTBORDEREAUX.A_DATE_TIME_CREATED) <= TRUNC(PRM_AGTBDX_DATE)
                AND     AGB_VALID_IND = 'Y'
;   
rFCE cFCE%ROWTYPE; 
                 OPEN cFCE(PRM_POLICY_KEY,PRM_POLDEBTOR_SEQ);
                 LOOP
                    FETCH cFCE INTO rFCE;
                    EXIT WHEN cFCE%NOTFOUND;
                    V_FC_PREMIUM := V_FC_PREMIUM || rFCE.AGB_COMP_REF_NO || ' ; ';
                 END LOOP;
                 CLOSE cFCE;
                 
                 if length(V_FC_PREMIUM)>4000 then V_FC_PREMIUM:= substr(V_FC_PREMIUM,1,4000); end if;
                 
            END; 
        RETURN V_FC_PREMIUM;

    ELSE
        BEGIN
            /* AUTO RECONCILIATION */
            SELECT  AGB_COMP_REF_NO
            INTO    V_FC_PREMIUM
            FROM    CDNOTE_RECONCILE_DATA, CDNOTE, AGENTVOBRX, AGENTBORDEREAUX, LIFEPOLDEBTOR
            WHERE   CDR_RECONCILE_SOURCE = 'LIFE_POLICY'
            AND     CDR_RECONCILE_SOURCE = CDN_SOURCE_TABLE
            AND     NVL(CDR_MANUALRECONCILE_IND,'N') = 'N'
            AND     CDN_SOURCE_CDE = POL_POLICY_CDE
            AND     CDN_SOURCE_SEQ = POL_POLDEBTOR_SEQ
            AND     POL_POLICY_CDE = PRM_POLICY_KEY
            AND     POL_POLDEBTOR_SEQ = PRM_POLDEBTOR_SEQ
            AND     CDR_CDNOTE_TYPE = CDNOTE_TYPE
            AND     CDR_CDN_NUMBER_SYSTEM = CDNOTE_NUMBER
            AND     CDR_VO_BRX_PREM_CDN_TYPE = AVO_CDNOTE_TYPE
            AND     AVO_CDNOTE_NUMBER = CDR_CDN_NUMBER_VO_BRX
            AND     AVO_AGENTBORDEREAUX_CDE = AGENTBORDEREAUX_KEY
            AND     TRUNC(AGENTBORDEREAUX.A_DATE_TIME_CREATED) <= TRUNC(PRM_AGTBDX_DATE)
            AND     ROWNUM<=1; 
        EXCEPTION
            WHEN NO_DATA_FOUND THEN   
                /* MANUAL RECONCILIATION */
                SELECT  AGB_COMP_REF_NO 
                INTO    V_FC_PREMIUM
                FROM    MANUALPREMIUM_RECONCILE, CDNOTE_RECONCILE_DATA, CDNOTE, AGENTVOBRX, AGENTBORDEREAUX
                WHERE   CDNOTE.CDN_SOURCE_TABLE = 'LIFE_POLICY'
                AND     CDNOTE.CDN_SOURCE_CDE = PRM_POLICY_KEY
                AND     CDNOTE.CDN_SOURCE_SEQ = PRM_POLDEBTOR_SEQ
                AND     CDR_CDNOTE_TYPE = CDNOTE_TYPE
                AND     CDR_CDN_NUMBER_SYSTEM = CDNOTE_NUMBER
                AND     CDR_RECONCILE_WITH = 'AGENTVOBRX'
                AND     NVL(CDR_MANUALRECONCILE_IND,'N') = 'Y'
                AND     CDR_MANUALRECONCILE_BATCH_CDE = MPR_RECONCILE_BATCH_ID
                AND     CDR_VO_BRX_PREM_CDN_TYPE = AVO_CDNOTE_TYPE
                AND     AVO_CDNOTE_NUMBER = CDR_CDN_NUMBER_VO_BRX
                AND     AVO_AGENTBORDEREAUX_CDE = AGENTBORDEREAUX_KEY
                AND     MPR_CDNOTE_TYPE = CDR_CDNOTE_TYPE
                AND     MPR_CDNOTE_NUMBER = CDR_CDN_NUMBER_SYSTEM
                AND     TRUNC(AGENTBORDEREAUX.A_DATE_TIME_CREATED) <= TRUNC(PRM_AGTBDX_DATE)
                AND     AGB_VALID_IND = 'Y'
                AND     ROWNUM<=1;
            END; 
        RETURN V_FC_PREMIUM;
    END IF;
    
END;
/

i'm having this error

Encountered the symbol "CFCE" when expecting one of the following:= . ( @ % ; Note : when i declare the cursor in the first section no problem but for performance issue i need to declare it in the exception section

Answer 1

You're not allowed to declare a cursor within the exception section. you can try declaring the cursor within the declaration section and then use it by fetching in the exception section.

Answer 2

I've seen - by reading comments - that you found a workaround (the listagg function; note that it won't work if resulting string is longer than 4000 characters).

As of another comments/answer saying that you can not declare cursor in exception handling section - well, that's not true. You can, but you have to do it in a supported way, by including the declare keyword.

Here's example; see comments within code.

SQL> create or replace function f_test return varchar2
  2  is
  3    l_val  number;
  4    retval varchar2(10);
  5  begin
  6    retval := 'X';      
  7    -- this will raise ZERO_DIVIDE error
  8    l_val := 1 / 0;
  9    return retval;  --> function would return 'X', if there weren't for an error
 10
 11  exception
 12    when zero_divide then
 13      declare                 --> this is DECLARE you need
 14        cursor c1 is select dname from dept where rownum = 1;
 15        c1r c1%rowtype;
 16      begin
 17        open c1;
 18        fetch c1 into retval;
 19        close c1;
 20        return retval;
 21      end;
 22  end;
 23  /

Function created.

SQL> select f_test from dual;

F_TEST
--------------------------------------------------------------------------------
ACCOUNTING

SQL>

Answer 3

If you are going to use a CURSOR then it needs to be in the DECLARE section of a PL/SQL block:

(
    PRM_POLICY_KEY VARCHAR2,
    PRM_POLDEBTOR_SEQ NUMBER,
    PRM_SYSTEMIND VARCHAR2 DEFAULT 'G',
    PRM_AGTBDX_DATE DATE
) RETURN VARCHAR2
IS
  V_FC_PREMIUM  VARCHAR2(4000 BYTE);
  V_FC_PREMIUM  CLOB :='';  
BEGIN
  IF PRM_SYSTEMIND = 'G' THEN
    BEGIN
      /* AUTO RECONCILIATION */
      SELECT  AGB_COMP_REF_NO
      INTO    V_FC_PREMIUM
      FROM    CDNOTE_RECONCILE_DATA, CDNOTE, AGENTVOBRX, AGENTBORDEREAUX, GENPOLDEBTOR
      WHERE   CDR_RECONCILE_SOURCE = 'GENPOLICY'
      AND     CDR_RECONCILE_SOURCE = CDN_SOURCE_TABLE
      AND     NVL(CDR_MANUALRECONCILE_IND,'N') = 'N'
      AND     CDN_SOURCE_CDE = POL_POLICY_CDE
      AND     CDN_SOURCE_SEQ = POL_POLDEBTOR_SEQ
      AND     POL_POLICY_CDE = PRM_POLICY_KEY
      AND     POL_POLDEBTOR_SEQ = PRM_POLDEBTOR_SEQ
      AND     CDR_CDNOTE_TYPE = CDNOTE_TYPE
      AND     CDR_CDN_NUMBER_SYSTEM = CDNOTE_NUMBER
      AND     ((CDR_VO_BRX_PREM_CDN_TYPE = AVO_CDNOTE_TYPE AND AVO_CDNOTE_NUMBER = CDR_CDN_NUMBER_VO_BRX)
                OR (CDR_CDN_NUMBER_VO_BRX IS NULL AND CDR_PREMIUMS_VO_CDE = AVO_PREMIUMSVO_CDE)
              )
      AND     AVO_AGENTBORDEREAUX_CDE = AGENTBORDEREAUX_KEY
      AND     TRUNC(AGENTBORDEREAUX.A_DATE_TIME_CREATED) <= TRUNC(PRM_AGTBDX_DATE)
      AND     ROWNUM<=1; 
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        /* MANUAL RECONCILIATION */
        DECLARE
          CURSOR cFCE(PRM_POLICY_KEY VARCHAR2,PRM_POLDEBTOR_SEQ NUMBER) IS
          SELECT AGB_COMP_REF_NO
          FROM    MANUALPREMIUM_RECONCILE, CDNOTE_RECONCILE_DATA, CDNOTE, AGENTVOBRX, AGENTBORDEREAUX
          WHERE   CDNOTE.CDN_SOURCE_TABLE = 'GENPOLICY'
          AND     CDNOTE.CDN_SOURCE_CDE = PRM_POLICY_KEY
          AND     CDNOTE.CDN_SOURCE_SEQ = PRM_POLDEBTOR_SEQ
          AND     CDR_CDNOTE_TYPE = CDNOTE_TYPE
          AND     CDR_CDN_NUMBER_SYSTEM = CDNOTE_NUMBER
          AND     CDR_RECONCILE_WITH = 'AGENTVOBRX'
          AND     NVL(CDR_MANUALRECONCILE_IND,'N') = 'Y'
          AND     CDR_MANUALRECONCILE_BATCH_CDE = MPR_RECONCILE_BATCH_ID
          AND     CDR_VO_BRX_PREM_CDN_TYPE = AVO_CDNOTE_TYPE
          AND     AVO_CDNOTE_NUMBER = CDR_CDN_NUMBER_VO_BRX
          AND     AVO_AGENTBORDEREAUX_CDE = AGENTBORDEREAUX_KEY
          AND     MPR_CDNOTE_TYPE = CDR_CDNOTE_TYPE
          AND     MPR_CDNOTE_NUMBER = CDR_CDN_NUMBER_SYSTEM
          AND     TRUNC(AGENTBORDEREAUX.A_DATE_TIME_CREATED) <= TRUNC(PRM_AGTBDX_DATE)
          AND     AGB_VALID_IND = 'Y';   

          rFCE cFCE%ROWTYPE; 
        BEGIN
          OPEN cFCE(PRM_POLICY_KEY,PRM_POLDEBTOR_SEQ);
          LOOP
            FETCH cFCE INTO rFCE;
            EXIT WHEN cFCE%NOTFOUND;
            EXIT WHEN LENGTH(V_FC_PREMIUM) + LENGTH(rFCE.AGB_COMP_REF_NO) + 3 > 4000;
            IF V_FC_PREMIUM IS NULL THEN
              V_FC_PREMIUM := rFCE.AGB_COMP_REF_NO;
            ELSE
              V_FC_PREMIUM := V_FC_PREMIUM || ' ; ' || rFCE.AGB_COMP_REF_NO;
            END IF;
          END LOOP;
          CLOSE cFCE;
        END; 
    END; 
    RETURN V_FC_PREMIUM;
  ELSE
    BEGIN
      /* AUTO RECONCILIATION */
      SELECT  AGB_COMP_REF_NO
      INTO    V_FC_PREMIUM
      FROM    CDNOTE_RECONCILE_DATA, CDNOTE, AGENTVOBRX, AGENTBORDEREAUX, LIFEPOLDEBTOR
      WHERE   CDR_RECONCILE_SOURCE = 'LIFE_POLICY'
      AND     CDR_RECONCILE_SOURCE = CDN_SOURCE_TABLE
      AND     NVL(CDR_MANUALRECONCILE_IND,'N') = 'N'
      AND     CDN_SOURCE_CDE = POL_POLICY_CDE
      AND     CDN_SOURCE_SEQ = POL_POLDEBTOR_SEQ
      AND     POL_POLICY_CDE = PRM_POLICY_KEY
      AND     POL_POLDEBTOR_SEQ = PRM_POLDEBTOR_SEQ
      AND     CDR_CDNOTE_TYPE = CDNOTE_TYPE
      AND     CDR_CDN_NUMBER_SYSTEM = CDNOTE_NUMBER
      AND     CDR_VO_BRX_PREM_CDN_TYPE = AVO_CDNOTE_TYPE
      AND     AVO_CDNOTE_NUMBER = CDR_CDN_NUMBER_VO_BRX
      AND     AVO_AGENTBORDEREAUX_CDE = AGENTBORDEREAUX_KEY
      AND     TRUNC(AGENTBORDEREAUX.A_DATE_TIME_CREATED) <= TRUNC(PRM_AGTBDX_DATE)
      AND     ROWNUM<=1; 
    EXCEPTION
      WHEN NO_DATA_FOUND THEN   
        /* MANUAL RECONCILIATION */
        SELECT  AGB_COMP_REF_NO 
        INTO    V_FC_PREMIUM
        FROM    MANUALPREMIUM_RECONCILE, CDNOTE_RECONCILE_DATA, CDNOTE, AGENTVOBRX, AGENTBORDEREAUX
        WHERE   CDNOTE.CDN_SOURCE_TABLE = 'LIFE_POLICY'
        AND     CDNOTE.CDN_SOURCE_CDE = PRM_POLICY_KEY
        AND     CDNOTE.CDN_SOURCE_SEQ = PRM_POLDEBTOR_SEQ
        AND     CDR_CDNOTE_TYPE = CDNOTE_TYPE
        AND     CDR_CDN_NUMBER_SYSTEM = CDNOTE_NUMBER
        AND     CDR_RECONCILE_WITH = 'AGENTVOBRX'
        AND     NVL(CDR_MANUALRECONCILE_IND,'N') = 'Y'
        AND     CDR_MANUALRECONCILE_BATCH_CDE = MPR_RECONCILE_BATCH_ID
        AND     CDR_VO_BRX_PREM_CDN_TYPE = AVO_CDNOTE_TYPE
        AND     AVO_CDNOTE_NUMBER = CDR_CDN_NUMBER_VO_BRX
        AND     AVO_AGENTBORDEREAUX_CDE = AGENTBORDEREAUX_KEY
        AND     MPR_CDNOTE_TYPE = CDR_CDNOTE_TYPE
        AND     MPR_CDNOTE_NUMBER = CDR_CDN_NUMBER_SYSTEM
        AND     TRUNC(AGENTBORDEREAUX.A_DATE_TIME_CREATED) <= TRUNC(PRM_AGTBDX_DATE)
        AND     AGB_VALID_IND = 'Y'
        AND     ROWNUM<=1;
    END; 
    RETURN V_FC_PREMIUM;
  END IF;
END;
/

Note: This is untested as we do not have access to your tables.

Note 2: It helps if you indent your code in a consistent manner; otherwise it becomes difficult to read and you cannot easily tell which branch of the program you are following.

Note 3: You try to concatenate strings and then check if it has exceeded 4000 bytes; this will never occur as when you try to concatenate and it exceeds 4000 bytes then an exception will be raised and you will never get to the check condition. Instead check first and then concatenate.

Answer 4

Thank you all for you answers. I used the function LISTAGG and it resolved my issue