Archivo del Autor: PabloE

Conversión entre zonas horarias

Deja un comentario

La conversión de fechas y horas entre zonas horarias es una tarea delicada. Los distintos husos horarios no son un dato estático y hay que tener en cuenta si las zonas horarias entre las que queremos convertir horas mantienen el cambio de hora en verano y en qué fecha lo hacen cada año.

Oracle tiene una serie de funciones para tratar fechas que nos pueden resolver este problema. Las siguientes consultas, por ejemplo, convierten una hora en GMT-0 a los husos horarios de Madrid, Chicago y Bogotá en distintos momentos del año. Hemos elegido estas tres ciudades para representar las distintas casuísticas que se pueden presentar, ya que cuentan con distintas fechas de finalización del horario de verano o, como en el caso de Bogotá, zonas que nunca aplican este cambio:

  • Madrid: Domingo, 27 octubre 2013, 03:00
  • Chicago: Domingo, 03 noviembre 2013, 02:00
  • Bogotá no realiza el cambio de horario
-- Europe/Madrid and America/Chicago Daylight Saving Time
select FROM_TZ( to_timestamp('01-Oct-2013 03:11:00.123000', 'DD-Mon-YYYY HH24:MI:SS.FF'), 'Etc/GMT-0') AT TIME ZONE 'Europe/Madrid' as "Madrid hour",
       FROM_TZ( to_timestamp('01-Oct-2013 03:11:00.123000', 'DD-Mon-YYYY HH24:MI:SS.FF'), 'Etc/GMT-0') AT TIME ZONE 'America/Chicago' as "Chicago hour",
       FROM_TZ( to_timestamp('01-Oct-2013 03:11:00.123000', 'DD-Mon-YYYY HH24:MI:SS.FF'), 'Etc/GMT-0') AT TIME ZONE 'America/Bogota' as "Bogota hour"
from dual;

-- Only America/Chicago Daylight Saving Time
select FROM_TZ( to_timestamp('01-Nov-2013 03:11:00.123000', 'DD-Mon-YYYY HH24:MI:SS.FF'), 'Etc/GMT-0') AT TIME ZONE 'Europe/Madrid' as "Madrid hour",
       FROM_TZ( to_timestamp('01-Nov-2013 03:11:00.123000', 'DD-Mon-YYYY HH24:MI:SS.FF'), 'Etc/GMT-0') AT TIME ZONE 'America/Chicago' as "Chicago hour",
       FROM_TZ( to_timestamp('01-Nov-2013 03:11:00.123000', 'DD-Mon-YYYY HH24:MI:SS.FF'), 'Etc/GMT-0') AT TIME ZONE 'America/Bogota' as "Bogota hour"
from dual;

-- None Daylight Saving Time
select FROM_TZ( to_timestamp('01-Dic-2013 03:11:00.123000', 'DD-Mon-YYYY HH24:MI:SS.FF'), 'Etc/GMT-0') AT TIME ZONE 'Europe/Madrid' as "Madrid hour",
       FROM_TZ( to_timestamp('01-Dic-2013 03:11:00.123000', 'DD-Mon-YYYY HH24:MI:SS.FF'), 'Etc/GMT-0') AT TIME ZONE 'America/Chicago' as "Chicago hour",
       FROM_TZ( to_timestamp('01-Dic-2013 03:11:00.123000', 'DD-Mon-YYYY HH24:MI:SS.FF'), 'Etc/GMT-0') AT TIME ZONE 'America/Bogota' as "Bogota hour"
from dual;

El resultado debería ser algo similar al siguiente:

Madrid hour (Daylight Saving Time)      Chicago hour (Daylight Saving Time)     Bogota hour (Never)
-------------------------------------   ------------------------------------    ------------------------------------
01/10/2013 5:11:00,123000000 +02:00     30/09/2013 22:11:00,123000000 -05:00    30/09/2013 22:11:00,123000000 -05:00

Madrid hour (No Daylight Saving Time)   Chicago hour (Daylight Saving Time)     Bogota hour (Never)
-------------------------------------   ------------------------------------    ------------------------------------
01/11/2013 4:11:00,123000000 +01:00     31/10/2013 22:11:00,123000000 -05:00    31/10/2013 22:11:00,123000000 -05:00

Madrid hour (No Daylight Saving Time)   Chicago hour (NO Daylight Saving Time)  Bogota hour (Never)
-------------------------------------   ------------------------------------    ------------------------------------
01/12/2013 4:11:00,123000000 +01:00     30/11/2013 21:11:00,123000000 -06:00    30/11/2013 22:11:00,123000000 -05:00

