2 Replies Latest reply: Mar 2, 2013 1:36 AM by jariola RSS

    using cursor in apex page

    kp2000
      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
          >

          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
            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
            PL/SQL

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