How do I fix the error : Encountered the symbol "EXCEPTION" when expecting one of the foll-CodePudding

I get this erro when I try to raise an exception when no data is found.

Error at line 99/42: ORA-06550: line 89, column 8: PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following:

( begin case declare end 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 json_exists json_value json_query json_object json_array

BEGIN

DECLARE  
   p_amt                   number;
   p_user_id               number;
   p_reference             varchar2(100);
   p_name                  varchar2(100);
   p_narration             varchar2(100);
   p_payment_date          DATE default sysdate;
   p_net_amt               NUMBER default null;
   p_payment_type_id       number;
   p_transaction_type_id   number;
   p_payment_id            number;
   p_payment_email         varchar2(500);

BEGIN
for c in (
 SELECT
    amount, 
    reference,
    type,
    narration,
    paid_at,
    net_amt,
    payment_type_id,
    transaction_type_id,
    payment_id,
    payment_email 

  INTO
     p_amt,
     p_reference,
     p_name,
     p_narration,
     p_payment_date,
     p_net_amt,
     p_payment_type_id,
     p_transaction_type_id,
     p_payment_id,
     p_payment_email

         FROM pstk_payload p,  JSON_TABLE(
         p.webhook_payload,'$'
         COLUMNS (
             amount varchar2(500) path '$.data.amount',
             reference varchar2(500) path '$.data.reference', 
             type varchar2(500) path '$.data.fees_breakdown.type',
             narration varchar2(500) path '$.data.authorization.narration',
             paid_at DATE path '$.data.paid_at',
             net_amt number path '$.data.fees', 
             payment_type_id varchar2(500) path '$.data.fees',
             transaction_type_id varchar2(500) path '$.data.fees',
             payment_id  varchar2(500) path '$.data.id',
             payment_email  varchar2(500) path '$.data.customer.email'
        )
        ) 
       
         where status = 'done' 
         )

         LOOP


   p_amt := c.amount;
   p_reference := c.reference;
   p_name := c.type;
   p_narration :=  c.narration;
   p_payment_date := c.paid_at;
   p_net_amt :=  c.net_amt;
   p_payment_type_id := c.payment_type_id;
   p_transaction_type_id := c.transaction_type_id;
   p_payment_id := c.payment_id;
   p_payment_email := c.payment_email;

   select
    id
   into
    p_user_id
   from users where email = p_payment_email;

 EXCEPTION
    WHEN NO_DATA_FOUND

    THEN
      NULL;
WHEN OTHERS
    THEN
      RAISE;

   dbms_output.put_line(p_user_id); 

    --update pstk_payload set status = 'done';


    PAYMENT_PKG.add_payment(p_amt, p_user_id, p_reference, p_name, p_narration, p_payment_date, p_net_amt, p_payment_type_id, p_transaction_type_id, p_payment_id);

    END LOOP;
end;

END;

CodePudding user response：

You have:

BEGIN
  DECLARE  
    <some variables>
  BEGIN
    FOR c IN (<some query>)
    LOOP
      <some stuff>
    EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
        NULL;
      WHEN OTHERS
      THEN
        RAISE;
        <some more stuff>
    END LOOP;
  END;
END;

Inside the LOOP there is no BEGIN ... END that matches the exception.

Either:

put a BEGIN ... END inside the loop:

BEGIN
  DECLARE  
    <some variables>
  BEGIN
    FOR c IN (<some query>)
    LOOP
      BEGIN
        <some stuff>
      EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
          NULL;
        WHEN OTHERS
        THEN
          RAISE;
          <some more stuff>
      END;
    END LOOP;
  END;
END;

Or move the exception handling outside the loop:

BEGIN
  DECLARE  
    <some variables>
  BEGIN
    FOR c IN (<some query>)
    LOOP
      <some stuff>
    END LOOP;
  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
      NULL;
    WHEN OTHERS
    THEN
      RAISE;
      <some more stuff>
  END;
END;

Once you fix that then you will get more errors as you cannot have SELECT .. INTO inside a cursor. Also, the <some more stuff> will never be run as you re-raise the exception before it.

Something like:

DECLARE
  p_user_id USERS.ID%TYPE;
BEGIN
  FOR c IN (
    SELECT amount, 
           reference,
           type,
           narration,
           paid_at,
           net_amt,
           payment_type_id,
           transaction_type_id,
           payment_id,
           payment_email 
    FROM   pstk_payload p
           CROSS JOIN JSON_TABLE(
             p.webhook_payload,
             '$'
             COLUMNS (
               amount              varchar2(500) path '$.data.amount',
               reference           varchar2(500) path '$.data.reference', 
               type                varchar2(500) path '$.data.fees_breakdown.type',
               narration           varchar2(500) path '$.data.authorization.narration',
               paid_at             DATE          path '$.data.paid_at',
               net_amt             number        path '$.data.fees', 
               payment_type_id     varchar2(500) path '$.data.fees',
               transaction_type_id varchar2(500) path '$.data.fees',
               payment_id          varchar2(500) path '$.data.id',
               payment_email       varchar2(500) path '$.data.customer.email'
             )
           ) 
           where status = 'done' 
  )
  LOOP
    BEGIN
      select id
      into   p_user_id
      from   users
      where  email = c.payment_email;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        NULL;
      WHEN OTHERS THEN
        RAISE;
    END;
    dbms_output.put_line(p_user_id); 

    --update pstk_payload set status = 'done';

    PAYMENT_PKG.add_payment(
      c.amount,
      p_user_id,
      c.reference,
      c.type,
      c.narration,
      c.paid_at,
      c.net_amt,
      c.payment_type_id,
      c.transaction_type_id,
      c.payment_id
    );
  END LOOP;
END;
/