Oracle Notepad

Incorporando Google Maps en Application Express

2008-04-20T04:51:00.000-07:00

Diversos artículos pueden encontrarse en Internet acerca de cómo implementar Google Maps en Oracle Application Express (APEX). Desafortunadamente, la mayoría son algo básicos y se limitan a cargar un mapa dentro de una región, no teniendo interacción con la base de datos.

En esta oportunidad vamos a ver cómo lograr desplegar puntos dinámicamente en un mapa a partir de datos almacenados en una tabla. Únicamente se requieren conocimientos básicos de Application Express, PL/SQL y Javascript.

Este artículo no pretende mostrar todas las funcionalidades de la API de Google Maps, sino resolver el problema puntual de programación en Oracle, luego dependerá de las habilidades Javascript del programador y de conocer a fondo las funcionalidades que brinda esta maravillosa API.

Aclarado esto, comencemos, paso a paso:

Paso 1: Tener APEX instalado.

Lo primero es tener APEX instalado, ya que necesitaremos la URL o IP para el paso siguiente.
Si tienen duda si APEX está instalado en su base de datos, ver mi artículo anterior.

Paso 2: Solicitar una clave en Google Maps.

Ir a la siguiente direccion http://code.google.com/apis/maps/signup.html para registrarnos y solicitar un código que necesitamos para utilizar el servicio. En el registro, se debe ingresar la URL donde será utilizado Google Maps.
Por ejemplo, si utilizamos el espacio gratuito Oracle Apex, podemos ingresar la URL http://apex.oracle.com/pls/otn/. Si se está desarrollando localmente (en la propia máquina) puede registrarse la IP 127.0.0.1, pero cuando finalice el desarrollo habrá que solicitar una nueva clave con la URL de producción, ya que Google requiere que el sitio registrado sea de acceso público.
El código generado será referenciado desde de nuestro script.

Paso 3: Manos a la obra con APEX!

En nuestra nueva aplicación APEX, como primer paso debemos crear una página.

Editar los atributos de la página y en el campo Enfoque de Cursor elegir el valor No enfocar Cursor.

En la sección Cabecera HTML, incluir el siguiente script reemplazando nuestra clave:

<script src="http://maps.google.com/maps?file=api&v=2&key=la_clave_de_google_maps&hl=es"
type="text/javascript"></script>
<script type="text/javascript">

//<![CDATA[

function load() {
if (GBrowserIsCompatible()) {
miMapa();
}
}

//]]>
</script>

En este javascript acabamos de definir la función load, la cual invoca a otra función miMapa que crearemos más adelante.

En el campo Atributo de Cuerpo HTML de Página, pegar lo siguiente:

onload="load();" onunload="GUnload();"

Se puede observar cómo en el evento onload de la página, se invoca a la función load incluída en el cabezal HTML.

Hecho esto, guardar los cambios efectuados en la página

Ahora queda la parte interesante, ya que deberemos crear la función miMapa la cual ejecutará la carga del mapa y los puntos de referencia extraídos de la base de datos. Esta función será generada por un bloque anónimo PL/SQL el cual será ejecutado cada vez que se carga la página.
Para este ejemplo, se creó una sencilla tabla geopuntos la cual contiene información sobre los puntos de referencia que queremos desplegar en nuestro mapa:

id_punto     NUMBER(10)    NOT NULL
latitud      NUMBER(18,15) NOT NULL
longitud     NUMBER(18,15) NOT NULL
descripcion  VARCHAR2(100)

La precisión de latitud y longitud es adecuada para almacenar coordenadas geográficas de Google Maps.
Cada punto puede enriquecerse con valiosa información como pueden ser imagenes, archivos, links, íconos, etc.
Se recomienda consultar la extensa documentación y tutoriales que brinda Google Maps en su sitio oficial.

En el área Presentación de Página, en la sección Procesos, vamos a implementar nuestro bloque PL/SQL.
Crear un nuevo proceso de tipo PL/SQL. En Atributos de Proceso, ingresar un nombre cualquiera y elegir el valor En Carga, Antes de Cabecera. Clickear siguiente, y en Proceso, incluir el siguiente bloque:

DECLARE
  CURSOR cr_puntos IS select * from puntosmapa;
BEGIN
  htp.p('<script type="text/javascript">');
  htp.p('//<![CDATA[');  
  
  htp.p('function crearMarca(lat, long, descr) {');
  htp.p('  var point = new GLatLng(lat,long);');
  htp.p('  var opts = {title: descr};');
  htp.p('  var mark = new GMarker(point,opts);');
  htp.p('  GEvent.addListener(mark, "click", function() {
    mark.openInfoWindowHtml(''<div><B>'' + descr + ''</B><BR>Lat: '' + lat +
 ''<BR>Long: '' + long + ''</div>'');');
  htp.p('  });');
  htp.p('return mark;');
  htp.p('}');  
  
  htp.p('function miMapa() {'); 
  htp.p('var map = new GMap2(document.getElementById("map"));');
  htp.p('map.addControl(new GLargeMapControl());');
  htp.p('map.addControl(new GMapTypeControl());');
  htp.p('map.addControl(new GOverviewMapControl());');
  htp.p('map.setCenter(new GLatLng(-22.915739,-43.22912), 11);');
  
  -- Recorro cursor e inserto los puntos
  FOR punto IN cr_puntos
  LOOP
    htp.p('var marker = crearMarca('||to_char(punto.latitud,'999.9999999999999999')||','
   ||to_char(punto.longitud,'999.9999999999999999')||',"'||punto.descripcion||'");');  
    htp.p('map.addOverlay(marker);');
  END LOOP;
  htp.p('}');

  htp.p('//]]>');
  htp.p('</script>');
END;

Este es el punto neurálgico de la aplicación: en este bloque PL/SQL resolvemos la consulta a la base de datos, la creación del mapa y la publicación de los puntos de referencia extraídos. Notar el uso de la función PL/SQL htp.p(). Así como DBMS_OUTPUT.PUT_LINE envia caracteres a la salida en pantalla, la función htp.p envía cadenas de caracteres al browser, y esto precisamente lo que necesitamos ya que queremos construir el Javascript a ser ejecutado en la carga de la página.

Puede apreciarse la sencillez de esta implementación, en pocas líneas crea un mapa, lo centra y carga los controles básicos de zoom. La particularidad en este caso es el FOR LOOP, el cual recorre un cursor definido sobre la tabla, y en cada iteración define una marca "publicando" Javascript en tiempo de ejecución. Cada referencia tiene un toolTip cuando se pasa el mouse por encima y un globo informativo que se abre al clickear sobre la marca.

Y eso es todo, tras guardar el proceso (dejar el resto de los valores por defecto), hemos concluído nuestra aplicación y ya está lista para ser explorada.

Las posibilidades son muchísimas, y cuanto más se profundice sobre la documentación, mejores funcionalidades podremos lograr en APEX.

Un ejemplo implementado

Hice una página de demostración para que puedan ver que realmente funciona. En mi tabla de datos tengo almacenados puntos turísticos de Río de Janeiro, ciudad donde vivo actualmente :)

Para saber las coordenadas geográficas de un punto en la tierra, podemos utilizar por ejemplo la página Geocoder.

Hay mucha documentación y ejemplos disponibles sobre Google Maps y realmente es fácil comenzar a obtener resultados sorprendentes.

Finalmente le agradezco a Emilio Le Mener por su ayuda en Javascript.

Espero que este tutorial haya sido útil y puedan comenzar a implementar sin problemas con APEX y Google Maps.

