Mostrando entradas con la etiqueta PLSQL. Mostrar todas las entradas
Mostrando entradas con la etiqueta PLSQL. Mostrar todas las entradas

21 enero 2019

¿Cómo encontrar los metadatos de cualquier objeto en Oracle Database?


Normalmente nos vemos en la situación de necesitar definición de tablas, índices, vistas, etc. para replicar la estructura en otra base de datos. Oracle tiene un comando muy útil para encontrar la definición de metadatos de la estructura diversa. Este comando es muy útil para extraer los metadatos de cualquier objeto, como tabla, índice, vistas, vistas materializadas.


La salida de la consulta es de tipo de carácter large. Por favor, establezca la longitud length antes de comenzar la consulta.

Ejemplos variados:


Como es sabido, tenemos un comando para toda la creación de objetos en la base de datos Oracle. Este es un comando bastante poderoso y podemos usarlo para extraer la definición de tabla de todas las tablas en el esquema también.




04 octubre 2018

PL/SQL: Server Result cache otra herramienta más en nuestro cinturón


Una característica desde Oracle 11g, el llamado Result Cache, es una herramienta poderosa que se puede usar para almacenar en la memoria resultados de consultas y funciones. La información almacenada en caché se almacena en un área dedicada dentro de la Shared Pool donde puede ser compartida por otros programas PL/SQL que realizan cálculos similares. Si los datos almacenados en este caché cambian, los valores que se almacenan en el caché pierden su validez. Esta función es útil para las bases de datos con sentencias que necesitan acceder a un gran número de filas y devolver solo algunas de ellas. Como puede ocurrir en tiendas online, bancos, etc, ...

La caché de resultados se configura utilizando el parámetro de inicialización result_cache_mode con uno de estos tres valores:

  • auto: los resultados que deben almacenarse se resuelven mediante el optimizador de Oracle
  • manual: Almacene en caché los resultados indicando la declaración usando la sugerencia result_cache | no_result_cache
  • force: todos los resultados serán cacheados.

El paquete dbms_result_cache se usa para proporcionar las opciones de administración de DBA de la memoria utilizada tanto por la caché de resultados de SQL como por la caché de resultados de la función PL / SQL. Estos son los procedimientos y funciones del paquete dbms_result_cache:


A continuación se describe una descripción práctica de cómo utilizar estos procedimientos y funciones.

Para este ejemplo, la result cache de SQL se muestra y se explica cómo limpiar la memoria caché de resultados y cómo utilizar las sugerencias para corregir los resultados de una consulta en la memoria caché de resultados. Primero, eche un vistazo a los parámetros de inicialización del caché de resultados. Todos están configurados en sus valores predeterminados; por lo tanto, es necesario usar la sugerencia result_cache para mantener los resultados de las consultas en la memoria, ya que el parámetro de inicialización result_cache_mode es manual.



Ahora consulta las vistas de caché de resultados que proporcionan información como objetos en memoria, dependencias de relación y estadísticas de caché de resultados.


Seguimos con la comprobación



Ejecute la operación "flush" para eliminar todos los objetos de la memoria caché de resultados. Existe la opción de conservar o liberar la memoria y / o las estadísticas según sea necesario. Aquí se encuentran todas las posibilidades de vaciar el caché de resultados:


Es muy fácil mantener los resultados de las consultas en la memoria, pero presta atención a la columna de invalidaciones de la tabla v $ result_cache_objects. Esta columna muestra cuándo los resultados asociados a un objeto en la memoria dejan de ser válidos debido a una modificación de los datos.

El parámetro result_cache se usa para que los resultados devueltos por esta función se almacenen en la memoria caché hasta que se modifiquen los datos de la tabla de empleados, lo que invalidará los resultados almacenados en caché para esta función.



30 septiembre 2018

Proceso en PL/SQL para matar sesiones en Oracle 11g RAC


Alguna que otra vez, en tu que hacer diario, necesitas eliminar una sesión en una instalación Oracle database 11g RAC y más tarde, recibes un mensaje en el  que no se puede cancelar la sesión debido a que la sesión está conectada a una instancia diferente. Te tienes que conectar a esa instancia y matarla

Comenzando con 11g, el fabricante incluyó el nombre de instancia en el comando de alterar el sistema (3º parámetro o @instance_id). Por lo tanto, para matar la sesión de bloqueo en cualquier nodo desde cualquier nodo que ejecutaría:



Si ejecutamos nuestro código tenemos la siguiente salida:

Ya puedes matar sesiones en un servidorRAC por instancia de este.




22 noviembre 2017

Pruebas unitarias con utPLSQL

