Upgrade to SQL Developer 18.2 ?

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.

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.
sqldev_182_welcome_screen_b

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.

sqldev_182_create_like

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.
    sqldev_182_format_case_only

  • 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.

    sqldev_182_formatter_right_align
    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
    sqldev_182_DBMS_XPLAN
    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'));
    
  • DBMS_XPLAN is the best way to get a nicely formatted execution plan.

  • 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

    sqldev_182_ords_version

  • 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.

    sqldev_182_refactor_expression

    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.

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!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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