Cómo crear una vista parametrizada

En Oracle, podemos crear vistas que retornen resultados dependientes de parámetros previamente seteados. La forma de lograr esto es usando un feature llamado Application Contexts.

¿Qué es un contexto de aplicación?
El contexto de aplicación es simplemente un espacio en memoria que nos permite almacenar valores para luego utilizarlos en SQL o PL/SQL, como cualquier otra variable definida en el entorno. De forma transparente, mis objetos pueden ser 'manipulados' externamente sin necesidad que mis aplicaciones o procesos batch se enteren.
El contexto puede ser definido tanto localmente (privado para cada sesión) como globalmente, compartiendo sus valores para todas las sesiones de la instancia.
Para poder alterar los valores del contexto, debemos crear un paquete especialmente autorizado para ese fin. Esto es un requerimiento por razones de seguridad.
También necesitamos tener el permiso especial de sistema CREATE ANY CONTEXT.

Ejemplo (con sqlplus)

Vamos a ver un sencillo ejemplo de cómo implementar una vista parametrizada con contextos de aplicación, usando el popular usuario SCOTT. El parámetro para la vista en este caso será el número de departamento.

Paso 1: Crear el contexto de aplicación

CREATE CONTEXT app_ctx_scott USING pk_scott_app_context
/

Paso 2: Crear el paquete para manipular el contexto

CREATE OR REPLACE PACKAGE pk_scott_app_context AS
  -- El contexto tendra un unico valor deptno
  PROCEDURE set_dept (p_deptno IN NUMBER);
END;
/

CREATE OR REPLACE PACKAGE BODY pk_scott_app_context AS
  PROCEDURE set_dept (p_deptno IN NUMBER) IS
  BEGIN
    DBMS_SESSION.SET_CONTEXT('app_ctx_scott', 'deptno', p_deptno);
  END;
END;
/

Paso 3: Setear el parámetro de contexto deptno para el departamento de ventas

BEGIN
pk_scott_app_context.set_dept(30);
END;
/

Paso 4: Crear la vista parametrizada

CREATE VIEW empleados AS
SELECT e.empno, e.ename, e.job, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno
AND d.deptno = sys_context('app_ctx_scott','deptno');

Paso 5: Obtener los resultados consultando la vista

SELECT * FROM empleados;

     EMPNO ENAME      JOB       DNAME
---------- ---------- --------- --------------
      7499 ALLEN      SALESMAN  SALES
      7521 WARD       SALESMAN  SALES
      7654 MARTIN     SALESMAN  SALES
      7698 BLAKE      MANAGER   SALES
      7844 TURNER     SALESMAN  SALES
      7900 JAMES      CLERK     SALES

6 rows selected.

Actualmente la vista retorna los empleados del departamento de ventas, ya que así está definida la variable en el contexto. Ahora cambiaré el valor del parámetro para que la vista retorne resultados únicamente del departamento contable:

BEGIN
  pk_scott_app_context.set_dept(10);
END;
/

PL/SQL procedure successfully completed.

SELECT * FROM empleados;

     EMPNO ENAME      JOB       DNAME
---------- ---------- --------- --------------
      7782 CLARK      MANAGER   ACCOUNTING
      7839 KING       PRESIDENT ACCOUNTING
      7934 MILLER     CLERK     ACCOUNTING

3 rows selected.

De la misma forma podemos aplicar esta técnica a procedimientos y funciones, pudiendo manipular valores utilizados internamente o inclusive introduciendo fragmentos de código como sql dinámico.

Migrando MySQL a Oracle con SQL Developer

Siempre que evaluamos herramientas para realizar esta tarea, debemos considerar variables tales como rapidez, facilidad de uso y bajo costo, entre otras.

SQL Developer es un producto de costo cero, que permite migrar bases de datos MySQL, Access o SQL Server a Oracle con facilidad.
En este tutorial mostraré el método de migración rápida de esquemas MySQL 5 a Oracle XE, usando la versión 1.5.3 de SQL Developer.

