Why upgrade to SQL Developer 18?
This post intents to help you with the decision, if it is about time to upgrade. And if you are on the new version already, then you might find out about some new features that you might not have noticed so far.
I recently upgraded from version 17.3 to 18.2.
Here is a listing of my favourite changes. This listing includes new features, changed behaviours but also fixed bugs in either version 18.1 or 18.2. It is totally subjective. The focus is on SQL Developer only, I didn’t analyze changes regarding SQL Data Modeler, sqlcl or ORDS.
TL;DR;
Upgrade Now? => Yes!
Links
If you want you can go through the various documents of new features and bugfixes yourself.
- SQL Developer New Features 18.1
- SQL Developer Bugfixes in 18.1
- SQL Developer New Features 18.2
- SQL Developer Bugfixes in 18.2
- SQLcl Release Notes 18.1
- SQLcl Release Notes 18.2
- Data Modeler Release Notes 18.1
- Data Modeler Bugfixes 18.1
- Data Modeler Release Notes 18.2
- Data Modeler Bugfixes 18.2
Oracle SQL Developer
SQL command line
Oracle SQL Data Modeler
Top new features or bugfixes
New Welcome Page
It is very likely you disabled the welcome page in a previous version. Go to Help/Start Page to show it. Then you can decide to keep it around by checking the appropriate check box in the lower left corner.
In the past this welcome screen was essentially useless. Now it has a section “RECENT” connections. It holds the list of the last five recently opend connections which I find myself using a lot.
The welcome screen still has a lot that can be improved (in red is what I dislike). But now it is a working and useful part of the tool.
Also interesting is the DEMO tab in the “Getting Started” section. Among others it links to videos about “Debugging PLSQL”, “Importing Data from Excel” and “Introduction to Oracle SQLcl”.
Improved Editor / Syntax Highlighting
Syntax Highlighting is arguably one of the most crucial features of any IDE.
A lot of bugfixes have been done for that.
Here is a quick list of changes/bugfixes with regards to the editor or syntax highlighting. The list is not complete.
- Improved Arbori Editor:
- syntax error highlighting
- catch syntax errors before Arbori execution
- Change Case as You Type was removed from the Code Insight Preferences page. Use the Code Formatter to set your identifier and keyword preferences, and format your code on demand instead.
- SQL Injection vulnerability warnings for your PL/SQL
- 27678178 PARSER DOESN’T LIKE NEW FOR CALLING A USERDEFINED CONSTRUCTOR IN A SQLBLOCK
- 27718434 FORUM: SOME KEYWORDS ARE HIGHLIGHTED ONLY WHILE TYPING
- 27803006 FORUM: BRACES () COLORING IS INTERMITTENT AND DIFFERENT FROM PREFERENCES
- 27939025 FORUMS: BETTER PL/SQL SYNTAX HIGHLIGHTING/FORMATTER FOR CONDITIONAL COMPILATION SYNTAX
Since I use conditional compiling a lot in PLSQL the last fix is an essential one for me.
SQL Injection detection is a very promising feature. Especially when working with not so experienced developers. They easily might overlook some SQL injection issue and now the editor is able to warn about such scenarios. Jeff explains more about it here.
Btw: “Arbori” refers to the language used to describe parse trees during syntax analysis. Want to know more?
DBA Users Editor – SQL page now generates complete DDL for user including GRANTS
Way overdue. Happens a lot that you want a script to create the same schema that you have in some DEV environment and now you want to script the user creation for TEST and potentially PROD environments.
In the past the grants had been missing. A quick test of mine showed that all the grants that this schema needs seem to be there now. Granted roles, object privs, granted access to directory objects, granted system privileges. However grants that are given to other users are not included! So it is still not complete.
And remember, there also is a “create like” feature in the DBA panel.
This “create like” has not all the grants that are on the DBA Users Editor SQL Page.
27169586 ALTER SCRIPT SCROLLS OFF PAGE, IMPOSSIBLE TO READ WITHOUT MUCH SCROLLING/RESIZE
I’m not sure, but I think I hit this bug very often in the past. And then it was annoying as hell. However we can’t access the bug numbers, so I’m not sure if that fix covers the problematic behaviour that I remember. At least it looks as if it is fixed now. Will monitor it closely.
Improved Code Formatter
I’m not a big fan of code formatters. But I know those matters to many developers. The bugfix lists are full of minor changes to it.
Two things I would like to point out:
- Formatter: New Basic Preference: Convert Case Only
This new setting means, the code will not be reorganized. Only the UPPER/lower case logic is applied. I personally like to have everything in lowercase (apart from some plsql method names where I use lowerCamelCase). So this setting allows me to quickly format SQL statements in the worksheet.
- FORUM – ADDED FORMATTER OPTION: RIGHT-ALIGN QUERY KEYWORDS
I know that many developers like to format their SQL queries in such a way, that the keywords are right alined. So this is possible now.
Example Result-- left alined keywords select containerid ,status ,reason ,additional_info from table1 t1 left join table2 t2 on t1.id = t2.id where barcode = :barcode;-- right alined keywords select containerid ,status ,reason ,additional_info from table1 t1 left join table2 t2 on t1.id = t2.id where barcode = :barcode;
Interesting new features or bugfixes
The following features are less deciding. So they might not be the sole reason why you want to upgrade. However they might be useful to know and to try out eventually.
- Can now convert Oracle to ANSI Joins with context menu
This feature sounds very promising. Unfortunatly I didn’t find the context menu yet, that allows to do so. So far I tried it only on a 11.2.0.4 db. Maybe the option uses functionality that requires a higher db version.
- DBMS_XPLAN Added to Explain Drop Down toolbar menus
The new option does not create a plan immediatly. Instead it finds the sql_id and then produces a statement that uses the sql_id and then can read the execution plan using DBMS_XPLAN.select * from table(dbms_xplan.display_cursor( sql_id=>'d7yr3jw0rj963', format=>'ALLSTATS LAST')); - Enhanced DB Search for dependencies and selected object types
It is not clear what was enhanced. The search menu certainly looks the same. Also the search findings didn’t really seem to change.
In case you don’t know, but search does cover view sources. This was already implemented a few versions ago.
I use the DB Search a lot. And I welcome any improvements in that area, even if I don’t see them at first glance.
- ORDS 18.2 bundled
Tools/REST Data Services
- Extract variable/function prototype
Refactoring code is something that SQL Developer supports since many versions now. It is surprising that almost no developers know about it or even use that frequently.
This new option allows to quickly change expressions into local functions. This can save an aweful lot of codeing time!
Example
Somewhere inside my plsql code I have a concatenation.
... begin ... if v_json is not null then p_plate.additional_info := '{'||v_json||'}'; end if; ...I mark the
'{'||v_json||'}'part, open the right context menu, choose Refactoring / Extract Variable/Function/CTE and give the function a new name, for example make_json_object.
The result is that my procedure got a local function. And the marked expression is replaced with a call to that function.
... --refactored function function make_json_object(v_json clob) return varchar2 is begin return '{'||v_json||'}'; end make_json_object; begin ... if v_json is not null then p_plate.additional_info := make_json_object(v_json); end if; ...The code would still require some rework, but it is a nice quick starting point.
As always you can undo that change using CTRL-Z. - Allow explain/autotrace on selected text
This I like a lot. You can mark a part of a bigger sql statement and get the explain plan for that part only. The marked code needs to be a valid SQL statement of cause.
Highly useful for with clause statements. But also for expanded SQL queries.
- 27962558 DRAG AND DROP FROM GRID TO EDITOR SHOULD QUOTE STRINGS
The behaviour for drag and drop changed. I’m not so convinced about it, but I rarely use drag and drop from the grid.
If we drag from a single varchar2 column, then all the values are copied, put in single quotes and separated by comma.
However if we copy from multiple columns then the strings are not quoted.
The quoteing only happens during drag and drop. Not when copying with CTRL+C, CTRL+V.I think this feature can be highly helpful, but needs some getting used to it.
DBMS_XPLAN is the best way to get a nicely formatted execution plan.
Conclusion
There are several more changes, but those are the ones I am most interested in. As with any new toy we should check what is possible and how we can use it to improve our playing codeing experience.
Several of the new features are so convincing to me that I installed SQL Developer 18.2 now in all projects that I supervise.
Feel free to share what new features or bugfixes you like most!
