Cursor with iteration to grab data from last row PL/SQL

February 8, 2022

I have a test script that I’m beggining to play with. I’m getting stuck with something that seems simple.
I want to iterate through rows to fetch data from last row of result set to use only it.

procedure e_test_send
is

cursor get_rec is 
    select 
    id,
    email_from,
    email_to,
    email_cc,
    email_subject,
    email_message
    from test_email_tab;

begin
    for rec_ in get_rec loop
    ifsapp.send_email_api.send_html_email(rec_.email_to,rec_.email_from, rec_.email_subject, rec_.email_message);
    end loop;
    
    end e_test_send;

All I’m trying to do is send an email with a message and to a person from the last row only. This is a sample table that will grow in records. At the minute I have 2 rows of data in it, if I execute this procedure it will send 2 emails which is not the desired action.
I hope this makes sense.

Thanks

>Solution :

Do you know which row is the last row? The one with the MAX(ID) value? If so, then you could base cursor on a straightforward

SELECT id,
       email_from,
       email_to,
       email_cc,
       email_subject,
       email_message
  FROM test_email_tab
 WHERE id = (SELECT MAX (id) FROM test_email_tab)

As it scans the same table twice, its performance will drop as number of rows gets higher and higher. In that case, consider

WITH
   temp
   AS
      (SELECT id,
              email_from,
              email_to,
              email_cc,
              email_subject,
              email_message,
              ROW_NUMBER () OVER (ORDER BY id DESC) rn
         FROM test_email_tab)
SELECT t.id,
       t.email_from,
       t.email_to,
       t.email_cc,
       t.email_subject,
       t.email_message
  FROM temp t
 WHERE t.rn = 1

which does it only once; sorts rows by ID in descending order and returns the one that ranks as the "highest" (i.e. the last).