sql-server | oracle-database | cookbook

Microsoft SQL Server Guy Trying Oracle Database

by Abhith RajanMay 08, 2019 · 3 min read · Last Updated:
Share this

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.

Table of Contents

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

1CREATE USER <UserName> IDENTIFIED BY <Password>;
2GRANT 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,

1utl_raw.cast_to_raw('SOME_STRING')

Update BLOB via Query

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

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

1private 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";
2
3using (var dbConn = new OracleConnection(ConnectionString))
4{
5 ...
6}

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 by Abhith Rajan Buy me a coffee
Abhith Rajan is an aspiring software engineer with more than 6 years of experience and proven successful track record of delivering technology-based products and services.

Related Posts

Related Services

SmarterASP.net - Unlimited ASP.NET Web Hosting

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