sql-server , oracle-database , cookbook ,

Microsoft SQL Server Guy Trying Oracle Database

Abhith Rajan

Abhith Rajan Follow
May 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

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 YOURUSERID and YOURUSERPASSWORD 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.

Abhith Rajan

Written by Abhith Rajan FollowBuy 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 Stories

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.