Probar la Demo

Ver también:
Google Maps API
Presentación Google Developer Day 2007

Cómo saber si Application Express (APEX) está instalado en Oracle

2008-04-18T00:05:00.000-07:00

Ejecutar la siguiente consulta en SQL*Plus con un usuario con rol DBA:

SELECT username
FROM dba_users
WHERE username IN ('FLOWS_010500','FLOWS_010600','FLOWS_020000','FLOWS_020200','FLOWS_030000', 'FLOWS_030100');

Si no se obtiene ninguna fila como resultado, entonces APEX no está instalado en la base de datos.

Oracle libera parches de seguridad para todos sus productos

2008-04-17T08:34:00.000-07:00

Este 15 de abril fueron publicados parches para todos los productos Oracle, incluída la base de datos, la E-Business Suite, PeopleSoft, Siebel y otros productos.

En cuanto a la base de datos, los parches abarcan todas las versiones desde la 9.0.1.5, y algunos de los bugs que se corrigen son vulnerabilidades graves que permiten explotar la base de datos aún sin autenticación. Este es un asunto de alerta máxima y no debe demorarse la decisión de actualizar el software. La instalación es sencilla y no toma mucho tiempo.

El Application Server 9i Release 1, version 1.0.2.2 también presenta vulnerabilidades en el JInitiator 1.3.1.14, Enterprise Manager 1.0.2.2 y el Enterprise Portal 9.0.4.3. Estos agujeros de seguridad pueden ser explotados sin necesidad de usuario y password.

Oracle recomienda fuertemente descargar estos patches (gratuitamente desde otn.oracle.com) y aplicarlos en todos los casos para evitar riesgos ya detectados.

Ver también:
Oracle Critical Patch Update Advisory - April 2008

Sobre comentarios y cómo actualizar secuencias

2008-04-14T05:27:00.000-07:00

Cuando se realizan cargas de datos en un esquema existente y no se utilizan las secuencias para asignar los valores de llave primaria, se produce un desfasaje entre identificadores y secuencias. Este desfasaje provoca errores de clave primaria ya existente, ya que los valores retornados por secuencia.NEXTVAL, pueden ya existir entre los datos recientemente cargados.

Para sincronizar las secuencias con los identificadores de llave primaria, puede crearse un script en PL/SQL el cual recree cada secuencia con su valor inicial según el máximo identificador de la tabla correspondiente. Para poder realizarlo, necesitamos primero tener una correspondencia entre tabla y secuencia, y en Oracle esta dependencia no existe.

Es una buena oportunidad para comenzar a adoptar la buena práctica de usar comentarios sobre los objetos. Muchas veces, en el proceso de creación de una base de datos, este paso es omitido y considerado innecesario. Supuestamente, los objetos "no necesitan describirse" fundamentandose que el diseño es lo suficientemente explicativo y que los creadores van a permanecer durante todo el proceso de desarrollo, por lo tanto no vale la pena perder el tiempo describiendo los objetos. ¿No suenan estas excusas a holgazanería?

No se deben ignorar estas máximas: 1) el diseñador puede abandonar el proyecto y 2) las personas externas que miren nuestro modelo pueden no entenderlo.

En este sentido, el responsable de base de datos debe velar porque el modelo quede totalmente documentado y que no dependa de las personas que lo crearon, en definitiva que sea lo más auto-explicativo posible.

Alguien puede considerar suficiente que la base de datos quede documentada en un diagrama o en un documento de word, pero ningún esfuerzo es suficiente si podemos hacer un poquito más. Los comentarios sobre objetos son almacenados en el diccionario de datos, y van a ser exportados junto a los objetos cuando estos sean migrados. Sin necesidad de consultar diagramas ni documentos, un programador en apuros puede hacer un DESCRIBE sobre una tabla en SQLPLUS, y saber al instante donde encontrar el dato que busca. Los comentarios ayudan a disipar las dudas más rapido, sobre todo a los que no están empapados en el modelo.

Ahora si, volviendo al tema inicial, ¿cómo relacionamos una tabla con su secuencia? Precisamente, colocando en el comentario de la columna, el nombre de la secuencia que se utiliza:

SQL> COMMENT ON cuentas.id_cuenta IS 'CUENTA_SEQ'

Acabamos de documentar que para la columna id_cuenta de la tabla cuentas, se debe utilizar la secuencia cuenta_seq. Observar que el efecto de esta sentencia es únicamente documental, todavía puede utilizarse la secuencia de la forma que se desee. La ventaja es que ahora podemos consultar la tabla de diccionario dba_col_comments en nuestros scripts.

A modo de nota, mencionamos que tambien se pueden comentar tablas y vistas, con esta sintaxis:

SQL> COMMENT ON cuentas 'Tabla para el registro de cuentas corrientes personales'

Finalmente, el bloque que actualiza las secuencias utilizando los comentarios sobre columnas.

DECLARE
  v_max NUMBER(10) := 0;
  v_desc varchar2(50);
BEGIN

  FOR t IN (SELECT u.table_name,
                   u.column_name,
                   uc.comments secuencia
            FROM   user_tab_columns  u,
                   user_col_comments uc
            WHERE  u.table_name = uc.table_name
            AND    u.column_name = uc.column_name
            AND    u.column_name LIKE 'ID_%')
  LOOP
      EXECUTE IMMEDIATE 'SELECT MAX(' || t.column_name || ') FROM ' ||
t.table_name INTO v_max;

      dbms_output.put_line('Eliminando secuencia '|| t.secuencia||'...');
      v_desc := 'DROP SEQUENCE '|| t.secuencia;
      EXECUTE IMMEDIATE 'DROP SEQUENCE '|| t.secuencia;

      EXECUTE IMMEDIATE 'CREATE SEQUENCE '|| t.secuencia ||' INCREMENT BY
1 START WITH '|| (NVL(v_max,0) + 1);
      dbms_output.put_line('Secuencia '|| t.secuencia||' recreada.');

  END LOOP;
END;

Se requiere el permiso CREATE SEQUENCE por parte del ejecutor de este script.

Más control sobre los LOOPS en 11g

2008-03-28T13:31:00.000-07:00

En PL/SQL podemos salir de un LOOP con la cláusula EXIT. Típicamente la usamos para la condición de salida de un loop:

 BEGIN
   LOOP
       ...  
       EXIT WHEN condición ;
   END LOOP;
   -- el control sigue aquí
   ...
 END;

Ahora la versión 11g agrega un nivel intermedio: CONTINUE. Con esta sentencia, podemos cancelar la iteración del loop actual y pasar a la siguiente, sin abortar el loop. Puede usarse simplemente CONTINUE o CONTINUE WHEN. En este último caso podremos evitar el uso de la sentencia IF para controlar la condición de salida de la iteración.

 BEGIN
   LOOP
     ...
    CONTINUE WHEN condicion;  --vuelve al comienzo del loop

    -- si condicion=true, esta parte no se ejecuta
    ...
   END LOOP;
 END;

Los programadores conservadores se preguntarán para que necesitamos esta sentencia, despues de todo siempre nos hemos arreglado con la cláusula IF-THEN-ELSE de modo de avanzar a la siguiente iteración. Es cierto, no lo necesitamos para hacer cosas que antes no podíamos, pero sí para simplificar el código y facilitar la lectura.

Un ejemplo con CONTINUE

Hay un caso típico donde CONTINUE puede beneficiarnos en la claridad de código para el seguimiento de la lógica: cuando tenemos que hacer múltiples validaciones antes de procesar un registro.

