Monday, September 3, 2012

T-SQL vs PL\SQL

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation’s procedural extension language for SQL and the Oracle relational database

Sample PL SQL:
DECLARE
number1 NUMBER(2);
number2 NUMBER(2) := 17; — value default
text1 VARCHAR2(12) := ‘Hello world’;
text2 DATE := SYSDATE; — current date and time
BEGIN
SELECT street_number
INTO number1
FROM address
WHERE name = ‘INU’;
END;

Transact-SQL (T-SQL) is Microsoft’s and Sybase’s proprietary extension to SQL.

Sample T-SQL:

DECLARE @Counter INT
SET @Counter = 10
WHILE @Counter > 0
BEGIN
PRINT ‘The count is ‘ + CONVERT(VARCHAR(10), @Counter)
SET @Counter = @Counter – 1
END

1) In PL/SQL, Oracle has implemented true structured programming and encapsulation functionality that is completely missing from Transact-SQL.

2) In PL/SQL, you can nest in-line procedures and functions within another procedure/function, allowing you to hide internal details and easily implement repetitive tasks via the nested components.
In T-SQL, each function/procedure needs to be compiled separately.

3) In PL/SQL, if you need to do something twice, you just write a nested function once and call it twice.
In T-SQL, you need to decide if the tradeoff of creating yet another separate component is worth the trouble, or do you just copy and paste the same code. This can have signficant impacts on maintainability.

4) In PL/SQL, you can create Packages to contain entire suites of data structures, procedures, and functions, and only expose those public interfaces that you want external callers to see. Plus, Packages can maintain internal variable states between calls, even including complex temporary table structures. Neither of these capabilities is available in T-SQL.

SQL vs PL\SQL

SQL
Structured query language (SQL) pronounced as “sequel” is a database computer language designed for managing data in relational database management systems (RDBMS), and originally based upon relational algebra.
Basic scope of SQL is to insert data and perform update, delete, schema creation, schema modification and data access control against databases.
SQL has elements, sub-divided into the followings:
Queries – Retrieve data, based on specific criteria. There are few keywords which can be used in queries. (Select, From, Where, Having, Group by and order by)
e.g: SELECT * FROM table1 WHERE column1 > condition ORDER BY column2;
Statements – That may control transactions, program flow, connections, sessions, or diagnostics
Expressions – That can produce either;
Scalar values
Tables consisting of columns and rows of data
Predicates -Specify conditions that can be evaluated to SQL Boolean (true/false/unknown)
Clauses – Constituent components of statements and queries
PL/SQL
PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation’s procedural extension language for SQL and the Oracle relational database. PL/SQL supports variables, conditions, loops, arrays, exceptions. PL/SQL essentially code containers can be complied in to the oracle databases. Software developers can therefore implant PL/SQL units of functionality into the database straight.
PL/SQL program units can be defined as follows:
Anonymous blocks
Forms the basis of simplest PL/SQL code
Functions
Functions are a collection of SQL and PL/SQL statements. Functions execute a task and should return a value to the calling environment.
Procedures
Procedures are alike to Functions. Procedures also can be executed to perform work. Procedures cannot be used in a SQL statement, can return multiple values. In addition, functions can be called from SQL, while procedures cannot.
Packages
Use of packages is re-using of code. Packages are groups of theoretically linked Functions, Procedures, Variable, PL/SQL table and record TYPE statements, Constants & Cursors etc… Packages usually have two parts, a specification and a body
Two advantages of packages include:
Modular approach, encapsulation of business logic
Using packages variables can declare in session levels
Types of variables in PL/SQL
Variables
Numeric variables
Character variables
Date variables
Data types for specific columns
Difference between SQL and PL/SQL
1.) SQL is a data oriented language for selecting and manipulating sets of data. PL/SQL is a procedural language to create applications.

2.) PL/SQL can be the application language just like Java or PHP can. PL/SQL might be the language we use to build, format and display those screens, web pages and reports.SQL may be the source of data for our screens, web pages and reports.

