Recetassql

Recetas SQL¶

Fechas¶

Obtener todos los anteriores a la fecha actual (o una fecha cualquiera):
```
AND campoFecha < to_date(10/02/2020, 'DD/MM/YYYY')
```

Obtener el mínimo entre dos fechas: se puede hacer con LEAST

SELECT LEAST(date_1, date_2) FROM DUAL;

Ver fecha de creación y modificación de un objeto de la base de datos¶

SELECT object_name, object_type, created, TIMESTAMP
FROM user_objects
WHERE object_name = 'TR_SF_HIS_CEN'

Obtener listado de tablas con su descripción¶

SELECT TABLE_NAME, comments 
FROM user_tab_comments
WHERE table_type = 'TABLE'

Obtener listado columnas de una tabla con su descripción¶

SELECT col.column_name, com.comments FROM user_tab_columns col, user_col_comments com
WHERE 
col.table_name='SI_SOL_PRO_FIP' AND com.table_name='SI_SOL_PRO_FIP'
AND col.column_name=com.column_name
ORDER BY col.column_id

Campos BLOB¶

Ver el contenido de un campo BLOB¶

SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(CAMPO_BLOB))
FROM ...

Búsquedas en campos BLOB¶

Para buscar dentro de los campos BLOB se ejecuta una consulta del tipo:

select * from hipe.hipe_carga_lab
where dbms_lob.instr(t_archivo, utl_raw.cast_to_raw('0000004399'))>0;

que buscaría dentro los ficheros de texto enviados cuáles tienen el código de muestra cliente 0000004399.

Secuencias Oracle¶

Obtener número actual de todas las secuencias¶

SELECT sequence_name, last_number 
from user_sequences;

select secuencia.currval from dual solo se puede usar en la misma sesión que se haya hecho un secuencia.nextval.

Cómo reiniciar una secuencia en Oracle (con el nº apropiado)¶

ALTER SEQUENCE nombre_secuencia INCREMENT BY -n;

Hay que tener cuidado porque esto cambia la forma de funcionar la frecuencia, así cada vez que se hace un nexval decrementaría lo que le hemos puesto. Así que una vez que esté en el número correcto habrá que hacer:

ALTER SEQUENCE nombre_secuencia INCREMENT BY 1

Recompilar los objetos inválidos de la base de datos¶

SELECT 'alter ' || object_type || ' ' || object_name || ' compile;'
FROM user_objects
WHERE STATUS <> 'VALID'
AND object_type IN ('VIEW','SYNONYM','PROCEDURE','FUNCTION','PACKAGE','TRIGGER');

Plan de ejecución¶

Crear un plan de ejecución para una select

EXPLAIN PLAN
  for select * from val_val_tab;

Ver el plan de ejecución

SELECT * FROM plan_table 
where trunc(timestamp) = trunc(sysdate);

Obtener SID de la base de datos¶

SELECT sys_context('userenv','db_name') FROM dual

Obtener los números no enteros de un campo en la base de datos¶

SELECT * FROM val_esp_for WHERE round(efv_esp_hor) <> efv_esp_hor

Insertar un retorno de línea en un varchar¶

'Hello..' || chr(13) || chr(10) || 'how are you';

Hacer insert con una subselect¶

INSERT INTO DCF_COLECTIVOS (PRO_COD, PRO_ANO, COLECTIVO)  
(SELECT pro_cod, pro_ano, '001' FROM val_pro
WHERE pro_cod NOT IN ('FOD', 'FIP', 'FC', 'FCS') AND pro_ano>=2010)

Ejemplo de insert con varias subselects¶

INSERT INTO SI_SOL_PRO_FIP ( 
   SOL_COD, PRO_COD, PRO_ANO, SOL_ADM, SOL_VIA_PRO,
   CEN_NUM_PRV, CEN_NUM_COD, SOL_BEN_CIF, 
   SOL_CCC_ENT, SOL_CCC_SUC, SOL_CCC_DC, SOL_CCC_NUM, 
   SOL_ACC_ITI, SOL_ACC_ESP_COD, 
   SOL_ACC_DIR_VIA_COD, SOL_ACC_DIR_NOM_VIA, SOL_ACC_DIR_NUM_VIA, SOL_ACC_DIR_BIS_COD, 
   SOL_ACC_DIR_ESC, SOL_ACC_DIR_PIS, SOL_ACC_DIR_LET_NUM_PUE, SOL_ACC_DIR_MUN_COD, SOL_ACC_DIR_CPS_COD, 
   SOL_ACC_HOR, SOL_ACC_ALU, SOL_ACC_DIA_LEC)
