Beautifier Bug when using MERGE Statement with CASE

[marcosiefert]

Hi,

I think that I have found a bug in PL/SQL Beautifier.
The format of a merge statement is wrong when the last value of the UPDATE...SET... Part is a Case Block (see the attached images).

Example:

MERGE INTO test_new n
  USING (SELECT *
           FROM test_old) o
  ON (n.test_id = o.test_id)
  WHEN MATCHED THEN
    UPDATE
       SET t.test_name = s.test_name,
           t.changed   = CASE
                           WHEN test_name = 'bbb' THEN
                            1
                           ELSE
                            0
                         END WHEN NOT MATCHED THEN INSERT(test_id, test_name) VALUES(999, 'aaa');

When I comment out the CASE Part it is correct:

MERGE INTO test_new n
USING (SELECT *
         FROM test_old) o
ON (n.test_id = o.test_id)
WHEN MATCHED THEN
  UPDATE
     SET t.test_name = s.test_name,
         t.changed   = /*CASE
                             WHEN test_name = 'bbb' THEN
                              1
                             ELSE
                              0
                           END */

WHEN NOT MATCHED THEN
  INSERT
    (test_id,
     test_name)
  VALUES
    (999,
     'aaa');

Apart from that PL/SQL Developer is really a great tool, I nearly never find a bug in it.

Regards
Marco

 

[Marco Kalter]

This is indeed caused by the case expression. We'll fix it.

 

[marcosiefert]

Found antother bug in the beautifier.
It does not wirk correctly with FORALL and MERGE.

Example:

DECLARE
  l_nums dbms_sql.number_table;
BEGIN

  FORALL i in 1..l_nums.count
  MERGE INTO test_new n
  USING (SELECT *
           FROM test_old) o
  ON (n.test_id = o.test_id)
  WHEN MATCHED THEN
    UPDATE
       SET t.num = l_nums(i);

END;

Beautifier produces this:

DECLARE
  l_nums dbms_sql.number_table;
BEGIN

  FORALL i IN 1 .. l_nums.count MERGE INTO test_new n USING
                   (SELECT *
                      FROM test_old) o ON(n.test_id = o.test_id)
    WHEN MATCHED THEN UPDATE SET t.num = l_nums(i);

END;

Please fix so that my code is beautiful again .

 

[Marco Kalter]

We'll fix it.