3.) SQL is executed one statement at a time. PL/SQL is executed as a block of code.

4.) SQL tells the database what to do (declarative), not how to do it. In contrast, PL/SQL tell the database how to do things (procedural).

5.) SQL is used to write Queries, Data Manipulation Language (DML) and Data Definition Language (DDL) whereas PL SQL is used to write Program blocks, Triggers, Functions, Procedures, and Packages.

6.) We can embed SQL in a PL/SQL program, but we cannot embed PL/SQL within a SQL statement.

7.) SQL is a language that is used by relational database technologies such as Oracle, Microsoft Access, and Sybase etc., PL/SQL is commonly used to write data-centric programs to manipulate data in an Oracle database. PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading, and information hiding (all but inheritance).
 

Stored Procedures vs Functions

Differences between Stored Procedures and Functions

1) Stored procedure are compiled for first time and compiled format is saved and executes compiled code when ever it is called. But function is compiled and executed every time it is called.
2) Function must return a value but in stored procedure it is optional.
3) Function takes one input parameter it is mandatory but stored procedure may take o to n input parameters.
4) Functions can be called from select statement, but stored procedures can not be called from select statement.
5) We can build logic in functions and we can even break complex logic in to methods.
6) We can use try catch statements in stored procedures but in functions we can not use.
7) We can not use insert,delete,update and create statements in functions but in stored procedures we can use those statements.
8) Functions can have only input parameters but stored procedure can have input and out put parameters
9) UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
10) UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
11) Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

In details
Stored Procedure

A Stored Procedure is a program (or procedure) which is physically stored within a database. They are usually written in a proprietary database language like PL/SQL for Oracle database or PL/PgSQL for PostgreSQL. The advantage of a stored procedure is that when it is run, in response to a user request, it is run directly by the database engine, which usually runs on a separate database server. As such, it has direct access to the data it needs to manipulate and only needs to send its results back to the user, doing away with the overhead of communicating large amounts of data back and forth.

User-defined Function

A user-defined function is a routine that encapsulates useful logic for use in other queries. While views are limited to a single SELECT statement, user-defined functions can have multiple SELECT statements and provide more powerful logic than is possible with views.

User defined functions have three main categories:

Scalar-valued function - returns a scalar value such as an integer or a timestamp. Can be used as column name in queries.
Inline function - can contain a single SELECT statement.
Table-valued function - can contain any number of statements that populate the table variable to be returned. They become handy when you need to return a set of rows, but you can't enclose the logic for getting this rowset in a single SELECT statement.

Mysql vs Oracle

MySQL is free and open source. Oracle is enterprise and expensive. However, plenty of large sites such as Facebook and Digg run perfectly fine on MySQL.

Oracle is used by larger organizations because of many reasons:

1. The ability to audit.
2. Oracle also has better user management and security for larger enterprises.
3. Enterprise-level management tools

MySQL is free, cheap to manage, efficient, fast, scalable, and open-source.

MySQL in being Open Source and free, and it has none of the design flaws that limit MySQL and Oracle some of the differences between MySql and Oracle are given below:
1)Oracle has row locking while MySQL only has table locking.
2)Oracle makes the most sense where you have huge volumes of complex datawhile MySQL is good for simple data,
3) Low end versions of MySQL and Oracle are free while high end versions of oracle are expensive.
4) Tables : MySQL supports more types of tables than Oracle, including MyISAM, MERGE, ISAM, innoDB, BDB and HEAP
5) Sequences : MySQL uses an AUTO-INCREMENT attribute assigned to a column when creating a table. . . The ability to start and increment sequence numbers values at the initial value within a group is a useful feature provided by MySQL that is not available in Oracle 9i database.
6) SQL Inserts : Unlike Oracle 9i database, MySQL allows INSERT statements with a VALUES clause to specify multiple sets of data to insert
7) Mysql is generally used by web application whereas Oracle is used by companies.
8) Mysql is easy as compared to Oracle.

Top 10 Difference between Oracle and Mysql ?

