Navigation in Large PL/SQL Code

blank

Are you editing large PL/SQL code in SQL Developer? Have you noticed that sometimes you cannot navigate to a declaration anymore? No Ctrl-Click under Windows. No Command-Click under macOS. In this blog post, I explain the reason and how to fix that in SQL Developer 20.2.

What Is Large?

Usually, we define the size of code using common software metrics. Here are some examples:

  • characters,
  • lines,
  • statements,
  • McCabe’s cyclomatic complexity,
  • Halstead volume or
  • maintainability index.

SQL Developer uses the number of lexer tokens. For SQL Developer the magic number is 15000 lexer tokens . This is the so-called parseThreshold. PL/SQL code with 15000 lexer tokens or more is considered large.

Counting Lexer Tokens

Lexer tokens are similar to words. They are used as input for the parser. In fact for parsing some lexer tokens are irrelevant. Whitespace and comments, for instance. Here is an example:

Simple code
begin
   dbms_output.put_line('Hello World!');
end;
/

This code contains the following 11 relevant lexer tokens:

  • begin (IDENTIFIER)
  • dbms_output (IDENTIFIER)
  • . (OPERATION)
  • put_line (IDENTIFIER)
  • ( (OPERATION)
  • Hello World! (QUOTED_STRING)
  • ) (OPERATION)
  • ; (OPERATION)
  • end (IDENTIFIER)
  • ; (OPERATION)
  • / (OPERATION)

I put the token type in parenthesis.

You can run the following Arbori program to print the number of lexer tokens in the SQL Developer console.

Arbori program to count tokens
countTokens:
  [node) sql_statements
  -> {
    var LexerToken = Java.type('oracle.dbtools.parser.LexerToken'); 
    var Token = Java.type('oracle.dbtools.parser.Token');
    var tokens = LexerToken.parse(target.input, false);
    print("Number of tokens: " + tokens.size());
  }

Change parseThreshold Temporarily

The default parseThreshold is 15000. With that value the navigation to dbms_output.put_line is possible.

blank

A link is displayed when you hold down the Ctrl key under Windows or the Command key under MacOS while you move the mouse pointer over a linkable item.

Fortunately, we don’t need to generate a larger code to see what happens when we reach the parseThreshold. We can simply set the parseThreshold to 11 by executing the following command in a separate worksheet. The database connection is irrelevant.

Change parseThreshold to 11
set hidden param parseThreshold = 11;

Now we have to enforce a re-parse. For example by cutting and pasting the code. Afterwards, you should see an empty code outline window.

blank

The parseThreshold has been reached and SQL Developer does not parse the code anymore. As a result, you cannot navigate to the declaration of dbms_output.put_line. You cannot enable the link. SQL Developer needs the parse tree for the navigation. No parse-tree, no navigation.

But it is easy to get it working again. Just remove a token. The / at the end, for instance. Now we have only 10 lexer tokens. A complete code outline is shown and code navigation works again.

Change parseThreshold Permanently

You can configure a script to be executed when opening a connection in SQL Developer.

blank

In this script, you can define a higher threshold value. A magnitude of ten higher than the default value should be sufficient for most cases.

login.sql
set hidden param parseThreshold = 150000;

What’s the Impact of a Higher parseThreshold?

There is no impact, if you work with PL/SQL and SQL code with less than 15000 lexer tokens.

However, if work with larger code the code editor will need more time to open. And of course, it will consume more memory. That’s the price you pay for enabling navigation in large PL/SQL code.

2 Comments

  1. blank Pavel Vetesnik says:

    Thank you very much for this explanation.
    I believe that something has changed between Developer 20.2. and 20.4. I didn’t see this problem in older versions, but after upgrading to 20.4. I got messages “Exceeeded default parse threshold.” (and the “CTRL underline” ability was lost – but the shortcut SHIFT+F4 worked).
    I think I don’t have huge packages.
    Increasing number of tresholds fixed the problem.

    • Thank you Pavel for the heads-up.

      I made some tests and the parameter parseThreshold still works in SQL Developer 20.4.0. However, the events to read this parameter and applying the changed value the background parser is different. From a user-perspective it is a bit delayed. So, to reproduce the example with a too low value for parseThreshold you have to open a new worksheet after changing the parameter. Then the code outline will use the new settings.

      The new popup window that indicates a too low value for parseThreshold is in fact an improvement.

      blank

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.