Understanding Cursor For Loop statements

Question

I have been searching for some answers on how to get a function like this working. To be honest, the information I've found has a minimum level of understanding that is clearly beyond mine, and I'm hoping someone can help me understand.

I have a query that returns multiple concatenated rows:

select
           V_FEEDBACK_FORM.TEAM_ID, MANAGER_USER, listagg(REF_NUMBER, '</ br>') within group (order by REF_NUMBER) as "REF_NUMBER"
        from
           V_FEEDBACK_FORM
        left join
           (select distinct TEAM_ID, MANAGER_USER from V_AUTH_INFO) "V_AUTH_INFO" on V_FEEDBACK_FORM.TEAM_ID = V_AUTH_INFO.TEAM_ID
        where
           trunc(UPDATED_ON) < trunc(SYSDATE) - 7
        group by
           V_FEEDBACK_FORM.TEAM_ID, MANAGER_USER;

And I trying to write a loop that searches through these results, selects the result of "TEAM_ID" and e-mails the relevant manager the concatenated string.

If anyone can help point me in the direct I need to be looking in, I do believe it's a Cursor For Loop but I just don't understand how to build that query, and I have been reading everything online for several hours now.

Cheers.

Not clear what do yo uneed but if you are looking For Loop usage then read this link docs.oracle.com/cd/E11882_01/appdev.112/e25519/… — XING
– XING, Commented Oct 26, 2017 at 12:08
Also from where you get the manager email id, This is missing from your question — XING
– XING, Commented Oct 26, 2017 at 12:24
I'll have a look into that link, thank you very much. The e-mail I can pull from somewhere else. I was unclear on that, so I do apologise. All I need is a loop to run, pull the results per row and run a procedure, per row. And close when there are no rows returned. — Rhenderson91
– Rhenderson91, Commented Oct 26, 2017 at 12:35

kfinity · Accepted Answer · 2017-10-26 12:46:36Z

3

I usually prefer implicit cursor loops unless there's a need to use explicit cursors - they're efficient and the syntax is easy.

begin
  for r in 
  (select
       V_FEEDBACK_FORM.TEAM_ID, MANAGER_USER, 
       listagg(REF_NUMBER, '</ br>') within group (order by REF_NUMBER) as "REF_NUMBER"
    from V_FEEDBACK_FORM
    left join
       (select distinct TEAM_ID, MANAGER_USER from V_AUTH_INFO) "V_AUTH_INFO" on V_FEEDBACK_FORM.TEAM_ID = V_AUTH_INFO.TEAM_ID
    where trunc(UPDATED_ON) < trunc(SYSDATE) - 7
    group by V_FEEDBACK_FORM.TEAM_ID, MANAGER_USER)
  )
  loop
    -- put your code here to send an email for each row
    dbms_output.put_line(r.manager_user);
    dbms_output.put_line(r.team_id || ' ' || r."REF_NUMBER");
  end loop;
end;
/

Oh, and if you haven't sent emails before, I think people usually start with UTL_SMTP and write their own simple "send_email" procedures. Let us know if you need help with that.

answered Oct 26, 2017 at 12:46

kfinity

9,1411 gold badge15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rhenderson91 Over a year ago

This is exactly what I was looking for, thank you! Not only that, it's simple and easy enough for me to understand, so thank you very much :)

Collectives™ on Stack Overflow

Understanding Cursor For Loop statements

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related