Imaginemos nuestro loop actual (muy simplificado), en donde para cada registro hacemos una serie de validaciones antes de procesarlo. Si una validación falla, debemos pasar al siguiente registro. Para resolver esta lógica, debemos crear una estructura anidada de IFs, ya que debo evitar que se ejecute el resto del loop si una de las validaciones falla.

LOOP
 EXIT WHEN condicion;
 i:=i+1;

 a := valida_1(T(i));
 IF (a) THEN
   b := valida_2(a);
   IF (b) THEN
     ....
     IF (c) THEN

        .....
                procesoRegistro(T(i));
        .....
     END IF;
   ELSE
       ....
   END IF;
 END IF;

END LOOP;

Notemos como los múltiples IF anidados complican el seguimiento del flujo. Muchas veces nos hemos visto complicados al tener que agregar una nueva validación, ya que hay que agregar un nuevo IF al loop, y debo mantener consistentes los correspondientes END IF que deshacen el nivel de anidación.

Ahora veamos como puede verse mejorado con CONTINUE-WHEN:

LOOP
  EXIT WHEN condicion;
  i:=i+1;
  
  a := valida_1(T(i));
  CONTINUE WHEN NOT a;

  b := valida_2(a);
  CONTINUE WHEN NOT b;

  ....

  procesoRegistro(T(i));
  
END LOOP;

La mejoría es evidente, conseguimos disminuir la anidación (a 1) y evitar el uso repetitivo de IF/END-IF, reduciendo la posibilidad de cometer errores de lógica al momento de modificar.

CONTINUE es un recurso que evidentemente solo puede utilizarse en Oracle 11g, sin embargo, siendo conscientes que nuestro código no va a compilar en versiones anteriores, es una práctica que contribuye a la legibilidad del código, con todos los beneficios que eso trae aparejado.

Ver también sobre 11g
11g y sus índices invisibles

NATURAL JOIN antinatural

2008-03-26T11:36:00.000-07:00

El NATURAL JOIN en Oracle tiene un bug. Las versiones 9i y 10g retornan extraños productos cartesianos cuando ejecutamos joins naturales con varias tablas a la vez.

El join natural
Un natural join toma las columnas de igual nombre entre dos tablas y las utiliza para realizar un join. ¿Cuál es el beneficio? No hay que nombrar las columnas en el join.
Si bien puede sonar fantástico el ahorrarnos de escribir las columnas en el JOIN, lo cierto es que el natural join introduce algunos riesgos que veremos más adelante.

Un caso de prueba
Tengo tablas que describen clientes, órdenes, y libros. Deseo realizar una consulta joineandolas a todas.

SQL> desc customers
Name      Type         Nullable Default Comments
--------- ------------ -------- ------- --------
CUSTOMER# NUMBER(4)         
LASTNAME  VARCHAR2(10) Y    
FIRSTNAME VARCHAR2(10) Y    
ADDRESS   VARCHAR2(20) Y


SQL> desc orders
Name Type Nullable Default Comments
--------- --------- -------- ------- --------
ORDER# NUMBER(4)
CUSTOMER# NUMBER(4) Y
ORDERDATE DATE Y


SQL> desc orderitems
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
ORDER# NUMBER(4)
ISBN VARCHAR2(10)
QUANTITY NUMBER(3) Y


SQL> desc books
Name Type Nullable Default Comments
------- ------------ -------- ------- --------
ISBN VARCHAR2(10)
TITLE VARCHAR2(30) Y
PUBDATE DATE Y
COST NUMBER(5,2) Y

A considerar:

Todas las tablas tienen primary key, foreign keys, índices y estadísticas.
El JOIN que deseo hacer puede verse mirando las cuatro tablas desde arriba hacia abajo.
Las columnas con igual color determinan las llaves del JOIN.
La consulta que quiero realizar es --> Cuáles son los libros que ordenó JAKE LUCAS?

Resultado con INNER JOIN

