Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql select loop
pl/sql select loop [message #379470] Tue, 06 January 2009 12:31 Go to next message
decarlomw
Messages: 12
Registered: January 2009
Junior Member
Hi All,

First let me tell you what I am trying to achieve.

I have a package that sends data, inside the package there is a procedure called send_string. It currently loops until there are no more records, then it sends the data off to another program but before that it shows how the data is being sent in a log file. Instead of processing one at a time I want it to process three at a time. If you look below I got it to process three at a time but if there are one or two records it doesn't display the data. The same if there are four it will show the first three but the forth would not be in a new record.

Current way with four records
0106:12:13:09.335|3815703|out_rec.count:4
0106:12:13:09.335|3815703|---> Start mlgw_otl_validation_pkg.send_string <---
0106:12:13:09.336|3815703|EE#:01241|time_building_block_id:-23 |To MSS: VALOTL 0000000000000000000000000 450190000000000000010Y01241
0106:12:13:09.338|3815703|EE#:01241|time_building_block_id:-21 |To MSS: VALOTL 0000000000000000000000000 450190000000000000010Y01241
0106:12:13:09.339|3815703|EE#:01241|time_building_block_id:-19 |To MSS: VALOTL 0000000000000000000000000 450190000000000000010Y01241
0106:12:13:09.339|3815703|EE#:01241|time_building_block_id:-19 |To MSS: VALOTL 0000000000000000000000000 450190000000000000010Y01241
0106:12:13:09.339|3815703|---> End mlgw_otl_validation_pkg.send_string <---
0106:12:13:09.340|3815703|---> End mlgw_otl_validation_pkg.validate_timecard <---


Outcome wanted.

0106:10:53:46.445|3815648|out_rec.count:4
0106:10:53:46.445|3815648|---> Start mlgw_otl_validation_pkg.send_string <---
0106:10:53:46.447|3815648|EE#:02404|time_building_block_id: |To MSS: VALOTL 0000000000000000000000000 450190000000000000010Y02404 VALOTL 0000000000000000000000000 450190000000000000010Y02404 VALOTL 0000000000000000000000000 450190000000000000010Y02404
0106:10:53:46.447|3815648|EE#:02404|time_building_block_id: |To MSS: VALOTL 0000000000000000000000000 450190000000000000010Y02404 VALOTL

I attached the actual package here is the part that I can't get the loop right.
procedure send_string is
x number;
l_head_string       varchar2(100):= '        VALOTL  ';
l_mss_return        varchar2(1000);
l_valotl_string      varchar2(5000);
l_performing_area_val  varchar2(50);
l_fixed_dist     varchar2(1);
l_employee_number    varchar2(10);
--
begin
--
write_log('---> Start mlgw_otl_validation_pkg.send_string <---');
--
x := out_rec.first;
loop
exit when x is null;
    --
    --
    
    
    
 begin
   select substr(hou.name,2,6)
        ,(select decode(nvl(sum(pca.proportion),0),0,'N','Y')
            from pay_cost_allocations_f pca
           where pca.assignment_id = paf.assignment_id
             and l_effective_date between pca.effective_start_date and pca.effective_end_date) proportion
        ,lpad(ppf.employee_number,5,'0')
     into   l_performing_area_val
           ,l_fixed_dist
           ,l_employee_number
     from per_all_assignments_f paf
         ,hr_all_organization_units hou
         ,per_all_people_f ppf
     where  paf.organization_id = hou.organization_id
     and    l_effective_date between paf.effective_start_date and paf.effective_end_date
     and    l_effective_date between ppf.effective_start_date and ppf.effective_end_date
     and    ppf.person_id = paf.person_id
     and    paf.assignment_type = 'E'
     and    paf.primary_flag = 'Y'
     and    paf.person_id = out_rec(x).person_id
   group by substr(hou.name,2,6),  ppf.employee_number, paf.assignment_id;
 exception
    when others then
        l_performing_area_val := '000000';
        l_fixed_dist    := 'N';
        l_employee_number   := '00000';
 end;
 --
 out_rec(x).performing_area   := lpad(l_performing_area_val,6,'0');
 --
    out_rec(x).work_ticket       := upper(nvl(out_rec(x).work_ticket,lpad(' ',8,' ')));
    out_rec(x).repair_order_num  := nvl(out_rec(x).repair_order_num,lpad(0,7,'0'));
    out_rec(x).crew_number   := nvl(out_rec(x).crew_number,lpad(0,4,'0'));
    out_rec(x).equipment_number  := nvl(out_rec(x).equipment_number,lpad(0,4,'0'));
    out_rec(x).charge_code   := nvl(out_rec(x).charge_code,rpad('0',4,'0'));
    out_rec(x).requesting_area  := nvl(out_rec(x).requesting_area,lpad(0,6,'0'));
    out_rec(x).wr_nbr     := upper(nvl(out_rec(x).wr_nbr,rpad(' ',8,' ')));
    out_rec(x).performing_area   := nvl(out_rec(x).performing_area,lpad(0,6,'0'));
    out_rec(x).start_miles   := nvl(out_rec(x).start_miles,lpad(0,6,'0'));
    out_rec(x).ending_miles     := nvl(out_rec(x).ending_miles,lpad(0,6,'0'));
 out_rec(x).legacy_earn_code  := nvl(out_rec(x).legacy_earn_code,'000');
 out_rec(x).woc_job_class  := nvl(out_rec(x).woc_job_class,lpad(' ',6,' '));
 --
 
 l_valotl_string := (l_head_string||
             out_rec(x).work_ticket||
            out_rec(x).repair_order_num||
            out_rec(x).crew_number||
            out_rec(x).equipment_number||
            out_rec(x).charge_code||
            out_rec(x).requesting_area||
            out_rec(x).wr_nbr||
            out_rec(x).performing_area||
            out_rec(x).start_miles||
            out_rec(x).ending_miles||
      out_rec(x).legacy_earn_code||
      l_fixed_dist||
      l_employee_number||
      out_rec(x).woc_job_class
      );
 --
 -- Call MSS Function to validate timecard data
 --
 err_tbl.delete;
 write_log('EE#:'||l_employee_number||'|time_building_block_id:'||x||' |To MSS:'||l_valotl_string);
 --
-- l_mss_return := trim(mgwuser.mlgw_mss.valtimec(l_valotl_string));
 --
 --write_log('EE#:'||l_employee_number||'|time_building_block_id:'||x||' |From MSS:'||l_mss_return);
 --
--  hr_utility.set_location('-----------------------------------'||x,9);
--  hr_utility.set_location('x: '||x,9);
--  hr_utility.set_location('Header: '||l_head_string,9);
--  hr_utility.set_location('out_rec.work_ticket: '||out_rec(x).work_ticket,9);
--  hr_utility.set_location('out_rec.repair_order_num: '||out_rec(x).repair_order_num,9);
--  hr_utility.set_location('out_rec.crew_number: '||out_rec(x).crew_number,9);
--  hr_utility.set_location('out_rec.equipment_number: '||out_rec(x).equipment_number,9);
--  hr_utility.set_location('out_rec.charge_code: '||out_rec(x).charge_code,9);
--  hr_utility.set_location('out_rec.requesting_area: '||out_rec(x).requesting_area,9);
--  hr_utility.set_location('out_rec.wr_nbr: '||replace(out_rec(x).wr_nbr,' ','#'),9);
--  hr_utility.set_location('out_rec.performing_area: '||out_rec(x).performing_area,9);
--  hr_utility.set_location('out_rec.start_miles: '||out_rec(x).start_miles,9);
--  hr_utility.set_location('out_rec.ending_miles: '||out_rec(x).ending_miles,9);
--  hr_utility.set_location('l_mss_return: '||l_mss_return,9);
    --
    if replace(substr(l_mss_return,11,50),' ') is not null then
     err_tbl(1) := trim(substr(l_mss_return,11,50));
    end if;
    --
    if replace(substr(l_mss_return,61,50),' ') is not null then
     err_tbl(2) := trim(substr(l_mss_return,61,50));
    end if;
    --
    if replace(substr(l_mss_return,111,50),' ') is not null then
     err_tbl(3) := trim(substr(l_mss_return,111,50));
    end if;
    --
    if replace(substr(l_mss_return,161,50),' ') is not null then
     err_tbl(4) := trim(substr(l_mss_return,161,50));
    end if;
    --
    if replace(substr(l_mss_return,211,50),' ') is not null then
     err_tbl(5) := trim(substr(l_mss_return,211,50));
    end if;
    --
    --
    if substr(l_mss_return,9,2) = '01' then
        --
  write_log('EE#:'||l_employee_number||'hxc_time_entry_rules_utils_pkg.publish_message:'||err_tbl.count );
     for ers in 1..5 loop
         --
      if err_tbl.exists(ers) and err_tbl(ers) is not null then
       --
       --
        hxc_time_entry_rules_utils_pkg.publish_message
         (p_name                    => 'MLGW'
         ,p_token_name              => 'MSS'
         ,p_token_value             => err_tbl(ers)
         ,p_time_building_block_id  => x
            );
       --
      end if;
     end loop;
     --
 elsif substr(l_mss_return,9,2) != '00' then
     write_log('EE#:'||l_employee_number||'hxc_time_entry_rules_utils_pkg.publish_message:Invalid Response from MSS' );
  hxc_time_entry_rules_utils_pkg.publish_message
   (p_name                    => 'MLGW'
   ,p_token_name              => 'MSS'
   ,p_token_value             => 'Invalid Response from MSS'
   ,p_time_building_block_id  => x
      );
    end if;
    --
 --
 -- Call internal validation
    validate_entries(p_time_building_block_id  => x
                    ,p_person_id               => out_rec(x).person_id
           ,p_effective_date     => l_effective_date
           ,p_element_type_id    => out_rec(x).element_type_id
     );
    --
 x := out_rec.next(x);
end loop;
write_log('---> End mlgw_otl_validation_pkg.send_string <---');
end send_string;
--      


[Mod-edit: Frank added [code]-tags]

[Updated on: Wed, 07 January 2009 02:42] by Moderator

Report message to a moderator

Re: pl/sql select loop [message #379475 is a reply to message #379470] Tue, 06 January 2009 12:47 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
You might get more/better responses if you followed the Posting Guidelines in URL above.
Re: pl/sql select loop [message #379481 is a reply to message #379470] Tue, 06 January 2009 13:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unreadable.

Regards
Michel
Re: pl/sql select loop [message #379485 is a reply to message #379481] Tue, 06 January 2009 14:11 Go to previous messageGo to next message
decarlomw
Messages: 12
Registered: January 2009
Junior Member
Hi All,

First let me tell you what I am trying to achieve.

I have a package that sends data, inside the package there is a procedure called send_string. It currently loops until there are no more records, then it sends the data off to another program but before that it shows how the data is being sent in a log file. Instead of processing one at a time I want it to process three at a time. If you look below I got it to process three at a time but if there are one or two records it doesn't display the data. The same if there are four it will show the first three but the forth would not be in a new record.

Current way with four records
0106:12:13:09.335|3815703|out_rec.count:4
0106:12:13:09.335|3815703|---> Start mlgw_otl_validation_pkg.send_string <---
0106:12:13:09.336|3815703|EE#:01241|time_building_block_id:-23 |To MSS: VALOTL 0000000000000000000000000 450190000000000000010Y01241
0106:12:13:09.338|3815703|EE#:01241|time_building_block_id:-21 |To MSS: VALOTL 0000000000000000000000000 450190000000000000010Y01241
0106:12:13:09.339|3815703|EE#:01241|time_building_block_id:-19 |To MSS: VALOTL 0000000000000000000000000 450190000000000000010Y01241
0106:12:13:09.339|3815703|EE#:01241|time_building_block_id:-19 |To MSS: VALOTL 0000000000000000000000000 450190000000000000010Y01241
0106:12:13:09.339|3815703|---> End mlgw_otl_validation_pkg.send_string <---
0106:12:13:09.340|3815703|---> End mlgw_otl_validation_pkg.validate_timecard <---


Outcome wanted.

0106:10:53:46.445|3815648|out_rec.count:4
0106:10:53:46.445|3815648|---> Start mlgw_otl_validation_pkg.send_string <---
0106:10:53:46.447|3815648|EE#:02404|time_building_block_id: |To MSS: VALOTL 0000000000000000000000000 450190000000000000010Y02404 VALOTL 0000000000000000000000000 450190000000000000010Y02404 VALOTL 0000000000000000000000000 450190000000000000010Y02404
0106:10:53:46.447|3815648|EE#:02404|time_building_block_id: |To MSS: VALOTL 0000000000000000000000000 450190000000000000010Y02404 VALOTL

I attached the actual package here is the part that I can't get the loop right.
/* Formatted on 2009/01/06 14:09 (Formatter Plus v4.8.8) */
PROCEDURE send_string
IS
   x                       NUMBER;
   l_head_string           VARCHAR2 (100)  := ' VALOTL ';
   l_mss_return            VARCHAR2 (1000);
   l_valotl_string         VARCHAR2 (5000);
   l_performing_area_val   VARCHAR2 (50);
   l_fixed_dist            VARCHAR2 (1);
   l_employee_number       VARCHAR2 (10);
--
BEGIN
--
   write_log ('---> Start mlgw_otl_validation_pkg.send_string <---');
--
   x := out_rec.FIRST;

   LOOP
      EXIT WHEN x IS NULL;

--
--
      BEGIN
         SELECT   SUBSTR (hou.NAME, 2, 6),
                  (SELECT DECODE (NVL (SUM (pca.proportion), 0), 0, 'N', 'Y')
                     FROM pay_cost_allocations_f pca
                    WHERE pca.assignment_id = paf.assignment_id
                      AND l_effective_date BETWEEN pca.effective_start_date
                                               AND pca.effective_end_date)
                                                                   proportion,
                  LPAD (ppf.employee_number, 5, '0')
             INTO l_performing_area_val,
                  l_fixed_dist,
                  l_employee_number
             FROM per_all_assignments_f paf,
                  hr_all_organization_units hou,
                  per_all_people_f ppf
            WHERE paf.organization_id = hou.organization_id
              AND l_effective_date BETWEEN paf.effective_start_date
                                       AND paf.effective_end_date
              AND l_effective_date BETWEEN ppf.effective_start_date
                                       AND ppf.effective_end_date
              AND ppf.person_id = paf.person_id
              AND paf.assignment_type = 'E'
              AND paf.primary_flag = 'Y'
              AND paf.person_id = out_rec (x).person_id
         GROUP BY SUBSTR (hou.NAME, 2, 6),
                  ppf.employee_number,
                  paf.assignment_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_performing_area_val := '000000';
            l_fixed_dist := 'N';
            l_employee_number := '00000';
      END;

--
      out_rec (x).performing_area := LPAD (l_performing_area_val, 6, '0');
--
      out_rec (x).work_ticket :=
                     UPPER (NVL (out_rec (x).work_ticket, LPAD (' ', 8, ' ')));
      out_rec (x).repair_order_num :=
                          NVL (out_rec (x).repair_order_num, LPAD (0, 7, '0'));
      out_rec (x).crew_number :=
                               NVL (out_rec (x).crew_number, LPAD (0, 4, '0'));
      out_rec (x).equipment_number :=
                          NVL (out_rec (x).equipment_number, LPAD (0, 4, '0'));
      out_rec (x).charge_code :=
                             NVL (out_rec (x).charge_code, RPAD ('0', 4, '0'));
      out_rec (x).requesting_area :=
                           NVL (out_rec (x).requesting_area, LPAD (0, 6, '0'));
      out_rec (x).wr_nbr :=
                          UPPER (NVL (out_rec (x).wr_nbr, RPAD (' ', 8, ' ')));
      out_rec (x).performing_area :=
                           NVL (out_rec (x).performing_area, LPAD (0, 6, '0'));
      out_rec (x).start_miles :=
                               NVL (out_rec (x).start_miles, LPAD (0, 6, '0'));
      out_rec (x).ending_miles :=
                              NVL (out_rec (x).ending_miles, LPAD (0, 6, '0'));
      out_rec (x).legacy_earn_code :=
                                     NVL (out_rec (x).legacy_earn_code, '000');
      out_rec (x).woc_job_class :=
                           NVL (out_rec (x).woc_job_class, LPAD (' ', 6, ' '));
--
      l_valotl_string :=
         (   l_head_string
          || out_rec (x).work_ticket
          || out_rec (x).repair_order_num
          || out_rec (x).crew_number
          || out_rec (x).equipment_number
          || out_rec (x).charge_code
          || out_rec (x).requesting_area
          || out_rec (x).wr_nbr
          || out_rec (x).performing_area
          || out_rec (x).start_miles
          || out_rec (x).ending_miles
          || out_rec (x).legacy_earn_code
          || l_fixed_dist
          || l_employee_number
          || out_rec (x).woc_job_class
         );
--
-- Call MSS Function to validate timecard data
--
      err_tbl.DELETE;
      write_log (   'EE#:'
                 || l_employee_number
                 || '|time_building_block_id:'
                 || x
                 || ' |To MSS:'
                 || l_valotl_string
                );

--
-- l_mss_return := trim(mgwuser.mlgw_mss.valtimec(l_valotl_string));
--
--write_log('EE#:'||l_employee_number||'|time_building_block_id:'||x||' |From MSS:'||l_mss_return);
--
-- hr_utility.set_location('-----------------------------------'||x,9);
-- hr_utility.set_location('x: '||x,9);
-- hr_utility.set_location('Header: '||l_head_string,9);
-- hr_utility.set_location('out_rec.work_ticket: '||out_rec(x).work_ticket,9);
-- hr_utility.set_location('out_rec.repair_order_num: '||out_rec(x).repair_order_num,9);
-- hr_utility.set_location('out_rec.crew_number: '||out_rec(x).crew_number,9);
-- hr_utility.set_location('out_rec.equipment_number: '||out_rec(x).equipment_number,9);
-- hr_utility.set_location('out_rec.charge_code: '||out_rec(x).charge_code,9);
-- hr_utility.set_location('out_rec.requesting_area: '||out_rec(x).requesting_area,9);
-- hr_utility.set_location('out_rec.wr_nbr: '||replace(out_rec(x).wr_nbr,' ','#'),9);
-- hr_utility.set_location('out_rec.performing_area: '||out_rec(x).performing_area,9);
-- hr_utility.set_location('out_rec.start_miles: '||out_rec(x).start_miles,9);
-- hr_utility.set_location('out_rec.ending_miles: '||out_rec(x).ending_miles,9);
-- hr_utility.set_location('l_mss_return: '||l_mss_return,9);
--
      IF REPLACE (SUBSTR (l_mss_return, 11, 50), ' ') IS NOT NULL
      THEN
         err_tbl (1) := TRIM (SUBSTR (l_mss_return, 11, 50));
      END IF;

--
      IF REPLACE (SUBSTR (l_mss_return, 61, 50), ' ') IS NOT NULL
      THEN
         err_tbl (2) := TRIM (SUBSTR (l_mss_return, 61, 50));
      END IF;

--
      IF REPLACE (SUBSTR (l_mss_return, 111, 50), ' ') IS NOT NULL
      THEN
         err_tbl (3) := TRIM (SUBSTR (l_mss_return, 111, 50));
      END IF;

--
      IF REPLACE (SUBSTR (l_mss_return, 161, 50), ' ') IS NOT NULL
      THEN
         err_tbl (4) := TRIM (SUBSTR (l_mss_return, 161, 50));
      END IF;

--
      IF REPLACE (SUBSTR (l_mss_return, 211, 50), ' ') IS NOT NULL
      THEN
         err_tbl (5) := TRIM (SUBSTR (l_mss_return, 211, 50));
      END IF;

--
--
      IF SUBSTR (l_mss_return, 9, 2) = '01'
      THEN
--
         write_log (   'EE#:'
                    || l_employee_number
                    || 'hxc_time_entry_rules_utils_pkg.publish_message:'
                    || err_tbl.COUNT
                   );

         FOR ers IN 1 .. 5
         LOOP
--
            IF err_tbl.EXISTS (ers) AND err_tbl (ers) IS NOT NULL
            THEN
--
--
               hxc_time_entry_rules_utils_pkg.publish_message
                                              (p_name                        => 'MLGW',
                                               p_token_name                  => 'MSS',
                                               p_token_value                 => err_tbl
                                                                                   (ers
                                                                                   ),
                                               p_time_building_block_id      => x
                                              );
--
            END IF;
         END LOOP;
--
      ELSIF SUBSTR (l_mss_return, 9, 2) != '00'
      THEN
         write_log
            (   'EE#:'
             || l_employee_number
             || 'hxc_time_entry_rules_utils_pkg.publish_message:Invalid Response from MSS'
            );
         hxc_time_entry_rules_utils_pkg.publish_message
                                (p_name                        => 'MLGW',
                                 p_token_name                  => 'MSS',
                                 p_token_value                 => 'Invalid Response from MSS',
                                 p_time_building_block_id      => x
                                );
      END IF;

--
--
-- Call internal validation
      validate_entries (p_time_building_block_id      => x,
                        p_person_id                   => out_rec (x).person_id,
                        p_effective_date              => l_effective_date,
                        p_element_type_id             => out_rec (x).element_type_id
                       );
--
      x := out_rec.NEXT (x);
   END LOOP;

   write_log ('---> End mlgw_otl_validation_pkg.send_string <---');
END send_string;
--
Re: pl/sql select loop [message #379609 is a reply to message #379485] Wed, 07 January 2009 05:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Just modify your proecedure so that instead of writing output each time it gets a record, it instead concatenates the records together, and then write the output as soon as the loop is finished.
Re: pl/sql select loop [message #379663 is a reply to message #379609] Wed, 07 January 2009 09:34 Go to previous message
decarlomw
Messages: 12
Registered: January 2009
Junior Member
Hi,

I tried that but I could never get it to print out if the loop count was under 3. I want it to print out even if it is one or two.

Thanks
Previous Topic: how to combine two queries
Next Topic: Table Design for summary data
Goto Forum:
  


Current Time: Mon Dec 05 04:47:00 CST 2016

Total time taken to generate the page: 0.07961 seconds