SQL-Developer: Create External Tool dialog – Macros (“variables”)

You can use the folloeing commands in “Arguments” and “Run Directory” sections of the “Create External Tool” dialog:

Macro Meaning
${sqldev.conn} DB Connection String
${sqldev.dbuser} DB UserName
${env:var=} Environment Variable
${file.dir} File Directory
${file.ext} File Extension
${file.name} File Name
${file.name.no.ext} File Name Without Extension
${file.path} File Path
${file.url} File URL
${ide.name} IDE Application Name
${ide.classpath} IDE Classpath
${ide.dir} IDE Install Directory
${ide.oracle.dir} IDE Oracle Home Directory
${ide.user.dir} IDE User Directory
${prompt} Prompt
${promptl:=} Prompt with Label
${prop:name=} System Property

SQL-Developer: Activate logging

In case you want to activate logging in SQL-Developer – here is how:

  1. Close all open SQL-Developer instances.
  2. Find the “sqldeveloper.conf” file
    1. Windows:
      1. goto the directory where the SQL-Developer program is installed.
      2. goto the subdirectory “sqldeveloper\bin”
    2. Mac OS X:
      1. goto your applications folder (“/Applications”)
      2. open the SQLDeveloper-App via context menu (“right mouse click”)
      3. goto the subdirectory “Contents/Resources/sqldeveloper/sqldeveloper/bin”
  3. Open the file “sqldeveloper.conf” with the text editor of your choise.
  4. Deactivate the following line by adding a “#” at the beginning:
    #IncludeConfFile sqldeveloper-nondebug.conf
  5. Add a new line with the following text to the file:
    IncludeConfFile sqldeveloper-debug.conf
    AddVMOption -Dcom.methodr.loglevel=all

  6. Save the file.

SQL-Developer: How to prevent the “Java Heap Space” error

When you are working with a lot of worksheets and reports or try to open large SQL files, you may run into “Java Heap Space” errors.

To fix this problem you need to increase the Xmx (maximum heap size).

Here is how:

  1. Close all open SQL-Developer instances.
  2. Find the “sqldeveloper.conf” file
    1. Windows:
      1. goto the directory where the SQL-Developer program is installed.
      2. goto the subdirectory “sqldeveloper\bin”
    2. Mac OS X:
      1. goto your applications folder (“/Applications”)
      2. open the SQLDeveloper-App via context menu (“right mouse click”)
      3. goto the subdirectory “Contents/Resources/sqldeveloper/sqldeveloper/bin”
  3. Open the file “sqldeveloper.conf” with the text editor of your choise.
  4. Add a new line with the following text to the file:
    AddVMOption -Xmx1024M
  5. Save the file.

SQL-Developer: Using macros – Update for version 4.01 ++

[This is a follow up to post SQL-Developer: Using macros]

Has the macro function been dropped?

I got a question from “Bob Thompson“, he asked if the macro function has been removed in SQL Developer version 4.01.
So I tried to use the function on my computer and it still worked.
But then I looked in the preferences (menu “Tools/Preferences”) under “Shortcut Keys”: the macro commands are no loger there!

Dont panik!

The macro feature is still there, what’s missing from version 4.01 on is the ability to add the neccessary shortcut keys via the preferences window.
That’s why it still working on my computer – the update to version 4.01 took over the settings for the shortcut keys.

Workaround

Here is workaround to add the shortcut keys for macro recording and playback manually:

  1. Quit Oracle SQL Developer
    .
  2. Look for the file settings.xml in the following folder:
    %USERPROFILE%\AppData\Roaming\SQL Developer\system4.0.1.14.48\o.ide.12.1.3.2.41.140207.1351\settings.xml
    On some systems it might be in a different place:
    %USERPROFILE%\Application Data\Sql Developer\system4.0.1.14.48\o.ide.12.1.3.2.41.140207.1351\settings.xml
    .
  3. Open the file settings.xml with the text editor of your choice.
  4. Search for the following section in the file:
       oracle/ide/ceditor/keymap/accelerators.xml
          oracle.ide.keyboard.KeyStrokeMap">
             <data>
    
  5. Add the following lines underneath the tag “<data>”:
    <Item class="oracle.javatools.util.Pair">
       <first class="java.lang.String">toggle-macro-recording</first>
       <second class="oracle.ide.keyboard.KeyStrokes">
          <data>
             <Item class="javax.swing.KeyStroke">shift control PERIOD</Item>
          </data>
       </second>
    </Item>
    <Item class="oracle.javatools.util.Pair">
       <first class="java.lang.String">playback-macro</first>
       <second class="oracle.ide.keyboard.KeyStrokes">
          <data>
             <Item class="javax.swing.KeyStroke">control PERIOD</Item>
          </data>
       </second>
    </Item>
    
  6. The final result should look like this:
       oracle/ide/ceditor/keymap/accelerators.xml
          oracle.ide.keyboard.KeyStrokeMap">
             <data>
                <Item class="oracle.javatools.util.Pair">            
                   <first class="java.lang.String">toggle-macro-recording</first>
                   <second class="oracle.ide.keyboard.KeyStrokes">
                      <data>
                         <Item class="javax.swing.KeyStroke">shift control PERIOD</Item>
                      </data>
                   </second>
                </Item>
                <Item class="oracle.javatools.util.Pair">
                   <first class="java.lang.String">playback-macro</first>
                   <second class="oracle.ide.keyboard.KeyStrokes">
                      <data>
                         <Item class="javax.swing.KeyStroke">control PERIOD</Item>
                      </data>
                   </second>
                </Item>
    
  7. Save the file.
    .
  8. Restart Oracle SQL-Developer and use the macro function as describe here:
    SQL-Developer: Using macros

 

Additional informations can be found in these OTN Community posts:

SQL-Developer: Copy result grid cells (columns) with headers

We will use the following Select during this post:

SELECT c.owner, c.table_name, c.column_name, c.data_type 
  FROM ALL_TAB_COLUMNS c 
 WHERE table_name = 'ALL_TAB_COLUMNS'
   AND c.column_name like 'DATA%';

Here is the result:

CopyColumns_01

Select certain cells in the result grid

Before we can copy cells in the result grid, we need to select them:

  1. click (left mouse button) inside the first cell of the grid you want to copy
  2. hold down the left mouse button and move your mouse until all cells you want are selected (marked)
  3. release the left mouse button

CopyColumns_Selected

Select all cells in the result grid

If you want to select all cells in the result grid

  1. click somewhere in the result grid (left mouse button)
  2. press CTRL+a to select all cells

Copy cells from the result grid

Select the cells you want to copy and press CTRL+c.
Go to the program of your choice, I will use Excel here, and press CTRL+v:

CopyColumns_Exel

Copy cells with headers from the result grid

Select the cells you want to copy and press CTRL+Shift+c.
Go to the program of your choice, again Excel here, and press CTRL+v:

CopyColumns_Exel_Header

SQL-Developer: How to change the date format of the result grid – “Why does my date column have no time?”

You can decide how SQL-Developer display date and timestamp columns.

  1. Go to the “Tools” menu and open “Preferences…”
  2. In the tree on the left open the “Database” branch and select “NLS”
  3. Now change the entries “Date Format”, “Timestamp Format” and “Timestamp TZ Format” as you wish!

ISO formated output with date and time

  • Date Format: YYYY-MM-DD HH24:MI:SS
  • Timestamp Format: YYYY-MM-DD HH24:MI:SSXFF
  • Timestamp TZ Format: YYYY-MM-DD HH24:MI:SSXFF TZR

Image

The result looks like this:

Image

German format for date and time

  • Date Format: YYYY-MM-DD HH24:MI:SS
  • Timestamp Format: YYYY-MM-DD HH24:MI:SSXFF
  • Timestamp TZ Format: YYYY-MM-DD HH24:MI:SSXFF TZR

Image

The result looks like this:

Image

Just choose any format you like.

Date format elements