SQL> SELECT isbn, title
  2  FROM ((customers INNER JOIN orders USING (customer#))
  3        INNER JOIN orderitems USING (order#))
  4        INNER JOIN books USING (isbn)
  5  WHERE firstname='JAKE' AND lastname='LUCAS';


ISBN       TITLE
---------- ------------------------------
2491748320 PAINLESS CHILD-REARING
9247381001 HOW TO MANAGE THE MANAGER
2491748320 PAINLESS CHILD-REARING


3 rows selected.

Es el resultado esperado. El cliente JAKE LUCAS ordenó los 3 libros que aparecen. ¿Qué sucede si ejecutamos la misma consulta pero con NATURAL JOIN?

Resultado con NATURAL JOIN

SQL> SELECT isbn, title
  2  FROM customers NATURAL JOIN orders
  3                 NATURAL JOIN orderitems
  4                 NATURAL JOIN books
  5  WHERE firstname='JAKE' AND lastname='LUCAS';


ISBN       TITLE
---------- ------------------------------
1059831198 BODYBUILD IN 10 MINUTES A DAY
0401140733 REVENGE OF MICKEY
0401140733 REVENGE OF MICKEY
0401140733 REVENGE OF MICKEY
0401140733 REVENGE OF MICKEY
8843172113 DATABASE IMPLEMENTATION
8843172113 DATABASE IMPLEMENTATION
8843172113 DATABASE IMPLEMENTATION
3437212490 COOKING WITH MUSHROOMS
3437212490 COOKING WITH MUSHROOMS
3437212490 COOKING WITH MUSHROOMS
3957136468 HOLY GRAIL OF ORACLE
1915762492 HANDCRANKED COMPUTERS
...


64 rows selected.

Retorna 64 filas, algo inquietante. Se estará realizando un producto cartesiano? Veamos el plan de ejecución:

SQL> set autotrace traceonly explain

SQL> SELECT isbn, title
  2  FROM customers NATURAL JOIN orders
  3                 NATURAL JOIN orderitems
  4                 NATURAL JOIN books
  5  WHERE firstname='JAKE' AND lastname='LUCAS';


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=3 Bytes=204)
   1    0   NESTED LOOPS (Cost=9 Card=3 Bytes=204)
   2    1     MERGE JOIN (CARTESIAN) (Cost=9 Card=1 Bytes=57)
   3    2       MERGE JOIN (Cost=6 Card=1 Bytes=22)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS' (TABLE) (Cost=2 Card=1 Bytes=18)
   5    4           INDEX (FULL SCAN) OF 'SYS_C00138991' (INDEX (UNIQUE)) (Cost=1 Card=20)
   6    3         SORT (JOIN) (Cost=4 Card=21 Bytes=84)
   7    6           TABLE ACCESS (FULL) OF 'ORDERS' (TABLE) (Cost=3 Card=21 Bytes=84)
   8    2       BUFFER (SORT) (Cost=6 Card=14 Bytes=490)
   9    8         TABLE ACCESS (FULL) OF 'BOOKS' (TABLE) (Cost=3 Card=14 Bytes=490)
  10    1     INDEX (RANGE SCAN) OF 'IX_BOOKS' (INDEX) (Cost=0 Card=2 Bytes=22)

Efectivamente, en la línea 2 del plan aparece un MERGE JOIN (CARTESIAN) lo cual nos indica que un producto cartesiano se llevó a cabo. Esto no debería suceder, ya que las columnas para vincular las tablas con NATURAL JOIN existen y fueron verificadas en la primera consulta, usando la cláusula USING del INNER JOIN.

Resultado con NATURAL JOIN variando las columnas del SELECT
Si bien lo anterior no era normal, es aún más desconcertante lo que obtenemos si variamos las columnas del select: ¡La cantidad de registros también varía!

SQL> SELECT #order
  2  FROM customers NATURAL JOIN orders
  3                 NATURAL JOIN orderitems
  4                 NATURAL JOIN books
  5  WHERE firstname='JAKE' AND lastname='LUCAS';


42 rows selected.


SQL> SELECT isbn
  2  FROM customers NATURAL JOIN orders
  3                 NATURAL JOIN orderitems
  4                 NATURAL JOIN books
  5  WHERE firstname='JAKE' AND lastname='LUCAS';


64 rows selected.


SQL> SELECT #customer
  2  FROM customers NATURAL JOIN orders
  3                 NATURAL JOIN orderitems
  4                 NATURAL JOIN books
  5  WHERE firstname='JAKE' AND lastname='LUCAS';


896 rows selected.


SQL> SELECT *
  2  FROM customers NATURAL JOIN orders
  3                 NATURAL JOIN orderitems
  4                 NATURAL JOIN books
  5  WHERE firstname='JAKE' AND lastname='LUCAS';


3 rows selected.

Unicamente cuando seleccionamos todas las columnas (*) obtenemos el resultado correcto. ¡Esto debería desalentar a cualquiera a usar NATURAL JOIN!

Bug reconocido
La verdad es, que este bug fue reconocido por Oracle en Metalink, el soporte oficial. Lo que se informa es, que este bug se cree que será eliminado a partir de la versión 11.2. Habrá que esperar que salga el próximo release de 11g? Yo no me molestaría.

Nunca usar NATURAL JOIN
El NATURAL JOIN es una sentencia que está en Oracle desde la versión 9i en un esfuerzo por cumplir con el estándar ANSI SQL. Dentro del mismo SQL, el NATURAL JOIN es uno de esos accesorios inútiles, de los cuales podemos prescindir totalmente (de hecho deberíamos).

Más allá de que no funcione correctamente en este RDBMS, imaginemos que si lo hace y que en Oracle 11g R2 tenemos este bug solucionado: el NATURAL JOIN funciona de maravillas.
Ahora, que sucede con mi procedimiento si el día de mañana agregan una columna de nombre quantity a mi tabla books?
Como orderitems ya tiene una columna quantity, se van a retornar resultados inesperados, ya que la tablas se van a combinar por isbn y quantity (lo cual no tiene sentido). Un cambio leve en las estructuras modifica el comportamiento de mi JOIN, y en este caso no se trata de un bug sino que sería bastante lógico que lo hiciera.

Usar NATURAL JOIN también nos quita claridad en nuestro código: cada vez que lo encontramos en una consulta compleja perdemos tiempo buscando en las estructuras cuáles son las columnas que coinciden.

Lo mejor en todos los casos es usar INNER JOIN o la notación original de Oracle por medio de comparadores en el where. Con ellas el programador explicita cuáles son las columnas que deben entrar en juego, sin dejar nada librado al azar.

En conclusión, usar NATURAL JOIN es totalmente desaconsejado en SQL, ya que nuestras consultas dependen del nombrado de columnas en las tablas relacionadas. Un cambio insignificante como agregar una columna a una tabla, podría hacer dejar de funcionar mi aplicación.

Compilación condicional en 9i

2008-03-24T14:51:00.000-07:00

La compilación condicional permite indicarle al compilador los fragmentos de código que debe compilar en nuestro código PL/SQL, según las condiciones que el programador indique utilizando directivas especiales.
Ahora, si bien la compilación condicional fue presentada con Oracle 10g R2, es también posible contar con esta característica en 9i y 10g R1, gracias a la aplicación de patches sobre la base de datos.

En Oracle 10g y superiores
En Oracle 10g ya viene incorporada a partir de la versión 10.1.0.4 y activada por defecto, por lo tanto no debemos hacer nada para comenzar a utilizarla.

En Oracle 9i
Para contar con ella en 9i debemos primero tener instalado el patch 9.2.0.6 o superiores. Luego debemos setear el parámetro indocumentado _plsql_conditional_compilation=TRUE. Este parámetro es del sistema y no es dinámico, por lo que el DBA deberá incluirlo en el archivo de parámetros y reiniciar la instancia.

En Oracle 8i e inferiores
No es soportada.

Una aplicación de la compilación condicional bastante práctica y recomendable es la de hacer que nuestro código PL/SQL compile en cualquier versión, utilizando los features propios de cada release cuando es posible, todo en el mismo código. Esta buena práctica tiene dos objetivos: el primero es hacer que nuestro paquete sea reutilizable (por ejemplo un paquete de funciones de uso general), y el segundo que nuestro código no genere un impacto al momento de migrar de versión.

Un ejemplo de compilación condicional
En este ejemplo, vamos a realizar un procedimiento para hacer un spool de determinadas cuentas que cumplen con un patrón en su código.
La compilación condicional va a entrar en juego con la siguiente lógica:
-Si la base es 9i, ejecuta SQL con invocaciones a SUBSTR para verificar el patrón
-Si la base es 10g, ejecuta SQL con expresiones regulares ya que es más performante

Primero compilamos la especificación de nuestro paquete, que no tiene nada de especial:

CREATE OR REPLACE PACKAGE pack_cuentas
IS
   PROCEDURE list_cuentas (p_max IN VARCHAR2);
END pack_cuentas;
/

Luego compilamos el body, donde está la diferencia:

CREATE OR REPLACE PACKAGE BODY pack_cuentas
IS
   PROCEDURE spool_cuentas (p_max IN VARCHAR2)
   IS
   BEGIN
   $IF DBMS_DB_VERSION.VER_LE_9
   $THEN
      DECLARE
         v_pattern VARCHAR2(20) := '00[0-'||p_max||']-[0-9]+A';
      BEGIN
         FOR cue IN (SELECT cod_cuenta FROM cuentas
                     WHERE activo='Y' AND REGEXP_LIKE (cod_cuenta,v_pattern))
         LOOP
             dbms_output.put_line(cue.cod_cuenta);
         END LOOP;
      END;
   $ELSE
      FOR cue IN (SELECT cod_cuenta FROM cuentas
                  WHERE activo='Y' AND SUBSTR(cod_cuenta,1,2)='00'
                  AND SUBSTR(cod_cuenta,3,1) IN ('0','1','2','3')
                  AND SUBSTR(cod_cuenta,LENGTH(cod_cuenta),-1)='A')
         LOOP
             dbms_output.put_line(cue.cod_cuenta);
         END LOOP;
   $END
   END spool_cuentas;
END pack_cuentas;
/

Las directivas especiales $IF $THEN $END hacen el truco. Observar que este mismo código se compila en Oracle 9i, donde la función REGEXP_LIKE retornaría un error de compilación, sin embargo el fragmento de código que no corresponde a la versión ni siquiera es analizado por el compilador.
Para poder diferenciar entre las versiones 9i, 9iR1, 9iR2, 10g, 10gR1 y 10gR2 contamos con el paquete DBMS_DB_VERSION, el cual incluye constantes para cada versión, como la que usé en el ejemplo. En el ejemplo usamos la constante ver_le_9 que implica 'menor o igual a 9i', pero también tenemos para indicar menor o igual a un release como por ejemplo ver_le_9_2.

CREATE OR REPLACE package dbms_db_version is
  version constant pls_integer := 10; -- RDBMS version number
  release constant pls_integer := 2; -- RDBMS release number
  ver_le_9_1 constant boolean := FALSE;
  ver_le_9_2 constant boolean := FALSE;
  ver_le_9 constant boolean := FALSE;
  ver_le_10_1 constant boolean := FALSE;
  ver_le_10_2 constant boolean := TRUE;
  ver_le_10 constant boolean := TRUE;
end dbms_db_version;
/

Ahora podremos arreglar nuestro viejo código para que sea más eficiente en las versiones siguientes y que no tengamos que hacer cambios al momento de migrar, en definitiva adelantarnos y minimizar el retrabajo en el futuro.

Cómo verificar archivos y directorios en Shell

2008-03-19T13:47:00.000-07:00

Estos son mis recursos favoritos cuando necesito comandos en Unix para verificar la existencia de carpetas, saber si existen archivos o si están vacíos. Acudo a ellos comúnmente en scripts de respaldo de bases de datos, antes de invocar a RMAN o EXP bajo Linux y después de que se realizan los mismos para verificar los resultados.
También recurro al tamaño en bytes de archivos o de todos los archivos de un directorio, especialmente para generar reportes o logs en nuestros scripts.

Verificar si una carpeta existe

  if [ -d archivo ]
  then
    # Codigo si existe
    echo existe
  else
    # codigo si no existe
    echo no existe
  fi

Verificar si un archivo de salida no esta vacío

  if [ -s archivo ]
  then
    # Codigo si existe y no es vacio
    ...
  else
    # codigo si no existe o es vacio
    ...
  fi

Verificar si un archivo es escribible

  if [ -w archivo ]
  then
    # Codigo si existe y es escribible
    ...
  else
    # codigo si no existe o no es escribible
    ...
  fi

Verificar la integridad de un archivo comparando checksum

  check1=`md5sum archivo_origen | cut -d' ' -f1`

  check2=`md5sum archivo_destino | cut -d' ' -f1`

  if [ $check1 -eq $check2 ]
  then
    # Integridad del archivo correcta
    ...
  else
    # Error de checksum
    ...
  fi

Obtener el tamaño de un archivo en bytes

  x=`du -m archivo | cut -f1`

Sumar la cantidad total de bytes en un directorio

  suma=0
  for arch in *
  do
    if [ ! -d $arch ]
    then
      tamano=`du -b $arch | cut -f1`
      let suma=$suma+$tamano
    fi
  done
  echo $suma

Error ORA-942 while gathering statistics

2008-03-17T08:49:00.000-07:00

Este error puede suceder cuando un usuario no DBA quiere setear AUTOTRACE en su sesión.

SQL> set autotrace traceonly statistics;
Error ORA-942 while gathering statistics

SP2-0611: Error enabling STATISTICS report

Si es un usuario con permisos limitados, entonces es de esperar que no tenga permisos de acceso sobre las vistas que se necesitan para leer las estadísticas generadas por AUTOTRACE. Se requieren permisos explícitos de lectura sobre las vistas v$_session, v_$sesstat y v_$statname.

Para facilitar la implementación de estos permisos, el administrador cuenta con un script para crear un rol especial y así poder extenderlo a los usuarios.

Script:
$ORACLE_HOME/sqlplus/admin/PLUSTRCE.SQL

Cómo hacer para que un usuario pueda usar AUTOTRACE

1. En el sistema operativo, posicionarse sobre el directorio con el script.

    lfer@linux>  cd $ORACLE_HOME/sqlplus/admin

2. Loguearse a SQL*plus con usuario 'AS SYSDBA'

3. Ejecutar en SQL*plus:

    SQL> @PLUSTRCE.SQL

4. Otorgar el rol PLUSTRACE a todos los usuarios que deseen usar AUTOTRACE:

    grant PLUSTRACE to USUARIO;

Ver también:
SQLPlus Users Guide and Reference 10g sobre AUTOTRACE
Error ORA-942 while gathering statistics

Cómo detectar caracteres extraños o no imprimibles

2008-03-12T10:52:00.000-07:00

Muchas veces pasa que en las cargas de datos aparecen caracteres de control como ^M o símbolos ilegibles, y no es deseable que estos queden almacenados entre los datos.
¿Cómo detectar si existen?
Es sencillo si contamos con expresiones regulares en SQL como en 10g, pero en 9i igualmente podemos crear una función que recorra cada uno de los caracteres y verifique si pertenece al juego de caracteres 'raros'.
Mostraré una sencilla implementación compatible con todas las versiones de Oracle, pero puede considerarse utilizar compilación condicional para utilizar expresiones regulares cuando sea posible.
El código:

CREATE OR REPLACE FUNCTION buscar_no_imprimible(v_cadena VARCHAR2) 
RETURN BOOLEAN IS
  v_ret BOOLEAN := FALSE;    
  v_iter NUMBER := 1;    
  v_ascii_min NUMBER := 33;  
  v_ascii_max NUMBER := 126;
BEGIN
  WHILE (v_iter <= LENGTH(v_cadena))
    LOOP
      IF ASCII(SUBSTR(v_cadena,v_iter,1))
      NOT BETWEEN v_ascii_min AND v_ascii_max THEN
        v_ret := TRUE;
      EXIT;   
      END IF;        
      v_iter := v_iter + 1; 
    END LOOP;    
    RETURN(v_ret);
END buscar_no_imprimible;

Esta función booleana retorna TRUE si la cadena contiene un caracter no imprimible, FALSE en caso contrario. Una rápida mirada en una tabla ASCII nos dice que el rango de caracteres válidos se encuentra entre las representaciones decimales 33 y 126.

Si se desean considerar caracteres del ASCII extendido (por ejemplo vocales con acentos), bastará ajustar el procedimiento para incluir el conjunto de caracteres deseado.

Ver también:
Como eliminar acentos del español
Limpiando código DDL
Compilación condicional en 9i

11g y sus Indices Invisibles

2008-03-10T13:40:00.000-07:00

Los índices invisibles, disponibles a partir de Oracle Database 11g, según la documentación oficial, son una interesante alternativa al borrado de índices o seteo como "unusable". Pero, son realmente una alternativa?

Según la información disponible, un índice invisible será ignorado por el optimizador al momento de evaluar los planes de ejecución, excepto que alteremos el valor de sesión (o sistema) OPTIMIZER_USE_INVISIBLE_INDEXES, pero en todo momento el estado del índice será válido. Esto nos brinda múltiples beneficios.

Un índice unusable es una forma sutil de dejar un índice "fuera de combate" temporalmente en TODAS las sesiones. Un índice invisible en cambio es selectivo: podemos verlo (y usarlo) en una sesión mientras en el resto hacer como si no existiera.

Un índice invisible puede volverse visible sin costo alguno, con una simple alteración del mismo: ALTER INDEX mi_indice VISIBLE. Un índice unusable en cambio deberá ser reconstruído, ya que las actualizaciones de datos sobre la tabla no fueron reflejadas mientras el índice estuvo en ese estado. Los índices invisibles SIEMPRE son mantenidos por Oracle con cada sentencia DML.

Con estas afirmaciones, podemos concluir que los índices invisibles llegan para sustituir el estado unusable?
-No! El propósito es completamente diferente. Los índices invisibles evitarán, por ejemplo, que tengamos que 'inutilizar' índices para poder realizar performance tunning, pero en ciertos casos particulares seguiremos necesitando del estado unusable.

Cuando tengamos que realizar una carga masiva de datos, tendremos que alterar el índice como 'unusable' para realizar la carga eficientemente, ya que la invisibilidad de índices de 11g tiene efecto sobre las consultas SELECT y no sobre las demás sentencias DML. El índice invisible continúa aportando overhead en cada modificación de datos.

La clave de este novedoso estado está en el testeo de consultas. Por ejemplo, podremos probar cómo se comporta un SELECT con determinado índice, sin que el ambiente en el cual lo estoy probando se entere. Creo un índice invisible, seteo el valor de OPTIMIZER_USE_INVISIBLE_INDEXES como true, y testeo mi aplicación sin efectos colaterales.

Otro beneficio, razonando en forma opuesta: ¿Cómo funcionaría mi aplicación si determinado índice no existiera?
Alteramos el índice como 'invisible', seteamos en nuestra sesión OPTIMIZER_USE_INVISIBLE_INDEXES = false, y probamos. Los demás usuarios mientras tanto, continuarán utilizándolo normalmente.

Los índices invisibles son un valor agregado a nuestra forma de trabajo, ya que nos proveen transparencia sobre el ambiente. Nos permiten realizar nuestras pruebas tranquilos de que nadie está siendo afectado.

Ver también:
Oracle Database Administrator's Guide 11g sobre índices invisibles
Indices condicionales
Mis índices no funcionan!

SOUNDEX en español

2008-03-08T13:19:00.000-08:00

SOUNDEX, función que ha estado presente en Oracle desde tiempos remotos, es un clásico algoritmo fonético utilizado para indexar nombres propios, asociando palabras que suenen igual a pesar de ocasionales diferencias en su escritura.

El algoritmo SOUNDEX fue concebido teniendo en cuenta la fonética del idioma inglés, por lo que hace inadecuado su uso en el castellano. Realizando adaptaciones propias del idioma español, implementé una versión en PL/SQL llamada SOUNDESP, la cual respeta la esencia del algoritmo original.

Los pasos básicos son:

Retener la primera letra de la cadena. Tener en cuenta las letras dobles como CH y LL.
Remover todas las ocurrencias de las letras siguientes a partir de la segunda posición: a, e, i, o, u, h, w, y (cuando suena como vocal i )
Asignar números a las siguientes letras (luego de la primera):
- b, f, p, v = 1
- c, g, j, k, q, s, x, z = 2
- d, t = 3
- l = 4
- m, n = 5
- r = 6
- ll, y, ch = 7
Si hay números consecutivos, dejar solamente uno en la serie.
Retornar los cuatro primeros caracteres, si son menos de cuatro completar con ceros.

SOUNDESP es un proyecto abierto y es bienvenido cualquier comentario para mejorar su implementación.

Descargar Código Probar SOUNDESP

NOTA: Para descargar el código correctamente, haga click derecho y elija Guardar destino

Ver también:
Wikipedia: Soundex

Insertar o actualizar un único registro con MERGE

2008-02-27T11:36:00.000-08:00

La versión 9i de Oracle Database traía entre sus SQL features, la incorporación de una novedosa sentencia: MERGE (ahora parte del estándar ANSI SQL). Hasta ese momento, cuando se quería insertar/actualizar datos en una tabla no vacía, había que revisar si el registro ya existía para aplicar INSERT o UPDATE, manejándolo proceduralmente con algún lenguaje, por ejemplo PL/SQL. Con MERGE, podemos tener esta lógica en una única sentencia SQL, simplificando el código y haciendo la tarea más performante.

La sintáxis de MERGE está pensada para que la fuente de datos que se va a insertar sea una tabla o una consulta, de esta manera:

MERGE INTO [tabla_destino]
USING ([tabla o vista o consulta])
ON ([condición de existencia de registro])
WHEN MATCHED THEN [sentencia de actualización]
WHEN NOT MATCHED THEN [sentencia de inserción];

Hay veces en que los datos a incorporar no provienen de ninguna tabla, sino que se trata de un único registro enviado por una aplicación (por ejemplo a través de parámetros). Una forma de poder aprovechar la sentencia MERGE en estos casos, es construir una consulta sobre la tabla DUAL que convenientemente contiene una única fila.

Partiendo de una tabla tabla_destino, donde queremos hacer la actualización de datos:
CREATE TABLE tabla_destino (a int, b varchar2(10));

Si:

Nuestro registro a insertar es {param_a, param_b}
La condición de que el registro exista es la columna a
Lo que queremos actualizar en caso que el registro exista es la columna b

...entonces usamos MERGE así:

1 MERGE INTO tabla_destino td
2 USING (SELECT param_a a FROM dual) d
3 ON (td.a = d.a)
4 WHEN MATCHED THEN UPDATE SET td.b = param_b
5 WHEN NOT MATCHED THEN INSERT (td.a, td.b) VALUES (param_a, param_b);

En la línea 2 (USING) seleccionamos el registro que participa en la condición que el registro exista.
En la línea 3 especificamos cual es la condición que se considera que el registro ya existe
Las líneas 4 y 5 actualizan o insertan el registro.

Una forma simplificada y equivalente es la siguiente:

MERGE INTO tabla_destino td
USING dual d
ON (td.a = param_a)
WHEN MATCHED THEN UPDATE SET td.b = param_b
WHEN NOT MATCHED THEN INSERT (td.a, td.b) VALUES (param_a, param_b);

Se recomienda el uso de MERGE ya que es una única sentencia SQL y naturalmente está optimizada por el motor. Hay excepciones como es el caso de cargas masivas de datos con millones de registros donde la performance de MERGE se degrada. Allí existen otras alternativas más eficientes.

Ver también:
Oracle 9i SQL Reference para MERGE
Oracle 10g SQL Reference para MERGE
Update condicional

Patches críticos en enero para todos los productos Oracle

2008-01-29T04:39:00.000-08:00

Acaban de ser liberados patches críticos que afectan a los principales productos de Oracle: Oracle Database (9i, 10g, 11g), Application Server (9, 10g R2 y R3), Collaboration Suite 10g, E-Bussiness Suite (11i y 12) y People Soft PeopleTools.

Se recomienda fuertemente que estos patches sean aplicados, ya que reducen potencialmente las posibilidades de ataques recientemente descubiertos.

En la Base de Datos se descubrieron 8 puntos vulnerables en los siguientes módulos: XML DB, Advanced Queuing, Oracle Spatial, Ultra-Search y en el propio CORE de 11g.
En el Application Server los fixes afectan al J-Initiator, BPEL, Forms y JDeveloper 10g, Internet Directory.
Como parte de la Collaboration Suite, Oracle Ultra-Search 10.1.2 es el único componente afectado.

Vea también:
Oracle Critical Patch Update Advisory - January 2008
Oracle Critical Patch Update Advisory - April 2008

Sesiones de Búsqueda

2008-01-28T12:33:00.000-08:00

Ya lo habiamos comentado hace algún tiempo, la consola de administración de Oracle 10g en español tiene algunos errores de traducción. Este es de los más feos que he visto.

Si en algún momento buscan el listado de sesiones de usuario en la instancia, no esperen hallarla con un nombre coherente.

Les doy una pista, en la version en inglés se llega a a través del link Session Search...

Espera encontrar Búsqueda de Sesiones? Mal!, no realice la traducción correctamente. Hallará en su lugar Sesiones de Búsqueda. Una preciosura. Ahora ejecute una búqueda en blanco para obtener el listado.

Cadena de conexión con jdbc thin

2007-12-26T06:06:00.000-08:00

Error al testear la conexión JDBC en JDeveloper 10g ->

Test Failed: La dirección URL de Oracle especificada no es válida

Un método que puede solucionar este frustrante mensaje es usar la cadena de conexión que incluye la descripción del servicio (tal como la colocaríamos en el archivo tnsnames.ora)

jdbc:oracle:thin:@(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host)(PORT = puerto))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = base_de_datos)
)
)

