Incorrect parser operation. The rule 'Specify the columns in this INSERT' is not working correctly.

When parsing PL/SQL code that contains the 'default ... on conversion error' construction, which appeared in Oracle version 12.2, the code is incorrectly tokenized (using ZPA Toolkit), causing the rules to also work incorrectly. Example of such code:

CREATE OR REPLACE PACKAGE Employee_Management AS

  TYPE emp_record IS RECORD (
    emp_id    NUMBER(10),
    first_name VARCHAR2(100),
    last_name  VARCHAR2(100),
    salary     NUMBER(8, 2)
  );

  TYPE emp_table IS TABLE OF emp_record;

  FUNCTION get_salary(emp_id NUMBER, default_salary NUMBER DEFAULT 1000)
    RETURN NUMBER;

  PROCEDURE insert_employee(emp_data IN emp%ROWTYPE);

END Employee_Management;
/

CREATE OR REPLACE PACKAGE BODY Employee_Management AS

FUNCTION get_salary(emp_id NUMBER, default_salary NUMBER DEFAULT 1000)
    RETURN NUMBER
  IS
    v_salary NUMBER;
  BEGIN
    BEGIN
      SELECT TO_NUMBER(salary DEFAULT default_salary ON CONVERSION ERROR)
      INTO v_salary
      FROM emp
      WHERE emp_id = emp_id;
    EXCEPTION
      WHEN OTHERS THEN
        RETURN default_salary;
    END;

    RETURN v_salary;
  END get_salary;

  PROCEDURE insert_employee(emp_data IN emp%ROWTYPE) IS
    v_emp_data emp%ROWTYPE;

    v_salary NUMBER;
  BEGIN
    v_emp_data := emp_data;

    BEGIN
      v_salary := TO_NUMBER(v_emp_data.salary DEFAULT 1000 ON CONVERSION ERROR);
    EXCEPTION
      WHEN OTHERS THEN
        v_salary := 1000;
    END;

    INSERT INTO emp
    VALUES (v_emp_data);

  END insert_employee;

END Employee_Management;
/

Equivalent code for creating a package without using the 'default ... on conversion error' construction, where the parsing works correctly:

CREATE OR REPLACE PACKAGE Employee_Management AS

  TYPE emp_record IS RECORD (
    emp_id    NUMBER(10),
    first_name VARCHAR2(100),
    last_name  VARCHAR2(100),
    salary     NUMBER(8, 2)
  );

  TYPE emp_table IS TABLE OF emp_record;

  FUNCTION get_salary(emp_id NUMBER, default_salary NUMBER DEFAULT 1000)
    RETURN NUMBER;

  PROCEDURE insert_employee(emp_data IN emp%ROWTYPE);

END Employee_Management;
/

CREATE OR REPLACE PACKAGE BODY Employee_Management AS

FUNCTION get_salary(emp_id NUMBER, default_salary NUMBER DEFAULT 1000)
    RETURN NUMBER
  IS
    v_salary NUMBER;
  BEGIN
    BEGIN
      SELECT TO_NUMBER(salary)
      INTO v_salary
      FROM emp
      WHERE emp_id = emp_id;
    EXCEPTION
      WHEN OTHERS THEN
        RETURN default_salary;
    END;

    RETURN v_salary;
  END get_salary;

  PROCEDURE insert_employee(emp_data IN emp%ROWTYPE) IS
    v_emp_data emp%ROWTYPE;

    v_salary NUMBER;
  BEGIN
    v_emp_data := emp_data;

    BEGIN
      v_salary := TO_NUMBER(v_emp_data.salary);
    EXCEPTION
      WHEN OTHERS THEN
        v_salary := 1000;
    END;

    INSERT INTO emp
    VALUES (v_emp_data);

  END insert_employee;

END Employee_Management;
/

Also, if you run any test in the zpa-main project for code with the 'default ... on conversion error' construction, an exception occurs:


com.felipebz.flr.api.RecognitionException: Parse error at line 29 column 24:

   21: CREATE OR REPLACE PACKAGE BODY Employee_Management AS
   22: 
   23: FUNCTION get_salary(emp_id NUMBER, default_salary NUMBER DEFAULT 1000)
   24:     RETURN NUMBER
   25:   IS
   26:     v_salary NUMBER;
   27: BEGIN
   28: BEGIN
  -->  SELECT TO_NUMBER(salary DEFAULT default_salary ON CONVERSION ERROR)
   30: INTO v_salary
   31: FROM emp
   32: WHERE emp_id = emp_id;
   33: EXCEPTION
   34:       WHEN OTHERS THEN
   35:         RETURN default_salary;
   36: END;
   37: 
   38: RETURN v_salary;
   39: END get_salary;

felipebz / zpa

Incorrect parser operation. The rule 'Specify the columns in this INSERT' is not working correctly. #192