sql-server | oracle-database | cookbook

Microsoft SQL Server Guy Trying Oracle Database

This post contains my personal experiences trying Oracle Database.

Abhith Rajan
Abhith RajanMay 08, 2019 · 2 min read · Last Updated:

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.

This page is open source. Noticed a typo? Or something unclear?
Improve this page on GitHub


Abhith Rajan

Written byAbhith Rajan
Abhith Rajan is a software engineer by day and a full-stack developer by night. He's coding for almost a decade now. He codes 🧑‍💻, write ✍️, learn 📖 and advocate 👍.
Connect

Is this page helpful?

Related SnippetsView All

Related ArticlesView All

Related VideosView All

High Availability and SLA for Azure SQL Managed Instance

Using SQL Data Sync for Bidirectional Data Replication in SQL Server & Azure SQL DBs | Data Exposed

Build fast, scalable data system on Azure SQL Database Hyperscale | Clearent

Related Tools & ServicesView All

sqlfum.pt

sequel fumpt

sqlfmt is an online SQL formatter. It is pronounced sequel fumpt. Its purpose is to beautifully format SQL statements.
SmarterASP.NET

SmarterASP.net - Unlimited ASP.NET Web Hosting

ASP.NET Hosting by SmarterASP.net. Unlimited ASP.NET Hosting Plans Starting at $2.95 a month.