Si todavía no funciona es recomendable probar la conexión a través de un cliente Oracle, configurando el archivo tnsnames.ora. Al momento que esta conexión logre conectarnos, solo resta reemplazar en la cadena de conexión la misma descripción del servicio del tnsnames (arriba en azul).

Indices condicionales

2007-11-11T17:18:00.000-08:00

Hay tablas para las cuales desearíamos hacer valer condiciones de unicidad pero dadas ciertas condiciones que dependan de alguna otra columna de control.

Un ejemplo típico se da cuando mantenemos registros en una tabla con borrado lógico, y queremos que exista un único registro activo (pudiendo aparecer el registro inactivo más de una vez). Digamos que tenemos la tabla t, con dos columnas fundamentales: un id y un indicador de registro activo Y/N. Queremos que el Id sea único solamente cuando Activo es 'Y'.

Muchas veces, mucha gente se enfrenta a este problema y lo resuelve creando una tabla auxiliar para hacer valer la condición de unicidad, almacenando en ella las tuplas que no desea repetir para lograr dicho control.
Todo el problema que implica crear (y mantener) estructuras adicionales y la complejidad de manipulación, puede evitarse utilizando lo que siempre estuvo al alcance: el índice de función y la sentencia CASE.

SQL> CREATE TABLE t (sec int primary key, id VARCHAR2(100), activo VARCHAR2(1));

