Módulo 27: Tablas y tipos de datos
Objetivos del módulo
- Conocer los objetos que puede contener un esquema de Oracle y su finalidad
- Aplicar las reglas de nombres de Oracle: longitud, caracteres permitidos, palabras reservadas e identificadores entre comillas
- Dominar la sintaxis de
CREATE TABLEcon columnas, valores por defecto (DEFAULTyDEFAULT ON NULL) yIF NOT EXISTS - Conocer en profundidad los tipos de datos de Oracle: caracteres, numéricos, fecha y hora, intervalos, LOB,
RAW,ROWID,BOOLEAN,JSONyVECTOR - Prever el redondeo y los errores de
NUMBER(p,s)y la diferencia entre semánticaBYTEyCHAR - Saber cómo traduce Oracle los tipos del estándar ANSI
- Definir columnas identidad, columnas virtuales y columnas invisibles
- Crear tablas a partir de consultas (CTAS), tablas temporales globales y privadas
- Elegir el tipo de dato adecuado para cada atributo y consultar la estructura de las tablas en el diccionario de datos
1. Los objetos de una base de datos Oracle
Con este módulo empieza el DDL (Data Definition Language), la parte de SQL que crea y modifica la estructura de la base de datos. En el diseño lógico (bloque 3) obtuviste un esquema relacional; ahora vas a convertir cada relación en una tabla de Oracle.
📘 Concepto: Un objeto de esquema es cualquier estructura con nombre que pertenece a un usuario (a su esquema) y se crea con DDL. La tabla es el objeto principal: es el único que almacena datos de usuario por sí mismo; casi todos los demás objetos sirven para acceder a esas tablas de otra forma, más rápida o más segura.
| Objeto | Para qué sirve | Sentencia de creación | Módulo |
|---|---|---|---|
| Tabla (table) | Almacena los datos en filas y columnas | CREATE TABLE | 19, 20, 21 |
| Restricción (constraint) | Regla de integridad asociada a una tabla (PK, FK, UNIQUE, CHECK, NOT NULL) | Dentro de CREATE/ALTER TABLE | 20 |
| Vista (view) | Consulta almacenada con nombre que se usa como una tabla virtual | CREATE VIEW | 22 |
| Índice (index) | Estructura auxiliar que acelera búsquedas y garantiza unicidad | CREATE INDEX | 22 |
| Secuencia (sequence) | Generador de números únicos (para claves sustitutas) | CREATE SEQUENCE | 22 |
| Sinónimo (synonym) | Nombre alternativo de otro objeto | CREATE SYNONYM | 22 |
| Vista materializada | Vista cuyo resultado se guarda físicamente y se refresca | CREATE MATERIALIZED VIEW | 22 |
| Dominio (domain) | Tipo de dato reutilizable con restricciones (desde 23ai) | CREATE DOMAIN | 20 |
| Procedimiento, función, paquete, disparador | Código PL/SQL almacenado | CREATE PROCEDURE, CREATE TRIGGER… | Curso de PL/SQL |
💡 Consejo: Todas las salidas de este módulo se han obtenido con la configuración de sesión del módulo 22:
SET NULL '(nulo)', idioma y territorio españoles (por eso los decimales aparecen con coma) yNLS_DATE_FORMAT = 'DD/MM/YYYY'. Úsala tú también para que tus resultados coincidan. Las tablas de ejemplo (..._demo) puedes borrarlas al terminar cada apartado conDROP TABLE nombre PURGE;(módulo 25).
Puedes ver los objetos de tu esquema en la vista del diccionario USER_OBJECTS. Recién cargado EMPRESA, tienes 9 tablas y 14 índices (que Oracle creó automáticamente para las claves primarias y únicas, como verás en el módulo 24):
SELECT object_type, COUNT(*)
FROM user_objects
GROUP BY object_type;
OBJECT_TYPE COUNT(*)
----------------------- ----------
TABLE 9
INDEX 14
2 filas seleccionadas.
2. Reglas de nombres en Oracle
2.1. Identificadores normales (sin comillas)
Los nombres de tablas, columnas, restricciones y demás objetos son identificadores. Un identificador sin comillas debe cumplir:
| Regla | Detalle |
|---|---|
| Longitud | Entre 1 y 128 bytes (desde Oracle 12.2; antes, 30 bytes) |
| Primer carácter | Una letra |
| Resto de caracteres | Letras, dígitos y los símbolos _, $ y # (Oracle desaconseja $ y #) |
| Mayúsculas | No se distinguen: Oracle los guarda en MAYÚSCULAS |
| Palabras reservadas | No pueden ser palabras reservadas de SQL (SELECT, DATE, ORDER, LEVEL…) |
| Unicidad | Dentro de un esquema, dos objetos del mismo “espacio de nombres” (tablas, vistas, secuencias, sinónimos…) no pueden llamarse igual; dos columnas de una misma tabla tampoco |
⚠️ Importante: El límite es de 128 bytes, no de caracteres. En UTF-8 una letra con tilde ocupa 2 bytes. Si tienes que trabajar con 19c en modo compatible con versiones antiguas, o con herramientas viejas, limítate a 30 caracteres.
Veamos qué ocurre al saltarse cada regla. Un nombre de 130 caracteres:
CREATE TABLE tabla_con_un_nombre_larguisimo_que_supera_con_creces_el_limite_de_ciento_veintiocho_bytes_que_permite_oracle_desde_la_version_12_2 (id NUMBER);
CREATE TABLE tabla_con_un_nombre_larguisimo_que_supera_con_creces_el_limite_de_ciento_veintiocho_bytes_que_permite_oracle_desde_la_version_12_2 (id NUMBER)
*
ERROR en línea 1:
ORA-00972: The identifier TABLA_CON_...VERSION_12... exceeds the maximum length of 128 bytes.
Un nombre que empieza por un dígito, o una palabra reservada como nombre de tabla:
CREATE TABLE 2026_ventas (id NUMBER);
CREATE TABLE order (id NUMBER);
CREATE TABLE 2026_ventas (id NUMBER)
*
ERROR en línea 1:
ORA-00903: invalid table name
CREATE TABLE order (id NUMBER)
*
ERROR en línea 1:
ORA-00903: invalid table name
Una palabra reservada como nombre de columna. Desde 23ai el mensaje es muy claro:
CREATE TABLE prueba_demo (id NUMBER, date DATE);
CREATE TABLE prueba_demo (id NUMBER, date DATE)
*
ERROR en línea 1:
ORA-03050: invalid identifier: "DATE" is a reserved word
Lo mismo ocurre con otros nombres que los principiantes intentan usar a menudo: LEVEL, USER, SIZE, COMMENT, ORDER, GROUP, NUMBER, TABLE, ACCESS, ROWID… En cambio, hay palabras clave no reservadas que sí se admiten, como NAME o TYPE:
CREATE TABLE prueba_demo (id NUMBER, name VARCHAR2(10), type VARCHAR2(10));
Tabla creada.
💡 Consejo: Para no tener que memorizar la lista de palabras reservadas, usa nombres en español y descriptivos:
fechaen lugar dedate,nivelen lugar delevel,usuarioen lugar deuser,comentarioen lugar decomment. La lista completa está en el apéndice “Oracle SQL Reserved Words” de la SQL Language Reference, y un administrador puede consultarla en la vistaV$RESERVED_WORDS.
Las letras con tilde y la eñe son letras válidas (la base de datos está en Unicode), pero evítalas en identificadores: complican escribir los nombres en algunos teclados, scripts y lenguajes de programación.
CREATE TABLE año_2026 (mes NUMBER); -- funciona, pero mejor anio_2026
2.2. Identificadores entre comillas dobles
Si escribes un nombre entre comillas dobles, Oracle lo guarda tal cual: respeta mayúsculas y minúsculas, admite espacios, palabras reservadas y casi cualquier carácter.
CREATE TABLE "Mi Tabla" ("Nombre" VARCHAR2(20), "nombre" VARCHAR2(20));
INSERT INTO "Mi Tabla" VALUES ('Ana', 'Luis');
Parece cómodo, pero a partir de ese momento tendrás que escribir siempre las comillas y las mayúsculas exactas:
SELECT nombre FROM "Mi Tabla";
SELECT nombre FROM "Mi Tabla"
*
ERROR en línea 1:
ORA-00904: "NOMBRE": invalid identifier
SELECT * FROM mi_tabla;
SELECT * FROM mi_tabla
*
ERROR en línea 1:
ORA-00942: table or view "CURSO"."MI_TABLA" does not exist
SELECT "Nombre", "nombre" FROM "Mi Tabla";
Nombre nombre
-------------------- --------------------
Ana Luis
1 fila seleccionada.
¡La tabla tiene dos columnas distintas llamadas Nombre y nombre! Y en el diccionario aparece exactamente con ese nombre, en lugar de en mayúsculas:
SELECT table_name FROM user_tables WHERE table_name LIKE 'Mi%';
TABLE_NAME
------------------------------
Mi Tabla
1 fila seleccionada.
⚠️ Importante: No uses identificadores entre comillas. Obligan a todos los programadores, herramientas y consultas a recordar las mayúsculas exactas, provocan errores difíciles de ver y suelen aparecer al importar tablas desde otros sistemas (Access, Excel, algunos ORM). La única excepción razonable es tener que trabajar con un esquema ajeno que ya los usa.
Un efecto relacionado que debes recordar: como los identificadores normales se guardan en mayúsculas, al consultar el diccionario de datos tienes que escribir el nombre en mayúsculas:
CREATE TABLE clientes_demo (id NUMBER);
SELECT table_name FROM user_tables WHERE table_name = 'clientes_demo';
ninguna fila seleccionada
SELECT table_name FROM user_tables WHERE table_name = 'CLIENTES_DEMO';
TABLE_NAME
------------------------------
CLIENTES_DEMO
1 fila seleccionada.
2.3. Convenciones de nombres del curso
| Elemento | Convención | Ejemplos |
|---|---|---|
| Tablas | Plural, minúsculas, palabras separadas por _ | empleados, lineas_pedido |
| Columnas | Singular, descriptivas | fecha_alta, limite_credito |
| Clave primaria sustituta | <entidad>_id | cliente_id, pedido_id |
| Clave ajena | El mismo nombre que la columna referenciada, o uno que indique el papel | dept_id, jefe_id, comercial_id |
| Restricciones | Prefijo de tipo + tabla abreviada + columna | pk_empleados, fk_emp_dept, uq_emp_email, ck_emp_salario, nn_emp_nombre |
| Tablas de prueba | Sufijo _demo | alumnos_demo |
3. La sentencia CREATE TABLE
3.1. Sintaxis general
CREATE TABLE [IF NOT EXISTS] [esquema.]nombre_tabla (
columna1 tipo_dato [DEFAULT expresión | DEFAULT ON NULL expresión] [restricciones_de_columna],
columna2 tipo_dato ...,
...
[restricciones_de_tabla]
)
[TABLESPACE nombre_tablespace];
- Entre paréntesis va la lista de columnas separadas por comas. Cada columna tiene un nombre y un tipo de dato obligatorios.
- Opcionalmente, cada columna puede tener un valor por defecto y restricciones en línea (
NOT NULL,PRIMARY KEY…). - Al final de la lista pueden ir restricciones de tabla (las verás en el módulo 24).
- Detrás del paréntesis van las cláusulas físicas opcionales: tablespace, almacenamiento, particionado, compresión… (diseño físico, fuera del alcance de este curso salvo
TABLESPACE).
💻 Ejemplo: una tabla sencilla para guardar los cursos de formación que reciben los empleados:
CREATE TABLE formaciones_demo (
formacion_id NUMBER(6),
titulo VARCHAR2(100),
horas NUMBER(4),
fecha_inicio DATE,
presencial CHAR(1)
);
Tabla creada.
DESCRIBE formaciones_demo
Nombre ¿Nulo? Tipo
----------------------------------------- -------- ----------------------------
FORMACION_ID NUMBER(6)
TITULO VARCHAR2(100)
HORAS NUMBER(4)
FECHA_INICIO DATE
PRESENCIAL CHAR(1)
Esta tabla funciona, pero es un mal diseño: no tiene clave primaria, todas las columnas admiten nulos y presencial admite cualquier carácter. En el módulo 24 aprenderás a añadirle restricciones. En este módulo nos centramos en las columnas y sus tipos.
Por defecto la tabla se crea en el tablespace por defecto del usuario (USERS). Puedes indicarlo explícitamente:
CREATE TABLE con_tablespace_demo (id NUMBER) TABLESPACE users;
SELECT table_name, tablespace_name FROM user_tables WHERE table_name = 'CON_TABLESPACE_DEMO';
TABLE_NAME TABLESPACE
------------------------------ ----------
CON_TABLESPACE_DEMO USERS
1 fila seleccionada.
3.2. Errores de sintaxis frecuentes
CREATE TABLE sin_tipo (a, b NUMBER);
CREATE TABLE sin_tipo (a, b NUMBER)
*
ERROR en línea 1:
ORA-02263: need to specify the data type for this column
CREATE TABLE coma_final (a NUMBER, b NUMBER,);
CREATE TABLE coma_final (a NUMBER, b NUMBER,)
*
ERROR en línea 1:
ORA-00931: missing identifier
ORA-03078: unexpected right parenthesis after ,
Crear una tabla que ya existe:
CREATE TABLE prueba_demo (id NUMBER);
CREATE TABLE prueba_demo (id NUMBER)
*
ERROR en línea 1:
ORA-00955: name is already used by an existing object
3.3. IF NOT EXISTS
⚠️ Importante: Disponible desde Oracle 23ai. En versiones anteriores la sentencia fallaría con error de sintaxis.
CREATE TABLE IF NOT EXISTS crea la tabla solo si no existe; si ya existe, no hace nada y no da error. Es muy útil en scripts que pueden ejecutarse varias veces. Su pareja es DROP TABLE IF EXISTS (módulo 25).
CREATE TABLE IF NOT EXISTS notas_demo (id NUMBER, texto VARCHAR2(20));
CREATE TABLE IF NOT EXISTS notas_demo (otra_cosa DATE);
DESCRIBE notas_demo
Tabla creada.
Tabla creada.
Nombre ¿Nulo? Tipo
----------------------------------------- -------- ----------------------------
ID NUMBER
TEXTO VARCHAR2(20)
⚠️ Importante: Fíjate en la trampa: la segunda sentencia muestra también “Tabla creada.”, pero no ha creado ni modificado nada: la tabla conserva la estructura original.
IF NOT EXISTScomprueba solo el nombre, no la estructura. Tampoco existeCREATE OR REPLACE TABLEen Oracle (ORA-00922: missing or invalid option): para cambiar una tabla se usaALTER TABLE(módulo 25).
En 19c y 21c, la alternativa es simplemente ejecutar el CREATE TABLE y ignorar el error ORA-00955 si la tabla ya existía (es lo que hacen los scripts de EMPRESA con ORA-00942 al borrar).
4. Valores por defecto
4.1. DEFAULT
📘 Concepto: La cláusula
DEFAULT expresiónindica el valor que tomará la columna cuando unINSERTno la mencione. La expresión puede ser un literal, una función comoSYSDATE,SYSTIMESTAMPoUSER, o (desde 12c)secuencia.NEXTVAL. No puede hacer referencia a otras columnas.
CREATE TABLE incidencias_demo (
inc_id NUMBER(6),
titulo VARCHAR2(40),
prioridad VARCHAR2(5) DEFAULT 'MEDIA',
fecha_alta DATE DEFAULT SYSDATE,
creada_por VARCHAR2(30) DEFAULT USER,
puntos NUMBER(3) DEFAULT ON NULL 0
);
-- 1) No se mencionan las columnas con DEFAULT: toman su valor por defecto
INSERT INTO incidencias_demo (inc_id, titulo) VALUES (1, 'Pantalla en negro');
-- 2) Se mencionan con NULL explícito
INSERT INTO incidencias_demo VALUES (2, 'Ratón roto', NULL, NULL, NULL, NULL);
-- 3) Palabra clave DEFAULT en el VALUES
INSERT INTO incidencias_demo VALUES (3, 'Sin red', DEFAULT, DEFAULT, DEFAULT, DEFAULT);
-- 4) Se dan valores propios
INSERT INTO incidencias_demo (inc_id, titulo, prioridad, puntos) VALUES (4, 'Impresora', 'ALTA', 5);
SELECT * FROM incidencias_demo ORDER BY inc_id;
INC_ID TITULO PRIOR FECHA_ALTA CREADA_POR PUNTOS
---------- ------------------ ------ ---------- ---------- ----------
1 Pantalla en negro MEDIA 03/10/2026 CURSO 0
2 Ratón roto (nulo) (nulo) (nulo) 0
3 Sin red MEDIA 03/10/2026 CURSO 0
4 Impresora ALTA 03/10/2026 CURSO 5
4 filas seleccionadas.
Analiza la fila 2: al escribir NULL de forma explícita, prioridad, fecha_alta y creada_por quedan nulas. DEFAULT solo actúa cuando la columna no se menciona (o se usa la palabra DEFAULT), no cuando se inserta un nulo. En cambio, puntos, definida con DEFAULT ON NULL, ha recibido 0 también en la fila 2.
4.2. DEFAULT ON NULL
DEFAULT ON NULL (desde Oracle 12c) aplica el valor por defecto también cuando se inserta un NULL explícito. Implica que la columna es NOT NULL, y por eso no se le puede asignar un nulo con UPDATE:
UPDATE incidencias_demo SET puntos = NULL WHERE inc_id = 4;
UPDATE incidencias_demo SET puntos = NULL WHERE inc_id = 4
*
ERROR en línea 1:
ORA-01407: cannot update ("CURSO"."INCIDENCIAS_DEMO"."PUNTOS") to NULL
⚠️ Importante: Disponible desde Oracle 23ai: la variante
DEFAULT ON NULL FOR INSERT AND UPDATEaplica el valor por defecto también cuando unUPDATEasignaNULL, en lugar de dar error.
CREATE TABLE stock_demo (
art_id NUMBER(4),
unidades NUMBER(5) DEFAULT ON NULL FOR INSERT AND UPDATE 0
);
INSERT INTO stock_demo VALUES (1, NULL);
INSERT INTO stock_demo VALUES (2, 7);
UPDATE stock_demo SET unidades = NULL WHERE art_id = 2;
SELECT * FROM stock_demo;
ART_ID UNIDADES
---------- ----------
1 0
2 0
2 filas seleccionadas.
4.3. Limitaciones de DEFAULT
CREATE TABLE def_malo (a NUMBER, b NUMBER DEFAULT a + 1);
CREATE TABLE def_malo (a NUMBER, b NUMBER DEFAULT a + 1)
*
ERROR en línea 1:
ORA-00904: "A": invalid identifier
El valor por defecto debe ser compatible con el tipo de la columna; Oracle lo comprueba al crear la tabla:
CREATE TABLE def_malo (a NUMBER(2) DEFAULT 'hola');
CREATE TABLE def_malo (a NUMBER(2) DEFAULT 'hola')
*
ERROR en línea 1:
ORA-01722: unable to convert string value containing 'h' to a number:
ORA-03302: (ORA-01722 details) invalid string value: hola
Si necesitas un valor calculado a partir de otras columnas, lo que buscas es una columna virtual (sección 7).
📘 Concepto:
DEFAULTno es una restricción: no impide que se guarde otro valor ni un nulo. Es solo una ayuda para elINSERT. Lo verás comparado con las restricciones en el módulo 24.
5. Tipos de datos de Oracle
📘 Concepto: El tipo de dato de una columna fija el dominio de valores que puede contener (módulo 16), cuánto espacio ocupa, qué operaciones admite (no se puede sumar un texto ni concatenar una fecha sin convertirla) y cómo se ordena y compara.
| Categoría | Tipos | Uso típico |
|---|---|---|
| Caracteres | CHAR, VARCHAR2, NCHAR, NVARCHAR2 | Nombres, códigos, descripciones |
| Numéricos | NUMBER, FLOAT, BINARY_FLOAT, BINARY_DOUBLE | Importes, cantidades, identificadores, medidas científicas |
| Fecha y hora | DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE | Fechas de alta, de pedido, registros de auditoría |
| Intervalos | INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND | Duraciones, plazos |
| Objetos grandes (LOB) | CLOB, NCLOB, BLOB, BFILE | Textos largos, documentos, imágenes |
| Binarios cortos | RAW | Identificadores binarios, hashes |
| Direcciones de fila | ROWID, UROWID | Uso interno y avanzado |
| Lógico | BOOLEAN (23ai) | Indicadores sí/no |
| Documentos | JSON (21c) | Datos semiestructurados |
| Vectores | VECTOR (23ai) | Búsqueda por similitud con IA |
| Heredados | LONG, LONG RAW | Obsoletos: no los uses (sustitúyelos por CLOB y BLOB) |
5.1. Tipos de caracteres
| Tipo | Longitud | Máximo | Relleno | Juego de caracteres |
|---|---|---|---|---|
CHAR(n) | Fija | 2000 bytes | Rellena con espacios hasta n | El de la base de datos (AL32UTF8) |
VARCHAR2(n) | Variable | 4000 bytes (32767 con MAX_STRING_SIZE = EXTENDED) | No rellena | El de la base de datos |
NCHAR(n) | Fija | 2000 bytes | Rellena | Nacional (AL16UTF16) |
NVARCHAR2(n) | Variable | 4000 bytes (32767 extendido) | No rellena | Nacional |
CHAR frente a VARCHAR2
CREATE TABLE textos_demo (
c_char CHAR(10),
c_varchar2 VARCHAR2(10)
);
INSERT INTO textos_demo VALUES ('Hola', 'Hola');
SELECT '[' || c_char || ']' AS c_char,
'[' || c_varchar2 || ']' AS c_varchar2,
LENGTH(c_char) AS long_char,
LENGTH(c_varchar2) AS long_varchar2
FROM textos_demo;
C_CHAR C_VARCHAR2 LONG_CHAR LONG_VARCHAR2
------------ ------------ ---------- -------------
[Hola ] [Hola] 10 4
1 fila seleccionada.
CHAR(10) ha guardado 'Hola' seguido de 6 espacios: siempre ocupa 10 caracteres. VARCHAR2(10) guarda solo los 4 caracteres reales. Esto tiene consecuencias en las comparaciones:
SELECT COUNT(*) FROM textos_demo WHERE c_char = 'Hola'; -- devuelve 1
SELECT COUNT(*) FROM textos_demo WHERE c_char = c_varchar2; -- devuelve 0
Al comparar una columna CHAR con un literal, Oracle rellena el literal con espacios (“comparación con relleno”) y coinciden. Pero al comparar CHAR con VARCHAR2 compara sin relleno: 'Hola ' no es igual a 'Hola'. Es una fuente clásica de errores.
Si el valor no cabe, Oracle no lo recorta: da error.
INSERT INTO textos_demo VALUES ('Hola mundo cruel', NULL);
INSERT INTO textos_demo VALUES ('Hola mundo cruel', NULL)
*
ERROR en línea 1:
ORA-12899: value too large for column "CURSO"."TEXTOS_DEMO"."C_CHAR" (actual: 16, maximum: 10)
⚠️ Importante: En Oracle, la cadena vacía
''esNULL. Es una particularidad que no siguen otros SGBD (en PostgreSQL o SQL Server''yNULLson cosas distintas).
INSERT INTO textos_demo VALUES ('', '');
SELECT COUNT(*) AS filas, COUNT(c_varchar2) AS no_nulos FROM textos_demo;
FILAS NO_NULOS
---------- ----------
2 1
1 fila seleccionada.
COUNT(columna) solo cuenta los valores no nulos: la cadena vacía se ha guardado como NULL.
💡 Consejo: Usa
VARCHAR2para casi todo. ReservaCHARpara códigos de longitud realmente fija y corta:CHAR(1)para indicadores'S'/'N',CHAR(2)para códigos de país ISO o de idioma. Para el DNI o el NIF (siempre 9 caracteres) ambos sirven, aunqueVARCHAR2(9)evita las sorpresas de las comparaciones.
Semántica BYTE frente a CHAR
¿VARCHAR2(10) significa 10 bytes o 10 caracteres? Por defecto, bytes (parámetro NLS_LENGTH_SEMANTICS = BYTE). En una base de datos AL32UTF8 cada carácter ocupa de 1 a 4 bytes: las letras inglesas 1 byte, las vocales con tilde y la eñe 2 bytes, el símbolo del euro 3 bytes, muchos emojis 4 bytes. Puedes indicar la semántica de forma explícita:
CREATE TABLE semantica_demo (
en_bytes VARCHAR2(5 BYTE),
en_chars VARCHAR2(5 CHAR)
);
INSERT INTO semantica_demo VALUES ('ABCDE', 'ABCDE'); -- 5 bytes en ambas: correcto
INSERT INTO semantica_demo (en_chars) VALUES ('ÁÉÍÓÚ'); -- 5 caracteres: correcto
INSERT INTO semantica_demo (en_bytes) VALUES ('ÁÉÍÓÚ'); -- ¡10 bytes!
INSERT INTO semantica_demo (en_bytes) VALUES ('ÁÉÍÓÚ')
*
ERROR en línea 1:
ORA-12899: value too large for column "CURSO"."SEMANTICA_DEMO"."EN_BYTES" (actual: 10, maximum: 5)
INSERT INTO semantica_demo (en_bytes) VALUES ('ÁÉ');
SELECT en_bytes, LENGTH(en_bytes) AS caracteres, LENGTHB(en_bytes) AS bytes,
en_chars, LENGTH(en_chars) AS caracteres2, LENGTHB(en_chars) AS bytes2
FROM semantica_demo;
EN_BYTES CARACTERES BYTES EN_CHARS CARACTERES2 BYTES2
-------- ---------- ---------- --------- ----------- ----------
ABCDE 5 5 ABCDE 5 5
(nulo) (nulo) (nulo) ÁÉÍÓÚ 5 10
ÁÉ 2 4 (nulo) (nulo) (nulo)
3 filas seleccionadas.
LENGTH cuenta caracteres y LENGTHB cuenta bytes. En el diccionario, DATA_LENGTH es siempre el máximo en bytes, CHAR_LENGTH el máximo en caracteres y CHAR_USED la semántica (B o C):
SELECT column_name, data_type, data_length, char_length, char_used
FROM user_tab_columns
WHERE table_name = 'SEMANTICA_DEMO';
COLUMN_NAME DATA_TYPE DATA_LENGTH CHAR_LENGTH C
------------ ---------- ----------- ----------- -
EN_BYTES VARCHAR2 5 5 B
EN_CHARS VARCHAR2 20 5 C
2 filas seleccionadas.
Oracle reserva 20 bytes para en_chars (5 caracteres x 4 bytes como máximo), pero sigue sin permitir más de 5 caracteres.
💡 Consejo: Para textos en español, la semántica
CHARes la más natural (“este nombre admite 40 caracteres”). Puedes ponerla columna a columna (VARCHAR2(40 CHAR)) o para toda la sesión conALTER SESSION SET NLS_LENGTH_SEMANTICS = CHAR;antes de crear las tablas. En el esquema EMPRESA se usa la semántica por defecto (bytes) y se han dimensionado las columnas con holgura.
El límite de 4000 bytes
CREATE TABLE largo_demo (texto VARCHAR2(4001));
CREATE TABLE largo_demo (texto VARCHAR2(4001))
*
ERROR en línea 1:
ORA-00910: specified length too long for its datatype
El máximo de VARCHAR2 depende del parámetro de inicialización MAX_STRING_SIZE: con STANDARD (el valor por defecto, y el de la edición Free) es de 4000 bytes; con EXTENDED llega a 32767 bytes. Cambiar a EXTENDED es una operación de administración irreversible. Para textos de más de 4000 bytes, la solución habitual es CLOB (sección 5.4).
Además, VARCHAR2 exige indicar la longitud:
CREATE TABLE largo_demo (texto VARCHAR2);
CREATE TABLE largo_demo (texto VARCHAR2)
*
ERROR en línea 1:
ORA-00906: missing left parenthesis
NCHAR y NVARCHAR2
Usan el juego de caracteres nacional (AL16UTF16, 2 bytes por carácter casi siempre) y su longitud se expresa siempre en caracteres. Se crearon para guardar Unicode en bases de datos cuyo juego principal no lo era. Hoy, con bases de datos en AL32UTF8, rara vez son necesarios.
CREATE TABLE nchar_demo (n1 NCHAR(5), n2 NVARCHAR2(10));
INSERT INTO nchar_demo VALUES (N'Peña', N'Ñandú');
SELECT n2, LENGTH(n2), LENGTHB(n2) FROM nchar_demo;
N2 LENGTH(N2) LENGTHB(N2)
---------- ---------- -----------
Ñandú 5 10
1 fila seleccionada.
El prefijo N'...' indica un literal de texto nacional.
5.2. Tipos numéricos
NUMBER(p, s)
NUMBER es el tipo numérico principal de Oracle: un número decimal exacto (no hay errores de redondeo binario) que ocupa de 1 a 22 bytes.
- p (precision, precisión): número total de dígitos significativos, de 1 a 38.
- s (scale, escala): dígitos a la derecha de la coma decimal, de -84 a 127. Si es negativa, redondea a la izquierda de la coma.
- Dígitos enteros permitidos:
p - s.
| Declaración | Significado | Rango aproximado |
|---|---|---|
NUMBER | Sin restricciones: hasta 38 dígitos significativos, cualquier escala | ±1E-130 a ±1E126 |
NUMBER(5) | Entero de hasta 5 cifras (equivale a NUMBER(5,0)) | -99999 a 99999 |
NUMBER(5,2) | 5 dígitos, 2 decimales: 3 enteros y 2 decimales | -999.99 a 999.99 |
NUMBER(8,2) | Típico para importes en euros | -999999.99 a 999999.99 |
NUMBER(5,-2) | Redondea a centenas; 5 dígitos significativos | hasta 9999900 |
NUMBER(3,5) | Solo decimales muy pequeños: 0,00xyz | -0.00999 a 0.00999 |
NUMBER(*,2) | Precisión máxima (38) con 2 decimales |
Las reglas al guardar un valor son dos:
- Oracle redondea el valor a la escala
s(redondeo aritmético: 5 o más, hacia arriba). - Si después de redondear el número tiene más dígitos enteros de los permitidos (
p - s), da el errorORA-01438.
Compruébalo con una tabla de pruebas:
CREATE TABLE numeros_demo (
n_libre NUMBER,
n_5_2 NUMBER(5,2),
n_5 NUMBER(5),
n_5_m2 NUMBER(5,-2),
n_3_5 NUMBER(3,5)
);
INSERT INTO numeros_demo (n_libre, n_5_2) VALUES (123.456, 123.456);
INSERT INTO numeros_demo (n_libre, n_5_2) VALUES (999.994, 999.994);
INSERT INTO numeros_demo (n_libre, n_5_2) VALUES (999.995, 999.995);
INSERT INTO numeros_demo (n_libre, n_5_2) VALUES (1234.5, 1234.5);
INSERT INTO numeros_demo (n_libre, n_5_2) VALUES (-0.001, -0.001);
INSERT INTO numeros_demo (n_libre, n_5) VALUES (123.6, 123.6);
INSERT INTO numeros_demo (n_libre, n_5) VALUES (99999.4, 99999.4);
INSERT INTO numeros_demo (n_libre, n_5) VALUES (99999.5, 99999.5);
INSERT INTO numeros_demo (n_libre, n_5_m2) VALUES (12345, 12345);
INSERT INTO numeros_demo (n_libre, n_5_m2) VALUES (1234567, 1234567);
INSERT INTO numeros_demo (n_libre, n_5_m2) VALUES (12345678, 12345678);
INSERT INTO numeros_demo (n_libre, n_3_5) VALUES (0.00123, 0.00123);
INSERT INTO numeros_demo (n_libre, n_3_5) VALUES (0.001234, 0.001234);
INSERT INTO numeros_demo (n_libre, n_3_5) VALUES (0.01, 0.01);
Cuatro de las sentencias fallan:
INSERT INTO numeros_demo (n_libre, n_5_2) VALUES (999.995, 999.995)
*
ERROR en línea 1:
ORA-01438: value 999.995 greater than specified precision (5, 2) for column
INSERT INTO numeros_demo (n_libre, n_5_2) VALUES (1234.5, 1234.5)
*
ERROR en línea 1:
ORA-01438: value 1234.5 greater than specified precision (5, 2) for column
INSERT INTO numeros_demo (n_libre, n_5) VALUES (99999.5, 99999.5)
*
ERROR en línea 1:
ORA-01438: value 99999.5 greater than specified precision (5, 0) for column
INSERT INTO numeros_demo (n_libre, n_5_m2) VALUES (12345678, 12345678)
*
ERROR en línea 1:
ORA-01438: value 12345678 greater than specified precision (5, -2) for column
INSERT INTO numeros_demo (n_libre, n_3_5) VALUES (0.01, 0.01)
*
ERROR en línea 1:
ORA-01438: value .01 greater than specified precision (3, 5) for column
Y esto es lo que se ha guardado (la columna n_libre conserva el valor original para comparar):
SELECT * FROM numeros_demo;
N_LIBRE N_5_2 N_5 N_5_M2 N_3_5
---------- ---------- ---------- ---------- ----------
123,456 123,46 (nulo) (nulo) (nulo)
999,994 999,99 (nulo) (nulo) (nulo)
-,001 0 (nulo) (nulo) (nulo)
123,6 (nulo) 124 (nulo) (nulo)
99999,4 (nulo) 99999 (nulo) (nulo)
12345 (nulo) (nulo) 12300 (nulo)
1234567 (nulo) (nulo) 1234600 (nulo)
,00123 (nulo) (nulo) (nulo) ,00123
,001234 (nulo) (nulo) (nulo) ,00123
9 filas seleccionadas.
(Los decimales se muestran con coma porque la sesión está en español. SQL*Plus omite el cero inicial: ,001 es 0,001.)
Resumen de lo ocurrido:
| Columna | Valor insertado | Resultado | Por qué |
|---|---|---|---|
NUMBER(5,2) | 123.456 | 123.46 | Redondea a 2 decimales |
NUMBER(5,2) | 999.994 | 999.99 | Redondea hacia abajo y cabe |
NUMBER(5,2) | 999.995 | ORA-01438 | Al redondear se convierte en 1000.00: 4 enteros, solo caben 3 |
NUMBER(5,2) | 1234.5 | ORA-01438 | 4 dígitos enteros |
NUMBER(5,2) | -0.001 | 0 | Redondea a 0,00 |
NUMBER(5) | 123.6 | 124 | Escala 0: redondea a entero |
NUMBER(5) | 99999.5 | ORA-01438 | Redondea a 100000: 6 cifras |
NUMBER(5,-2) | 12345 | 12300 | Redondea a centenas |
NUMBER(5,-2) | 1234567 | 1234600 | 5 dígitos significativos + 2 ceros: cabe |
NUMBER(5,-2) | 12345678 | ORA-01438 | Necesitaría 6 dígitos significativos |
NUMBER(3,5) | 0.001234 | 0.00123 | Redondea a 5 decimales |
NUMBER(3,5) | 0.01 | ORA-01438 | Exige al menos 2 ceros tras la coma (5 - 3 = 2) |
⚠️ Importante: El redondeo es silencioso: si defines un precio como
NUMBER(8)(sin decimales) y guardas 19,90, Oracle guardará 20 sin avisar. Revisa siempre la escala de las columnas monetarias.
NUMBER sin precisión guarda el valor tal cual, con hasta 38-40 dígitos significativos:
INSERT INTO numeros_demo (n_libre) VALUES (1/3);
SET NUMWIDTH 45
SELECT n_libre FROM numeros_demo WHERE n_5_2 IS NULL AND n_5 IS NULL
AND n_5_m2 IS NULL AND n_3_5 IS NULL;
SET NUMWIDTH 10
N_LIBRE
---------------------------------------------
,3333333333333333333333333333333333333333
1 fila seleccionada.
Los límites de la declaración también se validan:
CREATE TABLE numero_malo (n NUMBER(39));
CREATE TABLE numero_malo (n NUMBER(5,130));
ERROR en línea 1:
ORA-01727: numeric precision specifier is out of range (1 to 38)
ERROR en línea 1:
ORA-01728: numeric scale specifier is out of range (-84 to 127)
⚠️ Importante: Al convertir textos en números, Oracle usa el separador decimal de la sesión. Con la sesión en español,
INSERT INTO numeros_demo (n_5_2) VALUES ('12,5');funciona (guarda 12,5), pero el mismo script fallaría en una sesión en inglés. Escribe los números sin comillas y con punto:VALUES (12.5). Un texto no numérico daORA-01722: unable to convert string value containing 'a' to a number.
Enteros
Oracle no tiene un tipo entero propio: los enteros son NUMBER(p) (escala 0). Los tipos ANSI INTEGER, INT y SMALLINT se convierten en NUMBER(38) (sección 5.9). Elige la precisión según el rango esperado:
| Dato | Tipo recomendado |
|---|---|
| Edad, número de planta, mes | NUMBER(3) o NUMBER(2) |
| Año | NUMBER(4) |
| Identificador de una tabla pequeña (departamentos, categorías) | NUMBER(4) |
| Identificador de una tabla grande (pedidos, movimientos) | NUMBER(10) o más |
| Cantidad de unidades | NUMBER(5) o NUMBER(6) |
FLOAT, BINARY_FLOAT y BINARY_DOUBLE
| Tipo | Naturaleza | Tamaño | Precisión |
|---|---|---|---|
FLOAT(p) | Subtipo de NUMBER (decimal) con precisión en dígitos binarios (1 a 126) | Como NUMBER | FLOAT(126) ≈ 38 dígitos decimales |
BINARY_FLOAT | Coma flotante binaria IEEE 754 de 32 bits | 4 bytes | ≈ 7 dígitos |
BINARY_DOUBLE | Coma flotante binaria IEEE 754 de 64 bits | 8 bytes | ≈ 15-16 dígitos |
Los tipos binarios son muy rápidos para cálculos científicos y admiten valores especiales (infinito, NaN), pero no son exactos:
CREATE TABLE bin_demo (bf BINARY_FLOAT, bd BINARY_DOUBLE, nm NUMBER, fl FLOAT(10));
INSERT INTO bin_demo VALUES (0.1, 0.1, 0.1, 0.1);
SELECT TO_CHAR(bf + 0.2, '0.99999999999999999999') AS bf_suma,
TO_CHAR(bd + 0.2D, '0.99999999999999999999') AS bd_suma,
TO_CHAR(nm + 0.2, '0.99999999999999999999') AS nm_suma
FROM bin_demo;
BF_SUMA BD_SUMA NM_SUMA
----------------------- ----------------------- -----------------------
0.30000001200000000000 0.30000000000000004000 0.30000000000000000000
1 fila seleccionada.
SELECT CASE WHEN 0.1D + 0.2D = 0.3D THEN 'iguales' ELSE 'distintos' END AS binary_double,
CASE WHEN 0.1 + 0.2 = 0.3 THEN 'iguales' ELSE 'distintos' END AS number_dec
FROM dual;
BINARY_DO NUMBER_
--------- -------
distintos iguales
1 fila seleccionada.
El sufijo D (o F) convierte un literal en BINARY_DOUBLE (o BINARY_FLOAT). En binario, 0,1 no tiene representación exacta, igual que 1/3 no la tiene en decimal. Además:
SELECT 1/0D AS infinito, 0/0D AS no_numero FROM dual;
INFINITO NO_NUMERO
---------- ----------
Inf Nan
1 fila seleccionada.
Mientras que con NUMBER la división entre cero es un error: SELECT 1/0 FROM dual; produce ORA-01476: divisor is equal to zero. Y FLOAT(10) (10 bits ≈ 3 dígitos decimales) redondea mucho: al guardar 123456.789 se almacena 123500.
⚠️ Importante: Nunca uses
BINARY_FLOAT,BINARY_DOUBLEniFLOATpara dinero. Usa siempreNUMBER(p,s).
5.3. Fecha y hora
| Tipo | Contenido | Precisión | Tamaño |
|---|---|---|---|
DATE | Año, mes, día, hora, minuto y segundo | Segundos | 7 bytes |
TIMESTAMP[(f)] | Como DATE + fracciones de segundo | f de 0 a 9 dígitos (por defecto 6) | 7-11 bytes |
TIMESTAMP[(f)] WITH TIME ZONE | TIMESTAMP + zona horaria (desplazamiento o región) | 13 bytes | |
TIMESTAMP[(f)] WITH LOCAL TIME ZONE | Se normaliza a la zona de la base de datos al guardar y se muestra en la zona de la sesión | 7-11 bytes |
El rango de DATE va del 1 de enero de 4712 a. C. al 31 de diciembre de 9999.
DATE siempre tiene hora
⚠️ Importante: A diferencia del estándar SQL (donde
DATEes solo la fecha), elDATEde Oracle siempre guarda la hora. Si no se indica, es medianoche (00:00:00). Que el formato de la sesión no la muestre no significa que no esté.
CREATE TABLE fechas_demo (
id NUMBER(2),
f_date DATE,
f_ts TIMESTAMP,
f_ts3 TIMESTAMP(3),
f_tstz TIMESTAMP WITH TIME ZONE,
f_tsltz TIMESTAMP WITH LOCAL TIME ZONE
);
INSERT INTO fechas_demo VALUES (1,
TO_DATE('05/10/2026 17:45:30', 'DD/MM/YYYY HH24:MI:SS'),
TIMESTAMP '2026-10-05 17:45:30.123456',
TIMESTAMP '2026-10-05 17:45:30.123456',
TIMESTAMP '2026-10-05 17:45:30.123456 +02:00',
TIMESTAMP '2026-10-05 17:45:30.123456 +02:00');
SELECT f_date FROM fechas_demo;
SELECT TO_CHAR(f_date, 'DD/MM/YYYY HH24:MI:SS') AS fecha_completa FROM fechas_demo;
F_DATE
----------
05/10/2026
FECHA_COMPLETA
-------------------
05/10/2026 17:45:30
La consecuencia más importante: una comparación de igualdad con una fecha “sin hora” no encuentra la fila:
SELECT COUNT(*) FROM fechas_demo WHERE f_date = DATE '2026-10-05'; -- 0
SELECT COUNT(*) FROM fechas_demo WHERE TRUNC(f_date) = DATE '2026-10-05'; -- 1
TRUNC elimina la parte horaria (la verás en el módulo 28). Por eso, si una columna representa solo un día (fecha de nacimiento, de alta…), conviene insertar los valores sin hora, como hace EMPRESA con los literales DATE '...', aunque al usar SYSDATE como valor por defecto se guarda también la hora actual.
La aritmética de fechas trabaja en días: sumar 1 es sumar un día, sumar 1/24 es sumar una hora, y restar dos fechas da el número de días entre ellas.
SELECT DATE '2026-10-05' + 1 AS manana,
TO_CHAR(DATE '2026-10-05' + 1/24, 'DD/MM/YYYY HH24:MI') AS mas_una_hora,
DATE '2026-12-25' - DATE '2026-10-05' AS dias_hasta_navidad
FROM dual;
MANANA MAS_UNA_HORA DIAS_HASTA_NAVIDAD
---------- ---------------- ------------------
06/10/2026 05/10/2026 01:00 81
1 fila seleccionada.
TIMESTAMP y zonas horarias
COLUMN f_ts FORMAT A30
COLUMN f_ts3 FORMAT A30
COLUMN f_tstz FORMAT A36
COLUMN f_tsltz FORMAT A30
SELECT f_ts, f_ts3 FROM fechas_demo;
F_TS F_TS3
------------------------------ ------------------------------
05/10/26 17:45:30,123456 05/10/26 17:45:30,123
1 fila seleccionada.
TIMESTAMP(3) ha redondeado a milisegundos. El formato de visualización lo marca NLS_TIMESTAMP_FORMAT (en España, DD/MM/RR HH24:MI:SSXFF).
Las dos variantes con zona horaria se comportan de forma muy distinta. La zona horaria de la sesión es, en el contenedor Docker, +00:00 (UTC):
SELECT f_tstz, f_tsltz FROM fechas_demo;
F_TSTZ F_TSLTZ
------------------------------------ ------------------------------
05/10/26 17:45:30,123456 +02:00 05/10/26 15:45:30,123456
1 fila seleccionada.
WITH TIME ZONEguarda el instante y su zona (+02:00): se muestra tal cual se insertó.WITH LOCAL TIME ZONEconvierte el instante a la zona de quien consulta: las 17:45 en+02:00son las 15:45 en UTC.
Si cambiamos la zona de la sesión, solo cambia la segunda columna:
ALTER SESSION SET TIME_ZONE = 'America/Mexico_City';
SELECT f_tstz, f_tsltz FROM fechas_demo;
ALTER SESSION SET TIME_ZONE = 'Europe/Madrid';
SELECT f_tstz, f_tsltz FROM fechas_demo;
F_TSTZ F_TSLTZ
------------------------------------ ------------------------------
05/10/26 17:45:30,123456 +02:00 05/10/26 09:45:30,123456
F_TSTZ F_TSLTZ
------------------------------------ ------------------------------
05/10/26 17:45:30,123456 +02:00 05/10/26 17:45:30,123456
💡 Consejo: Para la mayoría de aplicaciones de gestión basta con
DATE. UsaTIMESTAMPcuando necesites fracciones de segundo (registros de actividad, auditoría) yTIMESTAMP WITH TIME ZONEcuando los usuarios estén en husos horarios distintos (reservas de vuelos, reuniones internacionales).
Intervalos
Los intervalos representan duraciones, no instantes:
| Tipo | Contenido | Literales |
|---|---|---|
INTERVAL YEAR[(p)] TO MONTH | Años y meses (p = dígitos del año, por defecto 2) | INTERVAL '2-6' YEAR TO MONTH, INTERVAL '18' MONTH |
INTERVAL DAY[(p)] TO SECOND[(f)] | Días, horas, minutos, segundos y fracciones | INTERVAL '3 08:30:00' DAY TO SECOND, INTERVAL '90' MINUTE |
CREATE TABLE intervalos_demo (
duracion_contrato INTERVAL YEAR(2) TO MONTH,
duracion_tarea INTERVAL DAY(3) TO SECOND(0)
);
INSERT INTO intervalos_demo VALUES (INTERVAL '2-6' YEAR TO MONTH, INTERVAL '3 08:30:00' DAY TO SECOND);
INSERT INTO intervalos_demo VALUES (INTERVAL '18' MONTH, INTERVAL '90' MINUTE);
SELECT * FROM intervalos_demo;
DURACION_CONTRATO DURACION_TAREA
-------------------- -------------------------
+02-06 +003 08:30:00
+01-06 +000 01:30:00
2 filas seleccionadas.
Oracle normaliza: 18 meses se guardan como 1 año y 6 meses; 90 minutos como 1 hora y 30 minutos. Los intervalos se suman a fechas y se obtienen restando marcas de tiempo:
SELECT DATE '2026-01-15' + INTERVAL '1' MONTH AS mas_un_mes,
TIMESTAMP '2026-10-05 10:00:00' + INTERVAL '3 08:30:00' DAY TO SECOND AS fin_tarea
FROM dual;
SELECT TIMESTAMP '2026-10-05 18:00:00' - TIMESTAMP '2026-10-05 09:15:00' AS diferencia FROM dual;
MAS_UN_MES FIN_TAREA
---------- ----------------------------------
15/02/2026 08/10/26 18:30:00,000000000
DIFERENCIA
-----------------------------
+000000000 08:45:00.000000000
⚠️ Importante: Sumar
INTERVAL '1' MONTHal 31 de enero da error, porque el 31 de febrero no existe:ORA-01839: date not valid for month specified. Para sumar meses respetando el fin de mes se usa la funciónADD_MONTHS(módulo 28).
5.4. Objetos grandes (LOB)
Los LOB (Large Objects) almacenan datos de gran tamaño, hasta varios terabytes (en concreto, 4 GB menos 1 multiplicado por el tamaño de bloque).
| Tipo | Contenido | Ejemplo de uso |
|---|---|---|
CLOB | Texto en el juego de caracteres de la base de datos | Artículos, contratos, descripciones largas, XML |
NCLOB | Texto en el juego de caracteres nacional | Como CLOB en bases de datos no Unicode |
BLOB | Datos binarios | Imágenes, PDF, audio, ficheros comprimidos |
BFILE | Puntero a un fichero externo del servidor, de solo lectura | Archivos multimedia gestionados fuera de la BD |
CREATE TABLE documentos_demo (
doc_id NUMBER(4),
titulo VARCHAR2(100),
contenido CLOB,
foto BLOB,
huella RAW(16)
);
INSERT INTO documentos_demo VALUES (1, 'Manual', 'Texto muy largo del manual...',
HEXTORAW('FFD8FFE0'), HEXTORAW('0A1B2C3D'));
INSERT INTO documentos_demo VALUES (2, 'Vacío', EMPTY_CLOB(), EMPTY_BLOB(), SYS_GUID());
SELECT doc_id, LENGTH(contenido) AS long_texto, DBMS_LOB.GETLENGTH(foto) AS bytes_foto
FROM documentos_demo;
DOC_ID LONG_TEXTO BYTES_FOTO
---------- ---------- ----------
1 29 4
2 0 0
2 filas seleccionadas.
EMPTY_CLOB() y EMPTY_BLOB() crean un LOB vacío (longitud 0), que no es lo mismo que NULL. En una aplicación real, los BLOB se cargan desde el programa cliente (Java, Python…), no escribiendo los bytes en hexadecimal.
Los LOB tienen limitaciones importantes: no se pueden comparar con =, ni ordenar, ni agrupar, ni formar parte de claves primarias o índices normales.
SELECT doc_id FROM documentos_demo WHERE contenido = 'Vacío';
SELECT doc_id FROM documentos_demo WHERE contenido = 'Vacío'
*
ERROR en línea 1:
ORA-22848: cannot use CLOB type as comparison key
LIKE sí está permitido sobre un CLOB (WHERE contenido LIKE 'Texto%' devuelve el documento 1). Internamente, cada columna LOB se guarda en su propio segmento:
SELECT column_name, segment_name FROM user_lobs WHERE table_name = 'DOCUMENTOS_DEMO';
COLUMN_NAME SEGMENT_NAME
--------------- ------------------------------
CONTENIDO SYS_LOB0000073462C00003$$
FOTO SYS_LOB0000073462C00004$$
2 filas seleccionadas.
Para usar BFILE hace falta un objeto DIRECTORY creado por un administrador, que apunta a una carpeta del servidor; los datos no se guardan en la base de datos y, por tanto, no participan en las transacciones ni en las copias de seguridad de la BD.
💡 Consejo: Antes de guardar ficheros grandes en un
BLOB, valora la alternativa de guardarlos en un sistema de ficheros o almacenamiento de objetos y guardar en la tabla solo la ruta (VARCHAR2). Ambas opciones son válidas; elBLOBgana en integridad y transaccionalidad, la ruta en sencillez y tamaño de la base de datos.
5.5. RAW y ROWID
RAW(n) guarda datos binarios cortos (hasta 2000 bytes, 32767 con MAX_STRING_SIZE = EXTENDED), que se muestran en hexadecimal. Su uso típico son los identificadores globales únicos: SYS_GUID() genera un valor RAW(16) distinto cada vez.
COLUMN huella FORMAT A34
SELECT doc_id, titulo, huella FROM documentos_demo;
DOC_ID TITULO HUELLA
---------- ---------- ----------------------------------
1 Manual 0A1B2C3D
2 Vacío 5CF8751550D40862E063020011ACDCB6
2 filas seleccionadas.
ROWID es la dirección física de una fila: identifica el objeto, el fichero, el bloque y la posición de la fila dentro del bloque. Toda tabla tiene una pseudocolumna ROWID que puedes consultar:
SELECT ROWID, dept_id, nombre FROM departamentos WHERE dept_id <= 30;
ROWID DEPT_ID NOMBRE
------------------ ---------- --------------------
AAAR5FAAAAAAAPEAAA 10 DIRECCIÓN
AAAR5FAAAAAAAPEAAB 20 VENTAS
AAAR5FAAAAAAAPEAAC 30 INFORMÁTICA
3 filas seleccionadas.
Acceder por ROWID es la forma más rápida de llegar a una fila (es lo que hacen los índices internamente), pero no lo uses como clave: puede cambiar si la tabla se reorganiza o se exporta e importa. Existe un tipo de dato ROWID para guardar estas direcciones en una columna (y UROWID para direcciones “universales”), pero su uso en tablas de aplicación es excepcional; lo verás en el módulo 24 en la tabla de excepciones de las restricciones.
5.6. BOOLEAN
⚠️ Importante: Disponible desde Oracle 23ai. En 19c y 21c el tipo
BOOLEANexistía solo en PL/SQL, no en las tablas.
El tipo BOOLEAN del estándar SQL:1999 admite tres valores: TRUE, FALSE y NULL (desconocido).
CREATE TABLE tareas_demo (
tarea_id NUMBER(4),
titulo VARCHAR2(30),
completada BOOLEAN DEFAULT FALSE
);
INSERT INTO tareas_demo VALUES (1, 'Instalar Oracle', TRUE);
INSERT INTO tareas_demo VALUES (2, 'Cargar EMPRESA', 'yes');
INSERT INTO tareas_demo VALUES (3, 'Leer el módulo 23', 0);
INSERT INTO tareas_demo (tarea_id, titulo) VALUES (4, 'Hacer los ejercicios');
INSERT INTO tareas_demo VALUES (5, 'Repasar', NULL);
INSERT INTO tareas_demo VALUES (6, 'Error', 'quizas');
La última falla:
INSERT INTO tareas_demo VALUES (6, 'Error', 'quizas')
*
ERROR en línea 1:
ORA-61800: invalid boolean literal: quizas
SELECT * FROM tareas_demo;
TAREA_ID TITULO COMPLETADA
---------- ---------------------- -----------
1 Instalar Oracle TRUE
2 Cargar EMPRESA TRUE
3 Leer el módulo 23 FALSE
4 Hacer los ejercicios FALSE
5 Repasar (nulo)
5 filas seleccionadas.
Oracle convierte a BOOLEAN los textos 'TRUE'/'FALSE', 'YES'/'NO', 'ON'/'OFF', 'Y'/'N', 'T'/'F' y '1'/'0' (sin distinguir mayúsculas) y los números (0 es falso, cualquier otro es verdadero). Lo mejor es que una columna booleana puede usarse directamente como condición:
SELECT tarea_id, titulo FROM tareas_demo WHERE completada;
SELECT tarea_id, titulo FROM tareas_demo WHERE NOT completada;
TAREA_ID TITULO
---------- ----------------------
1 Instalar Oracle
2 Cargar EMPRESA
TAREA_ID TITULO
---------- ----------------------
3 Leer el módulo 23
4 Hacer los ejercicios
La tarea 5 (nula) no aparece en ninguna de las dos consultas: ni es verdadera ni es falsa. Para encontrarla hay que usar WHERE completada IS NULL.
Alternativa clásica (19c, 21c, o para máxima compatibilidad): CHAR(1) con una restricción CHECK, como la columna descatalogado de PRODUCTOS. Otra opción habitual es NUMBER(1) con CHECK (col IN (0, 1)).
CREATE TABLE tareas_clasico (
tarea_id NUMBER(4),
completada CHAR(1) DEFAULT 'N'
CONSTRAINT ck_tar_completada CHECK (completada IN ('S', 'N'))
);
INSERT INTO tareas_clasico VALUES (1, 'S'); -- correcto
INSERT INTO tareas_clasico VALUES (2, 's'); -- falla: la minúscula no está permitida
INSERT INTO tareas_clasico VALUES (2, 's')
*
ERROR en línea 1:
ORA-02290: check constraint (CURSO.CK_TAR_COMPLETADA) violated
5.7. JSON
⚠️ Importante: El tipo de dato
JSONestá disponible desde Oracle 21c. En 19c se guarda el JSON enVARCHAR2,CLOBoBLOBcon una restricciónCHECK (columna IS JSON)(disponible desde 12.1.0.2).
El tipo JSON guarda documentos JSON en un formato binario optimizado (OSON), valida que sean JSON correcto y permite consultarlos con notación de puntos:
CREATE TABLE eventos_demo (
evento_id NUMBER(6),
datos JSON
);
INSERT INTO eventos_demo VALUES (1, '{"tipo":"login","usuario":"ana","dispositivo":{"so":"Android","version":15}}');
INSERT INTO eventos_demo VALUES (2, '{"tipo":"compra","usuario":"luis","importe":149.90,"productos":[6,9]}');
INSERT INTO eventos_demo VALUES (3, '{"tipo": "error" mal formado');
INSERT INTO eventos_demo VALUES (3, '{"tipo": "error" mal formado')
*
ERROR en línea 1:
ORA-40441: JSON syntax error
JZN-00073: missing colon in member:value pair (line 1, position 18)
SELECT e.evento_id,
e.datos.tipo.string() AS tipo,
e.datos.usuario.string() AS usuario,
e.datos.dispositivo.so.string() AS so
FROM eventos_demo e;
SELECT evento_id, JSON_VALUE(datos, '$.importe' RETURNING NUMBER) AS importe FROM eventos_demo;
EVENTO_ID TIPO USUARIO SO
---------- ---------- ---------- ----------
1 login ana Android
2 compra luis (nulo)
EVENTO_ID IMPORTE
---------- ----------
1 (nulo)
2 149,9
La notación de puntos requiere un alias de tabla (e). Los atributos que no existen en un documento devuelven NULL. La alternativa de 19c:
CREATE TABLE eventos_19c (
evento_id NUMBER(6),
datos VARCHAR2(4000) CONSTRAINT ck_evt_json CHECK (datos IS JSON)
);
INSERT INTO eventos_19c VALUES (2, 'esto no es JSON');
ERROR en línea 1:
ORA-02290: check constraint (CURSO.CK_EVT_JSON) violated
💡 Consejo: JSON es útil para datos semiestructurados (atributos que varían mucho de un registro a otro, configuración, respuestas de servicios web). No lo uses para evitar diseñar tablas: un dato que vas a filtrar, combinar o restringir con integridad referencial debe ser una columna normal, como has aprendido en la normalización.
5.8. VECTOR
⚠️ Importante: Disponible desde Oracle 23ai.
El tipo VECTOR(dimensiones, formato) guarda vectores numéricos (embeddings) generados por modelos de inteligencia artificial, que representan el significado de un texto o una imagen. Con la función VECTOR_DISTANCE se buscan los registros más parecidos (búsqueda semántica), base de las aplicaciones de IA generativa con datos propios. Un ejemplo de juguete con vectores de 3 dimensiones:
CREATE TABLE frases_demo (
frase_id NUMBER(4),
texto VARCHAR2(60),
embedding VECTOR(3, FLOAT32)
);
INSERT INTO frases_demo VALUES (1, 'El gato duerme', '[0.9, 0.1, 0.0]');
INSERT INTO frases_demo VALUES (2, 'El felino descansa', '[0.85, 0.15, 0.05]');
INSERT INTO frases_demo VALUES (3, 'Subió la bolsa', '[0.0, 0.2, 0.95]');
SELECT texto,
ROUND(VECTOR_DISTANCE(embedding, VECTOR('[0.9, 0.1, 0.0]', 3, FLOAT32), COSINE), 4) AS distancia
FROM frases_demo
ORDER BY distancia;
TEXTO DISTANCIA
-------------------- ----------
El gato duerme 0
El felino descansa ,0037
Subió la bolsa ,9772
3 filas seleccionadas.
Los vectores reales tienen cientos o miles de dimensiones y los genera un modelo, no se escriben a mano. Su estudio queda fuera de este curso; basta con que sepas que existen y para qué sirven.
5.9. Tipos ANSI y su conversión en Oracle
Oracle acepta los nombres de tipos del estándar ANSI (y de otros SGBD como DB2 o SQL/DS) y los convierte en sus tipos nativos:
CREATE TABLE ansi_demo (
c_integer INTEGER,
c_int INT,
c_smallint SMALLINT,
c_decimal DECIMAL(8,2),
c_numeric NUMERIC(6),
c_dec DEC,
c_float FLOAT,
c_real REAL,
c_double DOUBLE PRECISION,
c_varchar VARCHAR(20),
c_charvar CHARACTER VARYING(20),
c_character CHARACTER(3),
c_nchar NATIONAL CHARACTER(3),
c_nvarchar NATIONAL CHARACTER VARYING(10)
);
DESCRIBE ansi_demo
Nombre ¿Nulo? Tipo
----------------------------------------- -------- ----------------------------
C_INTEGER NUMBER(38)
C_INT NUMBER(38)
C_SMALLINT NUMBER(38)
C_DECIMAL NUMBER(8,2)
C_NUMERIC NUMBER(6)
C_DEC NUMBER(38)
C_FLOAT FLOAT(126)
C_REAL FLOAT(63)
C_DOUBLE FLOAT(126)
C_VARCHAR VARCHAR2(20)
C_CHARVAR VARCHAR2(20)
C_CHARACTER CHAR(3)
C_NCHAR NCHAR(3)
C_NVARCHAR NVARCHAR2(10)
| Tipo ANSI / otros SGBD | Se convierte en Oracle en |
|---|---|
INTEGER, INT, SMALLINT | NUMBER(38) (entero sin límite práctico: SMALLINT no es pequeño en Oracle) |
NUMERIC(p,s), DECIMAL(p,s), DEC(p,s) | NUMBER(p,s) |
FLOAT, DOUBLE PRECISION | FLOAT(126) |
REAL | FLOAT(63) |
VARCHAR(n), CHARACTER VARYING(n), CHAR VARYING(n) | VARCHAR2(n) |
CHARACTER(n), CHAR(n) | CHAR(n) |
NATIONAL CHARACTER(n), NCHAR(n) | NCHAR(n) |
NATIONAL CHARACTER VARYING(n) | NVARCHAR2(n) |
BOOLEAN | BOOLEAN (desde 23ai) |
⚠️ Importante: Escribe
VARCHAR2, noVARCHAR. Hoy son sinónimos, pero Oracle advierte en su documentación queVARCHARpodría cambiar de comportamiento en el futuro (para ajustarse al estándar en el tratamiento de la cadena vacía y los nulos).
6. Columnas identidad
⚠️ Importante: Disponible desde Oracle 12c. En versiones anteriores la numeración automática se hacía con una secuencia y un disparador (PL/SQL).
📘 Concepto: Una columna identidad (identity column, SQL:2003) es una columna numérica cuyo valor genera automáticamente el SGBD, normalmente para servir de clave primaria sustituta (surrogate key). Oracle crea por debajo una secuencia asociada a la columna.
6.1. Las tres variantes
| Variante | ¿Se puede dar un valor explícito? | ¿Qué pasa con NULL explícito? |
|---|---|---|
GENERATED ALWAYS AS IDENTITY | No: siempre lo genera Oracle (ORA-32795) | Error |
GENERATED BY DEFAULT AS IDENTITY | Sí | Error ORA-01400 (cannot insert NULL) |
GENERATED BY DEFAULT ON NULL AS IDENTITY | Sí | Se genera un valor automático |
Todas implican NOT NULL. Opciones que admiten (entre paréntesis): START WITH n (primer valor), INCREMENT BY n (salto), MAXVALUE, MINVALUE, CYCLE, CACHE… las mismas que una secuencia (módulo 26).
6.2. GENERATED ALWAYS
CREATE TABLE socios_demo (
socio_id NUMBER GENERATED ALWAYS AS IDENTITY,
nombre VARCHAR2(30)
);
INSERT INTO socios_demo (nombre) VALUES ('Ana');
INSERT INTO socios_demo (nombre) VALUES ('Luis');
INSERT INTO socios_demo (socio_id, nombre) VALUES (100, 'Marta');
UPDATE socios_demo SET socio_id = 50 WHERE nombre = 'Ana';
INSERT INTO socios_demo (socio_id, nombre) VALUES (100, 'Marta')
*
ERROR en línea 1:
ORA-32795: cannot insert into a generated always identity column
UPDATE socios_demo SET socio_id = 50 WHERE nombre = 'Ana'
*
ERROR en línea 1:
ORA-32796: cannot update a generated always identity column
SELECT * FROM socios_demo;
SOCIO_ID NOMBRE
---------- ------------------------------
1 Ana
2 Luis
2 filas seleccionadas.
La secuencia interna aparece en el diccionario:
SELECT table_name, column_name, generation_type, sequence_name FROM user_tab_identity_cols;
TABLE_NAME COLUMN_NAM GENERATION_T SEQUENCE_NAME
------------ ---------- ------------ ----------------
SOCIOS_DEMO SOCIO_ID ALWAYS ISEQ$$_73507
1 fila seleccionada.
6.3. GENERATED BY DEFAULT con opciones
CREATE TABLE facturas_demo (
factura_id NUMBER(8) GENERATED BY DEFAULT AS IDENTITY (START WITH 1000 INCREMENT BY 10),
importe NUMBER(8,2)
);
INSERT INTO facturas_demo (importe) VALUES (100);
INSERT INTO facturas_demo (importe) VALUES (250);
INSERT INTO facturas_demo VALUES (5, 75); -- valor explícito: permitido
INSERT INTO facturas_demo VALUES (NULL, 80); -- NULL explícito: error
INSERT INTO facturas_demo VALUES (NULL, 80)
*
ERROR en línea 1:
ORA-01400: cannot insert NULL into ("CURSO"."FACTURAS_DEMO"."FACTURA_ID")
SELECT * FROM facturas_demo;
FACTURA_ID IMPORTE
---------- ----------
1000 100
1010 250
5 75
3 filas seleccionadas.
6.4. GENERATED BY DEFAULT ON NULL y el riesgo de duplicados
CREATE TABLE tickets_demo (
ticket_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
asunto VARCHAR2(30)
);
INSERT INTO tickets_demo (asunto) VALUES ('Primero');
INSERT INTO tickets_demo VALUES (NULL, 'Segundo');
INSERT INTO tickets_demo VALUES (2, 'Duplicado a mano');
INSERT INTO tickets_demo (asunto) VALUES ('Tercero');
SELECT * FROM tickets_demo;
TICKET_ID ASUNTO
---------- ------------------------------
1 Primero
2 Segundo
2 Duplicado a mano
3 Tercero
4 filas seleccionadas.
⚠️ Importante: Una columna identidad no garantiza la unicidad por sí sola: con
BY DEFAULTcualquiera puede insertar un valor que coincida con uno ya generado (o que la secuencia generará más adelante). Declara siempre la columna identidad comoPRIMARY KEY(módulo 24) y, salvo que necesites cargar datos con identificadores existentes, prefiereGENERATED ALWAYS.
💡 Consejo: Los valores generados pueden tener huecos (por
ROLLBACK, por la caché de la secuencia o por reinicios). Es normal: una clave sustituta solo tiene que ser única, no correlativa. Si necesitas una numeración sin huecos (por ejemplo, el número legal de una factura), no uses una identidad.
7. Columnas virtuales
⚠️ Importante: Disponible desde Oracle 11g.
📘 Concepto: Una columna virtual es una columna cuyo valor se calcula a partir de una expresión sobre otras columnas de la misma fila y no se almacena en disco: se evalúa cada vez que se consulta. Es la implementación directa de un atributo derivado del modelo E/R.
CREATE TABLE lineas_demo (
linea_id NUMBER(4),
cantidad NUMBER(5),
precio NUMBER(8,2),
descuento NUMBER(4,2) DEFAULT 0,
importe NUMBER(10,2) GENERATED ALWAYS AS (cantidad * precio * (1 - descuento / 100)) VIRTUAL
);
INSERT INTO lineas_demo (linea_id, cantidad, precio, descuento) VALUES (1, 2, 139, 5);
INSERT INTO lineas_demo (linea_id, cantidad, precio) VALUES (2, 10, 34.5);
SELECT * FROM lineas_demo;
LINEA_ID CANTIDAD PRECIO DESCUENTO IMPORTE
---------- ---------- ---------- ---------- ----------
1 2 139 5 264,1
2 10 34,5 0 345
2 filas seleccionadas.
El importe se recalcula solo cuando cambian los datos, y se puede usar en WHERE como cualquier columna:
UPDATE lineas_demo SET cantidad = 3 WHERE linea_id = 1;
SELECT * FROM lineas_demo WHERE importe > 300;
LINEA_ID CANTIDAD PRECIO DESCUENTO IMPORTE
---------- ---------- ---------- ---------- ----------
1 3 139 5 396,15
2 10 34,5 0 345
2 filas seleccionadas.
No se puede dar valor a una columna virtual. Observa además que, como importe es la quinta columna, un INSERT sin lista de columnas exige 5 valores… y falla:
INSERT INTO lineas_demo VALUES (3, 1, 10, 0, 10);
INSERT INTO lineas_demo VALUES (3, 1, 10, 0, 10)
*
ERROR en línea 1:
ORA-54013: INSERT operation disallowed on virtual columns
Las palabras GENERATED ALWAYS y VIRTUAL son opcionales, y el tipo de dato también (Oracle lo deduce de la expresión):
CREATE TABLE personas_demo (
nombre VARCHAR2(20),
apellidos VARCHAR2(30),
nombre_completo AS (nombre || ' ' || apellidos)
);
DESCRIBE personas_demo
Nombre ¿Nulo? Tipo
----------------------------------------- -------- ----------------------------
NOMBRE VARCHAR2(20)
APELLIDOS VARCHAR2(30)
NOMBRE_COMPLETO VARCHAR2(51)
Limitaciones: la expresión solo puede usar columnas de la misma fila y funciones deterministas (que siempre devuelven lo mismo para los mismos argumentos). Por eso no se puede calcular la edad a partir de la fecha de nacimiento, ya que depende de SYSDATE:
CREATE TABLE virt_mala (f DATE, edad AS (TRUNC(MONTHS_BETWEEN(SYSDATE, f) / 12)));
CREATE TABLE virt_mala (f DATE, edad AS (TRUNC(MONTHS_BETWEEN(SYSDATE, f) / 12)))
*
ERROR en línea 1:
ORA-54002: only pure functions can be specified in a virtual column expression
La expresión se guarda en el diccionario (columna DATA_DEFAULT) y VIRTUAL_COLUMN vale YES:
SELECT column_name, virtual_column, data_default
FROM user_tab_cols
WHERE table_name = 'LINEAS_DEMO'
ORDER BY column_id;
COLUMN_NAM VIR DATA_DEFAULT
---------- --- --------------------------------------------------
LINEA_ID NO (nulo)
CANTIDAD NO (nulo)
PRECIO NO (nulo)
DESCUENTO NO 0
IMPORTE YES "CANTIDAD"*"PRECIO"*(1-"DESCUENTO"/100)
5 filas seleccionadas.
💡 Consejo: Las columnas virtuales evitan redundancias (no hay que actualizar el importe cuando cambia la cantidad) y garantizan que el cálculo es siempre el mismo. Se pueden indexar y usar en restricciones
CHECK. Para atributos derivados que dependen de otras tablas (por ejemplo, el total de un pedido a partir de sus líneas) se usan vistas (módulo 26).
8. Columnas invisibles
⚠️ Importante: Disponible desde Oracle 12c.
Una columna invisible existe y se puede usar nombrándola explícitamente, pero no aparece en SELECT *, en DESCRIBE ni en los INSERT sin lista de columnas. Sirve para añadir columnas a una tabla sin romper aplicaciones antiguas que usan SELECT * o INSERT ... VALUES sin columnas.
CREATE TABLE usuarios_demo (
usuario_id NUMBER(4),
login VARCHAR2(20),
notas VARCHAR2(50) INVISIBLE
);
INSERT INTO usuarios_demo VALUES (1, 'ana'); -- solo 2 valores
INSERT INTO usuarios_demo (usuario_id, login, notas) VALUES (2, 'luis', 'nota interna');
SELECT * FROM usuarios_demo;
SELECT usuario_id, login, notas FROM usuarios_demo;
USUARIO_ID LOGIN
---------- --------------------
1 ana
2 luis
USUARIO_ID LOGIN NOTAS
---------- -------------------- --------------------------------------------------
1 ana (nulo)
2 luis nota interna
En SQL*Plus puedes verlas en DESCRIBE con SET COLINVISIBLE ON:
SET COLINVISIBLE ON
DESCRIBE usuarios_demo
SET COLINVISIBLE OFF
Nombre ¿Nulo? Tipo
----------------------------------------- -------- ----------------------------
USUARIO_ID NUMBER(4)
LOGIN VARCHAR2(20)
NOTAS (INVISIBLE) VARCHAR2(50)
En el diccionario, las columnas invisibles no tienen COLUMN_ID y HIDDEN_COLUMN vale YES en USER_TAB_COLS (la vista USER_TAB_COLUMNS es igual, pero oculta las columnas internas del sistema). Una columna se hace visible o invisible con ALTER TABLE ... MODIFY (col VISIBLE) (módulo 25).
9. Crear tablas a partir de consultas (CTAS)
CREATE TABLE ... AS SELECT (abreviado CTAS) crea una tabla con la estructura y los datos que devuelve una consulta. Es muy útil para copias de seguridad rápidas, tablas de trabajo y pruebas.
CREATE TABLE nombre_tabla [(nombres_de_columna)]
AS
SELECT ...;
9.1. Con datos
CREATE TABLE empleados_ventas AS
SELECT emp_id, nombre, apellidos, salario, comision
FROM empleados
WHERE dept_id = 20;
SELECT * FROM empleados_ventas;
EMP_ID NOMBRE APELLIDOS SALARIO COMISION
---------- ---------- ------------------ ---------- ----------
101 Luis Martínez Ruiz 4800 (nulo)
110 Carmen Ruiz Ortega 2200 500
111 David Torres Romero 2100 800
112 Elena Navarro Castro 2300 300
113 Sergio Jiménez Vidal 1900 0
114 Lucía Romero Serrano 1700 (nulo)
6 filas seleccionadas.
⚠️ Importante: CTAS copia los tipos y los datos, y las restricciones
NOT NULLexplícitas, pero no copia la clave primaria, las claves únicas, las claves ajenas, losCHECK, los valoresDEFAULTni los índices. Compruébalo:
DESCRIBE empleados_ventas
SELECT constraint_name, constraint_type, search_condition_vc
FROM user_constraints
WHERE table_name = 'EMPLEADOS_VENTAS';
Nombre ¿Nulo? Tipo
----------------------------------------- -------- ----------------------------
EMP_ID NUMBER(6)
NOMBRE NOT NULL VARCHAR2(30)
APELLIDOS NOT NULL VARCHAR2(50)
SALARIO NOT NULL NUMBER(8,2)
COMISION NUMBER(8,2)
CONSTRAINT_NAME C SEARCH_CONDITION_VC
----------------- - ------------------------------
SYS_C009599 C "NOMBRE" IS NOT NULL
SYS_C009600 C "APELLIDOS" IS NOT NULL
SYS_C009601 C "SALARIO" IS NOT NULL
3 filas seleccionadas.
emp_id ha perdido su NOT NULL porque en la tabla original no era una restricción NOT NULL explícita, sino consecuencia de la clave primaria. Y los NOT NULL copiados han perdido su nombre (nn_emp_nombre): ahora se llaman SYS_C....
9.2. Solo la estructura
Para copiar la estructura sin datos se usa una condición que nunca se cumple:
CREATE TABLE empleados_copia AS
SELECT * FROM empleados WHERE 1 = 0;
SELECT COUNT(*) FROM empleados_copia;
COUNT(*)
----------
0
1 fila seleccionada.
9.3. Columnas calculadas y nombres
Si la consulta tiene expresiones, cada una necesita un nombre (un alias o la lista de columnas tras el nombre de la tabla):
CREATE TABLE resumen_malo AS
SELECT dept_id, COUNT(*) FROM empleados GROUP BY dept_id;
ERROR en línea 1:
ORA-00998: must name this expression with a column alias
CREATE TABLE resumen_dept (dept_id, num_empleados, masa_salarial) AS
SELECT dept_id, COUNT(*), SUM(salario)
FROM empleados
WHERE dept_id IS NOT NULL
GROUP BY dept_id;
SELECT * FROM resumen_dept ORDER BY dept_id;
DEPT_ID NUM_EMPLEADOS MASA_SALARIAL
---------- ------------- -------------
10 1 6500
20 6 15000
30 6 18800
40 3 8450
50 3 9300
60 4 8950
6 filas seleccionadas.
Las columnas calculadas reciben tipo NUMBER sin precisión. En la lista de columnas de un CTAS no se pueden poner tipos (ORA-01773: may not specify column datatypes in this CREATE TABLE), pero sí restricciones:
CREATE TABLE salarios_anuales (
emp_id CONSTRAINT pk_salarios_anuales PRIMARY KEY,
salario_anual CONSTRAINT ck_sal_anual CHECK (salario_anual > 0)
) AS
SELECT emp_id, salario * 14 FROM empleados;
Tabla creada.
Si necesitas tipos concretos, convierte en la consulta con CAST(salario * 14 AS NUMBER(10,2)), o crea primero la tabla y después cárgala con INSERT INTO ... SELECT (módulo 34).
💡 Consejo: Antes de un cambio arriesgado en los datos, un CTAS es una copia de seguridad instantánea:
CREATE TABLE empleados_bak AS SELECT * FROM empleados;. Acuérdate de borrarla después.
10. Tablas temporales
10.1. Tablas temporales globales (GTT)
📘 Concepto: Una tabla temporal global (Global Temporary Table, GTT) tiene una definición permanente (la ven todos los usuarios con permiso), pero sus datos son privados de cada sesión y temporales: cada sesión solo ve las filas que ella misma ha insertado, y desaparecen al terminar la transacción o la sesión.
| Cláusula | Las filas se borran… | Uso típico |
|---|---|---|
ON COMMIT DELETE ROWS (por defecto) | Al terminar la transacción (COMMIT o ROLLBACK) | Cálculos intermedios dentro de una operación |
ON COMMIT PRESERVE ROWS | Al terminar la sesión | Resultados que se reutilizan durante toda la conexión (informes, carritos) |
CREATE GLOBAL TEMPORARY TABLE carrito_tmp (
producto_id NUMBER(6),
cantidad NUMBER(5)
) ON COMMIT DELETE ROWS;
INSERT INTO carrito_tmp VALUES (6, 2);
INSERT INTO carrito_tmp VALUES (9, 1);
SELECT * FROM carrito_tmp;
COMMIT;
SELECT * FROM carrito_tmp;
PRODUCTO_ID CANTIDAD
----------- ----------
6 2
9 1
2 filas seleccionadas.
Confirmación terminada.
ninguna fila seleccionada
CREATE GLOBAL TEMPORARY TABLE informe_tmp (
dept_id NUMBER(4),
total NUMBER(12,2)
) ON COMMIT PRESERVE ROWS;
INSERT INTO informe_tmp
SELECT dept_id, SUM(salario) FROM empleados WHERE dept_id IS NOT NULL GROUP BY dept_id;
COMMIT;
SELECT COUNT(*) FROM informe_tmp;
6 filas creadas.
Confirmación terminada.
COUNT(*)
----------
6
1 fila seleccionada.
En USER_TABLES, la columna TEMPORARY vale Y y DURATION indica el tipo:
SELECT table_name, temporary, duration
FROM user_tables
WHERE table_name IN ('CARRITO_TMP', 'INFORME_TMP');
TABLE_NAME T DURATION
------------- - ---------------
CARRITO_TMP Y SYS$TRANSACTION
INFORME_TMP Y SYS$SESSION
2 filas seleccionadas.
Una GTT con filas en uso en la sesión no se puede borrar hasta vaciarla:
DROP TABLE informe_tmp;
ERROR en línea 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
TRUNCATE TABLE informe_tmp;
DROP TABLE informe_tmp PURGE;
⚠️ Importante: Una GTT no es una tabla que creas y borras cada vez que la necesitas. Se crea una vez (como cualquier tabla de la aplicación) y se usa muchas veces; Oracle se encarga de que los datos de cada sesión sean privados y efímeros. Los datos de las GTT se guardan en el tablespace temporal.
10.2. Tablas temporales privadas (PTT)
⚠️ Importante: Disponible desde Oracle 18c.
Una tabla temporal privada (Private Temporary Table) va un paso más allá: también su definición es privada de la sesión y temporal. Se guarda solo en memoria y desaparece sola. Su nombre debe empezar por el prefijo ORA$PTT_ (parámetro PRIVATE_TEMP_TABLE_PREFIX).
| Cláusula | La tabla (definición y datos) desaparece… |
|---|---|
ON COMMIT DROP DEFINITION (por defecto) | Al terminar la transacción |
ON COMMIT PRESERVE DEFINITION | Al terminar la sesión |
CREATE PRIVATE TEMPORARY TABLE ora$ptt_ventas (
cliente_id NUMBER(6),
total NUMBER(12,2)
) ON COMMIT DROP DEFINITION;
INSERT INTO ora$ptt_ventas VALUES (1001, 2500);
SELECT * FROM ora$ptt_ventas;
COMMIT;
SELECT * FROM ora$ptt_ventas;
CLIENTE_ID TOTAL
---------- ----------
1001 2500
1 fila seleccionada.
Confirmación terminada.
SELECT * FROM ora$ptt_ventas
*
ERROR en línea 1:
ORA-00942: table or view "CURSO"."ORA$PTT_VENTAS" does not exist
También admiten CTAS:
CREATE PRIVATE TEMPORARY TABLE ora$ptt_sesion
ON COMMIT PRESERVE DEFINITION
AS SELECT cliente_id, nombre FROM clientes WHERE ciudad = 'Madrid';
COMMIT;
SELECT * FROM ora$ptt_sesion;
CLIENTE_ID NOMBRE
---------- ------------------------------
1003 María José Herrera
1004 Academia Nova
2 filas seleccionadas.
Sin el prefijo, la creación falla con ORA-00903: invalid table name. Las tablas privadas de tu sesión se ven en USER_PRIVATE_TEMP_TABLES.
| Tabla normal | GTT | PTT | |
|---|---|---|---|
| Definición | Permanente, compartida | Permanente, compartida | Temporal, privada de la sesión |
| Datos | Permanentes, compartidos | Temporales, privados de la sesión | Temporales, privados de la sesión |
| Desde | Siempre | Oracle 8i | Oracle 18c |
| Nombre | Libre | Libre | Prefijo ORA$PTT_ |
11. Cómo elegir el tipo de dato correcto
Elegir bien el tipo es una decisión de diseño físico que afecta a la integridad, al espacio y al rendimiento. Estas son las recomendaciones para los casos más habituales:
| Dato | Tipo recomendado | Por qué / errores a evitar |
|---|---|---|
| Clave sustituta | NUMBER(n) con identidad o secuencia | Entera, compacta y rápida de comparar |
| Nombre, apellidos, ciudad | VARCHAR2(n) (o n CHAR) | Longitud variable; dimensiona con holgura razonable (40-60) |
| Indicador sí/no | BOOLEAN (23ai) o CHAR(1) + CHECK | Nunca VARCHAR2(5) con 'true'/'false' sin restricción |
| Código de longitud fija (país, idioma) | CHAR(2) o VARCHAR2(2) | |
| DNI / NIF / CIF | VARCHAR2(9) | Puede empezar por letra (X, Y, Z, B…): no es un número |
| Teléfono | VARCHAR2(15) | No se opera con él; puede llevar +34, ceros iniciales o espacios |
| Código postal | CHAR(5) o VARCHAR2(5) | NUMBER perdería el cero inicial: 03001 se convertiría en 3001 |
| Número de cuenta / IBAN | VARCHAR2(34) | No es una cantidad |
| Importe en euros | NUMBER(10,2) (ajusta la precisión) | Exacto; nunca FLOAT ni BINARY_DOUBLE |
| Porcentaje | NUMBER(5,2) + CHECK (... BETWEEN 0 AND 100) | |
| Cantidad de unidades | NUMBER(6) + CHECK (... >= 0) | |
| Medida científica | BINARY_DOUBLE | Rápida, admite rangos enormes |
| Fecha de nacimiento, de alta | DATE (sin hora) | No uses VARCHAR2 para fechas: perderías validación, orden y aritmética |
| Momento exacto de un evento | TIMESTAMP o TIMESTAMP WITH TIME ZONE | |
| Duración | INTERVAL DAY TO SECOND o NUMBER con la unidad en el nombre (duracion_min) | |
| Año | NUMBER(4) | |
| Texto largo (más de 4000 bytes) | CLOB | |
| Imagen, PDF | BLOB (o ruta en VARCHAR2) | |
| Datos semiestructurados | JSON (21c) o CLOB + IS JSON | No como sustituto del diseño relacional |
| Dirección de correo | VARCHAR2(100) + CHECK o dominio (módulo 24) |
💡 Consejo: La pregunta clave es: “¿voy a hacer cálculos con este dato?”. Si la respuesta es no (teléfonos, códigos postales, DNI, números de cuenta), el dato es un texto aunque solo contenga dígitos.
⚠️ Importante: No sobredimensiones “por si acaso” (
VARCHAR2(4000)para todo): la longitud máxima es también una regla de validación que documenta el dato, influye en la memoria que reservan las aplicaciones y limita qué columnas se pueden indexar juntas.
12. Consultar la estructura de las tablas
12.1. DESCRIBE
DESCRIBE (sección 13 del módulo 22) muestra nombre, obligatoriedad y tipo de cada columna visible. Es rápido, pero no muestra valores por defecto, restricciones ni columnas invisibles.
12.2. El diccionario de datos
El diccionario de datos es el conjunto de tablas y vistas donde Oracle guarda la información sobre todos los objetos (metadatos, módulo 03). Las vistas USER_ muestran los objetos de tu esquema; las ALL_, los objetos a los que tienes acceso; y las DBA_, todos los de la base de datos (solo para administradores).
| Vista | Contenido |
|---|---|
USER_TABLES | Una fila por tabla: tablespace, si es temporal, estadísticas |
USER_TAB_COLUMNS | Una fila por columna visible: tipo, longitud, precisión, escala, nulos, valor por defecto |
USER_TAB_COLS | Como la anterior, incluyendo columnas ocultas e invisibles |
USER_TAB_IDENTITY_COLS | Columnas identidad y su secuencia |
USER_OBJECTS | Todos los objetos del esquema con su tipo y fecha de creación |
USER_CONSTRAINTS, USER_CONS_COLUMNS | Restricciones (módulo 24) |
DICTIONARY (DICT) | La lista de todas las vistas del diccionario con su descripción |
COLUMN column_name FORMAT A13
COLUMN data_type FORMAT A8
COLUMN nullable FORMAT A3 HEADING NUL
COLUMN data_default FORMAT A12
SELECT column_name, data_type, data_length, data_precision, data_scale, nullable, data_default
FROM user_tab_columns
WHERE table_name = 'PRODUCTOS'
ORDER BY column_id;
COLUMN_NAME DATA_TYP DATA_LENGTH DATA_PRECISION DATA_SCALE NUL DATA_DEFAULT
------------- -------- ----------- -------------- ---------- --- ------------
PRODUCTO_ID NUMBER 22 6 0 N (nulo)
NOMBRE VARCHAR2 60 (nulo) (nulo) N (nulo)
DESCRIPCION VARCHAR2 200 (nulo) (nulo) Y (nulo)
CATEGORIA_ID NUMBER 22 4 0 N (nulo)
PRECIO NUMBER 22 8 2 N (nulo)
STOCK NUMBER 22 6 0 N 0
FECHA_ALTA DATE 7 (nulo) (nulo) Y (nulo)
DESCATALOGADO CHAR 1 (nulo) (nulo) N 'N'
8 filas seleccionadas.
Lectura del resultado: DATA_LENGTH es el máximo en bytes (22 para cualquier NUMBER, 7 para DATE); DATA_PRECISION y DATA_SCALE son la p y la s de NUMBER(p,s) (nulas para los tipos que no las tienen); NULLABLE vale N si la columna tiene NOT NULL; DATA_DEFAULT muestra el texto del valor por defecto tal cual se escribió.
COLUMN tablespace_name FORMAT A10
SELECT table_name, tablespace_name, temporary FROM user_tables ORDER BY table_name;
TABLE_NAME TABLESPACE T
------------------------------ ---------- -
ASIGNACIONES USERS N
CATEGORIAS USERS N
CLIENTES USERS N
DEPARTAMENTOS USERS N
EMPLEADOS USERS N
LINEAS_PEDIDO USERS N
PEDIDOS USERS N
PRODUCTOS USERS N
PROYECTOS USERS N
9 filas seleccionadas.
💡 Consejo: En SQLcl,
INFO productosreúne en una sola orden columnas, valores por defecto, índices y restricciones; yDDL productoste devuelve la sentenciaCREATE TABLEcompleta. En SQL Developer tienes lo mismo en las pestañas Columnas, Restricciones y SQL al abrir una tabla desde el navegador de objetos.
13. Errores frecuentes al crear tablas
| Error | Causa | Solución |
|---|---|---|
ORA-00955: name is already used by an existing object | Ya existe un objeto con ese nombre | Elige otro nombre, bórralo antes o usa IF NOT EXISTS (23ai) |
ORA-00903: invalid table name | Nombre que empieza por dígito o palabra reservada | Renombra |
ORA-03050: invalid identifier: "X" is a reserved word | Columna con nombre reservado (DATE, LEVEL, USER…) | Usa un nombre descriptivo en español |
ORA-00972: ... exceeds the maximum length of 128 bytes | Identificador demasiado largo | Abrévialo |
ORA-02263: need to specify the data type for this column | Columna sin tipo | Añade el tipo |
ORA-00906: missing left parenthesis | VARCHAR2 sin longitud, o falta un paréntesis | VARCHAR2(n) |
ORA-00910: specified length too long for its datatype | VARCHAR2 de más de 4000 bytes o CHAR de más de 2000 | Reduce la longitud o usa CLOB |
ORA-00931 / ORA-03078 | Coma sobrante antes del paréntesis final | Quita la coma |
ORA-01438: value ... greater than specified precision | El número no cabe en NUMBER(p,s) | Revisa precisión y escala |
ORA-12899: value too large for column | El texto no cabe (cuidado con la semántica en bytes) | Aumenta la longitud o usa semántica CHAR |
ORA-00998: must name this expression with a column alias | CTAS con una expresión sin alias | Pon alias o lista de columnas |
ORA-01773: may not specify column datatypes in this CREATE TABLE | Tipos en la lista de columnas de un CTAS | Usa CAST en la consulta |
ORA-32795: cannot insert into a generated always identity column | Valor explícito en una identidad ALWAYS | No incluyas la columna, o usa BY DEFAULT |
ORA-54013: INSERT operation disallowed on virtual columns | Valor para una columna virtual | Usa lista de columnas en el INSERT |
14. Ejercicios
Ejercicios resueltos
Ejercicio 1: Elegir tipos de datos
Elige el tipo de dato de Oracle más adecuado para cada atributo de una aplicación de un taller mecánico y justifica tu elección: matrícula, número de bastidor (17 caracteres), kilómetros, fecha de entrada en el taller (con hora), precio de la mano de obra por hora, teléfono del cliente, código postal, ¿vehículo eléctrico?, descripción de la avería (puede ser muy larga), fotos del vehículo, porcentaje de IVA, año de fabricación.
Ver solución
| Atributo | Tipo | Justificación |
|---|---|---|
| Matrícula | VARCHAR2(10) | Texto con letras y números; formatos distintos según país y antigüedad |
| Número de bastidor (VIN) | CHAR(17) o VARCHAR2(17) | Longitud fija de 17 caracteres alfanuméricos |
| Kilómetros | NUMBER(7) | Entero; hasta 9.999.999 km |
| Fecha de entrada | DATE | Incluye la hora; no hacen falta fracciones de segundo. Usa TIMESTAMP solo si se necesitan |
| Precio de la mano de obra | NUMBER(6,2) | Importe exacto con céntimos |
| Teléfono | VARCHAR2(15) | No se opera con él; puede tener prefijo internacional |
| Código postal | CHAR(5) | Conserva el cero inicial (03001) |
| ¿Eléctrico? | BOOLEAN (23ai) o CHAR(1) con CHECK (... IN ('S','N')) | Valor lógico |
| Descripción de la avería | CLOB (o VARCHAR2(4000) si basta) | Puede superar 4000 bytes |
| Fotos | BLOB (o tabla aparte con la ruta del fichero) | Datos binarios; un vehículo puede tener varias fotos, así que irían en una tabla fotos relacionada (1:N) |
| Porcentaje de IVA | NUMBER(4,2) | 21, 10, 4… con posibles decimales |
| Año de fabricación | NUMBER(4) | Solo el año; no hace falta un DATE |
Fíjate en que varios datos “numéricos” (teléfono, código postal) son en realidad textos, y en que un atributo multivaluado como las fotos no se resuelve con un tipo de dato, sino con otra tabla (módulo 17).
Ejercicio 2: Socios de un gimnasio
Crea la tabla socios_gimnasio con: un identificador generado automáticamente que empiece en 1000 y que nadie pueda asignar a mano; el DNI y el nombre obligatorios; la fecha de nacimiento; la fecha de alta, que debe ser la del día si no se indica o si se inserta un nulo; la cuota mensual, por defecto 35 €; y un indicador de socio activo, verdadero por defecto. Inserta dos socios, uno con los valores por defecto y otro con fecha de alta nula, cuota de 29,90 € e inactivo. ¿Cómo sería el indicador en 19c?
Ver solución
CREATE TABLE socios_gimnasio (
socio_id NUMBER(6) GENERATED ALWAYS AS IDENTITY (START WITH 1000),
dni VARCHAR2(9) NOT NULL,
nombre VARCHAR2(40) NOT NULL,
fecha_nacimiento DATE,
fecha_alta DATE DEFAULT ON NULL SYSDATE,
cuota_mensual NUMBER(6,2) DEFAULT 35,
activo BOOLEAN DEFAULT TRUE
);
INSERT INTO socios_gimnasio (dni, nombre, fecha_nacimiento)
VALUES ('12345678Z', 'Irene Campos', DATE '1999-04-12');
INSERT INTO socios_gimnasio (dni, nombre, fecha_alta, cuota_mensual, activo)
VALUES ('87654321X', 'Óscar Lara', NULL, 29.90, FALSE);
SELECT * FROM socios_gimnasio;
SOCIO_ID DNI NOMBRE FECHA_NACI FECHA_ALTA CUOTA_MENSUAL ACTIVO
---------- --------- -------------- ---------- ---------- ------------- -----------
1000 12345678Z Irene Campos 12/04/1999 03/10/2026 35 TRUE
1001 87654321X Óscar Lara (nulo) 03/10/2026 29,9 FALSE
2 filas seleccionadas.
DESCRIBE socios_gimnasio muestra NOT NULL en socio_id (por ser identidad), en dni y nombre (por la restricción) y en fecha_alta (por DEFAULT ON NULL). En 19c, la columna activo sería CHAR(1) DEFAULT 'S' CHECK (activo IN ('S', 'N')). Falta todavía la clave primaria y la unicidad del DNI: las añadirás en el módulo 24.
Ejercicio 3: Predecir NUMBER(6,2)
Sin ejecutarlo, predice qué ocurrirá al insertar cada valor en una columna NUMBER(6,2): 1234.567, 9999.999, -12.3456, 12345.6, 0.005, 9999.994. Después compruébalo.
Ver solución
NUMBER(6,2) admite 6 - 2 = 4 dígitos enteros y 2 decimales: de -9999,99 a 9999,99.
CREATE TABLE prueba_numeros (valor NUMBER(6,2));
INSERT INTO prueba_numeros VALUES (1234.567);
INSERT INTO prueba_numeros VALUES (9999.999);
INSERT INTO prueba_numeros VALUES (-12.3456);
INSERT INTO prueba_numeros VALUES (12345.6);
INSERT INTO prueba_numeros VALUES (0.005);
INSERT INTO prueba_numeros VALUES (9999.994);
SELECT * FROM prueba_numeros;
ERROR en línea 1:
ORA-01438: value 9999.999 greater than specified precision (6, 2) for column
ERROR en línea 1:
ORA-01438: value 12345.6 greater than specified precision (6, 2) for column
VALOR
----------
1234,57
-12,35
,01
9999,99
4 filas seleccionadas.
| Valor | Resultado | Explicación |
|---|---|---|
| 1234.567 | 1234,57 | Redondea el tercer decimal (7) hacia arriba |
| 9999.999 | ORA-01438 | Al redondear da 10000,00: 5 enteros |
| -12.3456 | -12,35 | Redondea (el 5 sube) |
| 12345.6 | ORA-01438 | 5 dígitos enteros |
| 0.005 | 0,01 | Redondeo aritmético: 0,005 sube a 0,01 |
| 9999.994 | 9999,99 | Redondea hacia abajo y cabe |
Ejercicio 4: Bytes y caracteres
Crea una tabla con dos columnas, b VARCHAR2(5 BYTE) y c VARCHAR2(5 CHAR), e intenta guardar 'Nandu' en ambas y 'Ñandú' en cada una por separado. Explica los resultados mostrando la longitud en bytes de lo guardado.
Ver solución
CREATE TABLE prueba_semantica (
b VARCHAR2(5 BYTE),
c VARCHAR2(5 CHAR)
);
INSERT INTO prueba_semantica VALUES ('Nandu', 'Nandu');
INSERT INTO prueba_semantica VALUES (NULL, 'Ñandú');
INSERT INTO prueba_semantica VALUES ('Ñandú', NULL);
SELECT b, LENGTHB(b) AS bytes_b, c, LENGTHB(c) AS bytes_c FROM prueba_semantica;
INSERT INTO prueba_semantica VALUES ('Ñandú', NULL)
*
ERROR en línea 1:
ORA-12899: value too large for column "CURSO"."PRUEBA_SEMANTICA"."B" (actual: 7, maximum: 5)
B BYTES_B C BYTES_C
------ ---------- ------ ----------
Nandu 5 Nandu 5
(nulo) (nulo) Ñandú 7
2 filas seleccionadas.
'Nandu' tiene 5 caracteres de 1 byte: cabe en ambas. 'Ñandú' tiene 5 caracteres pero 7 bytes (la Ñ y la ú ocupan 2 cada una en UTF-8): cabe en la columna con semántica de caracteres, pero no en la de 5 bytes. Por eso, en una aplicación en español, VARCHAR2(n CHAR) evita sorpresas con nombres como Peña, Muñoz o Íñigo.
Ejercicio 5: Precio con IVA calculado
Crea la tabla articulos_demo con identificador, descripción, precio base, porcentaje de IVA (21 por defecto) y un precio final calculado automáticamente y redondeado a céntimos. Inserta un ratón (19,90 €, IVA general), un libro (32,50 €, IVA del 4 %) y pan (1,20 €, IVA del 0 %). Después cambia el IVA del pan al 10 % y comprueba el precio final.
Ver solución
CREATE TABLE articulos_demo (
articulo_id NUMBER(6),
descripcion VARCHAR2(60),
precio_base NUMBER(8,2),
iva_pct NUMBER(4,1) DEFAULT 21,
precio_final NUMBER(9,2) GENERATED ALWAYS AS (ROUND(precio_base * (1 + iva_pct / 100), 2)) VIRTUAL
);
INSERT INTO articulos_demo (articulo_id, descripcion, precio_base) VALUES (1, 'Ratón inalámbrico', 19.90);
INSERT INTO articulos_demo (articulo_id, descripcion, precio_base, iva_pct) VALUES (2, 'Libro de SQL', 32.50, 4);
INSERT INTO articulos_demo (articulo_id, descripcion, precio_base, iva_pct) VALUES (3, 'Pan', 1.20, 0);
SELECT * FROM articulos_demo;
ARTICULO_ID DESCRIPCION PRECIO_BASE IVA_PCT PRECIO_FINAL
----------- -------------------- ----------- ---------- ------------
1 Ratón inalámbrico 19,9 21 24,08
2 Libro de SQL 32,5 4 33,8
3 Pan 1,2 0 1,2
3 filas seleccionadas.
UPDATE articulos_demo SET iva_pct = 10 WHERE articulo_id = 3;
SELECT articulo_id, precio_base, iva_pct, precio_final FROM articulos_demo WHERE articulo_id = 3;
ARTICULO_ID PRECIO_BASE IVA_PCT PRECIO_FINAL
----------- ----------- ---------- ------------
3 1,2 10 1,32
1 fila seleccionada.
19,90 x 1,21 = 24,079, que se redondea a 24,08. El precio final nunca puede quedar desactualizado porque no se guarda: se calcula al consultar. En los INSERT es obligatorio usar lista de columnas para no dar valor a la columna virtual.
Ejercicio 6: Copias con CTAS
a) Crea clientes_valencia con el identificador, nombre, ciudad y límite de crédito de los clientes de la provincia de Valencia. b) Crea pedidos_historico con la misma estructura que pedidos pero vacía. c) Averigua qué restricciones y valores por defecto de pedidos se han conservado en pedidos_historico.
Ver solución
-- a)
CREATE TABLE clientes_valencia AS
SELECT cliente_id, nombre, ciudad, limite_credito
FROM clientes
WHERE provincia = 'Valencia';
SELECT * FROM clientes_valencia;
CLIENTE_ID NOMBRE CIUDAD LIMITE_CREDITO
---------- ---------------------------- ------------ --------------
1001 Talleres Martín S.L. Valencia 10000
1013 Carlos Ibáñez Rey Valencia 1500
2 filas seleccionadas.
-- b)
CREATE TABLE pedidos_historico AS
SELECT * FROM pedidos WHERE 1 = 0;
-- c)
SELECT constraint_name, constraint_type, search_condition_vc
FROM user_constraints
WHERE table_name = 'PEDIDOS_HISTORICO';
SELECT column_name, data_default
FROM user_tab_columns
WHERE table_name = 'PEDIDOS_HISTORICO'
ORDER BY column_id;
CONSTRAINT_NAM C SEARCH_CONDITION_VC
-------------- - ------------------------------
SYS_C0010324 C "CLIENTE_ID" IS NOT NULL
SYS_C0010325 C "FECHA_PEDIDO" IS NOT NULL
SYS_C0010326 C "ESTADO" IS NOT NULL
3 filas seleccionadas.
COLUMN_NAME DATA_DEFAULT
-------------- ---------------
PEDIDO_ID (nulo)
CLIENTE_ID (nulo)
EMPLEADO_ID (nulo)
FECHA_PEDIDO (nulo)
FECHA_ENVIO (nulo)
ESTADO (nulo)
6 filas seleccionadas.
Solo se conservan los tres NOT NULL explícitos (con nombres generados por el sistema). Se han perdido la clave primaria, las dos claves ajenas, los dos CHECK (ck_ped_estado, ck_ped_fechas) y los dos valores por defecto (SYSDATE y 'PENDIENTE'). Si quieres una copia fiel de la estructura, debes obtener el DDL original (por ejemplo con DDL pedidos en SQLcl) y adaptarlo.
Ejercicio 7: Vuelos con zona horaria
Crea vuelos_demo con el código de vuelo, la hora de salida con su zona horaria y la duración como intervalo. Inserta el vuelo IB6253, que sale de Madrid el 20/10/2026 a las 12:05 y dura 9 h 40 min, y el VY1234, que sale a las 07:30 y dura 75 minutos. Calcula la hora de llegada de ambos en la hora de Madrid y la del IB6253 en la hora de Nueva York.
Ver solución
CREATE TABLE vuelos_demo (
vuelo VARCHAR2(7),
salida TIMESTAMP(0) WITH TIME ZONE,
duracion INTERVAL DAY(1) TO SECOND(0)
);
INSERT INTO vuelos_demo VALUES ('IB6253', TIMESTAMP '2026-10-20 12:05:00 Europe/Madrid',
INTERVAL '09:40:00' HOUR TO SECOND);
INSERT INTO vuelos_demo VALUES ('VY1234', TIMESTAMP '2026-10-20 07:30:00 Europe/Madrid',
INTERVAL '75' MINUTE);
COLUMN salida FORMAT A32
COLUMN duracion FORMAT A12
COLUMN llegada FORMAT A45
SELECT vuelo, salida, duracion, salida + duracion AS llegada FROM vuelos_demo;
VUELO SALIDA DURACION LLEGADA
------- -------------------------------- ------------ ---------------------------------------------
IB6253 20/10/26 12:05:00 EUROPE/MADRID +0 09:40:00 20/10/26 21:45:00,000000000 EUROPE/MADRID
VY1234 20/10/26 07:30:00 EUROPE/MADRID +0 01:15:00 20/10/26 08:45:00,000000000 EUROPE/MADRID
2 filas seleccionadas.
SELECT vuelo,
TO_CHAR((salida + duracion) AT TIME ZONE 'America/New_York', 'DD/MM/YYYY HH24:MI TZR') AS llegada
FROM vuelos_demo
WHERE vuelo = 'IB6253';
VUELO LLEGADA
------- ---------------------------------------------
IB6253 20/10/2026 15:45 AMERICA/NEW_YORK
1 fila seleccionada.
La zona se guarda como región (Europe/Madrid) y no como desplazamiento fijo, así que Oracle aplica automáticamente el horario de verano. AT TIME ZONE convierte el instante a otra zona: las 21:45 en Madrid son las 15:45 en Nueva York. Los 75 minutos se normalizan a 1 h 15 min.
Ejercicio 8: Corregir un CREATE TABLE
Este CREATE TABLE tiene al menos seis errores. Encuéntralos, explícalos y escribe la versión correcta.
CREATE TABLE pedido-demo (
id NUMBER(3,5),
fecha DATE DEFAULT SYSDATE(),
comment VARCHAR2,
importe NUMBER(10,2) DEFAULT 'cero',
);
Ver solución
Oracle se detiene en el primer error que encuentra:
CREATE TABLE pedido-demo (
*
ERROR en línea 1:
ORA-00922: missing or invalid option
| Error | Explicación | Corrección |
|---|---|---|
pedido-demo | El guion no es válido en un identificador: Oracle entiende “pedido” menos “demo” | pedido_demo |
id NUMBER(3,5) | Escala mayor que la precisión: solo admitiría valores como 0,00123, absurdo para un identificador; además id es poco descriptivo | pedido_id NUMBER(8) |
SYSDATE() | SYSDATE es una función sin paréntesis en Oracle | DEFAULT SYSDATE |
comment | Palabra reservada (ORA-03050) | comentario |
VARCHAR2 sin longitud | La longitud es obligatoria (ORA-00906) | VARCHAR2(200) |
DEFAULT 'cero' | No convertible a número (ORA-01722) | DEFAULT 0 |
| Coma final | Coma antes del paréntesis de cierre (ORA-00931) | Quitarla |
CREATE TABLE pedido_demo (
pedido_id NUMBER(8),
fecha DATE DEFAULT SYSDATE,
comentario VARCHAR2(200),
importe NUMBER(10,2) DEFAULT 0
);
Tabla creada.
Ejercicio 9: Totales en una tabla temporal
Crea una tabla temporal global totales_pedido_tmp cuyos datos duren toda la sesión, con el identificador del pedido y su importe total. Cárgala con el total de cada pedido calculado a partir de lineas_pedido (cantidad x precio unitario aplicando el descuento) y muestra cuántos pedidos hay y cuál es el mayor total. ¿Qué verá otro usuario conectado a la vez que consulte la tabla?
Ver solución
CREATE GLOBAL TEMPORARY TABLE totales_pedido_tmp (
pedido_id NUMBER(8),
total NUMBER(12,2)
) ON COMMIT PRESERVE ROWS;
INSERT INTO totales_pedido_tmp
SELECT pedido_id, SUM(cantidad * precio_unitario * (1 - descuento / 100))
FROM lineas_pedido
GROUP BY pedido_id;
COMMIT;
SELECT COUNT(*) AS pedidos, MAX(total) AS mayor_total FROM totales_pedido_tmp;
24 filas creadas.
Confirmación terminada.
PEDIDOS MAYOR_TOTAL
---------- -----------
24 11657,5
1 fila seleccionada.
Hace falta ON COMMIT PRESERVE ROWS porque, si no, el COMMIT vaciaría la tabla. Otro usuario (otra sesión) que consulte totales_pedido_tmp verá cero filas: los datos de una GTT son privados de cada sesión. Si esa sesión inserta sus propias filas, tampoco tú las verás.
Ejercicios propuestos
Ejercicio 10: Tabla de una biblioteca
Crea la tabla libros_demo con ISBN (13 caracteres), título, subtítulo opcional, número de páginas, precio, fecha de publicación, idioma (código de 2 letras, 'ES' por defecto), un indicador de si se puede prestar (verdadero por defecto) y un resumen que puede superar los 4000 caracteres. Inserta tres libros y muestra su estructura con DESCRIBE y con USER_TAB_COLUMNS.
Ejercicio 11: Investigar el redondeo
Para una columna NUMBER(4,1), construye una tabla con al menos ocho valores de prueba (incluidos negativos, valores que redondean hacia arriba y casos límite como 999.94 y 999.95) con tu predicción y el resultado real.
Ejercicio 12: Identidades comparadas
Crea tres tablas con identidades ALWAYS, BY DEFAULT y BY DEFAULT ON NULL. Para cada una, prueba: insertar sin la columna, con un valor explícito y con NULL. Resume los resultados en una tabla y explica cuál usarías para una tabla de clientes y por qué.
Ejercicio 13: Columnas virtuales en EMPRESA
Crea con CTAS una copia empleados_v de empleados y diseña (sin modificar la original) una tabla nueva nominas_demo con salario base, comisión y una columna virtual con el salario total anual (14 pagas de salario más 12 comisiones, tratando la comisión nula como 0 con NVL). Carga los datos desde empleados con INSERT INTO ... SELECT y muestra los 5 salarios totales más altos.
Ejercicio 14: Fechas con y sin hora
Crea una tabla con una columna DATE, inserta cuatro filas del mismo día con horas distintas (una de ellas con DATE 'AAAA-MM-DD') y demuestra con consultas por qué WHERE fecha = DATE '...' no devuelve todas. Propón dos formas de obtenerlas todas.
Ejercicio 15: Tipos ANSI de otro SGBD
Te pasan este script escrito para PostgreSQL: CREATE TABLE ventas (id INTEGER, importe DECIMAL(10,2), descripcion VARCHAR(200), fecha TIMESTAMP, pagado BOOLEAN, notas TEXT);. Indica qué partes funcionan en Oracle 26ai, cuáles fallarían, cómo quedarían los tipos tras crearlas y cómo adaptarías el script para 19c.
Ejercicio 16: GTT frente a PTT
Diseña un experimento con dos sesiones abiertas a la vez (por ejemplo, dos hojas de trabajo de SQL Developer con conexiones distintas, o SQL Developer y SQLcl) que demuestre que los datos de una GTT son privados de cada sesión y que la definición de una PTT no es visible desde la otra sesión. Documenta las sentencias y resultados de cada sesión.
Ejercicio 17: Auditoría de tipos
Escribe una consulta sobre USER_TAB_COLUMNS que liste todas las columnas del esquema EMPRESA de tipo NUMBER con su precisión y escala, y otra que muestre las columnas que tienen valor por defecto. Para cada tabla, revisa si cambiarías algún tipo de dato a la vista de la tabla de la sección 11 y justifícalo.
Resumen
| Concepto | Sintaxis / clave |
|---|---|
| Crear tabla | CREATE TABLE [IF NOT EXISTS] t (col tipo [DEFAULT expr] [restricción], ...) [TABLESPACE ts]; |
| Identificadores | 1-128 bytes, empiezan por letra, _ $ #, no reservados; se guardan en MAYÚSCULAS |
| Identificadores entre comillas | "Mi Tabla": respetan mayúsculas y espacios; evítalos |
| Valor por defecto | DEFAULT expr (si no se menciona la columna); DEFAULT ON NULL (también con NULL); ... FOR INSERT AND UPDATE (23ai) |
| Texto | VARCHAR2(n [BYTE\|CHAR]) (4000 bytes), CHAR(n) (2000, relleno), NVARCHAR2, NCHAR; '' es NULL |
| Número exacto | NUMBER(p,s): redondea a s decimales; ORA-01438 si sobran enteros (p - s) |
| Número binario | BINARY_FLOAT, BINARY_DOUBLE (no exactos); FLOAT(p) en bits |
| Fecha y hora | DATE (con hora, 7 bytes), TIMESTAMP(f), WITH TIME ZONE, WITH LOCAL TIME ZONE |
| Intervalos | INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND |
| LOB | CLOB, NCLOB, BLOB, BFILE; no se comparan con = ni se ordenan |
| Otros | RAW(n), ROWID, BOOLEAN (23ai), JSON (21c), VECTOR (23ai) |
| ANSI → Oracle | INTEGER → NUMBER(38), DECIMAL(p,s) → NUMBER(p,s), VARCHAR → VARCHAR2, REAL → FLOAT(63) |
| Identidad | GENERATED ALWAYS \| BY DEFAULT [ON NULL] AS IDENTITY (START WITH n INCREMENT BY m) |
| Columna virtual | col [tipo] [GENERATED ALWAYS] AS (expresión) [VIRTUAL] |
| Columna invisible | col tipo INVISIBLE |
| CTAS | CREATE TABLE t AS SELECT ...; (sin datos: WHERE 1 = 0); no copia PK, FK, UK, CHECK ni DEFAULT |
| GTT | CREATE GLOBAL TEMPORARY TABLE ... ON COMMIT DELETE \| PRESERVE ROWS |
| PTT (18c) | CREATE PRIVATE TEMPORARY TABLE ora$ptt_x ... ON COMMIT DROP \| PRESERVE DEFINITION |
| Diccionario | DESCRIBE, USER_TABLES, USER_TAB_COLUMNS, USER_TAB_COLS, USER_TAB_IDENTITY_COLS |