Different behaviour on ZPA Toolkit AST parser and plugin

[Tiaguituh05]

Hi,

I've made a plugin to detect if a PL/SQL Block is missing a "/" at the end. This works well on most sql files I have, except on some very complex package bodies.

This is my rule code:

public class ForbiddenSlashCheck extends PlSqlCheck {

    @Override
    public void init() {
        subscribeTo(PlSqlGrammar.COMPILATION_UNIT);
    }

    @Override
    public void visitNode(AstNode node) {

        //System.out.println("Node: type=" + node.getName() + ", token=" + node.getTokenValue());

        AstNode xnode = node.getNextAstNode();

                // Debug output to help trace the node processing
                System.out.println("Node: type=" + node.getName() + ", token=" + node.getTokenValue());
                System.out.println("xnode: " + xnode);
                System.out.println("xnode getName: " + xnode.getName());
                System.out.println("xnode getToken: " + xnode.getToken());
                System.out.println("xnode getToken column: " + xnode.getToken().getColumn());

        if (!xnode.getName().equals("EXECUTE_PLSQL_BUFFER"))
            System.out.println("adding issue because type is different than EXECUTE_PLSQL_BUFFER");
            addIssue(node, "No / at end of compilation unit");
        else if (xnode.getToken().getColumn() != 0)
            addIssue(node, "/ not first char of line");
    }

}

When I place the package code into ZPA Toolkit, I get the following parse behavior:

Which detects the EXECUTE_PLSQL_BUFFER at the end, and therefore the "/". Which is perfect.

But when I run the scanner I get the following: debug.txt

That ends on like 1974, with a RECOVERY parse. But the it should end on line 1975, where the "/" is, and therefore detect the EXECUTE_PLSQL_BUFFER.

If scanner won't detect the EXECUTE_PLSQL_BUFFER, I won't be able to apply my rule successfully.

Any ideas on why this might be happening?

Thank you!

[felipebz]

Hi,

The issue may be happening because the package contains syntax not yet supported by the parser. When this occurs, the parser attempts to "ignore" the unrecognized code, resulting in the creation of RECOVERY nodes. Unfortunately, this is not helpful for your intended checks.

To pinpoint where the parsing fails, please navigate to the "Configuration" tab in the ZPA Toolkit and set the "Error recovery" option to false. Afterward, when you parse the code again, an exception will be logged in the "Console" tab, indicating the exact location of the failure along with a few lines preceding and following the error, for example:

Parse error at line 5 column 0:

    1: declare
    2:   x varchar2(10);
    3: begin
    4:   x := 'foo'
  -->  end;

If you could share the details of this exception, it would be immensely helpful. Please feel free to redact any sensitive code, I'm only interested in the syntax.

[Tiaguituh05]

Hi Felipe, sure thing. The issue seem to be on a cursor that we have. Full stack trace attached. debug.txt

I believe we might have that issue several times along the package, as we have similar cursors like the first one above.

    CURSOR C_get_all_scopes(I_json IN CLOB) IS
      WITH t AS (SELECT aud || scp aud_scope
                   FROM JSON_TABLE(I_json FORMAT JSON, '$[*]' COLUMNS (
                          scp VARCHAR2 PATH '$.scope',
                          NESTED PATH '$.aud[*]' COLUMNS (
                            aud VARCHAR2 PATH '$[*]')))),
           a AS (SELECT 1 b
                   FROM t,
                        TABLE(ORC_VARCHAR_TWF(I_collection => I_required_scopes_tbl)) tbl
                  WHERE tbl.column_value = t.aud_scope
                  FETCH FIRST 1 ROWS ONLY)
      SELECT t.aud_scope
        FROM t,
             a
       WHERE a.b IS NOT NULL;

    CURSOR C_get_all_scopes(I_json IN CLOB) IS
      SELECT aud || scp aud_scope
        FROM JSON_TABLE(I_json FORMAT JSON, '$[*]' COLUMNS (
               scp VARCHAR2 PATH '$.scope',
               NESTED PATH '$.aud[*]' COLUMNS (
                 aud VARCHAR2 PATH '$[*]')));

    CURSOR C_get_groups(I_json IN CLOB) IS
      WITH t AS (SELECT DISTINCT
                        group_name
                   FROM JSON_TABLE(I_json FORMAT JSON, '$[*]' COLUMNS (
                          NESTED PATH '$.groups[*]' COLUMNS (
                            group_name VARCHAR2 PATH '$.display')))),
           a AS (SELECT 1 b
                   FROM t,
                        TABLE(ORC_VARCHAR_TWF(I_required_roles_tbl)) tbl
                  WHERE tbl.column_value = t.group_name
                  FETCH FIRST 1 ROWS ONLY)
      SELECT group_name
        FROM t,
             a
       WHERE a.b IS NOT NULL;

[felipebz]

Thank you for the examples.

I noticed in the other issue that you were using version 3.4.0. Coincidentally, I released version 3.5.0 last week, which supports the syntax of all JSON functions.

Could you please try parsing the package using the latest zpa-toolkit? I have tested all your examples, and they are now recognized correctly.

[Tiaguituh05]

Thank you for the quick response.

Upgrading to sonar-zpa-plugin-3.5.1 did the trick! And yes, zpa-toolkit-3.5.1 also parses everything correctly now. Testing locally my custom plugin, I still get parsing errors. What do I need to upgrade/modify on the plsql-custom-rules project, so I can test locally? It makes life easier :)

[felipebz]

You need to upgrade the versions of the sonar-zpa-plugin and zpa-checks-testkit dependencies on builde.gradle or pom.xml.

[Tiaguituh05]

That worked! Thank you once again for all the help Felipe!