error in all 3 exception block of pl/sql

Question

CREATE OR REPLACE 
PROCEDURE   "p*_d******"
(V_Day IN CHAR, v_Pk_List IN CHAR, 
                v_Log OUT VARCHAR2, v_Msg OUT VARCHAR2)

is 
--v_Pk_List       VARCHAR2(50); 
v_DSQL1        VARCHAR2(2000);
v_DSQL2        VARCHAR2(2000);
V_COUNT        NUMBER(10);
V_Today_Day    VARCHAR2(20);
V_START_TS     TIMESTAMP(2);
v_Errormsg     VARCHAR2(100);
V_PK           NUMBER(10);

CURSOR pk_cur IS 
SELECT   *
FROM   (    SELECT   TRIM (REGEXP_SUBSTR (num_csv,
                                            '[^,]+',
                                            1,
                                            LEVEL))
                                          num_value
            FROM   (    SELECT   v_Pk_List num_csv FROM DUAL)
            CONNECT BY   LEVEL <= regexp_count (num_csv, ',', 1) + 1)
 WHERE   num_value IS NOT NULL;

BEGIN

SELECT TO_CHAR(SYSTIMESTAMP, 'DD-MON-YY HH:MI:SS AM') INTO V_START_TS FROM DUAL;

    IF

    NOT pk_cur%ISOPEN THEN 
        OPEN pk_cur; 
    END IF; 
 BEGIN    
  LOOP 

     FETCH pk_cur INTO V_PK; 
     EXIT WHEN pk_cur%NOTFOUND; 

     SELECT COUNT(*) 
     INTO V_COUNT 
     FROM 
     ***_***_****** 
     WHERE 
     RUN_MODE =V_DAY
     AND PK_ID=V_PK;
  IF 
     V_COUNT<=0 THEN 

EXECUTE IMMEDIATE 'INSERT INTO ***_***_******
           (
            fact********,
            PK_ID,
            pr**_d*****,
            RUN_MODE,
            CREATE_DATE
           )
            SELECT fact********,
            PK_ID,
            DUEDATE,
            '''||V_DAY||''',
            SYSTIMESTAMP
            FROM ***_***_*********
            WHERE PK_ID ='''||V_PK||'''';


  v_Errormsg :='INSERT SUCCESSFULL FOR';--||V_DAY||'AND PK_ID'||V_PK;

  EXECUTE IMMEDIATE 'INSERT INTO u**_s*****_e***_l**(MODULENAME,PROCEDURENAME,PKID,MODELID,LEVELID,COL_1,COL_2,ERRORMSG,ERRORTRACE,START_TS,END_TS)
                           VALUES(''EMEA_PROCESS'',
                           ''p*_d******'',
                           '''||v_Pk_List||''',
                           NULL,
                           NULL,
                           ''SUCCESS'',
                           '''||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||''',
                           NULL,
                           NULL,
                           '''||V_START_TS||''',
                           SYSTIMESTAMP)';
  BEGIN                          
  EXCEPTION WHEN OTHERS THEN 
  v_Errormsg := 'ERROR: p*_d****** PROCEDURE INSERT ERROR' ;--|| 'ERROR_MSG IS '||SQLERRM;
  EXECUTE IMMEDIATE 'INSERT INTO u**_s*****_e***_l**(MODULENAME,PROCEDURENAME,PKID,MODELID,LEVELID,COL_1,COL_2,ERRORMSG,ERRORTRACE,START_TS,END_TS)
                           VALUES(''????'',
                           ''p*_d******'',
                           '''||v_Pk_ID||''',
                           NULL,
                           NULL,
                           '''||v_Errormsg||''',
                           '''||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||''',
                           NULL,
                           NULL,
                           '''||V_START_TS||''',
                           SYSTIMESTAMP)';
        END;                   
  END IF;


EXECUTE IMMEDIATE 'MERGE INTO ***_***_********* GFO
            USING 
            ***_***_****** UFD 
            ON (GFO.fact********=UFD.fact********)
            WHEN MATCHED THEN 
            UPDATE 
            SET 
            GFO.pr**_d*****=UFD.pr**_d*****
            WHERE UFD.CREATE_DATE IS NOT NULL
            AND UFD.RUN_MODE= '''||V_DAY||'''
            AND GFO.PK_ID='''||V_PK||'''';

  v_Errormsg :='pr**_d***** OF GFO UPDATED FOR ';--||V_DAY||' AND PK_ID '||V_PK;
   EXECUTE IMMEDIATE 'INSERT INTO u**_s*****_e***_l**(MODULENAME,PROCEDURENAME,PKID,MODELID,LEVELID,COL_1,COL_2,ERRORMSG,ERRORTRACE,START_TS,END_TS)
                           VALUES(''EMEA_PROCESS'',
                           ''p*_d******'',
                           '''||v_Pk_List||''',
                           NULL,
                           NULL,
                           ''SUCCESS'',
                           '''||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||''',
                           NULL,
                           NULL,
                           '''||V_START_TS||''',
                           SYSTIMESTAMP)';

  BEGIN        
  EXCEPTION WHEN OTHERS THEN
  v_Errormsg := 'ERROR: p*_d****** PROCEDURE INSERT ERROR';-- || 'ERROR_MSG IS '||SQLERRM;
  EXECUTE IMMEDIATE 'INSERT INTO u**_s*****_e***_l**(MODULENAME,PROCEDURENAME,PKID,MODELID,LEVELID,COL_1,COL_2,ERRORMSG,ERRORTRACE,START_TS,END_TS)
                           VALUES(''????'',
                           ''p*_d******'',
                           '''||v_Pk_ID||''',
                           NULL,
                           NULL,
                           '''||v_Errormsg||''',
                           '''||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||''',
                           NULL,
                           NULL,
                           '''||V_START_TS||''',
                           SYSTIMESTAMP)';
  END;