Preparación del software

- Descargar e instalar SQL Developer:
La última versión está disponible en OTN.

- Instalar el plugin de conexión a MySQL:
Para esto, desde SQL Developer vamos al menú Ayuda, Verificar Actualizaciones, marcamos la casilla Third Party SQL Developer Extensions y descargamos el driver de conexión a MySQL. SQL Developer deberá ser reiniciado para que el plugin entre en efecto.

NOTA: Si la extensión no nos aparece entre las opciones de descarga, es posible que ya la tengamos instalada. Para verificar, vamos al menú Ayuda, Sobre, Extensiones, y verificamos que exista la entrada MySQL JDBC Driver.

Crear un usuario para la migración

El usuario de migración será el encargado del proceso de colección, transformación, carga y movimiento de datos, y necesitará un conjunto de permisos especiales para tales efectos.

Para crearlo, loguearse a sqlplus con el usuario system y ejecutar:

CREATE USER migration IDENTIFIED BY xxxxxxx DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; GRANT create session, create view, resource, create user, create role, alter any trigger TO migration WITH ADMIN OPTION;

Crear las conexiones

- Crear una conexión para el esquema MySQL a ser migrado:
Colocar un nombre para la conexión y el usuario MySQL (en el ejemplo será root, sin password). Luego seleccionar la lengueta MySQL para verificar el hostname (o IP del servidor) y el puerto (generalmente 3306).
Testeando la conexión, el resultado debe ser SUCCESS para poder continuar, de lo contrario revisar el nombre del servidor Apache, usuario de MySQL, y el puerto configurado en el archivo de configuración my.ini.

- Crear una conexión Oracle para el repositorio y el esquema destino:
Similarmente como hicimos para MySQL, crearemos una conexión para nuestra base de datos destino, en este caso XE. Configurar el usuario system con su password, y el nombre del servicio de nuestra base de datos.
Como en el caso anterior, testear la conexión.

Una vez creadas, ambas conexiones deberán aparecer en el menú vertical izquierdo. A partir de ellas podremos ver la definición de los objetos antes y después de la migración.



Es recomendado crear una conexión separada para el repositorio y otra para el esquema destino, la idea es que sean usuarios diferentes. Para simplificar este ejemplo utilizaremos el mismo esquema para las dos cosas.

Crear un repositorio

SQL Developer usa un Repositorio para almacenar los packages y datos temporales para validar y convertir nuestros objetos. Si no lo creamos, el propio wizard lo creará automáticamente, pero siempre es mejor crearlo antes y verificar que quede todo correctamente instalado.

Vamos a la conexión de Oracle recientemente creada y con el botón derecho seleccionamos Migration Repository, y Associate Migration Repository.

En un minuto, el repositorio estará creado. Podemos verificar que las tablas y vistas del repositorio fueron creadas, con prefijo 'MD_' y 'MGV_' respectivamente. También es bueno verificar que estén correctamente compilados los 4 packages: MD_META, MIGRATION, MIGRATION_REPORT y MIGRATION_TRANSFORMER.

Iniciar el asistente

Teniendo todos los pasos previos completados, comenzamos con la migración propiamente dicha. Vamos al menú Migration, Quick Migrate, y seleccionamos la conexión de la base de datos orígen, es decir MySQL.

Si nuestra conexión fue correctamente creada, nos pide la conexión destino. Colocaremos la conexión XE creada para recibir los objetos.

El siguiente paso es la verificación del repositorio a utilizar. Debe aparecer el mensaje OK: Using [nombre conexión].

El cuarto paso es la verificación de pre-requisitos. Luego de presionar el botón Verify, una serie de tests son ejecutados, que incluyen conectividad de fuente y destino, y permisos de usuario. Cualquier observación que Oracle levante aquí tiene que ser resuelta antes de poder avanzar.

