Oracle SQL Developer v4.2 Is Now Available

thatjeffsmith SQL Developer 39 Comments

Tell Others About This Story:

Today is the day! We have new releases of both SQL Developer and SQL Developer Data Modeler.

click here for SQLDev 4.2

I’ve been talking about v4.2 tweaks and enhancements for awhile now, and here’s the full list if you want to go back and review.

I’d like to share a few highlights though.

But first, FULL STOP. Go click that big button to the top and left.

Ok, let’s continue.


I’d like to thanks the entire community for all the feedback, especially those of you who provided test code to exercise the new formatter. We made a few changes since the Early Adopter 2 update – tons of bug fixes, and tweaked the options a bit more based on your feedback.

With 4.2 now live in production, if our parser (and accordingly our formatter) doesn’t understand your code, we just won’t format it. In the EA’s we printed comments that showed the code bits we didn’t understand. For v4.2 production you should just see the parser ‘squiggle’ indicating where the code isn’t being interpreted correctly. Please share your code samples with MOS and the community if you think that’s a bug.

Make your code, pretty.

Some things of note:

  • The preferences have moved AND changed from version 4.1. They’re now under the Code Editor page, and should also be simpler to understand now.
  • There’s a ‘simple’ mode, and advanced mode, and an uber-expert mode.
  • In advanced mode, you can format your code by hand, paste it into the preferences, and we’ll auto-detect how you want your code to look going forward.
  • In uber-expert mode, you can tell the formatter exactly what to do to your code. Our developer Vadim has a couple of blog posts showing how to do this with examples.
formatter preferences in oracle sql developer

Type code, as the formatter sees ‘stuff’ it changes the formatting options – these are highlighted in Green so you can see what’s changed.

Instance Viewer

Not a new feature – we first introduced the Instance Viewer in version 4.1. But we now offer a ‘Top SQL’ report. See your expensive queries, and drill down into them to see what’s going on or fix them.

sql developer top sql animated gif

Pretty colors, plus some interesting bits of data.

The licensing prompts on the drill down report let’s you know that the SQL Tuning Advisor and Run time history reports require the tuning and diagnostic packs, respectively. If you don’t click on those reports, then there’s nothing to worry about.

Everything the Instance Viewer hits on the main panel is of the ‘no additional cost’ data dictionary views category.

Easy Peasy Password Resets

No more requiring an Oracle Client to change your database passwords when you’re not already connected. Maybe the most compelling reason to upgrade your end users.

More details here.

right click connection to reset password in oracle sql developer

Yup, this one.

12c Release Two Enhancements

We support big-ole object names now.

You CAN do this. Not sure you SHOULD do this though.

And you can create Analytic Views too!

And the Multitenant option got a lot of upgrades. Among them, Application Containers. We support those too.

Go to the DBA panel for your CDB.

New to 12cR2? Did you know our VirtualBox VM was upgraded to 12.2? Also has APEX 5.1 and ORDS 309 for ya.

Speaking of ORDS…

You can now manage your RESTful Services directly in the database connection tree! This is much easier than setting up your ORDS Dev user connection. You’ll see the new REST item in the tree, and I talk about this here.

oracle rest data services modules

Note the ‘REST Data Services’ item on the tree.

And the Modeler!

I think the biggest two changes of note are the ability to include your diagrams in your HTML and PDF reports AND the ability to now store your repository in GIT.

On the diagrams, you can ask to include them in your reports AND you can just directly print them to HTML/SVG.

File..Print Digram…

And we get..

sql developer data modeler diagram html


Et cetera and potpourii

This marks the end of the SQL Developer version 4.X releases. We’re moving towards a quarterly release schedule to synchronize up with our Database Cloud Services updates. So our next version will start with a 17 – for 2017, then quarter (1,2,3,4), then week.

This release also ships with the fewest known number of bugs…ever…for a SQL Developer release. We hate bugs. So we tried to kill ’em all. I’m sure you’ll find some more. Let us know. We’ll be able to release updates more frequently, getting you bug fixes that much faster.


Tell Others About This Story:

