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. Whitespace 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.
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
parseThresholdstill 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
parseThresholdyou 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
parseThresholdis in fact an improvement.