Differences between SQL Server and Oracle

When the database is an SQL Server, the Enterprise Library is very accessible. You can use the Enterprise Library Configuration tool to configure and then access the database in the code, however, it is not that convenient when data is replaced with Oracle. After all, there are some differences between the two companies. The Oracle I know (taking 9i as an example) the differences with SQL Server are summarized as follows:
1. Data Types;
The basic data types of SQL Server2000 Transact-SQL include:

Bigint	Binary	Bit	Char	Cursor
Datetime	Decimal	Float	Image	Int
Money	Nchar	Ntext	Nvarchar	Real
Smalldatetime	Smallint	Smallmoney	Text	Timestamp
Tinyint	Varbinary	Varchar	Uniqueidentifier

SQL Server is familiar to all users. The meaning of each type is not listed here.

Oracle 9i basic data types include:

Name	Description
Char	Used to describe the fixed-length bytes data. The length is <= 2000 bytes.
Varchar2	Used to describe variable-length bytes of data. The length is <= 4000 bytes.
Nchar	It is used to store the fixed-length bytes data of the Unicode character set. The length is <= 1000 bytes.
Nvarchar2	Used to store variable-length bytes data of the Unicode character set. The length is <= 1000 bytes.
Number	Used to store integer or floating point values
Date	Used to store date data
Long	Used to store variable-length character data with a maximum length of 2 GB
Raw	Long character data used to store unstructured data. The length is <= 2000 bytes.
Long raw	Long character data used to store unstructured data. The length is <= 2 GB
Rowid	It is used to store the binary data of the physical address of the column in the table, occupying 10 fixed bytes.
Blob	Used to store up to 4 GB of unstructured binary data
Clob	Used to store up to 4 GB of character data
Nclob	Used to store up to 4 GB of Unicode character data
Bfile	Used to store unstructured binary data in operating system files other than databases
Urowid	Stores binary data that represents any type of column address.
Float	Used to store floating point numbers

2. Differences Between Auto-incrementing row Fields
In SQL server, it is an auto-increment column (identity );
In Oracle, the Oracle sequence is an atomic object and is consistent. That is to say, once you access a sequence number, Oracle will automatically increment the next number before processing the next request, so as to ensure that duplicate values do not appear.
3. Differences between Stored Procedure
In most Oracle books, stored procedures are called "procedures" in SQL Server. The main difference between the Oracle process and the Microsoft SQL Server Stored procedure is that the Oracle process must return the value as an output parameter, the result set must be returned to the caller as a ref cursor object using the output parameter.
4. A major difference between PL/SQL and stored procedures in the T-SQL is the Oracle package structure used by PL/SQL. There is no equivalent element in the T-SQL. A package is a container of logically related programming blocks (such as stored procedures and functions. It contains two parts:

● Specification: Define the package name and provide a method signature (prototype) for each stored procedure or function in the package ). The canonicalized header also defines all global declarations. The standard style is similar to the C or C ++ header file.
 
● Body: contains the stored procedure and function code defined in the header.
 

Parameters of each stored procedure or function are enclosed in brackets and separated by commas. Each parameter is also marked with one of the following three identifiers as needed:

● IN: this value is passed from the calling application to the PL/SQL block. If no identifier is specified, IN is the default transfer direction.
 
● OUT: this value is generated by the stored procedure and passed back to the called application.
 
● INOUT: The value is passed to the PL/SQL block. It may be modified within the block and then returned to the calling application.
 

Each parameter is also marked to indicate the data type.