1.    Oracle Support Pl-Sql(Procedural Language) in addition to Sql ,Mysql support only Sql
2.    Oracle Provides lots of security features such as row lock while Mysql provide column.
3.    Oracle requires username,password,profile validation at the time of logging while Mysql requires only username,password and host.
4.    Oracle9i Database supports the use of temporary tables for an individual session, or global to all users.
5.    Oracle is used for the large Enterprise .
6.    Mysql is light,reliable and can connect to multi-Client Program.
7.    facebook-The world 2nd number website drive on mysql.
8.    MySQL does not support data partitions, and requires a server for each set of data files. Scalability is limited to the size of a single server. The limitations within MySQL of not supporting distributed databases is a serious roadblock in creating a high-performance, scalable database solution.
9.    MySQL is an open source database, and is completely free.
10.    Compare to Oracle, MySQL doesn't have Tablespace, Role management, snapshots, synonym and packages.

 

Oracle and MySQL Compared


This chapter compares the MySQL database and the Oracle database. It includes the following sections:
·         Database Security
·         Schema Migration
·         Data Types
·         Data Storage Concepts

2.1 Database Security

This section includes information about security issues with MySQL databases and Oracle databases.
As with Oracle, MySQL users are maintained by the database. MySQL uses a set of grant tables to keep track of users and the privileges that they can have. MySQL uses these grant tables when performing authentication, authorization and access control for users.

1.1 Database Authentication

Unlike Oracle (when set up to use database authentication) and most other databases that use only the user name and password to authenticate a user, MySQL uses an additional location parameter when authenticating a user. This location parameter is usually the host name, IP address, or a wildcard (Ò%Ó). With this additional parameter, MySQL may further restrict a user access to the database to a particular host or hosts in a domain. Moreover, this also allows a different password and set of privileges to be enforced for a user depending on the host from which the connection is made. Thus, user scott, who logs on from abc.com may or may not the same as user scott who logs on from xyz.com.

1.2 Privileges

