Microsoft SQL Server Guy Trying Oracle Database

I have worked with Microsoft SQL Server for the past 7 years and still enjoying working with SQL Server. Now I got a chance to explore Oracle Database and here is my experience.

Noticed Differences

• There is NO DATABASE NAME, instead there is this user or schema wise separation.
• Preferred IDE is Oracle SQL Developer
• After executing query, you need to commit in order to retain the changes.
• SELECT TOP N is not that easy compared to SQL Server.

Docs

Tablespaces

An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database’s data.

VARCHAR vs VARCHAR2

VARCHAR is reserved by Oracle to support distinction between NULL and empty string in future, as ANSI standard prescribes.

VARCHAR2 does not distinguish between a NULL and empty string, and never will.

If you rely on empty string and NULL being the same thing, you should use VARCHAR2.

Problems

Show Line Numbers in Oracle SQL Developer

This can be done easily by right clicking the line number section in the IDE, then Toggle Line Numbers.

Create User

CREATE USER <UserName> IDENTIFIED BY <Password>;
GRANT CONNECT , RESOURCE, DBA TO <UserName>;

VARCHAR to BLOB

We can use the inbuilt function utl_raw.cast_to_raw which transforms a varchar2 into a BLOB value,

utl_raw.cast_to_raw('SOME_STRING')

Update BLOB via Query

Update TABLE_NAME SET COL_NAME = utl_raw.cast_to_raw('SOME_STRING');

Oracle localhost connection string for an ASP.NET CORE & Dapper project

private readonly string ConnectionString = @"Data Source = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = XE))); user id = YOUR_USER_ID; Password= YOUR_USER_PASSWORD";

using (var dbConn = new OracleConnection(ConnectionString))
{
  ...
}

Replace YOUR_USER_ID and YOUR_USER_PASSWORD with appropriate values.

ORA-00933: SQL command not properly ended

This is happened when my query contained semicolon ”;” at the end. Removing the semicolon ”;” from the end of the query solved the problem.

Conclusion

I just started exploring Oracle Database, so this post can be considered as a draft for now, more coming.