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





      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)

        ?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:

        d date;
        -- 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;
        • 1. Re: Arbori Code : trying to identify TO_CHAR() with single parameter
          Vadim Tropashko-Oracle


          ( [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




          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:



          ( [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:



          ( [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



          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: