miércoles, 31 de diciembre de 2008

Indices de función para mejorar un LIKE

Los índices de función son útiles para poder indizar condiciones aplicadas sobre columnas y así acceder rapidamente a los registros.
Un ejemplo típico es el uso de UPPER(columna) o el abuso de LIKE:
SELECT *
FROM my_big_table
WHERE buffer LIKE 'FF-%';
En este caso, una barrida por toda la tabla (FULL SCAN TABLE) es inevitable.

El uso de LIKE compromete el uso de índices resultando en baja peformance.
Usando la función substr podemos evitar el LIKE en este caso, ya que tenemos siempre le mismo prefijo 'FF-'. Para mejorar la ejecución, podemos crear un índice de función como el que sigue:
CREATE INDEX ix_my_big_table_01 ON my_big_table(substr(buffer,1,4));
La consulta quedaría entonces de esta forma:
SELECT *
FROM my_big_table
WHERE substr(buffer,1,4) = 'FF-';
Para que el índice sea tenido en cuenta por Oracle, necesitamos contar con el privilegio de sistema QUERY REWRITE y dos parámetros de la base seteados: query_rewrite_enabled=TRUE y query_rewrite_integrity=TRUSTED

Ver también:
Mis índices no funcionan!
Indices condicionales
11g y sus índices invisibles

sábado, 29 de noviembre de 2008

Limpieza de archivos en Unix

Para eliminar archivos de log inutiles y evitar que nuestro file system se llene con el paso del tiempo, podemos usar el comando find y programarlo para executar diariamente en la crontab de root:
find  -name "*.dmp.log" -mtime +2 -exec rm -f {} \;
En el ejemplo estamos eliminando archivos temporales de log de mas de 2 días atras.

Asimismo, la misma idea puede usarse para compactar archivos con cierta antiguedad:
find  -name "*.dmp"  -exec gzip {} \;

Vea también:
Cómo obtener la fecha de ayer en Unix
Comandos útiles para Unix Shell
Cómo verificar archivos y directorios en Shell

martes, 28 de octubre de 2008

Cómo saber si Pro*C está instalado en Oracle

Pro*C es un producto que por ser pre-compilador no viene incluído en el sistema operativo, sino en la propia distruibución de Oracle. Es un componente opcional que se puede instalar utilizando el Oracle Universal Installer bajo el check [Oracle 9i Develpment Kit]/[Oracle Programmer]/Pro*C.
Generalmente utilizamos Pro*C en conjunción con algún compilador C nativo como gcc, el cual a diferencia del primero, es incluído por el sistema operativo.

Pro*C, cuando instalado en nuestra base de datos Oracle, es un ejecutable:

$ORACLE_HOME/bin/proc

Si el ejecutable no se encuentra en esa ubicación, verificar si efectivamente el componente no fue incluído en la instalación.

Ejecutar el OUI y confirmar si entre las opciones instaladas el componente no aparece listado.

Cuando operamos bases de datos remotamente, a veces no tenemos ambiente gráfico, y por razones de conectividad, ni siquiera nos es posible exportar el OUI hacia nuestra consola. Deberemos entonces leer el log de instalación de la base de datos (disponible en $ORACLE_BASE/oraInventory/logs), y buscar si una entrada como la siguiente es listada:

Pro*C/C++ 9.2.0.4

En caso de no encontrarlo, entonces deberemos instalarlo usando el OUI.

lunes, 27 de octubre de 2008

Application Express y su supervivencia

Application Express, también conocida como APEX o HTMLDB, es la herramienta de desarrollo web de Oracle, que viene incluída en Oracle XE lista para usar. A la fecha de hoy, la versión actual (3.1.2) puede descargarse gratuitamente e instalarse en todas las versiones de la base de datos a partir de 9i R2 (incluída la versión XE, también gratuita). Aplication Express es un recurso gratuito tanto para desarrollar como para implantar, inclusive en ambientes de producción, no require de licencias ni posee limitaciones de distribución.

Desde 2004 a la fecha, la tecnología que ofrece APEX continúa siendo avanzada y cada vez más rica en recursos (AJAX, Web Services, CSS, Javascript, XML, etc) y es realmente rápido lograr resultados profesionales, pudiendo optar por diversas opciones de arquitectura y no teniendo que pagar absolutamente nada por ello.
¿Porqué entonces APEX continúa en las sombras, no logrando transformarse en una verdadera opción para los desarrolladores open source?

Pese a los esfuerzos de Oracle por disponibilizar gratuitamente esta tecnología, APEX nació con limitantes importantes que impiden su crecimiento en popularidad: no abundan los servicios de hosting gratuito de workspaces de Aplication Express (como sí los hay para otras tecnologías de libre uso). El libre licenciamiento de Oracle Aplication Express no es debidamente acompañado con los permisos de utilización del servidor de aplicaciones y base de datos requerida para estos fines. Para las empresas, no es redituable ofrecer APEX, ya que deben soportar las licencias de una de las bases de datos más costosas del mercado. APEX termina confinado a la privacidad de las intranets corporativas y los computadores portátiles.

¿No sería una buena movida estratégica para Oracle crear un nuevo tipo de licenciamiento que permita implantar bases de datos con mayor capacidad para ofrecer exclusivamente servicios de hosting APEX? ¿Porqué no difundir un producto, cuyo desarrollo le significa una cara inversión (300,000 líneas de código) destinada a satisfacer las necesidades de unos pocos? Con una comunidad tan reducida, el crecimiento de APEX es bastante más lento de lo que podría ser, dejándolo en amplia desventaja respecto a otros lenguajes de desarrollo web. Sin ir más lejos, fue así que Forms perdió vigencia, y cada vez más de estas aplicaciones emigran hacia otros lenguajes web, aún perdiendo parte de la seguridad y consistencia que Forms ofrece.

No se ven planes para intentar captar un público más amplio, sino de terminar de cerrar sus filas reclutando a los desarrolladores de Forms: la próxima versión de Aplication Express incluirá un convertidor de Forms a APEX, con lo que se facilitará la migración de estas aplicaciones a lo que será su sustituto inevitable.

Vea también:
Descargue la última versión de APEX
Cómo saber si tiene APEX instalado

viernes, 19 de septiembre de 2008

Generando planillas Excel con SQL*plus

Una de los comandos más interesantes de SQL*plus para formatear salida, es MARKUP HTML. Con el, podemos generar código HTML listo para mostrar en cualquier navegador.

Sintaxis básica: SET MARKUP HTML ON

Ejemplo:

SQL> set markup html on
SQL> spool tablas.html
SQL> select table_name, tablespace_name from user_tables;
SQL> spool off


Visto en el navegador, nuestra consulta de dos columnas es formateada como una tabla HTML.





Lista para Excel


Lo interesante de Excel es que permite ver páginas html como si fuesen planillas. Cuando abrimos el spool generado por SQL*plus, tenemos una planilla Excel pronta para usar.

Como el formato HTML generalmente es abierto por otras aplicaciones, podemos generar el spool como .xlt, y de esta forma abriremos el archivo en Excel con solamente dos clicks.


viernes, 1 de agosto de 2008

¿Cómo generar una fecha aleatoria con PL/SQL?

El paquete dbms_random implementa algunas funciones aleatorias de utilidad para generar números y strings aleatorios, pero no para fechas. Para generar fechas al azar, podemos sumar o restar una cantidad X de días a sysdate. El único detalle respecta al período de fechas que queremos obtener, debemos ajustar adecuadamente el número de días que se van a sumar/restar a la fecha actual.
CREATE TABLE fechas_aleatorias (fecha DATE);

DECLARE
sd NUMBER;
dias NUMBER := 5000; --max de dias que se suman/restan a sysdate
dia DATE;
BEGIN
-- inicializacion del seed
SELECT to_char(systimestamp,'FF') INTO sd FROM dual;
dbms_random.initialize(sd);

FOR i in 1..10000
LOOP
dia := TRUNC(sysdate) + dbms_random.value(-dias,dias);
INSERT INTO fechas_aleatorias VALUES (dia);
END LOOP;
END;

sábado, 26 de julio de 2008

ORA-01440: column to be modified must be empty to decrease precision or scale

ALTER TABLE t1 MODIFY (nr_pct NUMBER(5,2))
ORA-01440: column to be modified must be empty to decrease precision or scale
¿Cómo disminuir la longitud de una columna con datos y evitar el error?

Para este ejemplo, vamos a agregar una dificultad extra: la columna que queremos alterar es NOT NULL.

Presento dos formas de hacer esto con SQL*Plus:


FORMA 1: VACIANDO Y ALTERANDO


Respaldar la columna
CREATE TABLE t1_backup AS SELECT id, nr_pct FROM t1;
Setear la columna como NULL
ALTER TABLE t1 MODIFY (nr_pct NULL);
Vaciar la columna
UPDATE t1 SET nr_pct=NULL;
Ajustar el tamaño deseado de la columna
ALTER TABLE t1 MODIFY (nr_pct NUMBER(5,2));
Cargar los datos a la columna
UPDATE t1 SET nr_pct=(SELECT nr_pct FROM t1_backup WHERE t1_backup.id=t1.id);
Alterar la columna como NOT NULL nuevamente
ALTER TABLE t1 MODIFY (nr_pct NOT NULL);

