Parsing 'pivot' and 'unpivot' constructs.

[devops-unitybars]

When parsing PL/SQL code that contains 'pivot' and 'unpivot' constructs, the code is incorrectly tokenized (using ZPA Toolkit) in procedures, functions, and packages, resulting in rules not being applied correctly.

Example of such code with 'pivot':

CREATE OR REPLACE PROCEDURE pivot_example IS
BEGIN
  -- Using PIVOT to transform rows into columns
  FOR rec IN (
    SELECT *
    FROM (
      SELECT employee_id, department_id, sales_year, sales_amount
      FROM sales
    )
    PIVOT (
      SUM(sales_amount) 
      FOR sales_year IN ('2020' AS "2020", '2021' AS "2021", '2022' AS "2022")
    )
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || rec.employee_id || 
                         ', Department: ' || rec.department_id || 
                         ', Sales 2020: ' || rec."2020" || 
                         ', Sales 2021: ' || rec."2021" || 
                         ', Sales 2022: ' || rec."2022");
  END LOOP;
END;

Example of such code with 'unpivot':

CREATE OR REPLACE PROCEDURE unpivot_example IS
BEGIN
  -- Using UNPIVOT to transform columns into rows
  FOR rec IN (
    SELECT employee_id, department_id, sales_year, sales_amount
    FROM (
      SELECT employee_id, department_id, "2020", "2021", "2022"
      FROM sales_summary
    )
    UNPIVOT (
      sales_amount FOR sales_year IN ("2020" AS '2020', "2021" AS '2021', "2022" AS '2022')
    )
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || rec.employee_id || 
                         ', Department: ' || rec.department_id || 
                         ', Year: ' || rec.sales_year || 
                         ', Sales: ' || rec.sales_amount);
  END LOOP;
END;

Also, if any test is run in the zpa-main project for code with 'pivot' and 'unpivot' constructs, an exception occurs:

com.felipebz.flr.api.RecognitionException: Parse error at line 10 column 10:
 
    1: CREATE OR REPLACE PROCEDURE pivot_example IS
    2: BEGIN
    3: 
    4: FOR rec IN (
    5:     SELECT *
    6:     FROM (
    7:       SELECT employee_id, department_id, sales_year, sales_amount
    8:       FROM sales
    9:     )
  -->      PIVOT (
   11:       SUM(sales_amount)
   12:       FOR sales_year IN ('2020' AS "2020", '2021' AS "2021", '2022' AS "2022")
   13:     )
   14:   ) LOOP
   15:     DBMS_OUTPUT.PUT_LINE('Employee ID: ' |
 
 
com.felipebz.flr.api.RecognitionException: Parse error at line 10 column 12:
 
    1: IS
    2: BEGIN
    3: 
    4: FOR rec IN (
    5:     SELECT employee_id, department_id, sales_year, sales_amount
    6:     FROM (
    7:       SELECT employee_id, department_id, "2020", "2021", "2022"
    8:       FROM sales_summary
    9:     )
  -->      UNPIVOT (
   11:       sales_amount FOR sales_year IN ("2020" AS '2020', "2021" AS '2021', "2022" AS '2022')
   12:     )
   13:   ) LOOP
   14:     DBMS_OUTPUT.PUT_LINE('Employee ID: ' || rec.