Cómo obtener la instrucción de creación de un scheduler job

Sqlplus ‘/ as sysdba’
set linesize 10000
set pagesize 0
set long 200000
set trimspool on
set feed off
column columna format a500
set echo off
set pages 1000
SELECT dbms_metadata.get_ddl(‘PROCOBJ’,'<job_name>’, upper( ‘<user>’)) from dual;

Anuncios

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

Función BITAND de Oracle (bitwise AND)

Lo que hace está función es convertir los dos argumentos en números binarios y luego los combina utilizando un operador bitwise AND y el resultado de convierte de vuelta a un numero entero. Los ejemplos de más abajo pueden ser más ilustrativos.

SELECT BITAND(0, 0) AS "0, 0", -- i.e. 0 AND 0 = 0
 BITAND(0, 1) AS "0, 1", -- i.e. 0 AND 1 = 0
 BITAND(1, 0) AS "1, 0", -- i.e. 1 AND 0 = 0
 BITAND(1, 1) AS "1, 1" -- i.e. 1 AND 1 = 1
 FROM DUAL;
 2 3 4 5
 0,0     0, 1     1, 0    1, 1
------  -------  ------  -------  
 0         0       0        1
Lo que sería igual a 
BITAND(0, 0) 
0000 0
0000 0
----
0000 0

BITAND(0, 1)
0000 0
0001 0
—-
0000 0

BITAND(1, 0)
0001 1
0000 0
—-
0000 0

BITAND(1, 1)
0001 1
0001 1
—-
0001 1

 

Estos enlaces pueden servir de referencia:

https://en.wikipedia.org/wiki/Bitwise_operation

Oracle BITAND function (bitwise AND)

Publicado en SQL

Como eliminar registro duplicados en Oracle

Método 1:

SQL> DELETE FROM table_name A WHERE ROWID > (
  2    SELECT min(rowid) FROM table_name B
  3    WHERE A.key_values = B.key_values);

Se pueden borrar todos los rowids mayores o menores que el valor dado.

Método 2:

SQL> create table table_name2 as select distinct * from table_name1;
SQL> drop table table_name1;
SQL> rename table_name2 to table_name1;

Este método suele ser más rápido, pero hay que recrear todos los indexes, constraints, triggers, etc.

Método 3:

SQL> delete from my_table t1
SQL> where  exists (select 'x' from my_table t2
SQL>                 where t2.key_value1 = t1.key_value1
SQL>                   and t2.key_value2 = t1.key_value2
SQL>                   and t2.rowid      > t1.rowid);

Si se comparan columnas not null se tiene que utilizar la función NVL, ya NULL no es igual a NULL

Método 4:

SQL>DELETE  FROM EMP 
SQL>WHERE ('DELETE',SAL)  IN (
SQL>             SELECT CASE WHEN COUNT(*)>1 THEN 'DELETE' ELSE 'NO' END DELETEFLAG ,SAL 
SQL>                  FROM EMP 
SQL>                  GROUP BY SAL 
SQL>                  HAVING COUNT(*)>1)

Método 5:

SQL>DELETE table_name
SQL>WHERE  rowid IN
SQL>       ( SELECT LEAD(rowid) OVER
SQL>                (PARTITION BY key_values ORDER BY NULL)
SQL>         FROM   table_name );

Como encontrar solapamientos en rangos de fecha:

Queremos identificar todos los empleados que comienzan un nuevo proyecto antes de terminar el existente. Partiendo de la siguiente información:

EMP  NOM                  PROY FECHA_INI          FECHA_FIN
---- -------------------- ---- ------------------ ------------------
1    Juan                 1    16-JUN-11          18-JUN-11
1    Juan                 4    19-JUN-11          24-JUN-11
1    Juan                 7    22-JUN-11          25-JUN-11
1    Juan                 1    25-JUN-11          28-JUN-11
2    Pedro                2    17-JUN-11          21-JUN-11
2    Pedro                8    23-JUN-11          25-JUN-11
2    Pedro                1    29-JUN-11          30-JUN-11
2    Pedro                1    26-JUN-11          27-JUN-11
2    Pedro                5    20-JUN-11          24-JUN-11
3    Luis                 3    18-JUN-11          22-JUN-11
3    Luis                 1    27-JUN-11          28-JUN-11
3    Luis                 1    30-JUN-11          03-JUN-11
3    Luis                 9    24-JUN-11          27-JUN-11
3    Luis                 6    21-JUN-11          23-JUN-11

Revisando los resultados vemos por ejemplo que el empleado Juan comienza el proyecto 7 antes de terminar el 4
Para determinar que proyectos se están solapando podemos utilizar la siguiente select:

select a.IDempleado,a. Nombre,
        ‘proyecto ‘||b.Proyecto_id||
        ‘ se solapa con el proyecto ‘||a.Proyecto_id as msg
   from emp_proyectos a,
        emp_proyectos b
  where a.IDempleado = b.IDempleado
    and b.fecha_ini >= a.fecha_ini
    and b.fecha_ini <= a.fecha_fin
    and a.Proyecto_id != b.Proyecto_id
    
   
1|Juan|proyecto 7 se solapa con el proyecto 4
1|Juan|proyecto 10 se solapa con el proyecto 7
2|Pedro|proyecto 8 se solapa con el proyecto 5
2|Pedro|proyecto 5 se solapa con el proyecto 2
3|Luis|proyecto 12 se solapa con el proyecto 9
3|Luis|proyecto 6 se solapa con el proyecto 3

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 );
       ....