This discussion is archived
2 Replies Latest reply: Mar 1, 2013 11:36 PM by jariola RSS

using cursor in apex page

kp2000 Newbie
Currently Being Moderated
i have a simple cursor fetch data from the DB table.
parameter is page item parameter.

for example,

cursor emp_cur is
select first_name,last_name
from emp
where emp_id=:F100_emp_id;


for i in emp_cur
LOOP
loc_var:=i.first_name||i.last_name;
END LOOP;

I am passing the loc_var and calling the send email process via APEX API.

But when i use this api, i get only the latest value, not all values.

For eg;

tom jones
lilly jones
mary joe

I get only 'mary joe' not other values.[tom jones and lilly jones]

why cursor is fetching ONLY LAST value.
plesae advice


kp
  • 1. Re: using cursor in apex page
    fac586 Guru
    Currently Being Moderated
    >

    Please update your forum profile with a real handle instead of "user8612301".
    i have a simple cursor fetch data from the DB table.
    parameter is page item parameter.

    for example,
    Always post code using <tt>\
    ...\
    </tt> tags as described in the FAQ.
     cursor emp_cur is
    select first_name,last_name 
    from emp
    where emp_id=:F100_emp_id;
    
    
    for i in emp_cur
    LOOP
    loc_var:=i.first_name||i.last_name;
    END LOOP;
    I am passing the loc_var and calling the send email process via APEX API.

    But when i use this api, i get only the latest value, not all values.

    For eg;

    tom jones
    lilly jones
    mary joe

    I get only 'mary joe' not other values.[tom jones and lilly jones]

    why cursor is fetching ONLY LAST value.
    The cursor is coded to fetch rows where <tt>emp_id = :F100_emp_id</tt>. If <tt>emp_id</tt> is a primary key or unique identifier then this will only return one row. Even if the cursor did return more than one row, your code overwrites every value but the last one. It doesn't concatenate the latest value to the previous ones:
    loc_var:=i.first_name||i.last_name;
    Then there's the issue that <tt>i.first_name||i.last_name</tt> will not generate valid email addresses for use by the <tt>apex_mail.send</tt> API...

    Should have something like this:
    declare
    
       recipients varchar2(4000);
    
    begin
    
      for employee in (select email from emp)
      loop
        recipients := recipients || employee.email || ',';
      end loop;
    
      if recipients is not null
      then
        apex_mail.send(
            p_to => rtrim(recipients), ',')
          , ... );
      end if;
    
    end;
  • 2. Re: using cursor in apex page
    jariola Guru
    Currently Being Moderated
    Hi,

    Cursor fetch all values.
    In loop you assign values from cursor to variable. Variable holds only last assigned value.
    Try something like
    cursor emp_cur is
    select first_name,last_name
    from emp
    where emp_id=:F100_emp_id;
    
    for i in emp_cur
    LOOP
      loc_var := loc_var || ',' || i.first_name||i.last_name;
    END LOOP;
    LTRIM(loc_var,',');
    PS: your question is more PL/SQL related question and not directly related to Application Express.
    You can find PL/SQL forum from below link
    SQL and PL/SQL

    Regards,
    Jari
    -----
    My Blog: http://dbswh.webhop.net/htmldb/f?p=BLOG:HOME:0
    Twitter: http://www.twitter.com/jariolai

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points