Luego que todos los pasos de la verificación de pre-requisitos resultan en SUCCESS, seguimos.

Finalmente debemos elegir el tipo de migración entre MIGRATE TABLES ONLY, MIGRATE TABLES AND DATA o MIGRATE EVERYTHING.

En este caso elegiremos migrate everything: usuarios, tablas, datos, índices, constraints, vistas y código.

Al presionar Finalizar, cruzaremos los dedos.

Resultados de la migración

La imagen a la izquierda muestra el resultado final de cada stream que realiza el movimiento final en paralelo, junto con la cantidad de filas migradas y errores sucedidos. Es de esperar no encontrar ningún error aquí!

Durante la migración, podemos pasar todas las etapas sin inconvenientes - el caso ideal- o, podemos encontrar errores generalmente durante la ejecución (Build) de los DDLs generados, lo cual abortará el proceso. La verdad verdadera, es que no siempre lograremos terminar exitosamente la migración en la primera vez, solamente si la base de datos orígen respeta ciertas reglas en su definición.

Diversos errores de conversión pueden ocurrir desde que MySQL permite amplia libertad de declaraciones fuera del ANSI/ISO SQL standard (además de que MySQL tiene varias extensiones propias de SQL).

En caso de error, debemos leer el dump de la ejecución y analizar de qué tipo fue la falla. Una vez resuelto, eliminaremos los objetos 'sucios' creados y volveremos a ejecutar el asistente.

Este proceso de ensayo y error puede ser repetitivo, y es común en la gran mayoría de las migraciones hasta que finalmente logramos una ejecución limpia. Necesitaremos paciencia y perseverancia para llegar al objetivo.

Tareas post-migración

Luego de la migración, es recomendable verificar que cada tabla fue copiada correctamente. Revisar como fueron transformados los datos, verificar los campos de tipo fecha o numéricos, o aquellos que tienen valores por defecto.

Es también un buen momento para reforzar la integridad del esquema con todas aquellas mejoras que Oracle ofrece como foreign keys, constraints, e índices. MySQL es poco restrictivo en ese aspecto, y eso puede generar vicios para algunos programadores.
Por ejemplo, el hecho de que las fks y transacciones son soportadas solamente en tablas InnoDB. En este tipo de escenarios, son las aplicaciones las responsables de forzar la integridad de datos, y no siempre logran ese objetivo.

Cuidado con aplicar restricciones sin analizar previamente su impacto, ya que podremos estar causando un mal peor del que queremos remediar. Un ejemplo común, es el uso de claves referenciales fantasma (o dummy) en la aplicación (sin clave valida en la tabla padre). Tendremos que evaluar si al incorporar las constraints estaremos afectando a la aplicación que tiene implementada esta práctica.

Una crítica que encuentro oportuna, es el hecho de que para cada campo autonumérico de MySQL, SQL Developer crea una secuencia y un trigger asociado para 'simular' la asignación automática. Entiendo que está a favor de la transparencia, pero por otro lado me parece una decisión interesante en términos de diseño, y me gustaría que fuera un feature opcional para tener el control.

De la misma forma, otra opción 'indeseada' para mí, es que genera un trigger por cada campo enumerado ('1', '2', '3',..) para asegurar la asignación de valores. Desearía que SQL Developer creara check constraints en lugar de estos triggers que sólo empeoran la performance y limitan la escalabilidad de las aplicaciones.

En conclusión

Por la facilidad de operación, visibilidad gráfica de cada una de las etapas, bajo costo -y pese a las críticas que he expuesto-, recomiendo que le den una oportunidad a SQL Developer como opción para migrar de MySQL a Oracle.

Soporte Oracle con VMWare?