Ventajas: Se preserva el orden de las columnas dentro de la tabla
Desventajas: Poco performante para tablas muy grandes (millones de datos), requiere crear un índice en la tabla t1_backup para mejorar la performance


FORMA 2: AGREGANDO COLUMNA EXTRA

Agregar una columna a la tabla con el tamaño deseado
ALTER TABLE t1 ADD (nr_pct_2 NUMBER(5,2) NOT NULL);
Cargar la nueva columna con los datos
UPDATE t1 SET nr_pct_2=nr_pct;
Eliminar la columna original
ALTER TABLE t1 DROP COLUMN nr_pct;
Renombrar la nueva columna al nombre de la original
ALTER TABLE t1 RENAME COLUMN nr_pct_2 TO nr_pct;

Ventajas: Más performante que la forma 1 para tablas grandes. Más fácil de realizar.
Desventajas: Sólo aplicable a 9i o superiores (8i no posibilita el renombrado de columnas).
La columna alterada quedará al final de la tabla si hacemos un DESC.

martes, 8 de julio de 2008

Cómo obtener la fecha de ayer en Unix

Esta es una tarea sencilla en Linux. Para obtener el día anterior simplemente usamos la función date:
date --date='1 day ago'
Esta funcionalidad tiene varias formas que flexibilizan la tarea de obtener una fecha relativa del pasado o futuro. Sólo basta dar una mirada a la documentación del man.

El problema ocurre si usamos Sun Solaris: este sistema operativo no tiene una modalidad similar para date. Es frustrante para un programador casual como yo, que algo tan sencillo no esté implementado en el sistema operativo.
He visto algunos foros en la web donde se pueden encontrar diversos Shell scripts para calcular el día anterior dependiendo de si el mes tiene 30 o 31 dias, si es febrero, si es año bisiesto... y la verdad que la idea de tanta complejidad para algo tan simple no me satisface.

Una solución directa y fácil de obtener el dia anterior en Solaris es la siguiente: agendar en el programa crontab (o scheduler unix de preferencia) a las 23:59, un simple script que escriba la hora actual en un archivo:
#/bin/ksh
date > ayer.txt

Listo!
Ahora nuestros scripts pueden acceder en forma simple a la fecha de ayer.

Vea también:
Limpieza de archivos en Unix
Comandos útiles para Unix Shell
Cómo verificar archivos y directorios en Shell

miércoles, 2 de julio de 2008

Obtener la versión de la base desde PL/SQL

Si queremos obtener cual es la versión de la base de datos para hacer una u otra cosa, podemos utilizar el paquete dbms_utility y el procedimiento db_version. El mismo nos retorna un string del tipo 9.2.0.4, el cual podemos consultar para programar a conveniencia.
  dbms_utility.db_version(v_version, v_compatible);
Una observación: cuidado con utilizar comandos que no existan en otras versiones, para eso está la compilación condicional introducida en Oracle 9i R2. Esta es una simple función que nos retorna la versión de la base actual que nos puede servir por ejemplo para reconstruir nombres de archivos que dependan de la versión utilizada.

El siguiente bloque PL/SQL es un ejemplo de como puede utilizarse db_version para condicionar la programación.

DECLARE
v_version varchar2 (32);
v_compatible varchar2 (32);
BEGIN
dbms_utility.db_version(v_version, v_compatible);

if substr(v_version,1,2) = '10' then
...
elsif substr(v_version,1,1) = '9' then
...
elsif substr(v_version,1,3) = '8.1' then
...
elsif substr(v_version,1,3) = '8.0' then
...
elsif substr(v_version,1,1) = '7' then
...
end if;

...

END;
Ver también:
Compilación condicional en 9i

jueves, 12 de junio de 2008

SP2-1503 Unable to initialize Oracle call interface

SP2-1503: No es posible inicializar la interfase de llamada Oracle
SP2-0152: ORACLE tal vez no esté funcionando adecuadamente

Acabamos de instalar nuestro cliente Oracle en Windows Vista, configuramos el tnsnames.ora y al querer conectarnos a la base de datos obtenemos el error.

Una posible explicación, es que acabamos de instalar un cliente Oracle para Windows XP, y este no es compatible con Vista. Es necesario descargar e instalar el software de Oracle específico para ese sistema operativo.

Si se ejecuta PL/SQL Developer en estas condiciones, luego de pedir los datos para el login retorna un error nada explicativo: un pequeño popup con mensaje vacío y un botón OK.

Un consejo: al obtener errores de conexión con herramientas externas, testear la conexión a través de SQL*Plus, para poder ver errores directos del sistema operativo o de la base de datos. Si SQL*Plus se conecta exitosamente, entonces el problema estará en la herramienta.