Table created.

SQL> CREATE UNIQUE INDEX ix_t ON t
2 (CASE WHEN activo='Y' THEN id ELSE NULL END);

Index created.

SQL> insert into t values (1, 'COD1', 'Y');

1 row created.

SQL> insert into t values (2, 'COD1', 'N');

1 row created.

SQL> insert into t values (3, 'COD1', 'N');

1 row created.

SQL> insert into t values (4, 'COD1', 'Y');
insert into t values (4, 'COD1', 'Y')
*
ERROR at line 1:
ORA-00001: unique constraint (LFER.IX_T) violated

Los índices condicionales no son un feature especial de Oracle, sino que se construyen utilizando los mismos índices de función que usualmente definimos para todas las filas de una tabla. En este caso, la función CASE permite que el índice único se construya sobre un subconjunto de registros, aquellos en el que el campo activo sea igual a 'Y'.

Ver también:
11g y sus índices invisibles

Variables en SQL*Plus (parte 3)

2007-11-07T16:18:00.000-08:00

Anteriormente repasando variables en sqlplus, vimos las variables de sustitución y las de usuario. Finalmente llegamos al tercer tipo de variables, las variables bind (o de ligadura).

VARIABLES BIND

Estas variables tienen la particularidad de poder ser definidas en sqlplus, usadas y/o asignadas dentro de nuestros programas PL/SQL y luego leídas una vez terminado el programa. Son convenientes cuando queremos tener comunicación entre scripts de sqlplus y bloques de PL/SQL.