Las pruebas son un mal necesario del proceso de desarrollo de aplicaciones. Lamentablemente, las pruebas a menudo se pasan por alto o se pasan por alto cuando el tiempo es corto (seamos sinceros, ¿Quién no odia la fase de pruebas?). La distribución de código no probado o no marcado puede llevar a un código plagado de errores y a usuarios decepcionados. Las pruebas unitarias con un marco bien construido pueden ayudar a aliviar parte del tiempo que lleva ajustarse a un proceso de desarrollo bien probado.

Hay algunas opciones diferentes disponibles para probar tu código PL / SQL. La herramienta de Oracle SQL Developer:
  • http://www.oracle.com/technetwork/es/developer-tools/sql-developer/overview/index.html


Proporciona algunas buenas opciones de depuración.

También puede usar instrucciones DBMS_OUTPUT dentro de su código para mostrar los resultados de las variables a medida que se ejecuta su código. Esta es una buena técnica para ayudar a identificar problemas en su código.

Frameworks de prueba para PL/SQL

Los más conocidos en la comunidad de Oracle son quizás Quest's Code Tester, especialmente con personas que trabajan con Toad y utPLSQL, que ha sido desarrollado por el superhombre de PL / SQL Steven Feuerstein, y las características integradas de Oracle SQL Developer.
  • DbFit                                   
  • Desarrollador Oracle SQL
  • Pruebas unitarias PL / SQL para Oracle (PLUTO)
  • PL / Unidad
  • Quest Code Tester para Oracle
  • ruby-plsql-spec
  • utPLSQL