miércoles, 4 de junio de 2008

Cómo saber la última fecha en que un tablespace fue alterado

El único lugar donde encontramos esta información es el alert log file. Este archivo registra todas las alertas críticas de la base de datos, incluídas las alteraciónes de tablespaces. Es solo mirar este archivo, hacer una búsqueda por el nombre del tablespace y obtendremos todos los datos de la modificación.

El alert log se localiza en la ruta: ${ORACLE_HOME}/admin/${ORACLE_SID}/bdump
y su nombre generalmente es: alert_${ORACLE_SID}.log

La restricción, es que únicamente el administrador de la base de datos tendrá acceso a este archivo.

viernes, 30 de mayo de 2008

Comandos útiles para Unix Shell

Agrego algunos comandos útiles en Unix que siempre vienen bien tenerlos a mano. Hacen parte de las tareas diarias, en la creación de nuevos scripts de respaldo o en el tratamiento de logs que generan nuestros processos.

En este caso uso Solaris y ksh, pero dependiendo de la versión de Unix/Linux que estén utilizando, pueden variar algunos parámetros. De todas formas, la idea sirve para adaptarlos en cada ambiente.


Eliminar archivos gz con mas de 3 días de antiguedad
find . -mtime +3 -name "*.gz" -exec rm {} \;

Ejecutar un shell en background e independiente de la conexión
nohup ./shell.sh &
(este script retorna un número de job, puede ser útil anotarlo)

Para traer el proceso nuevamente al foreground
fg %(num_job)
(el número de job fue el que se retornó al colocar el proceso en background)

Colocar los nombres de archivo de un listado en un array
ARQS=$(cd ${DIR_RECARGA} ; ls *.dat)

Correr un script de sqlplus en Shell
#!/bin/ksh

sqlplus -s /nolog << !EOF
@$ORACLE_HOME/scripts/mi_script.sql

exit;
!EOF

Compactar todos los archivos de un directorio
#!/usr/sbin/ksh
for arch in `ls -t /*.arch tail +5`
do
gzip $arch
done

Ver y buscar en el contenido de un archivo comprimido con gzip
gzcat - CAT para archivos gz
gzgrep - GREP para archivos gz


Ver también:
Cómo verificar archivos y directorios en Shell
Limpieza de archivos en Unix
Cómo obtener la fecha de ayer en Unix

domingo, 20 de abril de 2008

Incorporando Google Maps en Application Express

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.
Ver también:
Google Maps API
Presentación Google Developer Day 2007

viernes, 18 de abril de 2008

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

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.

jueves, 17 de abril de 2008

Oracle libera parches de seguridad para todos sus productos


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

lunes, 14 de abril de 2008

Sobre comentarios y cómo actualizar secuencias

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.

viernes, 28 de marzo de 2008

Más control sobre los LOOPS en 11g

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

miércoles, 26 de marzo de 2008

NATURAL JOIN antinatural

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, en Oracle el natural join produce resultados inesperados 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. Pero eso deja de ser un problema si eliminamos hoy el uso de natural join, definitivamente.

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 propio mundo del SQL, el NATURAL JOIN es uno accesorio inútil como pocos, del cual podemos prescindir totalmente.

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.

lunes, 24 de marzo de 2008

Compilación condicional en 9i

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

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;
$ELSE
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;
$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.

miércoles, 19 de marzo de 2008

Cómo verificar archivos y directorios en Shell

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


Ver también
Comandos útiles para Unix Shell
Limpieza de archivos en Unix
Cómo obtener la fecha de ayer en Unix

lunes, 17 de marzo de 2008

Error ORA-942 while gathering statistics

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 acceso limitado, entonces es de esperar que no tenga grants 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 asignación de estos grants, 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

miércoles, 12 de marzo de 2008

Cómo detectar caracteres extraños o no imprimibles

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

lunes, 10 de marzo de 2008

11g y sus Indices Invisibles

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:
Indices condicionales
Mis índices no funcionan!
Indices de función para mejorar un LIKE
Oracle Database Administrator's Guide 11g sobre índices invisibles

sábado, 8 de marzo de 2008

SOUNDEX en español

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:

  1. Retener la primera letra de la cadena. Tener en cuenta las letras dobles como CH y LL.
  2. 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 )
  3. 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
  4. Si hay números consecutivos, dejar solamente uno en la serie.
  5. 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.
Ultima versión: 1.8 (04-MAR-2010)


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

Ver también:
Wikipedia: Soundex

miércoles, 27 de febrero de 2008

Insertar o actualizar un único registro con MERGE

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

martes, 29 de enero de 2008

Patches críticos en enero para todos los productos Oracle

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

lunes, 28 de enero de 2008

Sesiones de Búsqueda

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.