EXECUTE IMMEDIATE 'MERGE INTO ***_***_****** UFD
            USING
            ***_***_********* GFO
            ON (UFD.fact********=GFO.fact********)
            WHEN MATCHED THEN 
            UPDATE
            SET
            UFD.pr**_d*****=GFO.DUEDATE
            WHERE
            GFO.PK_ID='''||V_PK||'''';

  v_Errormsg :='pr**_d***** OF UFD BACKED UP IN GFO FOR ';--||V_DAY||' AND PK_ID '||V_PK;
  EXECUTE IMMEDIATE 'INSERT INTO u**_s*****_e***_l**(MODULENAME,PROCEDURENAME,PKID,MODELID,LEVELID,COL_1,COL_2,ERRORMSG,ERRORTRACE,START_TS,END_TS)
                           VALUES(''EMEA_PROCESS'',
                           ''p*_d******'',
                           '''||v_Pk_List||''',
                           NULL,
                           NULL,
                           ''SUCCESS'',
                           '''||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||''',
                           NULL,
                           NULL,
                           '''||V_START_TS||''',
                           SYSTIMESTAMP)';

  BEGIN
  EXCEPTION WHEN OTHERS THEN
  v_Errormsg := 'ERROR: p*_d****** PROCEDURE INSERT ERROR';--||'ERROR_MSG IS '||SQLERRM;
   EXECUTE IMMEDIATE 'INSERT INTO u**_s*****_e***_l**(MODULENAME,PROCEDURENAME,PKID,MODELID,LEVELID,COL_1,COL_2,ERRORMSG,ERRORTRACE,START_TS,END_TS)
                           VALUES(''????'',
                           ''p*_d******'',
                           '''||v_Pk_ID||''',
                           NULL,
                           NULL,
                           '''||v_Errormsg||''',
                           '''||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||''',
                           NULL,
                           NULL,
                           '''||V_START_TS||''',
                           SYSTIMESTAMP)';
    END;
    END LOOP; 
  END;
  END p*_d******;

I am getting following error for all the 3 EXCEPTION blocks:

Error(100,3): PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following: ( begin case declare 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

Answer 1

There's quite a lot wrong with your code. The immediate cause of your compilation error is that your anonymous blocks are not correctly structured: there must be some code between the BEGIN and the EXCEPTION keywords. You need to rethink your exception handling, perhaps by defining blocks with executed code and bespoke error handling.

For instance, this recasting of the final section of code puts two statements, with bespoke error messages, in one anonymous block with its own Exception section:

BEGIN
    v_Errormsg := 'ERROR: p*_d****** PROCEDURE INSERT ERROR';--||'ERROR_MSG IS '||SQLERRM;
    EXECUTE IMMEDIATE 'MERGE INTO ***_***_****** UFD
            USING
            ....';
    v_Errormsg :='pr**_d***** OF UFD BACKED UP IN GFO FOR ';--||V_DAY||' AND PK_ID '||V_PK;
    EXECUTE IMMEDIATE 'INSERT INTO u**_s*****_e***_l**(MODULENAME,PROCEDURENAME,PKID,MODELID,LEVELID,COL_1,COL_2,ERRORMSG,ERRORTRACE,START_TS,END_TS)
                           VALUES(''EMEA_PROCESS'',
            ....';
EXCEPTION 
    WHEN OTHERS THEN
        EXECUTE IMMEDIATE 'INSERT INTO u**_s*****_e***_l**(MODULENAME,PROCEDURENAME,PKID,MODELID,LEVELID,COL_1,COL_2,ERRORMSG,ERRORTRACE,START_TS,END_TS)
                           VALUES(''????'',
        ...
END;

It will compile (probably, I'm not going to try to re-create your environment to test it). But this only addresses only part of the problem.

Your procedure has a lot of duplication, and that's always a bad sign. We should design a procedures to re-use common functionality instead of relying on cut'n'paste.

But the most troubling bit is the use of dynamic SQL. It is really unnecessary. We can use PL/SQL variables in normal SQL. Dynamic SQL is very hard to get right, because any compilation bugs become runtime bugs. We should only use dynamic SQL if there is no alternative.