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

Publicado el 18/05/2016 por cajondesastreoracle | Deja un comentario

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;

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

Publicado el 02/04/2016 por cajondesastreoracle | 1 comentario

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)

Publicado el 30/01/2016 por cajondesastreoracle | Deja un comentario

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)

Query para convertir a hora local un string con timezone

Publicado el 08/06/2014 por cajondesastreoracle | Deja un comentario

select cast((to_timestamp_tz(‘2010-08-29T10:00:01+03′,’yyyy-mm-dd»T»hh24:mi:sstzh:tzm’) AT LOCAL) as date ) from dual;

Como eliminar registro duplicados en Oracle

Publicado el 18/12/2011 por cajondesastreoracle | 9 comentarios

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:

Publicado el 01/12/2011 por cajondesastreoracle | 1 comentario

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

Publicado el 24/11/2011 por cajondesastreoracle | Deja un comentario

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

Nueva sintaxis ISO para SQL

Publicado el 07/09/2011 por cajondesastreoracle | 2 comentarios

INNER JOIN:
Tipo de join común, solo muestra datos que coinciden entre dos tablas:
Sintaxis tradicional:

SELECT d.localizacion_id, departamento_nombre
FROM departamentos d, localizaciones l
WHERE d.localizacion_id = l.localizacion_id;

Sintaxis ISO
Para escribir el mismo query con la sintaxis ISO existen varias formas de escribir esta query:
Natural Join
Si se utilice natural join, se permite que Oracle determine la condición y las columnas que necesita para hacer el join:

SELECT localizacion_id, departamento_nombre
FROM departamentos NATURAL JOIN localizaciones;

Clausula: JOIN … USING
Si las tablas que vamos a utilizar para hacer el el join tienen columnas con el mismo nombre se puede utilizar esta clausula utilizando poniendo entre paréntesis la columna común:

SELECT localizacion_id, departamento_nombre
FROM departamento JOIN localizacion
USING (localizacion_id);

JOIN … ON clause
Cuando las tablas tienen columnas con nombre diferentes utilizaremos JOIN… ON

SELECT d.loc_id, departamentos_nombre
FROM departamentos d JOIN localizaciones l
ON l.localizacion_id = d.loc_id;

Ejemplo un poco más complejo:

SELECT apellido, nombre, departamento_nombre, ciudad, 
FROM empleados
JOIN departamentos USING (departamento_id)
JOIN ciudades USING (ciudad_id)
WHERE departamento_id = 20;

Cuando se utilizan estas clausulas JOIN … ON o JOIN … USING es más claro, aunque es opcional utilizar la palabra clave INNER así queda más claro el tipo de join:

SELECT location_id, department_name, city
FROM departments INNER JOIN locations
USING (location_id);

OUTER JOIN

Sintaxis tradicional
Se utilizaba un (+) en el where al lado de la columna de la tabla que se sabe que no va a tener datos

SELECT l.location_id, city, department_id, department_name
FROM locations l, departments d
WHERE l.location_id = d.location_id(+);

Sintaxis ISO
Se utilizan las clausulas LEFT JOIN or RIGHT JOIN

SELECT location_id, city, department_id, department_name
FROM locations LEFT JOIN departments d
USING (location_id);

Esta select es la equivalente a la de arriba. Por otro lado cuando se quería recuperar registros que podían no estar en alguna de las dos tablas se utilizaba un UNIO, ahora se simplifica con FULL OUTER JOIN.

Sintaxis tradicional

SELECT last_name, first_name, department_name
FROM employees e, departments d
WHERE e.manager_id = d.manager_id(+) 
UNION
SELECT last_name, first_name, department_name
FROM employees e, departments d
WHERE e.manager_id(+) = d.manager_id 
ORDER BY department_name, last_name, first_name;

Sintaxis ISO

SELECT last_name, first_name, department_name
FROM employees FULL JOIN departments
USING (manager_id);
ORDER BY 1;

CROSS JOIN

Join cartesiano que prácticamente no se utiliza pero en ocasiones puede ser útil: En este caso las vistas v$instance y v$database solo contiene un registro con lo cual no se produce un producto cartesiano: