Are Statements interpreted differently inside and outside of PACKAGE'S or PROCEDURES by SSLR Toolkit?

SonarQube Writing rules
1

I’m trying to validate the use of some operators within where clauses. My mistake was to create unique examples like DML’s or SQL queries outside of PACKAGE, PROCURES, FUNCTIONS because I’m now seeing a different AST in these situations. Example:

The following query generates an AST with the WHERE_CLAUSE node:

SELECT
	   coluna1,
	   coluna2 
	FROM
	   tabela1 
	where
	   coluna1 != 'teste' 
	   OR coluna2 <> 'teste' 
	   OR NOT 1 > 2 
	   OR coluna1 LIKE '%valor' 
	   OR coluna1 IS NOT NULL
	   OR coluna2  IS NULL;

The AST generated is:

<COMPILATION_UNIT tokenValue="SELECT" tokenLine="1" tokenColumn="0">
  <COMPILATION_UNIT tokenValue="SELECT" tokenLine="1" tokenColumn="0">
    <SELECT_WITH_SEMICOLON tokenValue="SELECT" tokenLine="1" tokenColumn="0">
      <SELECT tokenValue="SELECT" tokenLine="1" tokenColumn="0">
        <SUBQUERY tokenValue="SELECT" tokenLine="1" tokenColumn="0">
          <QUERY_BLOCK tokenValue="SELECT" tokenLine="1" tokenColumn="0">
            <SELECT tokenValue="SELECT" tokenLine="1" tokenColumn="0"/>
            <SELECT_LIST tokenValue="COLUNA1" tokenLine="2" tokenColumn="4">
              <SELECT_ELEMENT tokenValue="COLUNA1" tokenLine="2" tokenColumn="4">
                <SELECT_EXPRESSION tokenValue="COLUNA1" tokenLine="2" tokenColumn="4">
                  <EXPRESSION tokenValue="COLUNA1" tokenLine="2" tokenColumn="4">
                    <EXPRESSION_PRIMARY tokenValue="COLUNA1" tokenLine="2" tokenColumn="4">
                      <BIN_IDENTIFIER tokenValue="COLUNA1" tokenLine="2" tokenColumn="4">
                        <IDENTIFIER tokenValue="COLUNA1" tokenLine="2" tokenColumn="4"/>
                      </BIN_IDENTIFIER>
                    </EXPRESSION_PRIMARY>
                  </EXPRESSION>
                </SELECT_EXPRESSION>
              </SELECT_ELEMENT>
              <COMMA tokenValue="," tokenLine="2" tokenColumn="11"/>
              <SELECT_ELEMENT tokenValue="COLUNA2" tokenLine="3" tokenColumn="4">
                <SELECT_EXPRESSION tokenValue="COLUNA2" tokenLine="3" tokenColumn="4">
                  <EXPRESSION tokenValue="COLUNA2" tokenLine="3" tokenColumn="4">
                    <EXPRESSION_PRIMARY tokenValue="COLUNA2" tokenLine="3" tokenColumn="4">
                      <BIN_IDENTIFIER tokenValue="COLUNA2" tokenLine="3" tokenColumn="4">
                        <IDENTIFIER tokenValue="COLUNA2" tokenLine="3" tokenColumn="4"/>
                      </BIN_IDENTIFIER>
                    </EXPRESSION_PRIMARY>
                  </EXPRESSION>
                </SELECT_EXPRESSION>
              </SELECT_ELEMENT>
            </SELECT_LIST>
            <FROM tokenValue="FROM" tokenLine="4" tokenColumn="1"/>
            <FROM_SOURCE_LIST tokenValue="TABELA1" tokenLine="5" tokenColumn="4">
              <FROM_SOURCE tokenValue="TABELA1" tokenLine="5" tokenColumn="4">
                <FROM_SOURCE_TABLE tokenValue="TABELA1" tokenLine="5" tokenColumn="4">
                  <TABLE_REFERENCE tokenValue="TABELA1" tokenLine="5" tokenColumn="4">
                    <QUERY_TABLE_EXPRESSION tokenValue="TABELA1" tokenLine="5" tokenColumn="4">
                      <BIN_IDENTIFIER tokenValue="TABELA1" tokenLine="5" tokenColumn="4">
                        <IDENTIFIER tokenValue="TABELA1" tokenLine="5" tokenColumn="4"/>
                      </BIN_IDENTIFIER>
                    </QUERY_TABLE_EXPRESSION>
                  </TABLE_REFERENCE>
                </FROM_SOURCE_TABLE>
              </FROM_SOURCE>
            </FROM_SOURCE_LIST>
            <WHERE_CLAUSE tokenValue="WHERE" tokenLine="6" tokenColumn="1">
              <WHERE tokenValue="WHERE" tokenLine="6" tokenColumn="1"/>
              <EXPRESSION tokenValue="COLUNA1" tokenLine="7" tokenColumn="4">
                <EXPRESSION_OR tokenValue="COLUNA1" tokenLine="7" tokenColumn="4">
                  <EXPRESSION_RELATIONAL tokenValue="COLUNA1" tokenLine="7" tokenColumn="4">
                    <EXPRESSION_PRIMARY tokenValue="COLUNA1" tokenLine="7" tokenColumn="4">
                      <BIN_IDENTIFIER tokenValue="COLUNA1" tokenLine="7" tokenColumn="4">
                        <IDENTIFIER tokenValue="COLUNA1" tokenLine="7" tokenColumn="4"/>
                      </BIN_IDENTIFIER>
                    </EXPRESSION_PRIMARY>
                    <NE_2 tokenValue="!=" tokenLine="7" tokenColumn="12"/>
                    <EXPRESSION_PRIMARY tokenValue="'teste'" tokenLine="7" tokenColumn="15">
                      <CHAR tokenValue="'teste'" tokenLine="7" tokenColumn="15"/>
                    </EXPRESSION_PRIMARY>
                  </EXPRESSION_RELATIONAL>
                  <EXPRESSION_OR_OPERATOR tokenValue="OR" tokenLine="8" tokenColumn="4">
                    <OR tokenValue="OR" tokenLine="8" tokenColumn="4"/>
                  </EXPRESSION_OR_OPERATOR>
                  <EXPRESSION_RELATIONAL tokenValue="COLUNA2" tokenLine="8" tokenColumn="7">
                    <EXPRESSION_PRIMARY tokenValue="COLUNA2" tokenLine="8" tokenColumn="7">
                      <BIN_IDENTIFIER tokenValue="COLUNA2" tokenLine="8" tokenColumn="7">
                        <IDENTIFIER tokenValue="COLUNA2" tokenLine="8" tokenColumn="7"/>
                      </BIN_IDENTIFIER>
                    </EXPRESSION_PRIMARY>
                    <NE_1 tokenValue="<>" tokenLine="8" tokenColumn="15"/>
                    <EXPRESSION_PRIMARY tokenValue="'teste'" tokenLine="8" tokenColumn="18">
                      <CHAR tokenValue="'teste'" tokenLine="8" tokenColumn="18"/>
                    </EXPRESSION_PRIMARY>
                  </EXPRESSION_RELATIONAL>
                  <EXPRESSION_OR_OPERATOR tokenValue="OR" tokenLine="9" tokenColumn="4">
                    <OR tokenValue="OR" tokenLine="9" tokenColumn="4"/>
                  </EXPRESSION_OR_OPERATOR>
                  <EXPRESSION_NEGATION tokenValue="NOT" tokenLine="9" tokenColumn="7">
                    <NOT tokenValue="NOT" tokenLine="9" tokenColumn="7"/>
                    <EXPRESSION_RELATIONAL tokenValue="1" tokenLine="9" tokenColumn="11">
                      <EXPRESSION_PRIMARY tokenValue="1" tokenLine="9" tokenColumn="11">
                        <INTEGER tokenValue="1" tokenLine="9" tokenColumn="11"/>
                      </EXPRESSION_PRIMARY>
                      <GT tokenValue=">" tokenLine="9" tokenColumn="13"/>
                      <EXPRESSION_PRIMARY tokenValue="2" tokenLine="9" tokenColumn="15">
                        <INTEGER tokenValue="2" tokenLine="9" tokenColumn="15"/>
                      </EXPRESSION_PRIMARY>
                    </EXPRESSION_RELATIONAL>
                  </EXPRESSION_NEGATION>
                  <EXPRESSION_OR_OPERATOR tokenValue="OR" tokenLine="10" tokenColumn="4">
                    <OR tokenValue="OR" tokenLine="10" tokenColumn="4"/>
                  </EXPRESSION_OR_OPERATOR>
                  <EXPRESSION_RELATIONAL tokenValue="COLUNA1" tokenLine="10" tokenColumn="7">
                    <EXPRESSION_PRIMARY tokenValue="COLUNA1" tokenLine="10" tokenColumn="7">
                      <BIN_IDENTIFIER tokenValue="COLUNA1" tokenLine="10" tokenColumn="7">
                        <IDENTIFIER tokenValue="COLUNA1" tokenLine="10" tokenColumn="7"/>
                      </BIN_IDENTIFIER>
                    </EXPRESSION_PRIMARY>
                    <EXPRESSION_RELATIONAL_UNARY_OPERATOR tokenValue="LIKE" tokenLine="10" tokenColumn="15">
                      <LIKE tokenValue="LIKE" tokenLine="10" tokenColumn="15"/>
                      <EXPRESSION_PRIMARY tokenValue="'%valor'" tokenLine="10" tokenColumn="20">
                        <CHAR tokenValue="'%valor'" tokenLine="10" tokenColumn="20"/>
                      </EXPRESSION_PRIMARY>
                    </EXPRESSION_RELATIONAL_UNARY_OPERATOR>
                  </EXPRESSION_RELATIONAL>
                  <EXPRESSION_OR_OPERATOR tokenValue="OR" tokenLine="11" tokenColumn="4">
                    <OR tokenValue="OR" tokenLine="11" tokenColumn="4"/>
                  </EXPRESSION_OR_OPERATOR>
                  <EXPRESSION_RELATIONAL tokenValue="COLUNA1" tokenLine="11" tokenColumn="7">
                    <EXPRESSION_PRIMARY tokenValue="COLUNA1" tokenLine="11" tokenColumn="7">
                      <BIN_IDENTIFIER tokenValue="COLUNA1" tokenLine="11" tokenColumn="7">
                        <IDENTIFIER tokenValue="COLUNA1" tokenLine="11" tokenColumn="7"/>
                      </BIN_IDENTIFIER>
                    </EXPRESSION_PRIMARY>
                    <EXPRESSION_RELATIONAL_UNARY_OPERATOR tokenValue="IS" tokenLine="11" tokenColumn="15">
                      <IS tokenValue="IS" tokenLine="11" tokenColumn="15"/>
                      <NOT tokenValue="NOT" tokenLine="11" tokenColumn="18"/>
                      <NULL tokenValue="NULL" tokenLine="11" tokenColumn="22"/>
                    </EXPRESSION_RELATIONAL_UNARY_OPERATOR>
                  </EXPRESSION_RELATIONAL>
                  <EXPRESSION_OR_OPERATOR tokenValue="OR" tokenLine="12" tokenColumn="4">
                    <OR tokenValue="OR" tokenLine="12" tokenColumn="4"/>
                  </EXPRESSION_OR_OPERATOR>
                  <EXPRESSION_RELATIONAL tokenValue="COLUNA2" tokenLine="12" tokenColumn="7">
                    <EXPRESSION_PRIMARY tokenValue="COLUNA2" tokenLine="12" tokenColumn="7">
                      <BIN_IDENTIFIER tokenValue="COLUNA2" tokenLine="12" tokenColumn="7">
                        <IDENTIFIER tokenValue="COLUNA2" tokenLine="12" tokenColumn="7"/>
                      </BIN_IDENTIFIER>
                    </EXPRESSION_PRIMARY>
                    <EXPRESSION_RELATIONAL_UNARY_OPERATOR tokenValue="IS" tokenLine="12" tokenColumn="16">
                      <IS tokenValue="IS" tokenLine="12" tokenColumn="16"/>
                      <NULL tokenValue="NULL" tokenLine="12" tokenColumn="19"/>
                    </EXPRESSION_RELATIONAL_UNARY_OPERATOR>
                  </EXPRESSION_RELATIONAL>
                </EXPRESSION_OR>
              </EXPRESSION>
            </WHERE_CLAUSE>
          </QUERY_BLOCK>
        </SUBQUERY>
      </SELECT>
      <SEMICOLON tokenValue=";" tokenLine="12" tokenColumn="23"/>
    </SELECT_WITH_SEMICOLON>
  </COMPILATION_UNIT>
  <EOF tokenValue="EOF" tokenLine="12" tokenColumn="24"/>