(SELECT 
   seq_si_sol_pro_fip.nextval, 'FOD', '2017', 'N', '01',
   '47', '30195', '11111111H',
   '2100', '0418', '40', '1234567891',
   'N', 'ADGD0208', 
   tit_dir_via_cod, tit_dir_nom_via, tit_dir_num_via, tit_dir_bis_cod, 
   tit_dir_esc, tit_dir_pis, tit_dir_let_num_pue, tit_dir_mun_cod, tit_dir_cps_cod, 
   efv_esp_hor, efv_esp_alu, 
   '50' 
FROM 
  (SELECT 
     TIT_DIR_VIA_COD, TIT_DIR_NOM_VIA, TIT_DIR_NUM_VIA, TIT_DIR_BIS_COD, 
     TIT_DIR_ESC, TIT_DIR_PIS, TIT_DIR_LET_NUM_PUE, TIT_DIR_MUN_COD, TIT_DIR_CPS_COD 
   FROM SI_TIT 
   WHERE TIT_CIF = '11111111H'), 
  (SELECT 
     EFV_ESP_HOR, EFV_ESP_ALU 
   FROM VAL_ESP_FOR 
   WHERE ESP_COD = 'ADGD0208') 
);

Generar updates (comilla simple)¶

-- Ejemplo de cómo construir la comilla simple en oracle (chr(39)

SELECT 'update val_esp_for_vig SET efv_mda_eur=' || chr(39) || efv_mda_eur || chr(39) || ', efv_mdb_eur=' || chr(39) || efv_mdb_eur || chr(39) || ' WHERE esp_cod='|| chr(39) || ESP_COD || chr(39) ||';'
FROM val_esp_for

Crear tabla a partir de una vista¶

El problema era que con un

CREATE TABLE AS (SELECT * FROM <VISTA>)

daba error, porque la vista tenía muchos caracteres nulos

Lo he hecho así:

SELECT column_name || '    ' ||  data_type || '(' || data_length || '),' FROM all_tab_columns
WHERE TABLE_NAME = 'VW_LIS_USU_FPO'

y luego aprovechando la salida de esta select para el create table

habría que afinarlo, porque da error en los tipos date, pero basta con tratarlos aparte y ya está

Cambiar nulos por 0 en resultado de una select¶

La select original a veces devolvía nulos, lo que daba un error al utilizarlo en jasper dentro de una suma. Se modifica para que en vez de nulo dé 0

Un report con la siguiente consulta:

SELECT acp_imp_sub_for imp
FROM SI_ACC_FPO_PRE
WHERE SOL_COD =$P{CAM_SOL_COD} AND ACC_ID =$P{CAM_ACC_ID}  AND substr(acp_cod,8,1)=$P{CAM_ITI_ID}

devolvía en algún caso nulo (la última condición no se daba siempre). Este subreport se utilizaba dentro de una suma, así que cascaba

Se cambia por:

SELECT MAX(imp) FROM
(SELECT acp_imp_sub_for imp
FROM SI_ACC_FPO_PRE
WHERE SOL_COD =$P{CAM_SOL_COD} AND ACC_ID =$P{CAM_ACC_ID}  AND substr(acp_cod,8,1)=$P{CAM_ITI_ID}
UNION
SELECT 0 imp
FROM DUAL)

y ya funciona la suma

Buscar dentro de un campo BLOB de oracle¶

select * from hipe.hipe_carga_lab
where dbms_lob.instr(t_archivo, utl_raw.cast_to_raw('0000004399'))>0;

buscaría dentro los ficheros que están grabados en el campo blob t_archivo cuáles tienen el texto 0000004399

Última actualización: August 15, 2021