CODEWITHSUNDEEP
asp.netsql-serveroracle-database
Ankit
Ankit

Reputation: 6664

Converting SQL Server to Oracle

In my project, I have a database in SQL which was working fine. But now I have to make the application support oracle db too.

Some limitations I found out was that in Oracle, there is no bit field and the table name cannot be greater than 30 char. Is there any other limitation that I need to keep in mind.

Any suggestion from past experience will be helpful.

Upvotes: 1

Views: 332

Answers (4)

user2251793
user2251793

Reputation: 11

ORACLE do not support TOP clause. Instead of TOP you can use ROWNUM.

SQL Server: TOP (Transact-SQL)

SELECT TOP 3 * FROM CUSTOMERS

ORACLE: ROWNUM Pseudocolumn

SELECT * FROM CUSTOMERS WHERE ROWNUM <= 3

Upvotes: 1

bernd_k
bernd_k

Reputation: 11966

  • Varchar in Oracle Databases called varchar2 is limited to 4000 characters
  • Oracles concept of temporary tables is different, they have a global redefined structure
  • by default sort order and string compare is case-sensitive

When you add a column to a select * 

Select * from table_1 order by id;

you must prefix the * by the table_name or an alias

Select 
   (row_number() over (order by id)) rn,  
   t.* 
from table_1 t
order by id;

Oracle doesn't distinguish between null and '' (empty string). For insert and update you ca use '', but to query you must use null

create table t1 (
    id NUMBER(10),  
    val varchar2(20)
);

Insert into t1 values (1, '');
Insert into t1 values (2, null);
Select * from t1 where stringval = 0; -- correct but empty
Select * from t1 where stringval is null; -- returns both rows

Upvotes: 2

marc_s
marc_s

Reputation: 755531

If I recall correctly from my earlier Oracle days:

  • there's no IDENTITY column specification in Oracle (you need to use sequences instead)

  • you cannot simply return a SELECT (columns) from a stored procedure (you need to use REF CURSOR)

  • of course, all stored procs/funcs are different (Oracle's PL/SQL is not the same as T-SQL)

Upvotes: 3

Mutation Person
Mutation Person

Reputation: 30530

The SQL ISNULL counterpart in Oracle is NVL

select ISNULL(col, 0)...

select NVL(col, 0)...

You will also struggle if you attempt to select without a from in Oracle. Use dual:

select 'Hello' from DUAL

Bear in mind also, that in Oracle there is the distinction between PL/SQL (Procedural SQL) and pure SQL. They are two distinct and separate languages, that are commonly combined.

Upvotes: 2

Related Questions