CODEWITHSUNDEEP
mysqloracle-database
Lily Vasquez
Lily Vasquez

Reputation: 11

Creating a table in MySQL vs Oracle

In MySQL my table looks like this:  

 CREATE TABLE `cat_activo` (  
    `id_activo` int(11) NOT NULL AUTO_INCREMENT,
    `tarjeta` int(11) NOT NULL, 
    `id_area` int(11) NOT NULL, 
    `id_clase` int(11) NOT NULL,  
    `fecha_ingreso` date NOT NULL, 
    `fecha_retiro` date NOT NULL,
    `fecha_real_retiro` date NOT NULL,
    `nombre` varchar(300) NOT NULL, 
    `serie` varchar(100) NOT NULL,
    `placa` varchar(30) NOT NULL DEFAULT '',
    `caracter` text NOT NULL,
    `valor_original` double(18,2) NOT NULL DEFAULT '0.00',
    `valor_residual` double(18,2) NOT NULL DEFAULT '0.00',
    `plazo_depreciar` int(11) NOT NULL DEFAULT '0',
    `valor_depreciar` double(18,2) NOT NULL DEFAULT '0.00',
    `porcentaje` double(10,2) NOT NULL DEFAULT '0.00',
    `depreciacion_acumulada` double(18,2) NOT NULL DEFAULT '0.00',
    `cuota_anual` double(18,2) NOT NULL DEFAULT '0.00',
    `saldo_depreciar` double(18,2) NOT NULL DEFAULT '0.00',
    `cuota_mensual` double(18,2) NOT NULL DEFAULT '0.00',
    `cuota_mensual` double(18,2) NOT NULL DEFAULT '0.00',
    `ruta_archivo` varchar(200) NOT NULL DEFAULT ' ',
    `estado` tinyint(4) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_activo`,`tarjeta`),
    KEY `fkArea` (`id_area`),
    KEY `fkClase` (`id_clase`)
);

In Oracle:

CREATE TABLE cat_activo(
    id_activo number(10) NOT NULL,
    tarjeta number(10) NOT NULL,
    id_area number(10) NOT NULL,
    id_clase number(10) NOT NULL,
    fecha_ingreso date NOT NULL,
    fecha_retiro date NOT NULL,
    fecha_real_retiro date NOT NULL,
    nombre varchar2(300) NOT NULL,
    serie varchar2(100) NOT NULL,
    placa varchar2(30) NOT NULL,
    caracteristicas long NOT NULL,
    valor_original number(18,2) NOT NULL DEFAULT 0.00,
    valor_residual number(18,2) NOT NULL DEFAULT 0.00,
    plazo_depreciar number(10) NOT NULL DEFAULT 0,
    valor_depreciar number(18,2) NOT NULL DEFAULT 0.00,
    porcentaje number(10,2) NOT NULL DEFAULT 0.00,
    depreciacion_acumulada number(18,2) NOT NULL DEFAULT 0.00,
    cuota_anual number(18,2) NOT NULL DEFAULT 0.00,
    saldo_depreciar number(18,2) NOT NULL DEFAULT 0.00,
    cuota_mensual number(18,2) NOT NULL DEFAULT 0.00,
    ruta_archivo varchar2(200) NOT NULL DEFAULT ' ',
    estado number(4) NOT NULL DEFAULT 0,
    PRIMARY KEY (id_activo,tarjeta),
    KEY fkArea (id_area),
    KEY fkClase (id_clase)
);

The MySQL version works, but in Oracle I get the following error:

ORA-00907: missing right parenthesis ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_190200", line 592 ORA-06512: at "SYS.DBMS_SYS_SQL", line 1658 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_190200", line 578 ORA-06512: at "APEX_190200.WWV_FLOW_DYNAMIC_EXEC", line 2057 3. tarjeta number(10) NOT NULL,4. id_area number(10) NOT NULL,5. id_clase number(10) NOT NULL,6. fecha_ingreso date NOT NULL, 7. fecha_retiro date NOT NULL,

What's going wrong here?

Upvotes: 1

Views: 729

Answers (2)

spencer7593
spencer7593

Reputation: 108410

NOTES:

  • The NOT NULL constraint has to be after the DEFAULT clause. Sort of documented here in the train track syntax diagram. (The NOT NULL is actually a constraint definition, so has to be at the end of the column definition.

column definition train track diagram

  • The KEY syntax is not valid in Oracle. Use a CREATE INDEX statement instead.

  • The LONG datatype is deprecated since 8.0. Unless we have a specific reason to use that datatype, I'd opt for newer CLOB datatype.

  • I'd opt to use a specific name for the PRIMARY KEY constraint rather than a system assigned name; but that's not a syntax problem.

Something like this should work in Oracle:

CREATE TABLE cat_activo
( id_activo              NUMBER(10)     NOT NULL
, tarjeta                NUMBER(10)     NOT NULL
, id_area                NUMBER(10)     NOT NULL
, id_clase               NUMBER(10)     NOT NULL
, fecha_ingreso          DATE           NOT NULL
, fecha_retiro           DATE           NOT NULL
, fecha_real_retiro      DATE           NOT NULL
, nombre                 VARCHAR2(300)  NOT NULL
, serie                  VARCHAR2(100)  NOT NULL
, placa                  VARCHAR2(30)   NOT NULL
, caracteristicas      CLOB             NOT NULL
, valor_original         NUMBER(18,2)   DEFAULT 0.00   NOT NULL
, valor_residual         NUMBER(18,2)   DEFAULT 0.00   NOT NULL 
, plazo_depreciar        NUMBER(10)     DEFAULT 0      NOT NULL 
, valor_depreciar        NUMBER(18,2)   DEFAULT 0.00   NOT NULL  
, porcentaje             NUMBER(10,2)   DEFAULT 0.00   NOT NULL  
, depreciacion_acumulada NUMBER(18,2)   DEFAULT 0.00   NOT NULL  
, cuota_anual            NUMBER(18,2)   DEFAULT 0.00   NOT NULL  
, saldo_depreciar        NUMBER(18,2)   DEFAULT 0.00   NOT NULL  
, cuota_mensual          NUMBER(18,2)   DEFAULT 0.00   NOT NULL  
, ruta_archivo           VARCHAR2(200)  DEFAULT ' '    NOT NULL
, estado                 NUMBER(4)      DEFAULT 0      NOT NULL
, PRIMARY KEY (id_activo,tarjeta)
);
CREATE INDEX cat_activo_fkArea ON cat_activo (id_area) ;
CREATE INDEX cat_activo_fkClase ON cat_activo (id_clase) ;

Upvotes: 1

GMB
GMB

Reputation: 222482

You first problem is with this syntax:

valor_residual number(18,2) NOT NULL DEFAULT 0.00

In Oracle, the DEFAULT option must be placed after the DEFAULT, so this should be:

valor_original number(18,2) DEFAULT 0.00 NOT NULL

The second problem is with this syntax:

KEY fkArea (id_area)

As far as concerned there is no syntax in Oracle to create a non-unique index within a create table statement. If you want a unique index, this can be done by adding a constraint when the table is created:

id_area number(10) NOT NULL CONSTRAINT fkArea UNIQUE USING INDEX

If you do want a non-unique index, you need to use a separate create index statement:

create index fkArea on cat_activo(id_area);

Demo on DB Fiddle:

CREATE TABLE cat_activo(  
    id_activo number(10) NOT NULL,  
    tarjeta number(10) NOT NULL, 
    id_area number(10) NOT NULL CONSTRAINT fkArea UNIQUE USING INDEX,  -- unique index
    id_clase number(10) NOT NULL,  
    fecha_ingreso date NOT NULL,  
    fecha_retiro date NOT NULL,  
    fecha_real_retiro date NOT NULL,  
    nombre varchar2(300) NOT NULL,  
    serie varchar2(100) NOT NULL,  
    placa varchar2(30) NOT NULL, 
    caracteristicas long NOT NULL,  
    valor_original number(18,2) DEFAULT 0 NOT NULL, 
    valor_residual number(18,2) DEFAULT 0 NOT NULL,
    plazo_depreciar number(10) DEFAULT 0 NOT NULL, 
    valor_depreciar number(18,2) DEFAULT 0 NOT NULL,
    porcentaje number(10,2) DEFAULT 0 NOT NULL,  
    depreciacion_acumulada number(18,2) DEFAULT 0 NOT NULL,  
    cuota_anual number(18,2) DEFAULT 0 NOT NULL, 
    saldo_depreciar number(18,2) DEFAULT 0 NOT NULL, 
    cuota_mensual number(18,2) DEFAULT 0 NOT NULL, 
    ruta_archivo varchar2(200) DEFAULT ' ' NOT NULL,
    estado number(4) DEFAULT 0 NOT NULL, 
    PRIMARY KEY (id_activo,tarjeta)
);


-- non-unique index
create index fkClase on cat_activo(id_clase);

Upvotes: 0

Related Questions