Oracle establece según nota 249212.1 en Metalink, que no ha certificado ningún producto corriendo virtualizado con VMWare. Si un problema determinado ocurre y el usuario necesita soporte oficial Oracle, deberá probar que el error no se debe a estar corriendo bajo VMWare, por ejemplo reproduciéndolo bajo el sistema operativo nativo. Esto sin dudas complica la existencia de los administradores de sistemas y bases de datos, que deben preparar ambientes no virtualizados para intentar capturar el mismo error. En ese proceso, el tiempo puede ser el peor enemigo.

Desde el punto de vista del soporte, es entendible y sensato que Oracle no se responsabilice por productos de terceros como VMWare. Desde el punto de vista comercial, es una posición conveniente en tiempos que Oracle invierte en marketing para su propia infraestructura de virtualización (Oracle VM). Los potenciales compradores de una solución virtual deberán evaluar con especial cuidado el soporte que tendrán corriendo una base de datos Oracle virtualizada. Una decisión segura llevará a adoptar el producto que le garanta la máxima cobertura de soporte.

VMWare, INC comienza a experimentar un crecimiento desacelerado y ya tiene sus primeras bajas en la guerra de la competencia con Microsoft, Citrix, Sun, Oracle y otros. Su presidente ejecutivo fue recientemente demitido, y para empeorar los pronósticos, viene de recibir un duro golpe en el mercado de valores luego que sus acciones cayeran un 11%, pese al crecimiento del 53% en sus ganancias en el último balance de 2008.

Error al crear usuario en Oracle TimesTen

Cuando se intenta crear un usuario en Oracle TimesTen, se obtiene el siguiente error:

Command> create user lferTTadmin identified by '$ql450';
15007: Access control not enabled
The command failed.

Durante la instalación de TimesTen 7.0, se preguntó al usuario si se deseaba activar el access control (Do you want to enable Access Control? Yes/No). Si No fue la respuesta, aún hay una forma de poder activarlo 'post instalación'.

Abrir una consola del sistema operativo, y ejecutar:
ttmodinstall -enableAccessControl

Luego de eso, el control de acceso estará habilitado.

C:\Windows\system32>ttmodinstall -enableAccessControl

C:\Windows\system32>"C:\TimesTen\tt70_32\perl\bin\perl.exe" "C:\TimesTen\tt70_32
\bin\ttmodinstall" -enableAccessControl
Would you like to enable access control for this instance? [ no ] yes
NOTE: The daemon must be stopped before enabling access control.
Would you like to stop the daemon? [ yes ] yes
The TimesTen Data Manager 7.0 service is stopping...
The TimesTen Data Manager 7.0 service was stopped successfully.

Patching successful ...
Restarting the daemon ...
The TimesTen Data Manager 7.0 service is starting.
The TimesTen Data Manager 7.0 service was started successfully.

Access control is now enabled for this TimesTen instance.
C:\Windows\system32>ttisql TT_test

Copyright (c) 1996-2008, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
All commands must end with a semicolon character.

connect "DSN=TT_dns_prod04";
Connection successful: DSN=TT_test;UID=sixbell;DataStore=C:\Users\dashboard\Deskto
p\temp\TT_store;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;DR
IVER=C:\TimesTen\tt70_32\bin\ttdv70.dll;TypeMode=0;
(Default setting AutoCommit=1)
Command> create user lferTTadmin identified by '$ql450';
Command>

Parches de seguridad Enero 2009

Oracle ha anunciado un nuevo Critical Patch Update que afectan a la mayoría de sus productos, y recomienda fuertemente su aplicación en todos los ambientes, para evitar la explotación de accesos indebidos.
El set incluye 20 patches para Oracle Database (a partir de 9i Release 2), 4 para Application Server (a partir de 10g Release 2), 1 para Collaboration Suite (a partir de 10g), 4 para Applications Suite (a partir de 11i), 1 para Enterprise Manager (a partir de 10g Release 4), 6 para PeopleSoft y JDEdwards Suite (a partir de 8.9), y 5 para BEA Products Suite (a partir de 7.0).
Desde luego que el download está disponible en OTN.

Ver también
Critical Patch Update Advisory - January 2009