Las variables bind se definen así:

SQL> VARIABLE bvar NUMBER;

Pueden utilizarse también los tipos: CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, BINARY_FLOAT y BINARY_DOUBLE

Si queremos asignar estas variables antes de llamar a nuestros bloques de PL/SQL, debemos hacerlo desde otro bloque BEGIN END o simplemente usando la sentencia EXEC:

SQL> exec :bvar := 1;

PL/SQL procedure successfully completed.

Las variables dentro de PL/SQL se referencian con dos puntos seguidos del nombre de la misma.
Ahora ya podemos utilizar las variables dentro de nuestro programa e imprimir el resultado:

SQL> BEGIN
   2   IF :bvar = 1 THEN
   3     :bvar := 0;
   4   ELSE
   5     :bvar := 1;
   6   END IF;
   7 END;
   8 /

PL/SQL procedure successfully completed.

SQL> print bvar

BVAR
----------
0

SQL> /

PL/SQL procedure successfully completed.

SQL> print bvar

BVAR
----------
1

Y con esto culminamos el repaso de variables en sql*plus. Finalmente como complemento, veremos brevemente los cursores referenciales los cuales son también útiles para recuperar un conjunto de tuplas de nuestros SELECTs, desde los programas PL/SQL hacia fuera, y así tener más flexibilidad.

REFCURSORS

Se declaran como una variable bind, pero colocamos REFCURSOR en el tipo:

SQL> VARIABLE rcur REFCURSOR;

SQL> begin
2 OPEN :rcur FOR select segment_name, segment_type from user_segments order by segment_name;
3 end;
4 /

PL/SQL procedure successfully completed.

A diferencia de los cursores tradicionales que se abren y requieren del uso de fetch, al abrir estos cursores bind ya son ejecutados automáticamente y sus resultados almacenados en memoria. Solo resta imprimir las filas de la consulta.

SQL> print rcur

SEGMENT_NAME         SEGMENT_TYPE
-------------------- ------------------
ACUMULADO_CONSUMO    TABLE
BIG_TABLE            TABLE
BIG_TABLE_PK         INDEX
CLIENTE              TABLE
COUNTRY_C_ID_PK      INDEX
CUENTA               TABLE
INVENTARIO           TABLE
IX_T                 INDEX
MUNICIPIO            TABLE
NDX_ACUMCON          INDEX
NDX_ACUMRENT         INDEX
NDX_ACUMTERC         INDEX
NDX_ACUMVAR          INDEX

13 rows selected.

Ver también:
Variables en SQLPlus (parte 1) - Sobre variables de usuario
Variables en SQLPlus (parte 2) - Sobre variables de sustitución

Acentos del idioma español

2007-10-25T20:20:00.000-07:00

¿Cómo comparamos palabras que pueden contener acentos?

Un problema frecuente cuando manipulamos datos es la aparición de acentos propios del idioma español correctamente escrito. Como la mayoría de los juegos de caracteres están basados en Unicode, práctimente podemos encontrar símbolos de casi cualquier lengua.

Con nuestro idioma español seteado (parámetro NLS_LANGUAGE), podemos comprobar que 'Á' es mayor que 'á':
SQL> select greatest('Á','á') from dual;

GR
--
Á

A su vez 'á' es mayor que 'a':
SQL> select greatest('á','a') from dual;

GR
--
á

Luego 'a' es mayor que 'A'
SQL> select greatest('A','a') from dual;

G
-
a

Pasando en limpio tenemos que: Á > á > a > A

Para evitar estos problemas de orden de los caracteres con acentos (incluímos la ñ y Ñ) dentro de la plantilla de caracteres, nos conviene traducir estos caracteres a un solo formato en mayúscula y desasentuado (despues de todo para ordenar no nos importan estas diferencias).

La función TRANSLATE viene como anillo al dedo para esta necesaria conversión de caracteres. La sintáxis básica es: TRANSLATE(cadena,caracteres_a_buscar,caracteres_a_convertir)

Lo aplicaremos así:
UPPER(TRANSLATE(cadena,'ÁÉÍÓÚáéíóú','AEIOUAEIOU'))

SQL> SELECT UPPER(TRANSLATE('HABÍAn relámpagos','ÁÉÍÓÚáéíóú','AEIOUAEIOU'
)) TEXTO FROM DUAL;

TEXTO
-----------------
HABIAN RELAMPAGOS

Es aconsejable tener un paquete con una función que reciba una cadena, aplique estas funciones de conversión y retorne la cadena desacentuada. Evitará reescribir código y además tendremos la función cacheada en memoria para las siguientes invocaciones.

