Navigation in Large PL/SQL Code

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:

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.

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.

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.

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.

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.

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.

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.