ORA-01878: specified field not found in datetime or interval

El problema es que existen datos que queremos convertir con un función de timezone, en el intervalo horario del cambio de hora. Por ejemplo si el cambio de hora es el 27/3 a las 2:00. No podemos tener datos entre las 2:00 y las 2:59. El último tiene que ser a la 1:59 y el siguiente a las 3:00. Ya que ese intervalo de tiempo realmente no existe.

La solución es buscar los datos que se encuentran en ese intervalo con un betwen y actualizarlos a un valor a un valor correcto.

Realmente no es un problema de Oracle es la forma en que se tratan las horas con DST, cuando se modifica el offset , siempre hay una hora en que el tiempo se mueve hacia adelante (= no existe).

http://en.wikipedia.org/wiki/Daylight_saving_time

Recuperar información de una columna CLOB

Una columna CLOB puede contener hasta 4GB de información, por lo tanto  recuperar su información con sql*plus no es  tan sencillo, hay que hacerlo utilizando la librería DBMS_LOB

Utilizaremos las dos funciones siguientes:

DBMS_LOB.substr(CLOB, longitud, posicion_inicial);
DBMS_LOB.getlength(CLOB);

Con ambas podemos obtener la siguiente select:

SELECT DBMS_LOB.substr(valor,DBMS_LOB.getlength(valor),1)
 FROM table_valor;

Está select dará error con valores de más de 5000 caracteres:

SELECT DBMS_LOB.SUBSTR (valor,DBMS_LOB.getlength(valor),1) FROM tabla_valor
*
ERROR en línea 1:
ORA-06502: PL/SQL: error : buffer de cadenas de caracteres demasiado pequeño
numérico o de valor
ORA-06512: en línea 1

Esto se debe a que en sql el máximo de longitud permitido es 4000, con lo cual limitándolo a los primeros 4000 caracteres evitamos este error:

dbms_lob.substr( clob_column, for_how_many_bytes, from_which_byte );
 

Utilizando plsql podemos recuperar hasta 32K

declare
   my_var long;
begin
   for x in ( select X from t )
   loop
       my_var := dbms_lob.substr( x.X, 32000, 1 );
       ....

Uso de Bind variables con EXECUTE IMMEDIATE

El uso de execute immediate es muy cómodo para la construcción de sql dinámicos el problema es el parseo de estas sentencias, pongo un pequeño ejemplo de cómo utilizar bind variables en este tipo de sentencias, para evitar parseos inecesarios.

BEGIN
FOR i IN 100..206
LOOP
execute immediate 'UPDATE empleados SET salario=salario*0,1 WHERE id_empleado = :empid' USING i;
END LOOP;
COMMIT;
END;

Como borrar jobs duplicados

Hace unos días nos encontramos que unos imports que habíamos hecho sobre la base de datos, duplicaron unos jobs, este es el procedimiento que utilizamos para borrar los jobs duplicados:

DECLARE
   CURSOR c2
   IS
      SELECT job
        FROM user_jobs
       WHERE ROWID NOT IN (SELECT   MIN (ROWID)
                               FROM user_jobs
                           GROUP BY what);
BEGIN
   FOR c IN c2
   LOOP
      DBMS_JOB.remove (c.job);
   END LOOP;
END;
/

Activar y desactivar los jobs de un usuario:

Activarlos:

sqlplus ‘/ as sysdba’
SET SERVEROUTPUT ON;
DECLARE
CURSOR C_JOBS IS
select job,BROKEN from dba_jobs
where UPPER(schema_user)='<SCHEME_USER_IN_CAPITAL_LETTERS>';         
BEGIN
FOR VREG IN C_JOBS LOOP
BEGIN
IF VREG.BROKEN = 'Y' THEN
dbms_ijob.broken(VREG.JOB,FALSE);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
END LOOP;
END;
/

Desactivarlos:

sqlplus ‘/ as sysdba’
SQL>     SET SERVEROUTPUT ON;
DECLARE
CURSOR C_JOBS IS
select job,BROKEN from dba_jobs
where UPPER(schema_user)='<SCHEME_USER_IN_CAPITAL_LETTERS>';       
BEGIN
FOR VREG IN C_JOBS LOOP
BEGIN
IF VREG.BROKEN = 'N' THEN
dbms_ijob.broken(VREG.JOB,TRUE);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
END LOOP;
END;
/