Por ejemplo el framework PLUTO (PL / SQL Unit Testing for Oracle) (http://code.google.com/p/pluto-test-framework/) es uno de esos marcos.

Otro es el marco de pruebas unitarias utPLSQL, creado por Steven Feuerstein  y este artículo se centrará en utPLSQL ya que es más ampliamente adoptado que los demás. El framework de pruebas unitarias  utPLSQL (http://utplsql.org/downloads/) puede aliviar parte del dolor de realizar las pruebas unitarias. El marco es fácil de usar y funciona muy bien para probar código bajo cualquier circunstancia que pueda imaginarse. Ahí también hay muchas opciones en utPLSQL que se pueden usar para mejorar el proceso de prueba de su unidad. Como resultado del uso de pruebas unitarias, sus aplicaciones serán más fiables, y pasará mucho menos tiempo manteniendo la base de código.

Prueba de código PL / SQL almacenado sin pruebas unitarias

Problema

Desea asegurarse de que un bloque de código PL / SQL funciona correctamente, pero no quiere tomarse el tiempo para escribe una prueba unitaria

Solución

Envuelves el código en las sentencias DBMS_OUTPUT que muestran o imprimen los resultados de intermedio y final, así como los resultados de pasos y ramas condicionales complejos. Esto te permitirá ver la ruta que toma el código cuando se llama a la función con los parámetros especificados. El siguiente ejemplo muestra esta táctica para colocar comentarios en ubicaciones estratégicas dentro de un código PL / SQL para ayudar a determinar si el código está funcionando como se esperaba. Por ejemplo, supongamos que desea pruebe rápidamente la función que presentamos en el ejemplo. Así es como lo modificarías para probar rápidamente la exactitud de sus resultados.


Cuando la función CALC_HORAS_TRIMESTRALES se ejecuta con un valor de 7.34, los comentarios se mostrarán como se ve en el siguiente fragmento de una sesión:
SQL> set serveroutput on
SQL> select calc_horas_trimestrales(7.34) from dual;
CALC_HORAS_TRIMESTRALES(7.34)
-----------------------

7.25
El valor pasado fue mayor a una hora ...
La porción decimal <= .375

El uso de instrucciones DBMS_OUTPUT dentro del código PL / SQL para mostrar datos o información perteneciente a la funcionalidad del código ha sido una gran táctica para probar el código en cualquier idioma. Como una cuestión de hecho,
es probablemente una de las técnicas más utilizadas para depurar código. La capacidad de ver valores como se calculan o para determinar cómo se maneja una condición puede ser muy útil para determinar si su código se está ejecutando como debería.

Para utilizar las sentencias DBMS_OUTPUT para probar tu código, debes colocarlas en estrategias ubicaciones. En el ejemplo de esta receta, los comentarios se han colocado dentro de cada uno de los bloques IF-ELSE para mostrar un poco de texto que le dirá al desarrollador cómo se procesan los valores dentro de la función.
Esto puede ser muy útil cuando se prueba el código porque una serie de números se puede pasar al función para determinar si se está devolviendo el resultado correcto. Si no, entonces podrás  para ver exactamente dónde se está evaluando incorrectamente el código.


Aunque el uso de instrucciones DBMS_OUTPUT en el código puede ser muy útil para determinar dónde se encuentra el código funcionando correctamente, puede causar desorden y también puede crear sus propios problemas. Por ejemplo, si te olvidas de colocar una cita después de una de las instrucciones DBMS_OUTPUT que coloca en su código, entonces el código no se compila correctamente, lo que hace que busques la causa de otro problema. Además, es una buena idea eliminar las instrucciones de salida antes de que el código se libere en producción. Esto puede tomar un tiempo, que podría ser mejor invertido en el desarrollo. Como un medio para probar pequeñas unidades de código, usando DBMS_OUTPUT funciona bastante bien. Sin embargo, si desea desarrollar suites de prueba completas y una unidad automatizada probando, entonces debes de continuar leyendo el resto del artículo.

Instalar utPLSQL

Problema

Has elegido el marco de pruebas de unidad utPLSQL para PL / SQL para tu trabajo y quieres instalarlo.

Solución

Primero, descarga las fuentes de utPLSQL. Una vez que hayas obtenido las fuentes, sigue los siguientes pasos para instalar el paquete utPLSQL en la base de datos para la cual desea escribir las pruebas unitarias y ponerlo a disposición para todos los esquemas.
Crea un usuario para alojar las tablas, paquetes y otros objetos de utPLSQL. Por ejemplo, el usuario se llamará UTP y se usarán los tablespaces permanentes y temporales predeterminados.

Otorga privilegios al usuario UTP recién creado utilizando GRANT <privilege_name> TO  <user_name>, reemplazando valores con el privilegio apropiado y nombre de usuario El usuario requerirá los siguientes privilegios:
  • Create session
  • Create procedure
  • Create table
  • Create view
  • Create sequence
  • Create public synonym
  • Drop public synonym

Instalaa los objetos ejecutando el script ut_i_do.sql.

SQL> @ut_i_do instalar

Una vez que se hayan completado estos pasos, tendrás la posibilidad de ejecutar pruebas unitarias en paquetes que se cargan en diferentes esquemas dentro de la base de datos.

Usar utPLSQL

Problema

Te gustaría construir un paquete de prueba de unidad para uno o más de los objetos PL / SQL en su esquema de base de datos.

Solución

Deseas construir un paquete de prueba utPLSQL para probar un objeto en su base de datos. Un paquete de prueba consta de dos archivos separados, un encabezado de paquete y un cuerpo de paquete.

Cree un encabezado para el paquete de prueba y guárdelo en un archivo con el mismo nombre que le ha asignado al encabezado y con un sufijo .pks.
Un archivo de encabezado contiene tres procedimientos: 
  • ut_setup, 
  • ut_teardown 
  • El procedimiento que realiza las pruebas unitarias del objeto de destino en su base de datos. 

Por ejemplo, supòn que deseas crear un paquete de prueba de unidad para probar el código de la función CALC_HORAS_TRIMESTRALES. Este encabezado de paquete debe almacenarse en un archivo llamado ut_calc_horas_trimestrales.pks y cargado en la base de datos cuyos objetos estás probando.

Cree el cuerpo del paquete que implemente los procedimientos especificados por el encabezado del paquete de prueba de la unidad y guárdelo como un archivo con el mismo nombre que el encabezado, pero esta vez con un sufijo .pkb. El siguiente cuerpo del paquete debe almacenarse en un archivo llamado ut_calc_horas_trimestre.pkb y cargarse en la base de datos.

El cuerpo del paquete en este ejemplo se ajusta al formato que se debe usar para probar paquetes usando el marco utPLSQL.




07 noviembre 2017

Calidad del dato: Usar PL/SQL con cadenas de texto para estandarizar


La estandarización de cadenas de texto es uno de los temas recurrentes en el desarrollo de procesos ETL. Cuando se tratan datos que definen a personas físicas o jurídicas, es muy común encontrarse con duplicados debidos a errores tipográficos y de entradas de datos (y mas si son nomtbres de procedencia extranjera, árabe, chino, ...).

Consideremos el siguiente ejemplo:



Se puede ver que los dos registros se refieren a la misma persona física, pero hay elementos que impiden considerar los registros como duplicados perfectos. El NIF ha sido transcrito mal. Es presumible que el error venga de una introducción manual de los datos ya que la Y y la T están muy cerca en el teclado español. Tanto el nombre como la dirección es el acento el que hace que no coincidan las dos cadenas , mientras que en el caso de la población aparecen en dos formatos distintos.


¿Qué herramientas tenemos en PL/SQL para comparar los dos registros y eliminar los duplicados?

La función SOUNDEX devuelve una representación fonética de una cadena. El resultado es la codificación de cómo se pronunciaría un texto en inglés. A pesar de que estemos trabajando con texto en otro idioma, la función puede resultar útil.


Si la aplicamos por ejemplo a la población, tendremos:

SELECT DIRECCION, SOUNDEX(DIRECCION) FROM personas_ejemplos WHERE NOMBRE LIKE '%Rafael%';



El package UTL_MATCH contiene unas funciones desarrolladas para facilitar la detección de duplicados. Hay cuatro funciones:
  • EDIT_DISTANCE: la función calcula la distancia de Levenshtein. Esta medida debe el nombre al científico ruso que desarrolló un algoritmo para medir la distancia entre dos cadenas de texto s1 y s2. La distancia se calcula como numero de inserciones, cambios, cancelaciones de caracteres que permiten pasar de la cadena s1 a la cadena s2. La función devuelve un número que representa la distancia entre las dos cadenas: 0 indica dos cadenas idénticas. 
En el ejemplo, si asumimos que el formato correcto es “NOMBRE APELLIDO”, la función devuelve:

SELECT Nombre, utl_match.edit_distance(Nombre, 'Rafael Montesinos')
FROM personas_ejemplos WHERE Nombre LIKE '%Rafael%';


La distancia entre las dos cadenas no es muy grande y podríamos considerar las dos cadenas como coincidentes.


  • EDIT_DISTANCE_SIMILARITY: actúa como la función EDIT_SIMILARITY pero devuelve un valor normalizado entre 100 (cadenas coincidentes) y 0 (total discordancia).
  • JARO_WINKLER: esta función utiliza el algoritmo de Jaro-Winkler que calcula un índice de similitud entre dos cadenas, para intentar tener en cuenta posibles errores tipográficos. El valor 1 representa dos cadenas coincidentes.
SELECT NOMBRE, utl_match.JARO_WINKLER(NOMBRE, 'Rafael Montesinos')
FROM personas_ejemplos WHERE NOMBRE LIKE '%Rafael%';




  • JARO_WINKLER_SIMILARITY: la función utiliza el algoritmo de Jaro-Winkler y normaliza los resultados entre 0 y 100.

SELECT NOMBRE, utl_match.JARO_WINKLER_SIMILARITY(NOMBRE, 'Rafael Montesinos')
FROM personas_ejemplos WHERE NOMBRE LIKE '%Rafael%';


Las funciones del paquete UTL_MATCH permiten definir un umbral de aceptabilidad para establecer si dos cadenas coinciden “lógicamente” y pueden resultar una herramienta muy útil para limpiar datos en ETL.

29 octubre 2017

DBA WARS: "It's an older code sir, but it checks out."


En esta entrada del blog vamos a dar salida a varios còdigos SQL Y PL/SQL, antiguos pero todavía válidos.

Problemas con de PL / SQL Developer de Allround Automations

Al usar este IDE e su versión 11.X, una consulta interna toma muchos ciclos de CPU en el servidor de la base de datos (100% de una CPU).
¿Es este tu problema?  Por favor, compruebe si hay una consulta al lanzar el editor con el siguiente formato de consulta:
select s.synonym_name object_name, o.object_type
from sys.all_synonyms s, sys.all_objects o where s.owner in ('PUBLIC', user) and o.owner = s.table_owner and o.object_name = s.table_name and o.object_type in ('TABLE', 'VIEW', 'PACKAGE','TYPE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE');
Se debe a la opción Describir contexto del asistente de código. Para deshabilitar:
Mira en: Tools> Preferences> Code Assistant y deshabilitar la opción "Describe Context".
O comprarles la versión 12.X que ya está resuelto.


Depurar papelera de reciclaje anterior a ...


Tiene la papelera de reciclaje de la base de datos llena o su tamaño es considerablemente grande, pero no quiere hacer un "Borrado" completo?
¿Quieres eliminar solo más de x días (como más de 90 días)?

Una buena forma de hacerlo es:

# 90 días
Select 'purge table '||owner||'."'||OBJECT_NAME||'";' from dba_recyclebin where type='TABLE' and to_date(droptime,'YYYY-MM-DD:HH24:MI:SS')<sysdate-90;
# 90 minutos
Select 'purge table '||owner||'."'||OBJECT_NAME||'";' from dba_recyclebin where type='TABLE' and to_date(droptime,'YYYY-MM-DD:HH24:MI:SS')<sysdate-(90/(24*60));

Mirar esta nota de Metalink (Doc ID 1596147.1) por si teneis alguna qe otra duda.


Purga mejorada de la papelera de reciclaje Oracle

Desde que en la versión 10g de la base de datos , se introdujo el 'reciclaje' para segmentos. Si se establece el parámetro recyelebin = on, y se descarta una tabla, en realidad no se descarta; se renombra a un nombre generado por el sistema. Esto realmente puede ayudar en situaciones donde una tabla se cae accidentalmente, ya que se puede recuperar fácilmente. Sin embargo, sin monitorearlo, puede terminar causando problemas de muchos tipos, porque puede terminar hinchando el número total de segmentos en la base de datos a muchas veces más de lo que era antes.
Oracle solo tiene 4 métodos publicados para descartar lotes de objetos reciclados; siendo:

  • El comando SQL> PURGE RECYCLE BIN emitido por un propietario de segmento, que purga todos los segmentos reciclados para ese usuario.,
  • El comando SQL> PURGE DBA_RECYCLEBIN, emitido por un DBA, que purga todos los objetos reciclados en la base de dato.
  • El comando SQL> PURGE TABLESPACE <tablespace_name> USER <schema_name>; que purga objetos propiedad del propietario dado.
  • El comando SQL> PURGE TABLESPACE uwdata USUARIO uwclass; que purga los objetos propiedad del propietario y el espacio de tabla dados.
No hay comandos incorporados que permitan que solo los segmentos de papeleras de reciclaje se realicen antes de cierto tiempo, o que solo reciclen objetos de la papelera con más de un cierto número de versiones recicladas de la misma tabla. Es relativamente fácil crear un procedimiento almacenado para manejar estos casos; esto explica cómo se puede hacer eso.

En PL/SQL, tenemos:


create or replace procedure purge_user_recyclebin(
  p_purge_before_date   in date default=NULL,
  p_purge_keep_versions in number default=NULL,
  p_test_only           in varchar2 := 'Y'
)
is
  cursor c_purge_before_date is
    select object_name
    from  user_recyclebin
    where droptime > p_purge_before_time
    and   can_purge = 'YES'
    order by droptime;

  cursor c_purge_before_version is
     select * from 
       (select original_name,
               type,
               object_name,
               droptime,
               rank() over (partition by original_name,type order by droptime desc) as obj_rank
         from   user_recyclebin
         where  can_purge = 'YES')
       where obj_rank > p_purge_keep_versions
       order by droptime;

  v_sql varchar2(1024);

  exception e_bad_parameters;
  exception e_38302;
  pragma exception_init(e_38302,-38302);  

  procedure runsql(p_object_name in varchar2) 
  is
  begin
    v_sql := 'purge '||p_object_name;
    if (p_test_only = 'N') then
      begin
        execute immediate v_sql;
      exception
         when e_38302 then 
           dbms_output.put_line('Cuidado, el objeto '||p_object_name||' no existe ignóralo');
         when others then
           dbms_output.put_line('Error borrando '||p_object_name);
           dbms_output.put_line(dbms_utility.format_error_backtrace);
      end;
    else
      dbms_output.put_line(v_sql);
    end if;
  end;

begin

  if p_purge_before_date is not null and
     p_purge_keep_versions is null then
     for r in c_purge_before_date loop
       runsql(r.object_name);
     end loop;
  elsif p_purge_before_date is null and
     p_purge_keep_versions is not null then
     for r in c_purge_before_version loop
       runsql(r.object_name);
     end loop;
  else
     raise e_bad_parameters;
  end if;

end purge_user_recylebin;
/

Cómo usarlo

Si desea probar para ver qué haría, sin purgar nada, simplemente pase el parámetro 'p_test' al valor 'Y', y establezca la salida del servidor en el tamaño 1000000. Esto mostrará una lista de los comandos que se ejecutarían. pero en realidad no los ejecuta.

Purgar todos los objetos de la papelera de reciclaje antes de una fecha determinada
Esto purgará todos los objetos de reciclaje reciclados antes del 1 de noviembre, a medianoche.

execute purge_user_recylebin(to_date('20150801 00:00:00','YYYYMMDD HH24:MI:SS'),NULL,'Y');

Esto, cuando se ejecuta como propietario de un esquema, ayuda a mantener la cantidad de objetos reciclados a un nivel razonable, a la vez que se conserva, la capacidad de recuperar segmentos perdidos más recientemente.




04 septiembre 2017

Portar desde Oracle PL / SQL a PL/pgSQL (Primera parte)

¿Qué es PL/pgSQL?

PL/pgSQL (Procedural Language/PostgreSQL Structured Query Language) es un lenguaje imperativo provisto por el gestor de base de datos PostgreSQL. Permite ejecutar comandos SQL mediante un lenguaje de sentencias imperativas y uso de funciones, dando mucho más control automático que las sentencias SQL básicas.

Desde PL/pgSQL se pueden realizar cálculos complejos y crear nuevos tipos de datos de usuario. Como un verdadero lenguaje de programación, dispone de estructuras de control repetitivas y condicionales, además de la posibilidad de creación de funciones que pueden ser llamadas en sentencias SQL normales o ejecutadas en eventos de tipo disparador (trigger).

Una de las principales ventajas de ejecutar programación en el servidor de base de datos es que las consultas y el resultado no tienen que ser transportadas entre el cliente y el servidor, ya que los datos residen en el propio servidor. Además, el gestor de base de datos puede planificar optimizaciones en la ejecución de la búsqueda y actualización de datos.

Las funciones escritas en PL/pgSQL aceptan argumentos y pueden devolver valores de tipo básico o de tipo complejo (por ejemplo, registros, vectores, conjuntos o incluso tablas), permitiéndose tipificación polimórfica para funciones abstractas o genéricas (referencia a variables de tipo objeto).

Migrar desde Oracle PL / SQL a PL /pgSQL

Esta entrada en el blog explica las diferencias entre el lenguaje PL / pgSQL de PostgreSQL y el lenguaje PL / SQL de Oracle, para ayudar a los desarrolladores que portan aplicaciones desde Oracle a PostgreSQL.

PL / pgSQL es similar a PL / SQL en muchos aspectos. Es un lenguaje estructurado en bloques, imperativo, y todas las variables tienen que ser declaradas. Asignaciones, bucles, condicionales son similares. Las principales diferencias que debe tener en cuenta al trasladar de PL / SQL a PL / pgSQL son:
  • Si un nombre utilizado en un comando SQL puede ser un nombre de columna de una tabla o una referencia a una variable de la función, PL / SQL lo trata como un nombre de columna. Esto corresponde al comportamiento plpgsql.variable_conflict = use_column de PL / pgSQL, que no es el predeterminado, como se explica en la Sección 41.10.1. A menudo es mejor evitar esas ambigüedades en primer lugar, pero si tiene que cargar una gran cantidad de código que depende de este comportamiento, establecer variable_conflict puede ser la mejor solución.
  • En PostgreSQL el cuerpo de la función debe escribirse como una cadena literal. Por lo tanto, necesita usar cotizaciones en dólares o escapar de comillas simples en el cuerpo de la función. 
  • Los nombres de tipo de datos a menudo necesitan traducción. Por ejemplo, en los valores de cadena de Oracle se declaran normalmente como de tipo varchar2, que es un tipo no estándar de SQL. En PostgreSQL, utilice el tipo varchar o texto. De forma similar, reemplace el número de tipo por numérico o utilice otro tipo de datos numéricos si hay otro más apropiado.
  • En lugar de paquetes, utilice esquemas para organizar sus funciones en grupos.
  • Puesto que no hay paquetes, tampoco hay variables a nivel de paquete. Esto es algo molesto. Puede guardar el estado por sesión en tablas temporales en su lugar.
  • Integer Los bucles FOR con REVERSE funcionan de manera diferente: PL / SQL cuenta desde el segundo número hasta el primero, mientras que PL / pgSQL cuenta desde el primer número hasta el segundo, requiriendo que los límites del loop sean intercambiados al portar. Esta incompatibilidad es lamentable, pero es improbable que se cambie.
  • Los bucles FOR sobre consultas (que no sean cursores) también funcionan de manera diferente: la variable o variables de destino deben haber sido declaradas, mientras que PL / SQL las declara siempre implícitamente. Una ventaja de esto es que los valores de las variables siguen siendo accesibles después de que el bucle salga.
  • Existen varias diferencias de notación para el uso de variables de cursor.

En próximas entradas nos ocuparemos de hacer un ejemplo para mostrar como migrar funcionalidad de una base de datos a otra.

15 noviembre 2016

PL / SQL: Evitar algoritmos innecesariamente complejos



Si tienes un equipo de  programadores nuevos en PL / SQL (y SQL), no es raro encontrar que complican demasiado las cosas, escribiendo más código de lo necesario y poniendo demasiada lógica en PL / SQL. Ese problema puede ser agravado por accidentalmente obtener la "respuesta correcta" basada en pruebas inadecuadas y datos de prueba.

Supongamos que tengo una tabla y un conjunto de datos como sigue:



# 1. Éxito accidental con datos de prueba incorrectos


CREATE TABLE undead_type
(
   undead_id     INTEGER PRIMARY KEY,
   undead_name   VARCHAR2 (100)
)
/

BEGIN
   INSERT INTO undead_type (undead_id, undead_name)
        VALUES (1, 'Skeleton');
   INSERT INTO undead_type (undead_id, undead_name)
        VALUES (2, 'Zombie');
   INSERT INTO undead_type (undead_id, undead_name)
        VALUES (3, 'Ghoul');
   INSERT INTO undead_type (undead_id, undead_name)
        VALUES (4, 'Wight');
   INSERT INTO undead_type (undead_id, undead_name)
        VALUES (5, 'Wraith');
   INSERT INTO undead_type (undead_id, undead_name)
        VALUES (6, 'Mohrg');
  INSERT INTO undead_type (undead_id, undead_name)
        VALUES (7, 'Ghast');
   INSERT INTO undead_type (undead_id, undead_name)
        VALUES (8, 'Mummy');
   INSERT INTO undead_type (undead_id, undead_name)
        VALUES (9, 'Lich');
   INSERT INTO undead_type (undead_id, undead_name)
        VALUES (10, 'Demilich');
   INSERT INTO undead_type (undead_id, undead_name)
        VALUES (11, 'Vampire Spawn');
   INSERT INTO undead_type (undead_id, undead_name)
        VALUES (12, 'Vampire');
   INSERT INTO undead_type (undead_id, undead_name)
        VALUES (13, 'Allip');
   COMMIT;
END;
/
Y tengo que escribir un programa que produce la siguiente salida: 
Undead en Orden Alfabético: 
  • Allip 
  • Demilich 
  • Ghast
  • ...
DECLARE
   l_count   INTEGER;
   l_name    plch_animals.animal_name%TYPE;
BEGIN
   DBMS_OUTPUT.put_line ('Animals in Alphabetical Order');

   SELECT COUNT (*) INTO l_count FROM plch_animals;

   FOR indx IN 1 .. l_count
   LOOP
      SELECT animal_name
        INTO l_name
        FROM plch_animals
       WHERE animal_id = indx;

      DBMS_OUTPUT.put_line (l_name);
   END LOOP;
END;
/

# 2. Demasiado SQL, datos insuficientes

DECLARE
   TYPE undead_ids_t IS TABLE OF undead_type.undead_id%TYPE;

   l_undead_ids   undead_ids_t;
   l_name         undead_type.undead_name%TYPE;
BEGIN
   DBMS_OUTPUT.put_line ('undead in Alphabetical Order');

   SELECT undead_id
     BULK COLLECT INTO l_undead_ids
     FROM undead_type;

   FOR indx IN 1 .. l_undead_ids.COUNT
   LOOP
      SELECT undead_name
        INTO l_name
        FROM undead_type
       WHERE undead_id = indx;

      DBMS_OUTPUT.put_line (l_name);
   END LOOP;
END;
/

# 3. Vamos a pasarlo bien con las conexiones PL/SQL con colecciones

DECLARE
   TYPE undead_ids_t IS TABLE OF undead_type.undead_id%TYPE
      INDEX BY undead_type.undead_name%TYPE;

   l_undead_ids   undead_ids_t;
   l_index        undead_type.undead_name%TYPE;
   l_name         undead_type.undead_name%TYPE;
BEGIN
   DBMS_OUTPUT.put_line ('undeads in Alphabetical Order');

   FOR rec IN (  SELECT *
                   FROM undead_type
               ORDER BY undead_name DESC)
   LOOP
      l_undead_ids (rec.undead_name) := rec.undead_id;
   END LOOP;

   l_index := l_undead_ids.FIRST;

   WHILE l_index IS NOT NULL
   LOOP
      DBMS_OUTPUT.put_line (l_index);

      l_index := l_undead_ids.NEXT (l_index);
   END LOOP;
END;
/

#4. SQL Simple

SELECT undead_name FROM undead_type
 ORDER BY undead_name;

No es mucho más simple que eso. Y si lo necesita dentro de PL / SQL

 #5. PL/SQL Simple

 BEGIN
   DBMS_OUTPUT.put_line ('undead in Alphabetical Order');

   FOR rec IN (SELECT undead_name FROM undead_type
                ORDER BY undead_name)
   LOOP
      DBMS_OUTPUT.put_line (rec.undead_name);
   END LOOP;
END;


Lecciones aprendidas 


  •  Deje a SQL hacer el levantamiento de pesas, tanto como sea posible (no es que haya algo muy "pesado" para levantar en este ejercicio!) 
  • No sobre-complicar las cosas. Asegúrese de que sus datos de prueba tengan suficiente volumen y variedad para ejercer realmente su algoritmo. 
  • Si te encuentras pensando "¿tiene que ser tan complicado?", Casi con toda seguridad la respuesta es un resonante "No!" Y debe dar un paso atrás, desafiar sus suposiciones, y ver cómo se puede simplificar su código

12 noviembre 2016

Oracle APEX, pequeña introducción

¿Que es APEX?

Oracle Application Express (ApEx) es una herramienta de desarrollo web que permite compartir datos y crear aplicaciones personalizadas de forma rápida. Con ayuda de un explorador web y sin necesidad de contar con conocimientos avanzados de programación, se podrán desarrollar y desplegar potentes aplicaciones, rápidas y seguras.
El desarrollo de aplicaciones con APEXestá basado en el explorador y permite desarrollar aplicaciones desde cualquier PC conectado en red,utilizando para ello tan sólo un explorador web actual. La tecnología APEX se basa y usa como núcleo SQL y PL / SQL. APEX es una herramienta declarativa que proporciona gran cantidad de características diseñadas para hacer el trabajo de desarrollo más fácil. El propio APEX se encarga de muchas de las funciones subyacentes comunes a todas las aplicaciones basadas en web; esto permite al programador centrarse en la lógica específica de la aplicación.
¿Cuáles son los requisitos?

  • Conocer sobre consultas SQL (básico) y programación con PL SQL (básico)
  • HTML, CSS (no excluyente)


Instalación de Oracle APEX 

Antes de instalar Oracle Application Express (Oracle APEX) es necesario:
  • Instalar base de datos Oracle (10g, 11g o 12c)

Estas son las versiones soportadas por Oracle APEX:
  • Linux x86
  • Linux x86-64
  • Oracle Solaris on SPARC (64 bit)
  • Oracle Solaris x86-64 (64 bit)
  • HP-UX Itanium
  • Microsoft Windows (32-bit)
  • Microsoft Windows x64 (64-bit)
  • IBM AIX on POWER Systems (64-bit)
  • IBM: Linux on System z
  • HP-UX PA-RISC (64-bit)
Descomprima el archivo zip que ha descargado en alguna posición de la unidad de disco duro. Aquí lo descomprimo en C: / DevProgram

A continuación, introduzca la sentencia sqlplus para iniciar sesión en sqlplus (Nota: no abra sqlplus directamente, sino a través de CMD después de posicionarse en el APEX home)




--
-- Run script apexins.sql with parameters:
--
-- @apexins.sql tablespace_apex tablespace_files tablespace_temp images
--
-- Where:
-- tablespace_apex is the name of the tablespace for the Oracle Application Express application user.
-- tablespace_files is the name of the tablespace for the Oracle Application Express files user.
-- tablespace_temp is the name of the temporary tablespace or tablespace group.
-- images is the virtual directory for Oracle Application Express images.
-- (To support future Oracle Application Express upgrades, define the virtual image directory as /i/.)
--
 
@apexins.sql sysaux sysaux temp /i/
El script anterior crea un SCHEMA en la base de datos, puede probar consultando Ver ALL_USERS en SQLPlus




--
-- Running script apex_epg_config.sql with parameter:
-- @apex_epg_config.sql parent of apex directory
--
 
@apex_epg_config.sql C:\DevPrograms

Habilitar usuario anonymous

--
-- Unlock user anonymous:
 alter user anonymous account unlock;

apexconf.sql 

 Ejecutar script apexconf.sql Se utiliza para realizar los pasos finales de configuración de Oracle Application Express, incluida la configuración del puerto de escucha HTTP XDB ​​y la contraseña ADMIN de Application Express.


--
-- Running script: @apxconf.sql
-- Setup password for ADMIN
-- Configuring XDB Http Listener port
--
 
@apxconf.sql

El usuario, puede usar ADMIN de forma predeterminada y el correo electrónico puede omitirse. Para la contraseña, el sistema requiere que ingrese una contraseña segura: 

  • La contraseña no se ajusta a las reglas de complejidad de contraseñas de este sitio. 
  • La contraseña debe contener al menos 6 caracteres.
  • La contraseña debe contener al menos un carácter alfabético (abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ). 
  • La contraseña debe contener al menos un carácter de puntuación (! "# $% & ()` '* +, - /:;? _).
  • La contraseña debe contener al menos un carácter alfabético en mayúsculas. 
  • La contraseña debe contener al menos un carácter alfabético en minúsculas.


El puerto predeterminado es 8080

Vamos a un navegador y tecleamos



Introducimos clave y usuario.