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.
Usually, we define the size of code using common software metrics. Here are some examples:
SQL Developer uses 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 are considered large.
Lexer tokens are similar to words. They are used as input for the parser. In fact for parsing some lexer tokens are irrelevant. Whitespaces and comments, for instance. Here is an example:
This code contains the following
11 relevant lexer tokens:
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.
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());
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
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.
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.
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;
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.