Beautyfying forall with merge statement

[Bo Pedersen]

The beautifier cannot handle a merge statement nested inside a forall like in the following procedure:

procedure set_customer_mapping(p_customer_no varchar2,
                                 p_type        varchar2,
                                 p_years       string_array,
                                 p_months      string_array,
                                 p_readings    string_array,
                                 p_notes       string_array) is
    l_customer_no forretningspartner.partnernr%type := lpad(p_customer_no, 10, '0');
  begin
    forall i in 1 .. p_months.count
      merge into consumption_mappings m
      using (select p_years(i) year, p_months(i) month, p_readings(i) reading, p_notes(i) note
               from dual
              where p_readings(i) is not null or
                    p_notes(i) is not null) n
      on (m.year = n.year and m.month = n.month and m.customer_no = l_customer_no and m.type = p_type)
      when matched then
        update set m.reading = n.reading, m.note = n.note
      when not matched then
        insert
          (customer_no, type, year, month, reading, note)
        values
          (l_customer_no, p_type, n.year, n.month, n.reading, n.note);
  end;

And this leads to another request. It would be nice if the beatifier could be told to ignore a block like this that the beautifier cannot handle sufficiently well. Normally, I just beautify my whole package. I cannot do this anymore with this package. And I have to remember it .

 

[Marco Kalter]

Support for merge statements like this is planned for 6.1.

 

[Bo Pedersen]

The merge statement alone is OK (formats like above). Its when combined with the forall statement it goes wrong. Then the above forall-merge is written on three lines.

Bo