Comments 39

  1. Copying an object name from the Connections panel becomes unavailable in 4.2.
    Not sure it is an intended change or a bug. I have a screen snapshot but I cannot paste it here, so let me describe it. In an example, after I made the database connection, I expand Tables, and a list of table names displayed in the Connections panel. In the old version of SQL developer, if I click a table name and Ctrl-C to copy it, I then can paste this table name to anywhere. In new version 4.2 that we have just upgraded to, this copy/paste stops working. Instead, I have to right click it and open the Edit window and copy the table name from there. Is there an easier way to copy object name in 4.2 ?

    1. thatjeffsmith Post
  2. I’ve been using version 4.2 for a few weeks now and I keep getting an error message saying the database connection has been reset and any pending transactions have been lost.

    This occurs mainly when I attempt to look at any object in the object viewer.

    This doesn’t happen with v4.1.5

    Searches on the web came up with various causes including malware and a suggestion to add AddVMOption
    to the sqldeveloper_nodebug.conf file. The theory being that this is caused by and Out of Bands error in the jdbc driver or on the network. Doing this made no difference.

    I’m running this on Windows 7 64 bit against a local pluggable database (12.1).

    1. thatjeffsmith Post
  3. Hi, I’m using and installed version 4.2.
    I put sysWOW64 into the path so that MSVCR100.dll could be found and … version 4.2 still failed to run.
    Has anyone seen this behavior before?

    1. thatjeffsmith Post
      1. My version is:
        This is my path : Path=C:\Windows;C:\Windows\sysWOW64;C:\Windows\system32;C:\Windows\System32\Wbem;

      2. The OS I’m using is Windows 7 64bit.
        Interestingly, This works on my Windows 10 system

  4. Is it just me or does anyone else have issues with Developer queries crashing when it hits a ‘SELECT’ statement that is commented out with the double dashes –? Removing ‘SELECT’ it runs cleanly. Surround the ‘SELECT’ with /*..*/ also runs cleanly. This is annoying if this is how it works now. It was nice to just do the double dashes that you could drop in or out of the front of the SELECT statement that you wanted for testing.

    1. thatjeffsmith Post
      1. with Vari AS
        to_date(‘2017-04-30’, ‘YYYY-MM-DD’) v_end
        FROM dual
        /*Get all active clients as of month end*/
        actref AS
        cli_per_id AS paris_id
        ,inp_association AS ref_id
        ,trim([email protected](‘TREATREAS’,refe.ref_treat_reason)) AS ref_reason
        ,trim(inp_team) AS team_code
        ,[email protected](inp_staff) as primary_provider
        FROM [email protected]
        INNER JOIN [email protected] ON cli_id = inp_cli_id
        INNER JOIN clgry_sr_rpt.lup_team ON trim(inp_team) = team_code
        LEFT JOIN [email protected] refe ON inp_association = refe.ref_id
        CROSS JOIN Vari
        WHERE team_category IN (‘HC’,’SWCM’) — HC teams – Abram confirmed via email saying SWCM should not be counted in HC counts Dec . There is only 1 active record now. Gets separated out below
        AND inp_alloc_type = ‘MN’ –Primary provider
        AND (inp_from IS NULL OR inp_from = v_end)
        ) –SELECT * FROM actref;
        /*Get the most recent client grouping that occurred from the period onwards*/
        — this gets all client groupings as some active clients may not have a client group
        Client_group AS
        ,TRIM(grp_sub_group) as subgroup
        FROM [email protected]
        WHERE (grp_id, grp_per_id) IN
        MAX(grp_id) max_grp_id,
        FROM [email protected]
        CROSS JOIN Vari
        WHERE grp_group = ‘CG’
        AND grp_from = v_end
        AND (grp_per_id, grp_from) IN
        MAX(grp_from) max_grp_from
        FROM [email protected]
        CROSS JOIN Vari
        WHERE grp_group = ‘CG’
        AND grp_from = v_end
        group by grp_per_id
        GROUP BY grp_per_id
        /* Join the the two subqueries together*/
        HC_active_referral AS
        v_end AS report_month
        ,trim(nam.nam_fore1) as client_fname
        ,trim(nam.nam_surname) as client_lname
        ,trim(clgrp_lup.lcc_desc) as client_group
        FROM actref
        CROSS JOIN vari
        LEFT JOIN client_Group cg ON actref.paris_id = cg.grp_per_id
        LEFT JOIN [email protected] clgrp_lup ON cg.subgroup = trim(clgrp_lup.lcc_code)
        INNER JOIN [email protected] nam ON actref.paris_id = nam.nam_per_id
        ) –SELECT * FROM HC_active_referral;
        /*if the details are required run this lines of code below*/
        –select * FROM HC_active_referral where team_name in (‘HCRT – GENERALIST’,’HOME CARE RESPONSE TEAM’) ;
        –SELECT * FROM HC_active_referral where paris_id = 10130360;
        /*compile the client counts for each team*/
        totals AS
        ,count(*) as total
        FROM HC_active_referral
        GROUP BY
        ) –SELECT * FROM totals;

        /*Final result count*/
        /*Only show the results for the teams that are current teams in the monthly report (‘MR’) */
        ,to_char(v_end,’YYYY-MM’) AS event_ym
        ,v_end as report_month
        ,NVL(total, 0) as total
        FROM clgry_sr_rpt.lup_team ta
        CROSS JOIN vari
        LEFT JOIN totals t ON t.team_code = ta.team_code
        LEFT OUTER JOIN mrt_analysis.DIM_PERIODS_DT_months mths on mths.mn_nam_yr_num = to_char(v_end,’YYYY-MM’)

        WHERE monthly_report_team =’MR’;

        First error is:
        ORA-00928: missing SELECT keyword
        00928. 00000 – “missing SELECT keyword”
        Error at Line: 48 Column: 26
        which is at: ) –SELECT * FROM actref;

      2. thatjeffsmith Post

        >>–SELECT * FROM actref;

        That’s the BUG. Having the ‘;’ in a comment is tripping up the parser. We’ll have this fixed in a patch/update.

  5. Thanks for the new release.
    Hovering on star in order to expand all query columns in the projection does not work any more.
    Bug or feature?

    1. thatjeffsmith Post
      1. Thanks for double checking.
        It works for me in, but not in, nor the … strange …

      2. thatjeffsmith Post

        use the view > Log > Statements panel to debug – you’ll see bg parser queries we run when we ‘see’ your queries and we go out to get the columns.

  6. Is there a way of turning of comparing subprograms. In 4.2 on large packages this seems to be running a lot and stopping me from editing code. Thanks

    1. thatjeffsmith Post

      Preferences – Code Editor – Supported Gutter Navigation…and maybe also Supported Sync Spec and Body

      There were no changes in this area from 4.1.5 to 4.2 though, that I know of.

      1. Thanks Jeff, this is a huge help – I have to use OSD over a very laggy VPN and this is a great optimization!

  7. Hey Jeff,

    Thanks for the new release, I really like the new Formatter options!
    On the other hand,are there any chance you will implement a vim-like keybindings for the Code Editor in the funture? Something similar to the vim plugin for IntelliJ editors.

    Daniel Nyeste

    1. thatjeffsmith Post
      1. Thanks for the quick reply! Yes, I’ve set up and use the external editor function already (thanks to your post), but it’s just … cumbersome.


      2. thatjeffsmith Post
    1. thatjeffsmith Post
      1. Yes, Jeff. Sorry I missed it.
        I still have the problem that sql.bat does not restore the previous codepage.

      2. thatjeffsmith Post
  8. Love the new versions … .apart from the DBA frontpage. I am simply not able to read the page properly ….. with the grey and light blue ? (age 56). Is it possible to custmize the font colors ?

    Almost same problem with Apex 5 …. new and modern responsive …. but not user friendly


    1. thatjeffsmith Post
  9. Thanks for the write up! Is there any walk through on how to use the data modeler with GIT? I have been doing this manually for a while outside of sql developer, and after playing with the new release been unsuccessful in using git inside sql developer.


    1. thatjeffsmith Post
      1. Anyone experiencing bug with linesize?

        show linesize command outputs 1763 but the result displays only 72

      2. Jeff, Are you sure about Modeler and Git? I’ve heard that it is not supported yet as SVN is supported i.e. from within the Modeler’s object comparison dialogues and it is not on the list yet. Are you saying that there is a workaround presenting the Git repository as SVN for the modeler? The only workaround I know is using where the local check out is SVN and the remote repository is Git. If you know a more direct, better workaround, it would be great to hear about the steps to set up.

      3. thatjeffsmith Post

Leave a Reply

Your email address will not be published. Required fields are marked *