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 |
Exemple:
MySql:
#Create Report_megax_daily table and index;
CREATE TABLE REPORT_MEGAX_DAILY
(
SETTLED_TIME DATETIME,
ACCOUNT_ID INT(11),
AD_SPACE_ID INT(11),
CITY_ID INT(11),
IMPRESSIONS DECIMAL(10,0),
CLICKS DECIMAL(10,0),
COSTS DECIMAL(10,2)
);
CREATE INDEX REPORT_MEGAX_DAILY_IDX1 ON REPORT_MEGAX_DAILY
(ACCOUNT_ID);
CREATE INDEX REPORT_MEGAX_DAILY_IDX2 ON REPORT_MEGAX_DAILY
(AD_SPACE_ID);
#Create REPORT_MEGAX_HOURLY_NEW table and index;
CREATE TABLE REPORT_MEGAX_HOURLY_NEW
(
SETTLED_TIME DATETIME,
DSP_ACCOUNT_ID INT(11),
AD_SPACE_ID INT(11),
MEDIA_BUY_ID INT(11),
BID_REQUEST_COUNT DECIMAL(10,0),
BID_RESPONSE_COUNT DECIMAL(10,0),
BID_WIN_COUNT DECIMAL(10,0),
CLICKS DECIMAL(10,0),
BID_LOSE_COUNT DECIMAL(10,0),
BID_TIMEOUT_COUNT DECIMAL(10,0),
BID_PRICE DECIMAL(10,2),
BID_ALL_PRICE DECIMAL(10,2)
);
CREATE INDEX RPT_MEGAX_HOURLY_NEW_IDX1 ON REPORT_MEGAX_HOURLY_NEW
(SETTLED_TIME);
CREATE INDEX RPT_MEGAX_HOURLY_NEW_IDX2 ON REPORT_MEGAX_HOURLY_NEW
(DSP_ACCOUNT_ID, AD_SPACE_ID, MEDIA_BUY_ID);
#Create REPORT_MEGAX_DAILY_NEW table and index;
CREATE TABLE REPORT_MEGAX_DAILY_NEW
(
SETTLED_TIME DATETIME,
DSP_ACCOUNT_ID INT(11),
AD_SPACE_ID INT(11),
MEDIA_BUY_ID INT(11),
BID_REQUEST_COUNT DECIMAL(10,0),
BID_RESPONSE_COUNT DECIMAL(10,0),
BID_WIN_COUNT DECIMAL(10,0),
CLICKS DECIMAL(10,0),
BID_LOSE_COUNT DECIMAL(10,0),
BID_TIMEOUT_COUNT DECIMAL(10,0),
BID_PRICE DECIMAL(10,2),
BID_ALL_PRICE DECIMAL(10,2)
);
CREATE INDEX RPT_MEGAX_DAILY_NEW_IDX1 ON REPORT_MEGAX_DAILY_NEW
(SETTLED_TIME);
CREATE INDEX RPT_MEGAX_DAILY_NEW_IDX2 ON REPORT_MEGAX_DAILY_NEW
(DSP_ACCOUNT_ID, AD_SPACE_ID, MEDIA_BUY_ID);
Oracle:
--Create Report_megax_daily table and index;
CREATE TABLE REPORT_MEGAX_DAILY
(
SETTLED_TIME DATE,
ACCOUNT_ID NUMBER,
AD_SPACE_ID NUMBER,
CITY_ID NUMBER,
IMPRESSIONS NUMBER,
CLICKS NUMBER,
COSTS NUMBER
);
CREATE INDEX REPORT_MEGAX_DAILY_IDX1 ON REPORT_MEGAX_DAILY
(ACCOUNT_ID);
CREATE INDEX REPORT_MEGAX_DAILY_IDX2 ON REPORT_MEGAX_DAILY
(AD_SPACE_ID);
--Create REPORT_MEGAX_HOURLY_NEW table and index;
CREATE TABLE REPORT_MEGAX_HOURLY_NEW
(
SETTLED_TIME DATE,
DSP_ACCOUNT_ID NUMBER,
AD_SPACE_ID NUMBER,
MEDIA_BUY_ID NUMBER,
BID_REQUEST_COUNT NUMBER,
BID_RESPONSE_COUNT NUMBER,
BID_WIN_COUNT NUMBER,
CLICKS NUMBER,
BID_LOSE_COUNT NUMBER,
BID_TIMEOUT_COUNT NUMBER,
BID_PRICE NUMBER,
BID_ALL_PRICE NUMBER
);
CREATE INDEX RPT_MEGAX_HOURLY_NEW_IDX1 ON REPORT_MEGAX_HOURLY_NEW
(SETTLED_TIME);
CREATE INDEX RPT_MEGAX_HOURLY_NEW_IDX2 ON REPORT_MEGAX_HOURLY_NEW
(DSP_ACCOUNT_ID, AD_SPACE_ID, MEDIA_BUY_ID);
--Create REPORT_MEGAX_DAILY_NEW table and index;
CREATE TABLE REPORT_MEGAX_DAILY_NEW
(
SETTLED_TIME DATE,
DSP_ACCOUNT_ID NUMBER,
AD_SPACE_ID NUMBER,
MEDIA_BUY_ID NUMBER,
BID_REQUEST_COUNT NUMBER,
BID_RESPONSE_COUNT NUMBER,
BID_WIN_COUNT NUMBER,
CLICKS NUMBER,
BID_LOSE_COUNT NUMBER,
BID_TIMEOUT_COUNT NUMBER,
BID_PRICE NUMBER,
BID_ALL_PRICE NUMBER
);
CREATE INDEX RPT_MEGAX_DAILY_NEW_IDX1 ON REPORT_MEGAX_DAILY_NEW
(SETTLED_TIME);
CREATE INDEX RPT_MEGAX_DAILY_NEW_IDX2 ON REPORT_MEGAX_DAILY_NEW
(DSP_ACCOUNT_ID, AD_SPACE_ID, MEDIA_BUY_ID);