Snippets Oracle SQL

Mostrar las tablas a las que tiene acceso nuestra cuenta:

SELECT owner, TABLE_NAME
  FROM all_tables

Mostrar nuestras tablas:

SELECT TABLE_NAME
  FROM user_tables

Fecha de hace 6 meses:

SELECT sysdate, -- 01/07/25
       add_months(sysdate, - 6) -- 01/01/25
FROM dual

Diferencia entre fechas:

SELECT (sysdate - to_date('2020-01-01 00:00', 'YYYY-MM-DD hh24:mi') AS dias_diferencia
FROM dual

Mostraría los días entre sysdate y las 2020-01-01 00:00. Si queremos pasarlo a horas, habría que multiplicarlo por 24. Para pasar a minutos, multiplicarlo por 24 y por 60, etc.

Para extraer año, mes, día de una fecha:

SELECT EXTRACT(YEAR FROM sysdate)
FROM dual;
 
SELECT EXTRACT(MONTH FROM sysdate)
FROM dual;
 
SELECT EXTRACT(DAY FROM sysdate)
FROM dual;

Otra opción:

SELECT to_char(sysdate, 'yyyy')
FROM dual;
 
SELECT to_char(sysdate, 'mm')
FROM dual;
 
SELECT to_char(sysdate, 'dd')
FROM dual;

• TO_CHAR Day Format Masks

Si queremos agrupar por año y mes:

SELECT to_char(DATE_CREATED, 'YYYY-MM'), SUM(Num_of_Pictures)
FROM pictures_table
GROUP BY to_char(DATE_CREATED, 'YYYY-MM')
ORDER BY 1

De otra forma, con un SQL estándar:

SELECT EXTRACT(YEAR FROM date_created) AS yr, EXTRACT(MONTH FROM date_created) AS mon,
       SUM(Num_of_Pictures)
FROM pictures_table
GROUP BY EXTRACT(YEAR FROM date_created), EXTRACT(MONTH FROM date_created)
ORDER BY yr, mon;

Descartar fines de semana (sábados y domingos)

SELECT * 
FROM tabla
WHERE TO_CHAR(campo_fecha, 'DY','NLS_DATE_LANGUAGE=AMERICAN') NOT IN ('SAT', 'SUN')

Si vamos a exportar datos de una consulta a fichero CSV y queremos abrirlo con Excel con configuración española, para que Excel interprete bien las fechas hay que usar alguno de estos formatos:

• Estándar ISO: 'yyyy-mm-dd hh24:mi:ss'
• Formato Excel en español: 'dd/mm/yyyy hh24:mi:ss'

Ejemplo:

SELECT
    id,
    TO_CHAR(date_creation, 'yyyy-mm-dd hh24:mi:ss') AS fecha_creacion,
    TO_CHAR(date_solution, 'yyyy-mm-dd hh24:mi:ss') AS fecha_solucion
FROM tabla    

O:

SELECT
    id,
    TO_CHAR(date_creation, 'dd/mm/yyyy hh24:mi:ss') AS fecha_creacion,
    TO_CHAR(date_solution, 'dd/mm/yyyy hh24:mi:ss') AS fecha_solucion
FROM tabla    

Mediante la función REGEXP_SUBSTR se puede extraer un subcadena de un texto:

SELECT regexp_substr('Metallica - Nothing Else Matters', '[^-[:space:]]*$')
FROM dual

Devolvería Nothing Else Matters

SELECT column1, column2, column3
FROM dual
WHERE regexp_like(column1, '^(987|I)')

Filtraríamos por la column1 buscando que comenzase por 987 o por I.

SELECT banner 
FROM v$version 
WHERE banner LIKE 'Oracle%'

Ejemplo de salida:

Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production

Desde la versión 12c de Oracle:

SELECT *
FROM nombre_de_tabla
FETCH FIRST 10 ROWS ONLY;

En versiones anteriores:

SELECT *
FROM nombre_de_tabla
WHERE ROWNUM <= 10;

Para ver el formato de fecha actual (`NLS_DATE_FORMAT`) en Oracle Database, tienes varias opciones según lo que quieras comprobar.

Ver el formato en la sesión actual:

SELECT VALUE 
FROM nls_session_parameters 
WHERE parameter = 'NLS_DATE_FORMAT';

Ver el formato a nivel de base de datos (valor por defecto que puede ser sobrescrito por la sesión):

SELECT VALUE 
FROM nls_database_parameters 
WHERE parameter = 'NLS_DATE_FORMAT';

Ejemplo de salida:

DD-MON-RR

• DD: día con dos dígitos
• MON: mes abreviado en texto.
• RR: año con 2 dígitos (RR es parecido a YY, pero con lógica para siglos)

• Selecciónar solo 1 registro
• The Most Useful Date and Time Functions