Tag: Oracle

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.