Scripts for my DOAG 2014 presentation “1000 mal schneller – Praxisgeschichten aus der Oracle-Datenbankwelt”

Thank you very much for attending my presentation “1000 mal schneller – Praxisgeschichten aus der Oracle-Datenbankwelt”!
(English: “1000 times faster – Real life stories from the world of Oracle Databases”)

You can download the scripts i used to simulate the shown cases here:

I will speak at the “DOAG 2014 Conference + Exhibition” (“DOAG 2014 Konferenz + Ausstellung”)

I will be on the “DOAG 2014 Conference + Exhibition” in Nürnberg (Nuremberg), Germany from November 18th to 20th.

I will speak at November 19th, at 4 pm at room “Shanghai”:

1000 mal schneller – Praxisgeschichten aus der Oracle-Datenbankwelt

(English: “1000 times faster – Real life stories from the world of Oracle Databases”)

 

The DOAG is the largest German Oracle user group with over 5000 members.

2014-08-04-DOAG-2014-Konferenz-Banner-Speaker-Highlighnen-180x150

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

Oracle Advanced Queuing Series: 1. Create Message Type, AQ Table and Queue

Welcome to the first part of the series “Oracle Advanced Queuing”.

We start with the basic elements of Oracle Advanced Queuing:
the message type, the queue table and finally the queue.

  1. Create a Type for your messages:
    CREATE TYPE my_queue_typ  AS OBJECT(message_text VARCHAR2(4000));
  2. Create an AQ Table based on your type:
    EXEC DBMS_AQADM.CREATE_QUEUE_TABLE('my_queue_table', 'my_queue_type');
  3. Create a Message Queue with your table:
    EXEC DBMS_AQADM.CREATE_QUEUE('my_queue', 'my_queue_table');
  4. Start the queue:
    EXEC DBMS_AQADM.START_QUEUE('my_queue');