</COMPILATION_UNIT>

But by placing the same query inside a PL / SQL block it changes shape and loses levels that make it easier to navigate. Example:

BEGIN
	SELECT
	   coluna1,
	   coluna2 
	FROM
	   tabela1 
	WHERE
	   coluna1 != 'teste' 
	   OR coluna2 <> 'teste' 
	   OR NOT 1 > 2 
	   OR coluna1 LIKE '%valor' 
	   OR coluna1 IS NOT NULL
	   OR coluna2  IS NULL;
END;

The node of the where clause is no longer as before, e a AST ficou assim:

<COMPILATION_UNIT tokenValue="BEGIN" tokenLine="1" tokenColumn="0">
  <PL_SQL_COMPILATION_UNIT_NO_EOF tokenValue="BEGIN" tokenLine="1" tokenColumn="0">
    <PLSQL_BLOCK_MAIN tokenValue="BEGIN" tokenLine="1" tokenColumn="0">
      <PLSQL_BLOCK_WITH_SEMICOLON tokenValue="BEGIN" tokenLine="1" tokenColumn="0">
        <PLSQL_BLOCK tokenValue="BEGIN" tokenLine="1" tokenColumn="0">
          <BODY tokenValue="BEGIN" tokenLine="1" tokenColumn="0">
            <BEGIN tokenValue="BEGIN" tokenLine="1" tokenColumn="0"/>
            <STATEMENTS_OR_PRAGMA tokenValue="SELECT" tokenLine="2" tokenColumn="1">
              <STATEMENT tokenValue="SELECT" tokenLine="2" tokenColumn="1">
                <SELECT_INTO_STATEMENT_RECOVERY tokenValue="SELECT" tokenLine="2" tokenColumn="1">
                  <SELECT tokenValue="SELECT" tokenLine="2" tokenColumn="1"/>
                  <IDENTIFIER tokenValue="COLUNA1" tokenLine="3" tokenColumn="4"/>
                  <COMMA tokenValue="," tokenLine="3" tokenColumn="11"/>
                  <IDENTIFIER tokenValue="COLUNA2" tokenLine="4" tokenColumn="4"/>
                  <FROM tokenValue="FROM" tokenLine="5" tokenColumn="1"/>
                  <IDENTIFIER tokenValue="TABELA1" tokenLine="6" tokenColumn="4"/>
                  <WHERE tokenValue="WHERE" tokenLine="7" tokenColumn="1"/>
                  <IDENTIFIER tokenValue="COLUNA1" tokenLine="8" tokenColumn="4"/>
                  <NE_2 tokenValue="!=" tokenLine="8" tokenColumn="12"/>
                  <CHAR tokenValue="'teste'" tokenLine="8" tokenColumn="15"/>
                  <OR tokenValue="OR" tokenLine="9" tokenColumn="4"/>
                  <IDENTIFIER tokenValue="COLUNA2" tokenLine="9" tokenColumn="7"/>
                  <NE_1 tokenValue="<>" tokenLine="9" tokenColumn="15"/>
                  <CHAR tokenValue="'teste'" tokenLine="9" tokenColumn="18"/>
                  <OR tokenValue="OR" tokenLine="10" tokenColumn="4"/>
                  <NOT tokenValue="NOT" tokenLine="10" tokenColumn="7"/>
                  <INTEGER tokenValue="1" tokenLine="10" tokenColumn="11"/>
                  <GT tokenValue=">" tokenLine="10" tokenColumn="13"/>
                  <INTEGER tokenValue="2" tokenLine="10" tokenColumn="15"/>
                  <OR tokenValue="OR" tokenLine="11" tokenColumn="4"/>
                  <IDENTIFIER tokenValue="COLUNA1" tokenLine="11" tokenColumn="7"/>
                  <LIKE tokenValue="LIKE" tokenLine="11" tokenColumn="15"/>
                  <CHAR tokenValue="'%valor'" tokenLine="11" tokenColumn="20"/>
                  <OR tokenValue="OR" tokenLine="12" tokenColumn="4"/>
                  <IDENTIFIER tokenValue="COLUNA1" tokenLine="12" tokenColumn="7"/>
                  <IS tokenValue="IS" tokenLine="12" tokenColumn="15"/>
                  <NOT tokenValue="NOT" tokenLine="12" tokenColumn="18"/>
                  <NULL tokenValue="NULL" tokenLine="12" tokenColumn="22"/>
                  <OR tokenValue="OR" tokenLine="13" tokenColumn="4"/>
                  <IDENTIFIER tokenValue="COLUNA2" tokenLine="13" tokenColumn="7"/>
                  <IS tokenValue="IS" tokenLine="13" tokenColumn="16"/>
                  <NULL tokenValue="NULL" tokenLine="13" tokenColumn="19"/>
                  <SEMICOLON tokenValue=";" tokenLine="13" tokenColumn="23"/>
                </SELECT_INTO_STATEMENT_RECOVERY>
              </STATEMENT>
            </STATEMENTS_OR_PRAGMA>
            <END tokenValue="END" tokenLine="14" tokenColumn="0"/>
          </BODY>
        </PLSQL_BLOCK>
        <SEMICOLON tokenValue=";" tokenLine="14" tokenColumn="3"/>
      </PLSQL_BLOCK_WITH_SEMICOLON>
    </PLSQL_BLOCK_MAIN>
  </PL_SQL_COMPILATION_UNIT_NO_EOF>
  <EOF tokenValue="EOF" tokenLine="14" tokenColumn="4"/>
</COMPILATION_UNIT>

Why do AST changes behavior inside anonymous block, functions, procedures, packages?

2

@Welton_Leao_Machado the existence of SELECT_INTO_STATEMENT_RECOVERY means that the parser didn’t recognized your statement. In a PL/SQL context you should use SELECT ... INTO ... instead.

begin
  select * from dual;
end;

ORA-06550: line 2, column 3:
PLS-00428: an INTO clause is expected in this SELECT statement
1 Like