The MySQL privilege system is a hierarchical system that works through inheritance. Privileges granted at a higher level are implicitly passed down to all lower levels and may be overridden by the same privileges set at lower levels. MySQL allows privileges to be granted at five different levels, in descending order of the scope of the privileges:
·         Global
·         Per-host basis
·         Database-level
·         Table-specific
·         Column-specific (single column in a single table
Each level has a corresponding grant table in the database. When performing a privilege check, MySQL checks each of the tables in descending order of the scope of the privileges, and the privileges granted at a lower level take precedence over the same privileges granted at a higher level.
The privileges supported by MySQL are grouped into two types: administrative privileges and per-object privileges. The administrative privileges are global privileges that have server-wide effects and are concerned with the functioning of MySQL. These administrative privileges include the FILE, PROCESS, REPLICATION, SHUTDOWN and SUPER privilege. The per-object privileges affect database objects such tables, columns, indexes, and stored procedures, and can be granted with a different scope. These per-object privileges are named after the SQL queries that trigger their checks.
Unlike in Oracle, there is no concept of role in MySQL. Thus, in order to grant a group of users the same set of privileges, the privileges have to be granted to each user separately. Alternately, though less satisfactory for auditing, users performing tasks as a role may all share a single user account that is designated for the "role" and with the required privileges granted.

2 Schema Migration

The schema contains the definitions of the tables, views, indexes, users, constraints, stored procedures, triggers, and other database-specific objects. Most relational databases work with similar objects.
This section contains the following:
·         Schema Object Similarities
·         Schema Object Names
·         Table Design Considerations
·         Migrating Multiple Databases

2.1 Schema Object Similarities

There are many similarities between schema objects in Oracle and MySQL. However, some schema objects differ between these databases. For more information about schema objects, see Oracle SQL Reference.
Table 2-1 shows the differences between Oracle and MySQL.
Table 2-1 Schema Objects in Oracle and MySQL
Oracle
MySQL
AFTER trigger
trigger
BEFORE trigger
trigger
Check constraint
Check constraint
Column default
Column default
Database
Database
Foreign key
Foreign key
Index
Index
Package
N/A
PL/SQL function
Routine
PL/SQL procedure
Routine
Primary key
Primary key
Role
N/A
Schema
Schema
Sequence
AUTO_INCREMENT for a column
Snapshot
N/A
Synonym
N/A
Table
Table
Tablespace
N/A
Temporary table
Temporary table
Trigger for each row
Trigger for each row
Unique key
Unique key
User
User
View
View

2.2 Schema Object Names

Oracle is case insensitive to object names, and Oracle schema object names are stored as uppercase.
As in Oracle, column, index, stored procedure, and trigger names as well as column aliases in MySQL are case insensitive on all platforms. However, the case sensitivity of database and tables names for MySQL differs from Oracle. In MySQL, databases correspond to directories within the data directory, and tables correspond to one or more files within the database directory. As such, the case sensitivity of the database and table names is determined by the case sensitivity of the underlying operating systems. This means that database and table names are not case-sensitive in Windows and are case-sensitive in most varieties of Unix. However, MySQL allows users to determine how the database and table names are stored on disk and in their use in MySQL through the lower_case_table_names system variable. Table aliases are case-sensitive in releases before MySQL 4.1.1.
Both Oracle and MySQL let you use reserved words as object names by representing the name with a quoted identifier. However, MySQL allows some reserved words such as DATE and TIMESTAMP to be used as unquoted identifier for object names, although this is not allowed in Oracle. SQL Developer appends an underscore (_) to the name of a MySQL object that is an Oracle reserved word.
MySQL and Oracle have some minor differences in their definition of an identifier. In MySQL, an unquoted identifier may begin with a digit, and double quotation marks are allowed in a quoted identifier; however, neither of these is allowed in an Oracle identifier. In MySQL, the quote character is the backtick (`). If the SQL mode ANSI_QUOTES is set, double quotes can also be used to quote the identifiers. In Oracle, identifiers are quoted using double quotation marks.
You should choose a schema object name that is unique by case and by at least one other characteristic, and ensure that the object name is not a reserved word from either database.

2.3 Table Design Considerations

This section discusses table design issues that you need to consider when converting MySQL databases to Oracle. This section includes the following­:
·         Character Data Types
·         Column Default Value

2.3.1 Character Data Types

MySQL and Oracle have some differences in the character types that they support and in the way they store and retrieve the character type values.
MySQL supports the CHAR and VARCHAR type for character type with a length that is less than 65,535 bytes. The CHAR type can have a maximum length of 255 bytes, and as of MySQL 3.23 it may also be declared with a length of 0 byte. Before MySQL 5.0.3, the length specification for the VARCHAR type is the same as the CHAR type. From MySQL 5.0.3 on, the maximum length for the VARCHAR type is 65,535 bytes. Oracle supports four character types: CHAR, NCHAR, NVARCHAR2 and VARCHAR2. The minimum length that can be declared for all Oracle character types is 1 byte. The maximum size allowed for CHAR and NCHAR is 2,000 bytes, and for NVARCHAR2 and VARCHAR2 it is 4,000 bytes.
MySQL CHAR values are right-padded with spaces to the specified length when they are stored, and trailing spaces are removed when the values are retrieved. On the other hand, VARCHAR values are stored using as many characters as are given, but before MySQL 5.0.3 trailing spaces are removed when the values are stored and retrieved. Oracle blank-pads the value for its CHAR and NCHAR type to the column length if the value is shorter than the column length, and trailing spaces are not removed on retrieval. For NVARCHAR2 and VARVHAR2 data type columns, Oracle stores and retrieves the value exactly as is given, including trailing spaces.
If a value is assigned to a character type column that exceeds its specified length, MySQL truncates the value and does not generate an error unless the STRICT SQL mode is set. Oracle generates an error if the value assigned to a character type column exceeds its specified length.
In MySQL, every character type (CHAR, VARCHAR, and TEXT) column has a column character set and collation. If the character set or collation is not explicitly defined in the column definition, the table character set or collation is implied if specified; otherwise, the database character or collation is chosen. In Oracle, the character set for CHAR and VARCHAR2 types is defined by the database character set, and for the character set for NCHAR and NVARCHAR types is defined the national character set.
When declaring a CHAR or VARCHAR type in MySQL, the default length semantics is characters instead of bytes for MySQL 4.1 and later. In Oracle, the default length semantics is bytes for CHAR and VARCHAR2 types and characters for NCHAR and NVARCHAR2 types.
SQL Developer will map MySQL CHAR and VARCHAR types to Oracle CHAR and VARCHAR2 types, respectively. SQL Developer will determine the maximum number of bytes for the Oracle CHAR and VARCHAR2 data type columns from the number of bytes required to hold the maximum length specified for the corresponding MySQL CHAR and VARCHAR data type columns. If the MySQL VARCHAR2 column is such that the data exceeds 4000 bytes, convert the column to an Oracle CLOB data type column.

2.3.2 Column Default Value

MySQL differs from Oracle in the way it handles default value for a column that does not allow NULL value.
In MySQL, for a column that does not allow NULL value and for which no data is provided for the column when data is inserted into the table, MySQL determines a default value for the column. This default value is the implicit default value for the column data type. However, if the strict mode is enabled, MySQL generates errors, and for transactional tables it rolls back the insert statement.
In Oracle, when data is inserted into a table, data must be provided for all columns that do not allow NULL value. Oracle does not generate a default value for columns that have the NOT NULL constraint.

2.4 Migrating Multiple Databases

SQL Developer supports the migration of multiple MySQL databases if they are on the same MySQL database server.

2.5 Schema Migration Considerations for MySQL

Schema migration considerations for MySQL apply in the following areas"
·         Databases
·         Temporary Tables
·         Owner of Schema Objects

2.5.1 Databases

When migrating MySQL databases to Oracle, SQL Developer maps each MySQL database to a tablespace in Oracle. Database objects, such as tables, indexes and views are stored in the respective tablespaces and are referenced from the Oracle schema for the user that owns them.

2.5.2 Mapping MySQL Global and Database-Level Privileges to Oracle System Privileges

SQL Developer does not process all the administrative privileges on MySQL, except the SUPER privilege. Table 2-2 shows the mappings for MySQL per-object privileges granted at the different levels as well as the SUPER privilege granted at the global level.
Table 2-2 MySQL Privileges and Oracle System Privileges
Level
Privilege
System Privilege(s) on Oracle
Global
ALTER
ALTER ANY TABLE, ALTER ANY SEQUENCE, ALTER ANY CUSTER, COMMENT ANY TABLE
Global
ALTER ROUTINE
ALTER ANY PROCEDURE, DROP ANY PROCEDURE
Global
CREATE
CREATE ANY TABLE, CREATE ANY SEQUENCE, CREATE ANY CLUSTER, CREATE DATABASE LINK, COMMENT ANY TABLE
Global
CREATE ROUTINE
CREATE ANY PROCEDURE
Global
CREATE USER
CREATE USER, GRANT ANY PRIVILEGE
Global
CREATE VIEW
CREATE ANY VIEW
Global
DELETE
ALTER ANY TABLE, DROP USER, DELETE ANY TABLE
Global
DROP
DROP ANT TABLE, DROP ANY SEQUENCE, DROP ANY CLUSTER, DROP ANY VIEW
Global
EXECUTE
EXECUTE ANY PROCEDURE
Global
INDEX
CREATE ANY INDEX, ALTER ANY INDEX, DROP ANY INDEX
Global
INSERT
INSERT ANY TABLE
Global
LOCK TABLES
LOCK ANY TABLE
Global
SELECT
SELECT ANY TABLE
Global
SUPER
CREATE ANY TRIGGER, DROP ANY TRIGGER
Global
UPDATE
UPDATE ANY TABLE
Global
USAGE
CREATE SESSION, ALTER SESSION, UNLIMITED TABLESPACE
Database
CREATE
CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE TABLE
Database
CREATE ROUTINE
CREATE PROCEDURE
Database
CREATE VIEW
CREATE VIEW
Table
CREATE
CREATE TABLE
Table
CREATE VIEW
CREATE VIEW

2.5.3 Temporary Tables

SQL Developer does not support the migration of temporary tables.
In MySQL, temporary tables are database objects that are visible only to the current user session and are automatically dropped when the user session ends.
The definition of temporary tables in Oracle differs slightly from MySQL, in that the temporary tables, once created, exist until they are explicitly dropped and they are visible to all sessions with appropriate privileges. However, the data in the temporary tables is visible only to the user session that inserts the data into the table, and the data may persist for the duration of a transaction or a user session.

2.5.4 Owner of Schema Objects

SQL Developer creates an Oracle schema for the MySQL root user that owns, for all databases to be migrated, all database objects except stored procedures. For stored procedures, the MySQL users that created them remain the owner. SQL Developer creates an Oracle schema for each MySQL user that is migrated.

3 Data Types

This section describes the data types used within Oracle. It shows the MySQL data types and the Oracle equivalent. It includes information about the following:
·         Supported Oracle Data Types
·         Default Data Type Mappings
·         Comparing Data Types

3.1 Supported Oracle Data Types

Table 2-3 describes the Oracle data types supported by Oracle SQL Developer.
Table 2-3 Supported Oracle Data Types
Data Type
Description
BLOB
A binary large object. Maximum size is 4 gigabytes.
CHAR (SIZE)
Fixed-length character data of length size bytes. Maximum size is 2000 bytes. Default and minimum size is 1 byte.
CLOB
A character large object containing single-byte characters. Both fixed-width and variable-width character sets are supported, both using the CHAR database character set. Maximum size is 4 gigabytes.
DATE
The DATE data type stores date and time information. Although date and time information can be represented in both CHAR and NUMBER data types, the DATE data type has special associated properties. For each DATE value, Oracle stores the following information: century, year, month, day, hour, minute, and second.
FLOAT
Specifies a floating-point number with decimal precision 38, or binary precision 126.
LONG (SIZE)
Character data of variable length up to 2 gigabytes, or 2^31 -1 bytes.
LONG RAW
Raw binary data of variable length up to 2 gigabytes.
NCHAR (SIZE)
Fixed-length character data of length size characters or bytes, depending on the choice of national character set. Maximum size is determined by the number of bytes required to store each character, with an upper limit of 2000 bytes. Default and minimum size is 1 character or 1 byte, depending on the character set.
NCLOB
A character large object containing multibyte characters. Both fixed-width and variable-width character sets are supported, both using the NCHAR database character set. Maximum size is 4 gigabytes. Stores national character set data.
NUMBER
Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.
NVARCHAR2 (SIZE)
Variable-length character string having maximum length size characters or bytes, depending on the choice of national character set. Maximum size is determined by the number of bytes required to store each character, with an upper limit of 4000 bytes. You must specify size for NVARCHAR2.
RAW (SIZE)
Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value.
VARCHAR (SIZE)
The VARCHAR data type is currently synonymous with the VARCHAR2 data type. Oracle recommends that you use VARCHAR2 rather than VARCHAR. In the future, VARCHAR might be defined as a separate data type used for variable-length character strings compared with different comparison semantics. The maximum size is 4000 and the minimum of 1 is the default.
BINARY_DOUBLE
A 64-bit, double-precision floating-point number data type.
BINARY_FLOAT
A 32-bit, single-precision floating-point number data type.

For more information about Oracle data types, see Oracle Database SQL Language Reference.

3.2 Default Data Type Mappings

Table 2-4 shows the default settings used by SQL Developer to convert data types from MySQL to Oracle. SQL Developer enables you to change the default setting for certain data types by specifying an alternative type. For information about changing the default data type mappings, see the SQL Developer online help.
Table 2-4 Default Data Type Mappings Used by Oracle SQL Developer
MySQL Data Type
Oracle Data Type
BIGINT
NUMBER(19, 0)
BIT
RAW
BLOB
BLOB, RAW
CHAR
CHAR
DATE
DATE
DATETIME
DATE
DECIMAL
FLOAT (24)
DOUBLE
FLOAT (24)
DOUBLE PRECISION
FLOAT (24)
ENUM
VARCHAR2
FLOAT
FLOAT
INT
NUMBER(10, 0)
INTEGER
NUMBER(10, 0)
LONGBLOB
BLOB, RAW
LONGTEXT
CLOB, RAW
MEDIUMBLOB
BLOB, RAW
MEDIUMINT
NUMBER(7, 0)
MEDIUMTEXT
CLOB, RAW
NUMERIC
NUMBER
REAL
FLOAT (24)
SET
VARCHAR2
SMALLINT
NUMBER(5, 0)
TEXT
VARCHAR2, CLOB
TIME
DATE
TIMESTAMP
DATE
TINYBLOB
RAW
TINYINT
NUMBER(3, 0)
TINYTEXT
VARCHAR2
VARCHAR
VARCHAR2, CLOB
YEAR
NUMBER

Note:
The ENUM and SET data types have no direct mapping in Oracle. SQL Developer maps ENUM columns in MySQL to VARCHAR2 columns in Oracle. It then adds a constraint and a trigger to those columns to ensure that only values that were allowed by the ENUM data type are allowed in the column it was mapped to in Oracle.

3.3 Comparing Data Types

This section lists the difference between MySQL and Oracle data types. For some MySQL data types there is more than one alternative Oracle data type. The tables include information about the following:
·         Numeric Types
·         Date and Time Types
·         String Types

3.3.1 Numeric Types

When mapping MySQL data types to numeric data types in Oracle, the following conditions apply:
·         If there is no precision or scale defined for the destination Oracle data type, precision and scale are taken from the MySQL source data type.
·         If there is a precision or scale defined for the destination data type, these values are compared to the equivalent values of the source data type and the maximum value is selected.
The following table compares the numeric types of MySQL to Oracle:
MySQL
Size
Oracle
BIGINT
8 Bytes
NUMBER (19,0)
BIT
approximately (M+7)/8 Bytes
RAW
DECIMAL(M,D)
M+2 bytes if D > 0, M+1 bytes if D = 0 (D+2, if M < D)
FLOAT(24), BINARY_FLOAT
DOUBLE
8 Bytes
FLOAT(24), BINARY_FLOAT, BINARY_DOUBLE
DOUBLE PRECION
8 Bytes
FLOAT(24), BINARY_DOUBLE
FLOAT(25<=X <=53)
8 Bytes
FLOAT(24), BINARY_FLOAT
FLOAT(X<=24)
4 Bytes
FLOAT, BINARY_FLOAT
INT
4 Bytes
NUMBER (10,0)
INTEGER
4 Bytes
NUMBER (10,0)
MEDIUMINT
3 Bytes
NUMBER (7,0)
NUMERIC
M+2 bytes if D > 0, M+1 bytes if D = 0 (D+2, if M < D)
NUMBER
REAL
8 Bytes
FLOAT(24), BINARY_FLOAT
SMALLINT
2 Bytes
NUMBER(5,0)
TINYINT
1 Byte
NUMBER(3,0)

3.3.2 Date and Time Types

The following table compares the date and time types of MySQL to Oracle:
MySQL
Size
Oracle
DATE
3 Bytes
DATE
DATETIME
8 Bytes
DATE
TIMESTAMP
4 Bytes
DATE
TIME
3 Bytes
DATE
YEAR
1 Byte
NUMBER

3.3.3 String Types

When mapping MySQL data types to character data types in Oracle, the following conditions apply:
·         If there is no length defined for the destination data type, the length is taken from the source data type.
·         If there is a length defined for the destination data type, the maximum value of the two lengths is taken.
The following table compares the string types of MySQL to Oracle:
Note:
Reference to M indicates the maximum display size. The maximum legal display size is 255. A reference to L applies to a floating point types and indicates the number of digits following the decimal point.
MySQL
Size
Oracle
BLOB
L + 2 Bytes whereas L<2^16
RAW, BLOB
CHAR(m)
M Bytes, 0<=M<=255
CHAR
ENUM (VALUE1, VALUE2, ...)
1 or 2 Bytes depending on the number of enum. values (65535 values max)

LONGBLOB
L + 4 Bytes whereas L < 2 ^ 32
RAW, BLOB
LONGTEXT
L + 4 Bytes whereas L < 2 ^ 32
RAW, CLOB
MEDIUMBLOB
L + 3 Bytes whereas L < 2^ 24
RAW, BLOB
MEDIUMTEXT
L + 3 Bytes whereas L < 2^ 24
RAW, CLOB
SET (VALUE1, VALUE2, ...)
1, 2, 3, 4 or 8 Bytes depending on the number of set members (64 members maximum)

TEXT
L + 2 Bytes whereas L<2^16
VARCHAR2, CLOB
TINYBLOB
L + 1 Bytes whereas L<2 ^8
RAW, BLOB
TINYTEXT
L + 1 Bytes whereas L<2 ^8
VARCHAR2
VARCHAR(m)
L+1 Bytes whereas L<=M and0<=M<=255 before MySQL 5.0.3 (0 <= M <= 65535 in MySQL 5.0.3 and later; effective maximum length is 65,532 bytes)
VARCHAR2, CLOB

4 Data Storage Concepts

This section provide a description of the conceptual differences and similarities in data storage for MySQL and Oracle databases.
Data storage is an aspect of MySQL that sets it apart for nearly every database, including Oracle. In MySQL, databases correspond to directories within the data directory of the server. Tables within a database correspond to one or more files within the database directory, depending on the storage engine used for the tables.
A database can contain a mix of tables of different storage engines. A storage engine is responsible for the storage and retrieval of the data for a table.
MySQL offers a variety of storage engines (formerly called table types) to meet the different requirements of the user's environment. Table 2-5 shows the storage engines supported by MySQL.
Table 2-5 Storage Engines Supported by MySQL
Storage Engine
Description
MyISAM
The default non-transactional storage engine that provides full-text indexing and is highly portable
MERGE
A non-transactional storage engine that allows a collection of MyISAM tables with identical column and index information to be used as one
MEMORY (HEAP)
A non-transactional storage engine that stores data in memory
BDB (Berkeley DB)
The first transactional-safe storage engine
InnoDB
A transactional-safe storage engine designed for maximum performance when processing large volume of data and that provides row-level locking
FEDERATED
A storage engine that accesses data in tables of remote databases rather than in local tables
ARCHIVE
A storage engine that can store large amount of data without indexes in very small footprint
CSV
A storage engine that stores data in text file using comma-separated-values format
BLACKHOLE
A storage engine that acts as a "black hole" that accepts data but throws it away and does not store it
EXAMPLE
A "stub" engine that does nothing. Its purpose is to serve as an example that illustrates how to begin writing new engines.
ISAM
The original MySQL storage engine that has been deprecated in favor of the MyISAM storage engine as of version 5.0

Each storage engine has its benefits and drawbacks. Some of features that differentiate the storage engines are transaction, locking, concurrency and portability. The following table summarizes the features for four of the commonly used storage engines.
Table 2-6 Feature Comparison for Common Storage Engines
Feature
MyISAM
Heap
BDB
InnoDB
Transactional
No
No
Yes
Yes
Lock granularity
Table
Table
Page
Row
Storage
A data file (.MYD) and an index file (.MYI) for each table
In-memory
A single data and index file (.db) for each table
A set of data files for all the tables
Portable
Yes
N/A
No
Yes

An Oracle database consists of one or more tablespaces. Tablespaces provide logical storage space that link a database to the physical disks that hold the data. A tablespace is created from one or more data files. Data files are files in the file system or an area of disk space specified by a raw device. A tablespace can be enlarged by adding more data files.
An Oracle database consists of a least a SYSTEM tablespace, where the Oracle tables are stored. It can also consist of user defined tablespaces. A tablespace is the logical storage location for database objects. For example, you can specify where a particular table or index gets created in the tablespace.