Para obtener el nombre y abreviatura de las distintas zonas horarias que contempla Oracle solo tenemos que lanzar la siguiente consulta:

select TZNAME, TZABBREV, tz_offset(TZNAME)
from v$timezone_names;

Más información sobre soporte a la globalización y husos horarios en Oracle 11g:

http://docs.oracle.com/cd/E11882_01/server.112/e10729/applocaledata.htm#i637736

Ponencias Oracle 12c Madrid

Deja un comentario

En la presentación de la nueva versión de la base de datos Oracle 12c que hoy ha tenido lugar en Madrid hemos contado con dos ponentes internacionales.

Dominic Giles nos ha presentado la visión de Oracle sobre las bases de datos y las tecnologías cloud.

20131002-220802.jpg

Bob Dunsby se ha centrado en las nuevas capacidades de Oracle 12c para funcionar como un servicio: DBaaS o bases de datos como servicio.

20131002-225902.jpg

Volcado de una consulta SQL a fichero (.csv)

Deja un comentario

Mediante SQL*Plus se pueden realizar volcados de datos a fichero dando el formato que nos sea más conveniente. Todo depende de la parametrización que se realice de la sesión SQL y elaborar un poco las sentencias que utilicemos como fuente de datos.

En este ejemplo vamos a generar un fichero con estructura .csv utilizando la coma como separador y las dobles comillas para las columnas de tipo texto:

SET LINESIZE 80
SET NEWPAGE NONE
SET HEADING OFF
SET COLSEP ''
SET RECSEP OFF
SET PAGESIZE 0
SET FEEDBACK OFF
SET TRIMOUT OFF
SET TRIMSPOOL OFF
SET VERIFY OFF

ACCEPT what_owner PROMPT 'what owner? '

spool all_tables_list.csv;

-- Headers
select '"OWNER","TABLE NAME","NUM ROWS"' from dual;
-- Data
select '"'||owner||'","'||table_name||'",'||nvl(num_rows,0)||''
from all_tables
where owner = '&what_owner';

spool off;

Si guardamos el script del cuadro superior en un fichero con nombre «all_tables_list.sql» y lo ejecutamos el resultado mostrará algo similar a las siguientes líneas (el resultado puede variar dependiendo de la versión de Oracle y las opciones que se tengan instaladas):

SQL> @all_tables_list.sql
 what owner? SYS
 "OWNER","TABLE NAME","NUM ROWS"
 "SYS","AUDIT_ACTIONS",0
 "SYS","AW$AWCREATE",0
 "SYS","AW$AWMD",0
 "SYS","AW$EXPRESS",0
 "SYS","DUAL",0
 "SYS","ODCI_SECOBJ$",0
 "SYS","ODCI_WARNINGS$",0
 "SYS","OLAPTABLEVELS",0
 "SYS","OLAPTABLEVELTUPLES",0
 "SYS","PSTUBTBL",0
 "SYS","STMT_AUDIT_OPTION_MAP",0
 "SYS","SYSTEM_PRIVILEGE_MAP",0
 "SYS","TABLE_PRIVILEGE_MAP",0

Mediante el comando «spool», el script guarda el resultado de la consulta en un fichero con nombre «all_tables_list.csv». El fichero se escribirá en la ruta desde la que hayamos ejecutado SQL*Plus.

Si abrimos este fichero con una aplicación para el tratamiento de hojas de cálculo podremos trabajar con los datos y realizar ordenaciones, agrupaciones y todas las operaciones propias de estas aplicaciones.

Recompile all invalid objects

Deja un comentario

En el trabajo con PL/SQL es habitual encontrarse con distintos objetos de base de datos que han quedado en estado INVALID. Dependiendo de la cantidad de objetos y la complejidad del código PL/SQL utilizado la tarea de recompilar estos objetos puede volverse bastante pesada.

Oracle ofrece distintos medios para recompilar objetos:

  • Identifying Invalid Objects
  • The Manual Approach
  • Custom Script
  • DBMS_UTILITY.compile_schema
  • UTL_RECOMP
  • utlrp.sql and utlprp.sql

En el siguiente artículo detallan el uso y limitaciones de cada uno de ellos:

http://www.oracle-base.com/articles/misc/recompiling-invalid-schema-objects.php

Por mi parte, me decanto por el uso del paquete UTL_RECOMP. A continuación incluyo un script que nos permite recompilar todos los objetos de la base de datos marcados como no VALID:

set serveroutput on;

declare
  v_num_invalid_objects number;