UPPER/TRANSLATE podrá usarse en SQL para comparar cadenas, con el cuidado necesario para evitar anular índices por el uso de funciones. Es necesario evaluar el alterar los datos almacenados de forma de eliminar la acentuación, o crear índices de función que estén definidos con la funcion de conversión. Dependerá de cada caso particular que es lo más conveniente.

Ver también:
Como detectar caracteres extraños o no imprimibles

Update condicional

2007-10-23T20:25:00.000-07:00

¿Cómo se hace un UPDATE condicional?
Digamos que tengo una tabla t con 3 campos a, b y c. Quiero que si a=1 entonces actualizo b con algún valor, y si a=2 actualizo c.

Alguien de mi equipo se encontró con este dilema y lo resolvió de forma muy práctica: dos updates, uno para actualizar aquellos valores con a=1 y otro para actualizar los a=2:

1 BEGIN
2    UPDATE t
3   SET b=valor
4   WHERE a=1;
5
6   UPDATE t
7   SET c=valor
8   WHERE a=2;
9 END;

Otro programador del equipo refutó: -¿Para qué ejecutar dos sentencias update si en su lugar puedes hacer una?

1 BEGIN
2   UPDATE t
3   SET B=DECODE(A,1,valor,2,B),
4   C=DECODE(A,1,C,2,valor);
5 END;

¿Quién de los dos tiene razón?

...depende.

Dado que la performance parece ser lo que está en juego, la primera pregunta que cabe es:

¿Tiene la tabla t índice por A?

Si la tabla no tiene índice, entonces debemos recorrerla por entero. En el primer caso recorremos la tabla dos veces, mientras que en el segundo caso lo hacemos solamente una! Por lo tanto la primera solución en este escenario es el doble más lenta que la segunda. Al notar que la tabla no tiene índices, el Programador 2 asiente la cabeza con orgullo.

Inmediatamente el Programador 1 reacciona: -Pongámosle un índice a t entonces!
(el ávido Programador 1 sabe que la segunda consulta no puede sacar provecho de un índice por lo que su victoria parece asegurada)

Y aquí es donde cabe la segunda pregunta:

¿Cuál es la cardinalidad de la columna A?

Sería de gran ayuda saber si 1 y 2 son la mayoría de los valores que hay en la columna A, porque en ese caso, el optimizador va a continuar prefiriendo recorrer toda la tabla! Si la cardinalidad de los valores 1 y 2 son un porcentaje muy bajo del conjunto de valores distintos, entonces recién ahí los índices van a ser utilizados.
Cuando el índice es utilizado por la escasa presencia de valores 1 y 2, los tiempos de la primer solución es abismalmente inferior.

Conclusión:

La solución 1 es mejor si existen índice sobre la columna A y su cardinalidad es alta (hay pocos 1 y 2 en A).
La solución 2 es mejor si no existen índices o existe y la cardinalidad de A es baja para valores 1 y 2 (significa que 1 y 2 ocurren mucho en A).

Ver también:
Mis índices no funcionan!
Indices condicionales

tic... toc

2007-09-26T20:56:00.000-07:00

¿Cómo medir la performance de un código PL/SQL?

Muchas veces nos encontramos con procedimientos lentos de muchos pasos, con llamadas a funciones, DML, loops con cursores, etc, que queremos revisar. Como SYSDATE nos da la cantidad de segundos como mínima fracción de tiempo, no nos es de mucha ayuda consultarlo entre líneas.

Afortunadamente, contamos con la función get_time del paquete dbms_utility para obtener la cantidad de centésimas de segundo que transcurren entre dos instantes de tiempo.
A sabiendas de eso, debemos incluir los statements que invoquen a dichas funciones y desplieguen los tiempos por la salida, en un archivo o en una tabla de logs.

Con el fin de evitar duplicar código y además aprovechar las bondades del cacheado de paquetes en el área de memoria compartida, creé un pequeño paquete exclusivo para ese fin. El mismo tiene dos procedimientos: tic y toc. Cuando quiero comenzar a medir tiempo, invoco a tic (con un mensaje para registro). Cuando quiero medir el tiempo transcurrido, uso toc. Así de simple.

CREATE OR REPLACE PACKAGE crono AS
  v_tic PLS_INTEGER;
  evento VARCHAR2(100);
  PROCEDURE tic(p_evento IN VARCHAR2);
  PROCEDURE toc;
END crono;
/

CREATE OR REPLACE PACKAGE BODY crono AS
PROCEDURE tic(p_evento IN VARCHAR2) IS
BEGIN
   v_tic := DBMS_UTILITY.GET_TIME;
   evento := p_evento;
   DBMS_OUTPUT.PUT_LINE('TIC: '||p_evento);
END tic;

PROCEDURE toc IS
   df pls_integer := CAST(DBMS_UTILITY.GET_TIME AS PLS_INTEGER)-v_tic;
BEGIN
   DBMS_OUTPUT.PUT_LINE('TOC: '||evento||', T(1/100s): '||df);
END toc;
END crono;
/

El uso del paquete en un procedimiento cualquiera puede ser:

SQL> begin
2     crono.tic('creo tabla');
3     execute immediate 'CREATE TABLE testtable as select * from user_tables';
4     crono.toc;
5     crono.tic('elimino registros');
6     execute immediate 'delete from testtable';
7     crono.toc;
8     crono.tic('hago commit');
9     commit;
10     crono.toc;
11     crono.tic('borro tabla');
12     execute immediate 'drop table testtable';
13     crono.toc;
14 end;
15 /
TIC: creo tabla
TOC: creo tabla, T(1/100s): 34
TIC: elimino registros
TOC: elimino registros, T(1/100s): 5
TIC: hago commit
TOC: hago commit, T(1/100s): 0
TIC: borro tabla
TOC: borro tabla, T(1/100s): 50

Procedimiento PL/SQL terminado correctamente.

Aunque esta experiencia es meramente demostrativa, notar que no pasó un segundo en toda la operación, y sin embargo estamos sacando medidas ilustrativas en cada paso.

Es una solución práctica y disponible para todos los usuarios de la base de datos, solo tengo que dar permisos de ejecución a los desarrolladores o a public. Un sinónimo puede simplificar aún más las llamadas a tic y a toc.

Pese al mínimo costo de salida por pantalla, una desventaja es que el procedimiento tiene que terminar exitosamente para obtener las medidas (dbms_output despliega su salida al final del proceso). Una alternativa para lograr la medición "on the run", es implementar la salida a archivo (utilizando el paquete utl_file), o insertando en una tabla (con una transacción autónoma para no hacer commit sobre la actual).
Estos cambios pueden ser transparentes al usuario, ya que solo hará falta recompilar el body del paquete. Las ventajas están a la vista.

Ver también:
Uso del paquete utl_file

El usuario Scott no existe

2007-09-22T08:39:00.000-07:00

SQL> conn scott/tiger
ERROR:
ORA-01017: invalid username/password; logon denied

Dependiendo de las opciones de instalación o de los schemas que vengan instalados en algunas versiones del RDBMS (como Express por ejemplo), el usuario más famoso de Oracle, Scott, puede no estar presente. Citando el caso de Oracle XE, el único schema de ejemplo que viene instalado por defecto es HR.

Pero no hay problema, lo único que tenemos que hacer para instalar el schema Scott con sus clásicas tablas EMP, DEPT, BONUS y SALGRADE, es correr (en SQL*Plus) el script utlsampl.sql que está en el directorio del servidor $ORACLE_HOME/RDBMS/ADMIN.

Como se requiere de ciertos permisos especiales que un usuario corriente no tiene, deberá hacerlo el DBA.