My new job involves working with Oracle as well as SQL Server. Since I come from a SQL Server background, I had some ramping up to do to get up to speed on the differences with Oracle. I wanted to document some of the things I ran across or things that tripped me up. I’m sure there are many more things I’ll run across, but here are some basics to start.
Oracle SQL Developer is a popular IDE to connect to Oracle databases. It’s free to download.
By default, any query will return the first 50 records.
Concepts and Terminology:
Oracle uses some of the same terms as SQL Server, but uses them in a different sense. Here are some of the Oracle usages.
Database – Collection of physical files (data)
Schema roughly matches to a SQL Server Database – contains database objects
Instance – what the user connects to – manages files
A User has the same name as the schema it owns. The User is assigned permissions for the database objects.
One big difference is that an empty length string is treated the same as a NULL. ” isn’t equal to NULL but is treated the same way. But an empty length string can’t be inserted into a column defined as NOT NULL.
Oracle uses PL/SQL (PL stands for Procedural Language). For the most part, PL/SQL and T-SQL used by SQL Server adhere to the ANSI standard for SQL, but both deviate from the standard or offer extensions in certain cases.
1) Instead of ISNULL, Oracle uses NVL to substitute a value for a NULL, although both languages use COALESCE as well.
2) String concatenation is with || instead of +. Or the CONCAT() function can be used.
3) Table and column names are limited to 30 characters.
4) Oracle doesn’t use SELECT TOP to return a certain number of rows, but you can use ROWNUM in the WHERE clause.
WHERE rownum < 2 - To return 1 record.
5) In creating a Procedure, View or Function, Oracle uses 'Create or Replace' in the definition, so that the same statement can run no matter on if the object already exists or not.
6) Data Types: Oracle uses varchar2 and nvarchar2 data types. Date – down to seconds, no fractional seconds or time zone awareness, Timestamp – date plus fractional seconds and time zone awareness – BLOB data type
7) Oracle uses a RowId pseudocolumn to uniquely identify a row in a table.
select a, b, rowid from table; select * from table where rowid = '...'
8) Single quote delimiter for reserved words – Treated as case sensitive if delimited – Standard in Oracle is to capitalize object names.
9) You can select values into a variable:
SELECT COUNT( DISTINCT clientid || ' ' || itemid ) INTO vCount FROM table
10) Declaring variables:
DECLARE v_a NUMBER; v_b NUMBER; BEGIN ... END
11) Setting or changing a variable:
LType := 'My value has changed';
12) Working with dates in WHERE clauses.
When we want to specify a datetime in a WHERE clause with a literal value, we’ll also need to specify the format of the literal date.
to_date('10-JUL-15 00:00:00', 'DD-MON-YY HH24:MI:SS')
To match a datetime value you’ll need to specify the time as well, even if it’s a 00:00:00 value, in order to match.
13) Oracle includes a Dual table – One column 'Dummy' one row 'X'. This can be used to select literal values, where you aren’t selecting a value from a table. Otherwise, Oracle will raise an error.
select 1 from Dual;