Skip to main content

SQL Developer tips 2/3

T

This is a followup for part 1 which was mainly about setting up the SQL Developer interface for more efficient work. In this part I would like to cover tips for developers.

Shortcuts mentioned in these articles might not be the default ones. My bonus tip on shortcut is: if you can't remember shortcut, then change it to what you find intuitive.


Editor

First weird thing I see all the time is missing line numbers. It is so much easier to navigate through the code especially and cooperate with others when you actually can use line numbers as references. Turn that on in Code Editor - Line Gutter. Also you can quickly jump to specific line by pressing [ctrl+g] (like goto).

I use text transformations a lot. Keywords to upper case, identifiers to lowercase. I try to setup auto format as close as possible to my desired look, but mainly I use it just to format legacy code to understand it better. You can download my beautifier setup at the end of this article. To quickly fix something you have several options:

  • uppercase everything [ctrl+shift+u]
  • lowercase everything [ctrl+shift+l]
  • toggle between cases [ctrl+u] (with keyword/identifier split)
  • auto format selected code [ctrl+shift+f]

You can toggle comments [alt+backslash] which is also handy feature.

For long procedure or a package you can hide blocks on a line gutter (the thing with line numbers), just look for [-] marks close the the line gutter.

You can [control+click] to object name in worksheet and that will get you to the object content (but this sometimes don't work). So I like to setup a shortcut [ctrl+shift+d] to describe object under the cursor in a popup. Very useful for tables and views.

There are many interesting options when you right click in the worksheet. I love Refactoring and switch between Oracle and ANSI joins. A clever way how to get rid of the old joins.

I covered bookmarks in previous article, but there is another way how you can edit/see same document on multiple places at the same time. Right click on the tab with document and pick Split Vertically. Now you can be at two places. Click on Unsplit when done. Also a nice overview of bookmark actions is under the menu Navigation.


Cursors

Very powerful and underutilized is the multiple cursor feature. You can actually have multiple cursors t multiple rows and columns and then you can perform text corrections on all of there cursors at once. To turn this on/off press [ctrl+b] (or use menu Edit - Block Selection). Then you hold [shift+down/up] to add a next/prev line. Or you can click with mouse on specific place while holding [ctrl+shift].

You can also:

  • duplicate current line [alt+shift+d]
  • remove/delete current line [alt+shift+x]
  • add new line (indented) [alt+shift+n]
  • join with following line [alt+shift+j]
  • move line up/down [alt+shift+up/down]
  • delete rest of the line [alt+shift+z] (extra cool with multi cursor)


Code Templates vs Snippets

There is a huge difference between snippets and templates. Snippets allows you to drag and drop the syntax for specific function and you can create your own list of snippets. The issue I have is that there is no "All" category, no shortcuts, no double click on snippet to use it.

So I prefer templates, which you have to setup at Preferences - Code Editor - Code Templates. When you create a template called "func", then anytime you wrote a "func" in editor and press [ctrl+space] it will replace it with the template content. Neat. This way you save a lot of time, don't forgot things and the code will be more consistent.

I have for example these templates:

  • "func" for CREATE FUNCTION - keep PRAGMA UDF ready, exception block
  • "proc" for CREATE PROCEDURE - include Logger and exception handling
  • "sel" for SELECT from table
  • "ins"/"upd" for INSERT/UPDATE table
  • "with" for WITH clause - to easily access APEX items in views
  • "out" for DBMS_OUTPUT.PUT_LINE
  • "for" for FOR loop
  • "exc" for EXCEPTION block
  • "raise" for RAISE_APPLICATION_ERROR
  • "list" for LISTAGG
  • "order" for ORDER BY 1 FETCH FIRST 1 ROWS ONLY


Objects vs files

Normally you open objects from the Object Browser and you would edit them. Some types are slightly problematic, like views. When you edit view, you don't really get the full editor and the wole popup dialog limits you a lot. So if I have to edit views this way, I right click the view and choose Quick DDL - Save to Worksheet and I edit it there.

The way I usually work with on a project is that I create a connection to this project, list it in a folder. I also connect to the GitHub/BitBucket or I create a new repo. Now to the relevat part, I connect to this database through the OPY tool to basically dump the database objects into files. Then I work with these files. When I need to edit a view, I find the file and work on that file. When I am done, I commit my changes. So I rarely edit the objects from through the Object Browser.

The file approach has an interesting side effect. For example with packages, it depends on your file extension. When I have package body saved as package.sql, then it opens as a normal worksheet. If I have it as package.pkb, then it opens as a package with all pros and cons. So if you are using using the files, pick the file extension which suits you more.


Setup files

I have created a GitHub repository where you can get my setup files with the things I wrote about.


Tags:

Comments

Post a Comment