1 Reply Latest reply on May 29, 2019 5:32 PM by Vadim Tropashko-Oracle

    Arbori Code : trying to identify TO_CHAR() with single parameter

    Mike Kutz

      I'm trying to develop some arbori code in order to highlight "bad usage of TO_DATE()"

       

      For my first case (of "bad usage"), I'm trying to highlight all calls to TO_DATE() that have only a single parameter.  My test is currently narrowed down to a string_literal.

       

      The first problem I ran into was "how do you identify all instances of TO_DATE?".

      I can pick out one call to TO_DATE but I can't pick out all of them.  What is the correct node classification I should be using?

       

      The second problem was trying to identify the TO_DATE as having a single parameter.  (i've simplified my Use Case to search for string_literals only)

      Some how I keep screwing this one up.

      Where is my logic going wrong?

       

      Many thanks

       

      MK

       

       

      In the Arbori Editor, I've tried variations of the following:  (yes, the "->" line is missing for "highlighting code" but I'm trying to get this correct)

      PlSqlCustom3:
        ?name = 'to_date'
      & op^^ = node
      & name^ = node
      & [op) '('
      & [node) function_call
      
      
      --& ( [pel) paren_expr_list
      --& pel^ = node
      --& name+1 = pel
      --& [op) '('
      --& op^ = pel
      --& op^^ = node )
      
      
      --& op^^ = node
      --& [sl) string_literal
      --& [cp) ')'
      --& op+1 = sl
      --& sl+1 = cp
      ;
      

       

       

       

      My test PL/SQL code:

      declare
        d date;
      begin
        -- identifies as a function_call
        d := to_date( '5-may-19' );
      
        -- identifies as a datetime_literal
        -- should not highlight (unless Arbori can identify the usage of RR)
        -- I don't think Arbori can identify string literals that contain a specific text string, but highly desired
        d := to_date( '5-may-19', 'dd-mon-rr' );
      
        -- identifies as user_defined_function
        select to_date( '5-may-19') into d
        from dual;
      end;
      
      
      
        • 1. Re: Arbori Code : trying to identify TO_CHAR() with single parameter
          Vadim Tropashko-Oracle

          PlSqlCustom3:

          ( [node^) function_call

          | [node^) datetime_literal

          | [node^) function_expression

          ) & ?node = 'TO_DATE'

          & ([arg) expr

          | [arg) string_literal

          | [arg) pls_expr )

          &  node^ < arg

          & [arg+1) ')'

          & [arg-1) '('

          ;

           

          To work out a problem like this it is better to start with expanded parse tree in the structure panel

           

          datetime_literal_parse_tree.png

           

          Witness the parse tree branches of interest highlighted in orange. Apparently, the parser hasn't been consistent, and in all three TO_DATE calls has been recognized differently. This is why we have to use disjunction. Our first query attempt then is:

           

          AllFunctionCalls:

          ( [node) function_call

          | [node) datetime_literal

          | [node) function_expression

          ) ;

           

          Next, we want only TO_DATE calls which, and the function call name is the child of the node that we just have identified. At this point we could have introduced another attribute, but I have chosen to reidentify the node with TO_DATE, and make the parent to be a function call:

           

          AllToDateCalls:

          ( [node^) function_call

          | [node^) datetime_literal

          | [node^) function_expression

          ) & ?node = 'TO_DATE'

          ;

           

          Next, we have to introduce the second attribute -- arg, and, again, the grammar payload is different in 3 cases. More important, how to connect these two attributes, the node and the arg? It is ancestor-descendant relation, more specifically the closest ancestor-descendant. It is described at page 12 of

          https://vadimtropashko.files.wordpress.com/2019/05/arbori-1.pdf

           

          Actually, since we renamed the node to be the function call name only, it is the parent -- node^ -- which is the ancestor of the arg.

           

          The final condition is that a single argument of the TO_DATE function call would have the open and closed parenthesis as sibling parse tree nodes. The result:

          datetime_literal_syntax_colors.png