begin

  dbms_output.put_line('Process start at '||to_char(sysdate, 'dd-Mon-yyyy hh24:mi:ss'));

  select count(*) into v_num_invalid_objects from dba_objects where STATUS not like 'VALID';

  if v_num_invalid_objects = 0 then
    dbms_output.put_line('All objets are valid.');
  else

    dbms_output.put_line('There are '||trim(to_char(v_num_invalid_objects))||' invalid objects.');

    for c_schemas in (select OWNER
              from dba_objects
              where STATUS not like 'VALID'
              group by OWNER)
    loop
      begin
        UTL_RECOMP.RECOMP_SERIAL(c_schemas.OWNER);
        dbms_output.put_line('Schema compiled: '||c_schemas.OWNER);
      exception
        when others then
          dbms_output.put_line('Error when recompile objets for schema '||c_schemas.OWNER||': '||sqlerrm);
      end;
    end loop;

    select count(*) into v_num_invalid_objects from dba_objects where STATUS not like 'VALID';

    if v_num_invalid_objects = 0 then
      dbms_output.put_line('All invalid objects recompiled.');
    else

      dbms_output.put_line('**** After recompilation, there are '||trim(to_char(v_num_invalid_objects))||' invalid objects !!!!!');

      for c_invobj in (select OWNER, OBJECT_NAME, OBJECT_TYPE, OBJECT_ID
                          from dba_objects
                          where STATUS not like 'VALID')
      loop
        dbms_output.put_line('* Invalid '||lpad(c_invobj.OBJECT_TYPE, 12, ' ')||': '||c_invobj.OWNER||'.'||c_invobj.OBJECT_NAME||' - id '||c_invobj.OBJECT_ID);
      end loop;

    end if;

  end if;

  dbms_output.put_line('Process finished at '||to_char(sysdate, 'dd-Mon-yyyy hh24:mi:ss'));

exception
  when others then
    dbms_output.put_line('General error: '||sqlerrm);
end;

Este proceso identifica todos los objetos en estado distintos de VALID que se encuentran en la base de datos y los recompila mediante el paquete UTL_RECOMP. Este paquete permite realizar recompilaciones de distintas formas. En el script que se incluye se utiliza la más sencilla de ellas. Pero retocando este script se puede incluir la posibilidad de recompilar objetos en paralelo o lanzar la recompilación a través de jobs.

Para más información sobre el paquete UTL_RECOMP (Oracle 10R2):

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_recomp.htm

PLS-00653: aggregate/table functions are not allowed in PL/SQL scope

1 respuesta

El error PLS-00653 está reportado por el soporte de Oracle en la nota Doc ID 1371524.1 y afecta a las bases de datos 10.2.0.1 (10R2) y posteriores.

Según esta nota, el error está identificado y verificado en el Bug no publicado 5668788. Y como solución proponen no sobrecargar funciones PIPELINED.

Lo primero que tenemos que hacer, por lo tanto, es identificar las funciones que están generando este error de compilación. Y para lograrlo disponemos de una vista del diccionario de datos que nos ofrece toda la información necesaria: DBA_PROCEDURES (y sus alternativas ALL_PROCEDURES y USER_PROCEDURES)

La primera consulta sobre la vista DBA_PROCEDURES muestra todas las funciones PIPELINED que existen en nuestra base de datos y que se encuentran sobrecargadas. Las funciones que generan el problema se encontrarán por tanto en el resultado de esta consulta:

-- PIPELINED functions overloaded
select *
from DBA_PROCEDURES
where pipelined = 'YES'
    and nvl(overload, -1) > 0;

Afinando un poco más, podemos identificar exactamente qué funciones están sobrecargadas por otra función no PIPELINED y proceder a renombrarlas:

-- PIPELINED overloaded with other not PIPELINED functions
select *
from DBA_PROCEDURES pipe
where pipelined = 'YES'
    and nvl(overload, -1) > 0
    and exists
    (
    select object_id
    from DBA_PROCEDURES no_pipe
    where no_pipe.pipelined = 'NO'
        and no_pipe.owner = pipe.owner
        and no_pipe.object_name = pipe.object_name
        and no_pipe.procedure_name = pipe.procedure_name
    );

Ya solo nos queda decidir a qué funciones vamos a cambiar el nombre, y de este modo evitar la sobrecarga. La decisión dependerá del impacto que suponga en cada caso el renombrado y la cantidad de código que deberemos adaptar.

Oracle 12c (12.1) – New Features

Deja un comentario

Documentación sobre las nuevas funcionalidades que incorpora Oracle 12cR1:

ODB_12c

Bienvenida

Deja un comentario

Este sitio pretende ofrecer comentarios, ejemplos y anécdotas en torno al diseño y programación de bases de datos Oracle. También se va a reservar un espacio dedicado a APEX, dada la potencia que aporta a estas bases de datos.

Pronto estaremos con ustedes.