Element Description
Punctuation and quoted text is reproduced in the result.
/
,
.
;
:
“text”
AD AD indicator with or without periods.
A.D.
AM Meridian indicator with or without periods.
A.M.
BC BC indicator with or without periods.
B.C.
CC Century.
SCC
If the last 2 digits of a 4-digit year are between 01 and 99 (inclusive), then the century is one greater than the first 2 digits of that year.
If the last 2 digits of a 4-digit year are 00, then the century is the same as the first 2 digits of that year.
For example, 2002 returns 21; 2000 returns 20.
D Day of week (1-7). This element depends on the NLS territory of the session.
DAY Name of day.
DD Day of month (1-31).
DDD Day of year (1-366).
DL Returns a value in the long date format, which is an extension of the Oracle Database DATE format, determined by the current value of the NLS_DATE_FORMAT parameter. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format ‘fmDay,Monthdd,yyyy’. In the GERMAN_GERMANY locale, it is equivalent to specifying the format ‘fmDay, dd.Month yyyy‘.
Restriction: You can specify this format only with the TS element, separated by white space.
DS Returns a value in the short date format. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format ‘MM/DD/RRRR‘. In the ENGLISH_UNITED_KINGDOM locale, it is equivalent to specifying the format ‘DD/MM/RRRR‘.
Restriction: You can specify this format only with the TS element, separated by white space.
DY Abbreviated name of day.
E Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
EE Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
FF [1..9] Fractional seconds; no radix character is printed. Use the X format element to add the radix character. Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. If you do not specify a digit, then Oracle Database uses the precision specified for the datetime data type or the data type’s default precision. Valid in timestamp and interval formats, but not in DATE formats.
Examples: ‘HH:MI:SS.FF’
SELECT TO_CHAR(SYSTIMESTAMP, ‘SS.FF3’) from DUAL;
FM Returns a value with no leading or trailing blanks.
See Also: Additional discussion on this format model modifier in the Oracle Database SQL Language Reference
FX Requires exact matching between the character data and the format model.
See Also: Additional discussion on this format model modifier in the Oracle Database SQL Language Reference
HH Hour of day (1-12).
HH12
HH24 Hour of day (0-23).
IW Week of year (1-52 or 1-53) based on the ISO standard.
IYY Last 3, 2, or 1 digit(s) of ISO year.
IY
I
IYYY 4-digit year based on the ISO standard.
J Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers.
MI Minute (0-59).
MM Month (01-12; January = 01).
MON Abbreviated name of month.
MONTH Name of month.
PM Meridian indicator with or without periods.
P.M.
Q Quarter of year (1, 2, 3, 4; January – March = 1).
RM Roman numeral month (I-XII; January = I).
RR Lets you store 20th century dates in the 21st century using only two digits.
RRRR Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you do not want this functionality, then enter the 4-digit year.
SS Second (0-59).
SSSSS Seconds past midnight (0-86399).
TS Returns a value in the short time format. Makes the appearance of the time components (hour, minutes, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE initialization parameters.
Restriction: You can specify this format only with the DL or DS element, separated by white space.
TZD Daylight saving information. The TZD value is an abbreviated time zone string with daylight saving information. It must correspond with the region specified in TZR. Valid in timestamp and interval formats, but not in DATE formats.
Example: PST (for US/Pacific standard time); PDT (for US/Pacific daylight time).
TZH Time zone hour. (See TZM format element.) Valid in timestamp and interval formats, but not in DATE formats.
Example: ‘HH:MI:SS.FFTZH:TZM’.
TZM Time zone minute. (See TZH format element.) Valid in timestamp and interval formats, but not in DATE formats.
Example: ‘HH:MI:SS.FFTZH:TZM’.
TZR Time zone region information. The value must be one of the time zone region names supported in the database. Valid in timestamp and interval formats, but not in DATE formats.
Example: US/Pacific
WW Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
X Local radix character.
Example: ‘HH:MI:SSXFF’.
Y,YYY Year with comma in this position.
YEAR Year, spelled out; S prefixes BC dates with a minus sign (-).
SYEAR
YYYY 4-digit year; S prefixes BC dates with a minus sign.
SYYYY
YYY Last 3, 2, or 1 digit(s) of year.
YY
Y

SQL-Developer: Speed up program start

Starting SQL-Developer takes too long?

Here’s what you can do:

1. Use native look and feel

  1. Select “Tools/Preferences” from the main menu.
  2. Select “Environment” in the list on the left.
  3. Set “Look and Feel:” to the operation system you use.

2. Disable unneeded extensions

  1. Select “Tools/Preferences” from the main menu.
  2. Select “Extensions” in the list on the left.
  3. Unselect all extensions that you don’t need. Look for unneeded “Migrations”, “Spatial”, “TimesTen” and others.

Here’s my current configuration:

3. Optimize your virus scanner

Some virus scanners are slowing down the start of SQL-Developer by several minutes!
The reason for this is that SQL-Developer is a Java based program and consists of several large “.jar”-archives, which are just “.zip”-archives with another ending. When SQL-Developer gets started, several “.jar”-archives are accessed and some virus scanners will scan the whole “.jar”-archives, even when only one small file in it is accessed.

There is no easy workaround but here are some options:
(What ever you choose may affect your security – so be careful!)

  • Add the directory where you have installed SQL-Developer from virus scans.
  • Optimize your virus scanner to not always scan this directory.
  • Optimize your virus scanners handling of “.jar”-archives/Java programs.