Skip to Main Content
Forums

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Arbori: can we match a string_literal to a specific value?

Mike KutzMay 30 2019 — edited May 30 2019

I know we can identify if a token is a string literal.

But, can we identify if that string literal has a specific value?  How?

goal:

I want to highlight the 2nd parameter of TO_DATE if and only if the value is 'DD-MON-RR'.

(Preferred goal:  match against a regular expression)

I can identify the second parameter but I can't figure out how to match it to 'DD-MON-RR'.

TwoDigitYear:  -- modified from https://community.oracle.com/thread/4272273
( [td^) function_call
| [td^) datetime_literal
| [td^) function_expression
) & ?td = 'TO_DATE'
& [node) string_literal
&(-- for function_call
  ( -- case 1 : node is identified as part of an arg_list
      (    [node^) arg_list | [node^^) arg_list    )
    & (    [node^^) paren_expr_list | [node^^^) paren_expr_list )
    & ! [node-1-1) arg_list
  )
| ( -- case 2 : node is identified as part of something else
   [node-1) ',' & [node-1-1) string_literal & [node-1-1-1) '('
  )
)
& td^ < node
-- ?node = '''DD-MON-RR''' -- did not work
;

Test Code

declare
    d date;
begin
    d := to_date( '12:12:45', 'HH24:MI:SS' );
    d := to_date( '5-may-19', 'DD-MON-RR' );
    d := to_date( abc, 'HH24:MI:SS' );
    d := to_date( defg, 'DD-MON-RR' );
    d := to_date( f('xyh'), 'DD-mon-yyyy', 'nls setting' );
    d := to_date( f('xyh'), 'DD-MON-RR', 'nls setting' );
    commit;
end;
/

Thanks

MK
This post has been answered by Vadim Tropashko-Oracle on May 30 2019
Jump to Answer

Comments

Answer

First, let me correct the solution from previous exchange, because identifying a single argument does't really requires ancestor-descendant relation. You example

d := to_date( f('xyz'), 'DD-mon-yyyy', 'nls setting' );

is false positive, because 'xyz' is a single argument which is descendant of to_date(...). The corrected query is:

WrongToDateNumOfArgs:

( [node^) function_call

| [node^) datetime_literal

| [node^) function_expression

) & ?node = 'TO_DATE'

& ([arg) expr

| [arg) string_literal

| [arg) pls_expr )

& arg^-1 = node

& [arg+1) ')'

& [arg-1) '('

;

syntax.png

It is only when we have unknown number of arguments, so that those arguments can be positioned at arbitrary depth in the parse tree, then we need ancestor descendant.

Second, the failure to match string literal is a bug, for example the predicate ?node = '''5-may-19''' should find the three ocurrencies in the pl/sql snippet above. Fixed for 19.2.

Next, a significant Arbori amendment is coming in 19.2, where you would be able to add query post processing rules in java script. It is technically implemented via java nashorn engine; so for each tuple you can analyse its fields and leverage either JS or Java regular expression facilities.

Finally, to answer your question "Can we have more than 3 syntax highlighting rules?" In 19.2 the syntax colorizing functionality has been reorganized, so that you can specify any number of rules. As soon as the release is out I will write an article how to do that (together with description of JS nashorn amendment).

Marked as Answer by Mike Kutz · Sep 27 2020
1 person found this helpful
Mike Kutz

Finding potential usage of 2-digit years (through a Regular Expression) would be an excellent example of using the post processing feature.

It would make EdStevens happy (    )

Many thanks for the help!

MK

1 - 2

Post Details

Added on May 30 2019
#sql-developer
2 comments
540 views