That is my code below, I get this error when I try outputing the variable p_user_id.

ORA-01403: no data found ORA-06512: at line 84 ORA-06512: at line 84 ORA-06512: at "SYS.DBMS_SQL", line 1721

I don't know if I'm properly implementing the Select statement. How do I write the select statement to select ID from the users table where email is equal to the value of p_payment_email?

Below is my code:

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;


   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：

To me, it looks as if you'd want to handle exceptions. NO_DATA_FOUND is what you hit so - handle that, explicitly. You can handle others (using WHEN OTHERS) so that procedure keeps going or - if you want - let Oracle raise an error and you'll handle it differently.

This is just the offending piece of code, which contains an inner BEGIN-EXCEPTION-END block ("inner" means that it is within the FOR loop):

  for ...
    ...
    p_payment_email := c.payment_email;

    begin
      select id
        into p_user_id
        from users 
        where email = p_payment_email;

      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);

    exception
      when no_data_found then
        -- It means that SELECT ID INTO P_USER_ID didn't return anything.
        -- Log the error, somehow - the simplest way is to just display it.
        -- Doing so, you'll let the LOOP continue. Without error handline 
        -- section, as soon as there's an error, the procedure stops.
        dbms_output.put_line('No rows for ' || p_payment_email);

      when others then
        dbms_output.put_line('Unexpected error for ' || p_payment_email ||': '|| sqlerrm);
    end;
  end loop;
end;