Oracle DUAL Table

My expertise with databases was learned with Microsoft SQL Server. Over the years I learned how to use other DBMS, but Oracle is the only other enterprise level system I have attempted to learn. Although most mainstream database systems follow ANSI standards, there are several nuances to each one which make can make learning the next one challenging. In my experience, going from MS SQL to Oracle proved to be a steep learning curve. Properly assigning values to variables and using the Oracle DUAL Table was part of that learning curve.

Variables in Oracle vs. SQL Server

One area that is completely different is working with and assigning variables when writing SQL “programs” (TSQL in MS SQL and PL/SQL in Oracle). Using TSQL you can declare a variable using any name you like prefixed with an “@” and followed by a datatype. In PL/SQL you do not prefix variable names with any special characters. In this example I show how to declare and assign a string variable with a value.

Also in Oracle you cannot assign a string directly to a variable like you can in MS SQL. You must “select” the value from the DUAL table which is an automatically generated table in Oracle. More details on the DUAL table can be found here.

In SQL Server
DECLARE @Something VARCHAR(500)
SET @Something = ‘Some Random String’
OR
SELECT @Something = ‘Some Random String’

In Oracle
SpacePath varchar(500)
SELECT ‘Some Random String’ INTO SpacePath FROM dual;

Side Note

I recently revisited old unpublished blog posts and this was one of them. This was initially outlined in September 2017 using Oracle 12c. In January 2021 the details were elaborated and published.

Keyboard Shortcuts in Oracle SQL Developer (and how to change them)

It is quite easy to assign keyboard shortcuts in Oracle SQL Developer to actions in SQL Developer.  I recently configured shortcuts to convert text to all UPPER and all lower case.

It is quite easy to change the keyboard shortcuts in Oracle SQL Developer. Just go to Tools → Preferences and navigate to Shortcut Keys in the left side menu.  On the right there will be a lengthy list of actions to choose from.  For converting to upper/lower case look in the Code Editor category. You will find Convert Selection to Lower and Convert Selection to Uppercase. You can then change the short to the desired key combination.

oracle sql developer settings