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 tips: Failsave Inserts – never mix up column orders again!

Imagine you have an Insert statement like this:

INSERT INTO target_table
    (measure_id,
     value_01,
     value_02,
     value_03,
     value_04,
     value_05,
     value_06,
     value_07)
SELECT load_id * 100 AS measure_id,
       val_1 AS value_01,
       CASE
           WHEN val_2 < 10 THEN 1
           WHEN val_2 < 100 THEN 2
           WHEN val_2 < 1000 THEN 3
           ELSE 4 
       END  AS value_02,
       val_3 AS value_03,
       val_4 AS value_04,
       CASE
           WHEN val_5 = 1 THEN 1
           WHEN val_5 = 2 THEN 20
           WHEN val_5 > 2 THEN 300
           ELSE null
       END AS value_05,
       CASE
           WHEN val_5 IN (1, 5, 7, 9, 11) THEN 21
           WHEN val_5 IN (2, 3) THEN 22
           WHEN val_5 IN (4, 6, 8, 10) THEN 23
           ELSE 24
        END AS value_06,
       val_6 * 100 + val_1 AS value_07
  FROM measure_table
 WHERE load_day > trunk(sys_date);

A common mistake

I have already added the names of the target columns, but it is still hard to make sure that all columns in the Select are at the right position!

Now I have added a new row  but made a mistake!
Do you see what went wrong?

INSERT INTO target_table
    (measure_id,
     value_01,
     value_02,
     value_03,
     value_04,
     value_05,
     value_06,
     value_07,
     value_08)
SELECT load_id * 100 AS measure_id,
       val_1 AS value_01,
       CASE
           WHEN val_2 < 10 THEN 1
           WHEN val_2 < 100 THEN 2
           WHEN val_2 < 1000 THEN 3
           ELSE 4 
       END  AS value_02,
       val_3 AS value_03,
       val_4 AS value_04,
       CASE
           WHEN val_5 = 1 THEN 1
           WHEN val_5 = 2 THEN 20
           WHEN val_5 > 2 THEN 300
           ELSE null
       END AS value_05,
       CASE
           WHEN val_5 IN (1, 5, 7, 9, 11) THEN 21
           WHEN val_5 IN (2, 3) THEN 22
           WHEN val_5 IN (4, 6, 8, 10) THEN 23
           ELSE 24
        END AS value_06,
       val_6 * 100 + val_1 + val_02 AS value_08,
       val_6 * 100 + val_1 AS value_07
  FROM measure_table
 WHERE load_day > trunk(sys_date);

I got confused and added the new line above the column for value_07. I should have put it below!
Now the values meant for column value_08 will be inserted into column value_07, and vice versa.

 

Solution

Here is a simple but effective solution, that prevents mistakes like that:

INSERT INTO target_table
    (measure_id,
     value_01,
     value_02,
     value_03,
     value_04,
     value_05,
     value_06,
     value_07,
     value_08)
SELECT measure_id,
       value_01,
       value_02,
       value_03,
       value_04,
       value_05,
       value_06,
       value_07,
       value_08
  FROM (SELECT load_id * 100 AS measure_id,
               val_1 AS value_01,
               CASE
                   WHEN val_2 < 10 THEN 1
                   WHEN val_2 < 100 THEN 2
                   WHEN val_2 < 1000 THEN 3
                   ELSE 4 
               END  AS value_02,
               val_3 AS value_03,
               val_4 AS value_04,
               CASE
                   WHEN val_5 = 1 THEN 1
                   WHEN val_5 = 2 THEN 20
                   WHEN val_5 > 2 THEN 300
                   ELSE null
               END AS value_05,
               CASE
                   WHEN val_5 IN (1, 5, 7, 9, 11) THEN 21
                   WHEN val_5 IN (2, 3) THEN 22
                   WHEN val_5 IN (4, 6, 8, 10) THEN 23
                   ELSE 24
                END AS value_06,
               val_6 * 100 + val_1 + val_02 AS value_08,
               val_6 * 100 + val_1 AS value_07
          FROM measure_table
         WHERE load_day > trunk(sys_date));

I added an outer Select around the values Select and copied the column list from the Insert part right to the outer Select.
Just copy and past – no misplacement possible!

The inner Select still has to deliver all the columns, but now even with the right names assigned.
The order of the columns in the inner Select is now without any meaning, so my “misplacement” is fixed.